Any Microsoft Access experts around?

Factual Questions
1

I’m after some help with an Access database that I’m building. I haven’t used Access for many years and I was never an expert in the first place…

I have a table called Group. In that is a field called Group.

I have another table called Team. In that table is a field called *Group *which looks up the **Group **table. Then it has a text field called Team.

So the content of the **Team **table is kinda like this:
Group 1 | Team A
Group 1 | Team B
Group 1 | Team C
Group 2 | Team D
Group 2 | Team E, etc.

I’m building another table which is the main table of the database so far, and it contains a lot of fields. One of the fields in that table is Group, and it looks up group.group. The next field in that table is Team, and it looks up team.team.

I would like the drop-downs in the Team field to only display those teams that belong to the *Group *that has been selected. So using my example content above, if I select Group 1, then the only drop-downs available to me in the *Team *field are Teams A, B and C.

I am now looking at the table in design view, and I’m looking at the Lookup properties. In the row source field, it has:
SELECT [Group].[ID], [Group].[Group] FROM [Group] ORDER BY [Group];

I suspect there’s something I need to add here, to tell it to limit the list by whatever was selected in the field column. Is that right and can anyone help me?

2

Assuming that you have two combo boxes to select the group and the team: cmbGroup and cmbTeam.

Your select clause for cmbGroup should be:

Select distinct [ID] from [Group] Order By [ID]

Create the Click Event for cmbGroup:

Private Sub cmbGroup_Click()
Dim SQL As String
SQL = “Select Team.Team from [Team] Where Team.Group = '” & cmbGroup.Value & “’ order by Team.Team”
cmbGroup.RowSource = SQL
cmbGroup.Requery
End Sub

What this will do is load the cmbGroup object with all your group ids. As soon as the user clicks on a group, it will take that selected value and use it in a new query to load cmbTeam with only the teams matching the selected group.