Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2007
    Posts
    42

    Unanswered: Reusing Sub-forms

    Is there anyone out there who has reused the same sub-form within multiple tabs on a form? I want to filter the data differently for each tab, but can't find an event which will allow me to set the data or filter it when the user clicks on the tab. Any help would be greatly appreciated! Terri

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Sure....simply place your code into the OnChange event of the Tab Control but first....in case you didn't know....

    All the Tabs within your Tab Control are Indexed which means then that each Tab is referenced by a number like a page number and therefore there is a control property convieniently named PageIndex which holds this number.

    When you place a typical MS-Access Tab Control onto your Form, you start with two Tabs (pages). If you click on a Tab page then look in the properties window, you will notice that the first Tab holds the PageIndex property value of 0 and of course the second Tab (page) will contain the PageIndex property of 1. As you add Tabs (pages) to the Tab Control, each additional Tab (page) will have its' PageIndex property incremented by one. Therefore, a third Tab (page) will contain the PageIndex property of 2 and a fourth Tab will contain the PageIndex property of 3 and so on....This is important to know if you plan to deal specificly with the individual Tab pages as we are actually going to do.

    Like I mentioned at the beginning...we need to access the OnChange event of the actual Tab Control (not the Tab Page - Tab Pages don't have this event anyway). To do this simply click anywhere on the right side of the Tab Control (but not on a Tab) then in the Event section of the Properties Window locate the OnChange event. It should be the first event in the list. Select Event Procedure for this property then select the small button located on the far right side of this property (has three dots on it). This will open the Visual Basic for Applications IDE window.

    Place the following code into the event procedure:

    Code:
    Select Case Me.myTabControlName.Value
       Case 0    'Tab 1
          Me.mySubFormControlName.SourceObject = "Table name or Query here"
          Me.mySubFormControlName.LinkChildFields = "The Field name in Table above to link with field from table on Main Form OR just "" for nothing"
          Me.mySubFormControlName.LinkMasterFields = "The Field name in Table for Main Form to link with the field name indicated above OR just "" for nothing"
          Me.mySubFormControlName.Caption = "Use if you want to change the SubForm's Caption"
    
       Case 1    'Tab 2
          Me.mySubFormControlName.SourceObject = "Other Table name or Query here"
          Me.mySubFormControlName.LinkChildFields = "The Field name in Table above to link with field from table on Main Form OR just "" for nothing"
          Me.mySubFormControlName.LinkMasterFields = "The Field name in Table for Main Form to link with the field name indicated above OR just "" for nothing"
          Me.mySubFormControlName.Caption = "Use if you want to change the SubForm's Caption"
     End Select
    Always remember to change both the LinkChildFields and LinkMasterFields properties whenever changing the SourceObject propery of your SubForm Control otherwise you will run into problems.

    For your filtering....use the SourceObject property.


    .
    Last edited by CyberLynx; 09-16-07 at 20:36.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Apr 2007
    Posts
    42

    Unhappy Almost there ....

    Your code all makes sense, and I was close but not quite there.

    I've tried what you've said. I'm confused with the comment about "For your filtering .... use the SourceObject property". It doesn't seem to be letting me put a SQL statement for the SourceObject property - "The form name entered doesn't follow MS Office Access object naming rules" - it wants a form name.

    My SQL statement involves 2 tables, so I tried just using a table to see if I could get it to work. It tells me that the Jet database could not find the object.

    It only works when I put the sub-form name for the SourceObject property - but then I can't filter.

    I'm so close - What am I not understanding?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why have a duplicate of the sub form within the same master / parent form? I don't know for sure how Access allocates its storage but Im guessing that you are storing up performance problems for your self. Performance.. becuase effectively your are duplicating data, you are forcing access to do mor ework each tome you change a record in the other sub forms. Im guessing you will be using up Access resources (memory, GDI capacity needlessly)

    as a suggestion consider having (say) as radio button group as part of the sub form which allows a user to select the order or whatever.

    for each press of the radio button set the sub forms order / settings to whatever you require.

    things to look at are the sort order, the record source, the sub forms underlying SQL....
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2007
    Posts
    42

    Reusing Sub-Forms

    The reason I am duplicating the same sub-form multiple times within the same form is that my users have specifically asked for the contact information to be grouped by department - with a separate tab for each department. As it works out, I am actually using a department grouping because a few of the departments have to be grouped under a single tab.

    I understand what you are saying about different design options. I thought the beauty of OO design is that you can re-instantiate a class multiple times (which is what I thought I was doing by re-using a sub-form). I'm definitely NOT a OO guru, but thought that reusing sub-forms would be a better design - especially compared to having multiple forms which all look alike -or- having one tab where the users had to select a radio button to view just the department they are interested in.

    I have the same sort order for each tab (last name, first name). I have the same source tables and sub-form. I basically just want to filter to display all contacts within a department or group of departments within each tab.

    Any thoughts would be greatly appreciated.

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    What I would suggest is putting your subform on top of the tab control not on each page. If you add it to your form's detail and then put it on top of the tab control, visually it has the same effect as putting it on each page. Then as the user changes pages, modify the RecordSource of the subform.

    I did it using a Select statement in the OnChange event of the tab control and I changed the SQL depending on the page the user selected.

    I have created a form with many subforms in a tab control, and I can tell you that there is a good chance that over time, the form will take longer and longer to open.

  7. #7
    Join Date
    Apr 2007
    Posts
    42

    Cool Thanks so much !!

    Thanks, that's a good idea. I'll give it a try. I have two additional tabs which use other subforms, so I'll have to hide the reused sub-form when the user click on those other tabs. I see what you are saying about the performance.

    Thanks for your help - all of you!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •