Linking Combo Boxes


G

Guest

I have read the previous posts that pertain to linking combo boxes, but I am
still confused. It seems as if every one does things very differently.
I have tow combo boxes that I want to link.
The first combo box is cmbsupervisor. In this combo box I am storing
supervisor ID numbers.
The second combo box is cmbemployee. In this combo box I aqm storing
employee ID numbers.
After selecting the supervisor I need the second combo box to only pull
those employees that belong to the supervisor.

Here are my table formats:
tblEmployee
Employee ID (autonumber)
Employee LName (text)
Employee FName (text)
Supervisor ID (linked to Supervisor ID in tblSupervisor)

tblSupervisor
Supervisor ID (autonumber)
SupervisorLName (text)
Supervisor FName (text)

The closest answer that I could find was the following...however I can't
figure out how to insert my information...
"Leave the rowsource of the 2nd combo box blank.
Code the AfterUpdate event of the 1st combo box to fill the rowsource
of the 2nd.
Something like this:
Combo2.Rowsource = "Select Instructors.InstructorID,
Instructors.InstructorName from Instructors Where Instructors.ClassID
= " & Me!ComboName & ";"

Change the table and field names as needed.
The above assumes ClassID is a Number datatype and the combo box bound
column is Number also.
 
Ad

Advertisements

D

Douglas J. Steele

Assuming that your first combobox is set up such that the Supervisor ID is
the bound column, you need something like:

Combo2.Rowsource = "SELECT [Employee ID], " & _
"[Employee LName], [Employee FName] " & _
"FROM tblEmployee " & _
"WHERE [Supervisor ID] = " & Me!Combo1
 
G

Guest

In the RowSource of the Employee Combo, you can create a link to the
Supervisor Combo

Select [Employee ID], [Employee LName] From tblEmployee Where [Supervisor
ID] = Forms![FormName]![SupervisorComboName]

On the after update event of the supervisor combo, write the code
Me.[Employee Combo Name].Requery
==================================================
Or From the example you gave, in the after update event of the Supervisor
combo, you can write the code

Me.[Employee combo name].RowSource = "Select [Employee ID], [Employee LName]
From tblEmployee Where [Supervisor ID] = " & Me.[SupervisorComboName]
====================================================
 
Ad

Advertisements

J

Joan Wild

Select the cmbsupervisor control and open the properties sheet. Click on
the Events tab and then in After Update property choose Event Procedure.
There you would put

cmbemployee.Rowsource = "Select [Employee ID], [Employee LName] & ", " &
[Employee FName] AS EmployeeName from tblEmployee Where [Supervisor ID] = "
& Me!cmbsupervisor & ";"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top