By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,073 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

Change a subforms record source with a combo box

P: 29
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
Dec 28 '07 #1
Share this Question
Share on Google+
9 Replies


ADezii
Expert 5K+
P: 8,607
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:
Expand|Select|Wrap|Line Numbers
  1. Me![<SubFormControl>].Form.RecordSource = "Select * From <RecordSource_of_SubForm> Where & _
  2.                            [<Unique_Field_in_Child_Form>] = " & Me![<Bound_Combo_Column>] & ";"
P.S. The Link Child and Master Fields cannot contain values.
Dec 28 '07 #2

P: 4
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
Dec 29 '07 #3

ADezii
Expert 5K+
P: 8,607
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?
Dec 29 '07 #4

P: 29
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
Dec 31 '07 #5

P: 4
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.
Nov 10 '09 #6

P: 4
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!
Nov 10 '09 #7

Expert 100+
P: 1,287
You might try using the AfterUpdate event of your combo box to make the appropriate subform .Visible = True, and hide the rest.
Nov 10 '09 #8

P: 4
I've tried that, ChipR, but it doesn't work as well as I'd like.
Nov 10 '09 #9

P: 4
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!!!!
Nov 10 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.