Cascading Lookups in a Table

[mab9]

Probably an easy one that I just can't think my through it. I've been trying to create some (for lack of a better term) cascading fields using the lookup wizard (to eventually be used in a datasheet view/form).

In the main table, the user needs to select a Team (A, B, C), Sub-Team (A1, A2, A3, B1, B2, etc), and a Family (A1 contains bolts, screws, and washers).

The first lookup of selecting a Team (A, B or C) was easy. However after this point I'm stuck. If the user chooses A, I only want the "A" related sub-teams to show in the next lookup. Then based on the sub-team chosen, I want the Family list restricted again.

Any ideas would be appreciated. Thanks!

 

[neileg]

I agree with Pat. Do a search for cascading combo.

 

[mab9]

I found some examples on this & tried setting it up. The 2nd box (that feeds off the 1st) was no problem. However, I can't get the 3rd box (that feeds off the 2nd) to work. Ideas on what I'm doing wrong?

 

[neileg]

Tables:
Remove the lookup from Team_Code from the table Team_Level
Remove the field Team_Code from the table PM_Family

Queries:
Remove the field Team_Code from the query select_fam
Change the criterion in select_fam to [Forms]![Mgr_Form]![cboPM_Code]

Form:
Rename the combo boxes as they all have the same name as the underlying field, eg Team_Code becomes cboTeam_Code
Change the data source for cboPM_Code to the query select_team
In the After update event of cboTeamCode add the code:
Me.cboPM_Code.Requery
In the After update event of cboPM_Code add the code:
Me.cboPM_Family.Requery

 

[mab9]

Just got it working, thanks for the help!