Let's say we have a field called league and another field called teams. The league field has two values: National and American. The teams field has all the teams in both leagues. What I want to happen is that when I select American in the league combo box, the team combo box will only show me the American League teams.
Any idea of how you do this?

These are called cascading combo boxes, and this technique is also used for cascading list boxes. The first issue is, what is the underlying table structure?

Ideally, in your example, you would have two tables:

tblLeague
LeagueID = PK integer
LeagueName - text

tblTeam
TeamID - PK integer
TeamName - text
LeagueID - FK

The first combo box (cboLeague) control source would be a query:
SELECT LeagueID, LeagueName FROM tblLeague ORDER BY LeagueName

The On Change event of cboLeague would have this code:

Dim sSQL As String
Dim iLeague As Integer

iLeague = Me.cboLeague

sSQL = "SELECT TeamID, TeamName FROM tblTeam WHERE [LeagueID] = " & iLeague

Me.cboTeam.RowSource = sSQL
Me.cboTeam.Requery

Be sure to set up the cboTeam with the following:

Bound Column: 1
Column Count: 2
List Width: 0";2"

This will make sure that the meaningful data is displayed, but that the PK TeamID is bound for future operations.

Did that help?



mmcdonal

I figured out that PK means primary key. What does FK mean?

Foreign Key, that is, a column in the table that looks up to a PK in a Foreign table.

mmcdonal

OK. I tried it and it doesn't work. My understanding is that the FK is the many in the one to many relationship with LeagueID in tblLeague.

What I found confusing was the iLeague. Is this referring to tblLeague or League ID?

Not knowing Visual Basic, I'm not sure about what I am typing, but I checked it all and it is accurate, but I wasn't sure what this iLeaguewas.

iLeague is a variable name. I prefaced the name with "i" to indicate that it was an integer type. It referes to LeagueID. It is always better to take a value in a variable, and then put it into a string, like I did, rather than:

SELECT * FROM tblLeague WHERE [LeagueID] = " & Me.LeagueID

Access does not like that.

If your table structure is as I posted, this will work. If your table structure is different, this may not work. Remember I am using values from your example, so if you have different field names, etc, you need to substitute them where appropriate.


mmcdonal

What am I doing wrong???? Here is what I typed:

Private Sub cboleague_Change()
    Dim sSQL As String
    Dim ileague As Integer
    ileague = Me.cboleague
    sSQL "SELECT teamid, teamname FROM tblteam WHERE [leagueid] = " & ileague
    Me.cboteam.RowSource = sSQL
    Me.cboteam.Requery
End Sub

In my second combo box, it wouldn't let me put a second number in the list width property. Did you mean column width?

Does there need to be a control source for the second combo box?

I wish I could send you what I have done. Thank you for your patience. The variables are in lower case because that's the way I created them, just in case it is case sensitive.

When you create the combo boxes, there are some options to choose. Does what you choose affect the outcome? Again, thank you for your patience.

Column Widths is correct.
No Record source for the second combo.
The first combo box you can use the wizard for, the second one, do not use the wizard.
This line: sSQL "SELECT teamid, teamname FROM tblteam WHERE [leagueid] = " & ileague

Should be:
sSQL = "SELECT teamid, teamname FROM tblteam WHERE [leagueid] = " & ileague

Did that help?


mmcdonal

:( No! I have checked and rechecked. I've tried every combination. Unfortunately, this message board doesn't let me send you the little database. When I click on the combo box in the form, not the subform, it is blank. Same goes for the combo box on the subform - blank.

You have to click on my username, then send me a message, and when I reply, you can send the database (zipped).

mmcdonal