Change a subforms record source with a combo box

[frys]

Access 2003
Windows XP

I have a form that is for tracking attendance at programs.
the main form is the program information, and the sub form is the attendance information.

I have 5 homes and need to switch between each home at any given time. Currently i use a tab form with 5 subforms attached filtering the attendance data for each home.

What i wish to do is select a home from a combo/list box and have 1 subform show me the data for that home. I really hate having 5 subforms that essentially do the exact same thing

If any one can help that would be great

[ADezii]

Access 2003
Windows XP

I have a form that is for tracking attendance at programs.
the main form is the program information, and the sub form is the attendance information.

I have 5 homes and need to switch between each home at any given time. Currently i use a tab form with 5 subforms attached filtering the attendance data for each home.

What i wish to do is select a home from a combo/list box and have 1 subform show me the data for that home. I really hate having 5 subforms that essentially do the exact same thing

If any one can help that would be great

The general syntax for changing the Record Source of a Sub-Form from a Main Form would be:

Me![<SubFormControl>].Form.RecordSource = "Select * From <RecordSource_of_SubForm> Where & _
                           [<Unique_Field_in_Child_Form>] = " & Me![<Bound_Combo_Column>] & ";"

P.S. The Link Child and Master Fields cannot contain values.

[RandomElle]

Hi Fry

I would use above soln if all forms are exactly identical in terms of fields that are being displayed and updated and layout is the same. Just thought I'd throw in another alternative, and one that works great, especially if you do have uniquely different subforms depending on house...

1. Make subform names conform to a naming convention
You can either store the full name of the subform in the Combobox for the home, or one thing I've done is controled the naming of my subforms to match a code/key field identifying the home. For example

attendance data for Home 1 (A Unique identifier = H1) --> fsubAttendH1
attendance data for Home 2 (A Unique identifier = H2) --> fsubAttendH2
... etc

2, Create Main form (ie frmMain) with a subform control that has been assigned a generic Name property (ie fsubChild)

3. Add your Home selection combo to the Main (frmMain) form

4. Use the Home selection combobox's After Update event to set the subforms's SourceObject property. For example, let's say the unique code for the home (ie H1, H2, H3) is in Column(0). You could use something like

/untested code

Private Sub cboPickHouse_AfterUpdate()

Dim strSubFrmName As String

strSubFrmName = "fsubAttend" & Me!cboPickHouse.Column(0)

Me!fsubChild.SourceObject = strSubFrmName

'Following not manditory, but I like to set the focus in the subform
Me!fsubChild.SetFocus

End Sub

5. Remember that the subform is loaded before the main form, so you may want a default there.

Hope this helps,
RandomElle

Access 2003
Windows XP

I have a form that is for tracking attendance at programs.
the main form is the program information, and the sub form is the attendance information.

I have 5 homes and need to switch between each home at any given time. Currently i use a tab form with 5 subforms attached filtering the attendance data for each home.

What i wish to do is select a home from a combo/list box and have 1 subform show me the data for that home. I really hate having 5 subforms that essentially do the exact same thing

If any one can help that would be great

[ADezii]

Hi Fry

I would use above soln if all forms are exactly identical in terms of fields that are being displayed and updated and layout is the same. Just thought I'd throw in another alternative, and one that works great, especially if you do have uniquely different subforms depending on house...

1. Make subform names conform to a naming convention
You can either store the full name of the subform in the Combobox for the home, or one thing I've done is controled the naming of my subforms to match a code/key field identifying the home. For example

attendance data for Home 1 (A Unique identifier = H1) --> fsubAttendH1
attendance data for Home 2 (A Unique identifier = H2) --> fsubAttendH2
... etc

2, Create Main form (ie frmMain) with a subform control that has been assigned a generic Name property (ie fsubChild)

3. Add your Home selection combo to the Main (frmMain) form

4. Use the Home selection combobox's After Update event to set the subforms's SourceObject property. For example, let's say the unique code for the home (ie H1, H2, H3) is in Column(0). You could use something like

/untested code

Private Sub cboPickHouse_AfterUpdate()

Dim strSubFrmName As String

strSubFrmName = "fsubAttend" & Me!cboPickHouse.Column(0)

Me!fsubChild.SourceObject = strSubFrmName

'Following not manditory, but I like to set the focus in the subform
Me!fsubChild.SetFocus

End Sub

5. Remember that the subform is loaded before the main form, so you may want a default there.

Hope this helps,
RandomElle

Nice approach, RandomElle. Just for curiosity, I'm assuming that the Record Sources for each unique Sub-Form are predefined, is this correct?

[frys]

Thanks both of you. i have not had enough spare time to try your suggestions but i will post as soon as i have. t

[ARH64]

I have been searching for how to do something like this for a few days and stumbled upon this link.

Unfortunately, I'm confused. For one thing, how does one determine the Source Object of a main form's subform?

Also, do I make multiple subforms and the main form calls this subform based on my combo?

HELP!!!! :-)

Thanks.

[ARH64]

Here is what I am trying to do:

I have a main form: frmAssets

This main form as a combo to selet an asset type: cboAssetType

I have multiple subforms (five) which are called (I'll limit it to two):

tsfrmSoftwareDetails
tsrrmComputerDetails

If I choose Asset Type COMPUTER, I would like the appropriate tabbed subform to display. It I choose Asset Type SOFTWARE, I would like the software subform to display.

Can you help with this please?

Thanks!

[ChipR]

You might try using the AfterUpdate event of your combo box to make the appropriate subform .Visible = True, and hide the rest.

[ARH64]

I've tried that, ChipR, but it doesn't work as well as I'd like.

[ARH64]

I've got it working, RandomElle! Thanks so much! You're a genius!!!!!!

To your directions, I would add that the code must be added to the underlying table the cbo calls. For example, I added CD to the COMPUTER field of the Asset Type table.

Next, all subforms need to be renamed to the generic subform name noted with the code added. I named my subform a generic sfrmDetails and renamed all my subforms to accomodate this, for example sfrmDetailsCD.

The subform added to the main form must be unbound, named with the generic name, and the Source Object left blank.

Works PERFECTLY!!! YAY!!!!