Changing Combo Box Values Based on Previous Input

[randommetalguy]

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?

 

[HiTechCoach]

What you are needing is called cascading combo boxes

Here is an example:
Cascading Combo Boxes Using 2 Tables
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.

 

[randommetalguy]

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?

 

[HiTechCoach]

Glad that helped.


To select the first item in a combo box try:

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

 

[randommetalguy]

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?

 

[jal]

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