combo box selection based on value from other combo box

[drStealth]

Everyone, I have not quite seen this in any posting....

Say I have a SQL table:

Team League
NY Yankees American
KC Royals American
LA Dodgers National
Atlanta Braves National

On an Access form, I have a combo box for the user to select the league, either American or National. This part is no problem, it works fine.

I then have a second combo box for the user to select a team. Here is the question- how do I get this box to display only the teams belonging to the league chosen in the first combo box.

Example, if the user selects American in the first box, then only NY and KC should be available to select in the second combo box.

I can get all the teams to appear but I really only want those to appear based on the selection from the first box.

Any thoughts would be appreciated. Thanks. drStealth

 

[JoJ]

Put something like this in the after update event of your league combo box:

Dim strSQL As String

strSQL = "SELECT DISTINCT tblTEAMS.Team" & vbCrLf
strSQL = strSQL & " FROM tblTEAMS" & vbCrLf
strSQL = strSQL & " WHERE (((tblTEAMS.League)=" & Chr(34) & Me!cboLEAGUE & Chr(34) & &quot);"

Me!cboTEAMS.RowSourceType = "Table/Query"
Me!cboTEAMS.RowSource = strSQL
Me!cboTEAMS.Requery

Good luck ...