Hey guys,

I have a series of 3 combo boxes and I want the lists to generate based on previous input.

The best example of this I can think of is with sports teams.

Let's say I have 3 lists
1. League
2. Division
3. Team

For baseball there are 30 teams. So without picking a league or division all 30 would be available in the in team list. If the user picks either National League I want the team list to shrink down to just the 16 teams in the National League. Then if the user selects East from the Division List I want the teams list to only have the teams from the National League East Division.

What's the most efficient way to implement something like this using Access and VB?

What you are needing is called cascading combo boxes

Here is an example:
Cascading Combo Boxes Using 2 Tables (http://www.candace-tripp.com/download/2table_cascadecombo2k.zip)
Demonstrates how to make combo boxes whose values are filtered by the value of other combo boxes (whose record source are two tables) in a cascade.

Excellent it was just what I needed.

Just out of curiosity if my final resulting combo box only has one value in it I want it to automatically be selected. I could just make it a text box but I think there may be a few cases where multiple values could get generated. How would I go about doing this?

Glad that helped.


To select the first item in a combo box try:


Me.ComboBoxName = Me.ComboBoxName.ItemData(0)

I can do SQL code for combo boxes but it doesn't work for text boxes. I am trying to activate the control source during an AfterUpdate event.

Dim sqlSTR As String

sqlSTR = "SELECT table.returnValueFROM table "
sqlSTR = sqlSTR & " WHERE table.lookupValue = cboLookUpValue;"

Me.txtReturnValue.ControlSource = sqlSTR

txtReturnValue has a #NAME error after I select a value from cboLookUpValue

Any ideas?

I can do SQL code for combo boxes but it doesn't work for text boxes. I am trying to activate the control source during an AfterUpdate event.

Dim sqlSTR As String

sqlSTR = "SELECT table.returnValueFROM table "
sqlSTR = sqlSTR & " WHERE table.lookupValue = cboLookUpValue;"

Me.txtReturnValue.ControlSource = sqlSTR

txtReturnValue has a #NAME error after I select a value from cboLookUpValue

Any ideas? Apparetnly that isn't your exact SQL. Maybe paste it here so we can take a look at it. Also, for strings remember to use quotes:


Dim quote as string
quote = "'"
sqlSTR = "SELECT table.returnValueFROM table "
sqlSTR = sqlSTR & " WHERE table.lookupValue = " & quote &
cboLookUpValue & quote