Problem with limiting content from one Combo box to another.

[Bobbak]

Hello All,
I am having trouble with this forum I am developing in Access; I have
two combo boxes, one containing the Director info, and the other
containing the Manager info. Now what I want to happen is when
someone selects a specific Director when they go to choose the Manager
only the managers assigned to that Director to appear. In the
director combo box (which is called "ComboDirector") contains three
fields in it the "DirectorID" (column 0), "DirectorLastName" (column
1), and "DirectorFirstName" (column 2). and it's connected to the
table called "tblDirector". The manager combo ("ComboManager")
contains four fields, "ManagerID" (column 0), "ManagerFirstName"
(column 1), "ManagerFirstName" (column 2), and "DirectorID" (column
3), and it's connected to the table called "tblManager".
Now I tried to set in the RowSource query for the ManagerCombo so
that the DirectorID field equals the DirectorID field in the
DirectorCombo box, here is the example of the code:

SELECT tblManager. ManagerID, tblManager. ManagerLastName, tblManager.
ManagerFirstName
FROM tblManager INNER JOIN List_Director ON tblManager.DIRECTORADP =
List_Director. DirectorID
WHERE (((tblManager. DirectorID) Like [Forms]![frmEmployee]!
ComboDirector.Column(0)))

This doesn't work always giving me an error and leaving the contents
of the ComboManager blank.

If anyone can help me out with this problem I'd very much appreciate
it.

 

[Andrew Smith]

The row source for the manager combo box should be:

SELECT ManagerID,ManagerLastName,ManagerFirstName
FROM tblManager
WHERE DirectorID = Forms!frmEmployee.ComboDirector

(You could create a saved query like this or enter the SQL directly into the
RowSource).

I'm assuming that the bound field of ComboDirector is DirectorID.

You will need some code to refresh ComboManager:

Me.ComboManager.Requery

This could go either in the AfterUpdate event of ComboDirector, or in the
OnEnter event of ComboManager - I'm not sure which is best or if it matters.

Alternatively you could generate the SQL for ComboManager in the AfterUpdate
event of ComboDirector, something like this:

Dim strSQL as string

strSQL = "SELECT ManagerID,ManagerLastName,ManagerFirstName "
strSQL = strSQL & "FROM tblManager "
strSQL = strSQL & "WHERE DirectorID = " & Me.ComboDirector

Me.ComboManager.RowSource = strSQL