multiple cascading combo

[joemeshuggah]

having difficulty setting up a cascading combo...the idea is to have a combo
box with rep names. once the rep name is selected, an associate director
table is queried for the ad (ad name automatically populated in a list box),
and then a director table is queried to automatically pouplate the
appropriate director name in another list box.

i cant seem to get the director piece to work.

i have SELECT DIR_NM FROM qryDIR; as the rowsource for the director list
box, where qryDIR is a saved query:

SELECT DIR.DIR_NM
FROM DIR INNER JOIN AD ON DIR.DIR_ID=AD.DIR_ID
WHERE DIR.DIR_ID=FORMS!TEAM_ALL_DATA!ADName;

i based this query on the query for the AD Name, which works just fine:

SELECT AD.DIR_ID, AD.AD_NM
FROM AD INNER JOIN REP ON REP.AD_ID=AD.AD_ID
WHERE AD.AD_ID=FORMS!TEAM_ALL_DATA!RepName;

The rowsource for the repname combo box is SELECT ad_id, rep_nm FROM rep;

The tables are as follows: Rep (REP_NM, AD_ID), AD (AD_ID, AD_NM, DIR_ID),
and DIR (DIR_ID, DIR_NM).

i have afterupdate code for the rep combo box:

Private Sub RepName_AfterUpdate()
ADName = ""
DirName = ""
ADName.Requery
End Sub

and also afterupdate for the ad list box:

Private Sub ADName_AfterUpdate()
Option Compare Database

DirName = ""
DirName.Requery
End Sub

 

[Piet Linden]

http://www.mvps.org/access/forms/frm0028.htm

 

[Beetle]

From your post it would appear that your table heirarchy
is as follows;

DIR

AD (child of DIR. One record in DIR can have many related
records in AD)

Rep (child of AD. One record in AD can have many related
records in Rep)

However, the way you describe your combo boxes, it would
appear that you are attempting to go from the bottom up
(select a Rep, then the AD, then the DIR).

Is that correct?

 

[joemeshuggah]

correct

From your post it would appear that your table heirarchy
is as follows;

DIR

AD (child of DIR. One record in DIR can have many related
records in AD)

Rep (child of AD. One record in AD can have many related
records in Rep)

However, the way you describe your combo boxes, it would
appear that you are attempting to go from the bottom up
(select a Rep, then the AD, then the DIR).

Is that correct?