By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,248 Members | 1,961 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,248 IT Pros & Developers. It's quick & easy.

Problem with limiting content from one Combo box to another.

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
"Bobbak" <bo****@ottawa.com> wrote in message
news:64**************************@posting.google.c om...
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.

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.