drop down menu in form based on selection in previous box

  • Thread starter Thread starter Rinee
  • Start date Start date
R

Rinee

Hi,
I have two tables: EmployeeList which lists companies (with
ContractorID field) and their employees (with EmployeeID field), and
EmployeeDetails which associates details with employee IDs.
I want a simple form to input the employee details. The first field
is a drop down menu to select the company ID. Once that is selected,
I want the next box to give a drop down menu of only the employees
linked to that company.

Looking at old postings, the following update event for the first
combo box seems useful:

< Sub cbo1st_After_Update()

cbo2nd.RowSource="Select * from tblWhat where [KeyValue]='" & cbo1st &
"'"
cbo2nd.requery >

but, as I am a novice, I did not understand what [KeyValue] was meant
to be. I assumed cbo1st should be replaced with whatever I called the
first box...

Please be specific with code. Thanks very much for any help
 
Assuming you used CompanyID and ContractorID interchangably in your post:

If ContractorID is a text field:
Me.cbo2nd.RowSource="Select * from EmployeeList WHERE [ContractorID]= '" &
Me.cbo1st & "'"

If ContractorID is a number field:
Me.cbo2nd.RowSource="Select * from EmployeeList WHERE [ContractorID]= " &
Me.cbo1st

Yes, substitute the actual names of your 1st & 2nd comboboxes for cbo1st and
cbo2nd.
This should fill the 2nd combo with only those employees (in EmployeeList)
associated with that contractor (in EmployeeList). If I read your post
correctly, EmployeeDetails hasn't entered the picure yet (since ContractorID
isn't in that table, just EmployeeID?)

BTW, "Select *" will fill your list box with ALL fields from EmployeeList.
You probably don't want or need that. Consider changing that to "Select
[EmployeeID], [EmployeeFName], [EmployeeLName]..." and setting the
ColumnCount property of the Combobox to 3 (or some variation thereof).

In addition, if you want the items in your combo box to appear in any sort
of logical order, add an OrderBy clause at the end of your SQL statement:
Me.cbo2nd.RowSource="Select [EmployeeID], [EmployeeFName], [EmployeeLName]
from EmployeeList WHERE [ContractorID]= " & Me.cbo1st & " ORDER BY
[EmployeeLName], [EmployeeFName]"

HTH,
 
Back
Top