Cascading Combo Boxes to Control a Subform

[Cameron]

Hi,

For the database I am currently working on, my employer would like the
ability to use multiple combo boxes in order to filter the database.
For instance the structure of the company is based on regions, which
are managed by a number of coordinators, who oversee a large group of
associates. I would like to use a combo box so that people accessing
the database can choose a particular region and then have a second
combo box that only shows those coordinators located within that
region.

I have been able to get to this point, but am at a loss as to how to
get the resulting selection of a coordinator to limit a continuous
subform.

Any help you could provide would be appreciated.

Cameron

[Rich P]

In the AfterUpdate event of the Region combobox (which would be located
on your main form for this example) you can write this to filter your
subform:

Private Sub cboRegion_AfterUpdate()
Me.subform.Form.RecordSource = "Select * From tblx Where " _
Region = '"& cboRegion & "'"
Me.Requery
End Sub

This example assumes that there is no connection/relationship between
the main form and your subform. And, of course, you replace
Me.subform.Form... with the name of your actual subform --
Me.frmRegion.Form...

and replace tblx with the name of your table for the subform.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Cameron]

Rich P <rp*****@aol.com> wrote in message news:<40***********************@news.frii.net>...

In the AfterUpdate event of the Region combobox (which would be located
on your main form for this example) you can write this to filter your
subform:

Private Sub cboRegion_AfterUpdate()
Me.subform.Form.RecordSource = "Select * From tblx Where " _
Region = '"& cboRegion & "'"
Me.Requery
End Sub

This example assumes that there is no connection/relationship between
the main form and your subform. And, of course, you replace
Me.subform.Form... with the name of your actual subform --
Me.frmRegion.Form...

and replace tblx with the name of your table for the subform.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Rich, thanks for your response. Unfortunately I still have a
couple of questions, particularly because I am not experienced with
VBA, so please bear with me. Embarrassingly I am getting a compile
error which is stating Expected: End of Statement and is highlighting
regions. The code I currently have is:

Private Sub Combo0_AfterUpdate()
Me.Combo2.Requery
Me.Associate_Cascade.Form.RecordSource = "Select * From Regions Where
" _
Regions = '"& Combo0 & "'"
Me.Requery
End Sub

This brings me to my other questions. The first one is I currently
have Me.Combo2.Requery in the procedure as well, which is how I am
updating the second or coordinators combo box. Will this affect your
code in anyway? My other question is, since the filtering of the
subform is based on both regions and coordinators should the code to
filter not be associated with the Coordinators or second combo box?
If not could you explain why?

Thanks again,
Cameron