filling combo box

[Guest]

I've got 3 tables, a main table which stores the values entered into the
form, and 2 smaller tables [employees and functions], which provide a range
values for input.

Employee has the following fields ID[auto], name[text], active[Y/N] and
Department_Code[Number]
Functions has ID[auto], Functionname[text], Department_Code[Number]
Each employee only has one Department_Code, but a Department_Code might be
assigned to more than one Functionname

A first combo box queries the employees table and fills it based on whether
or not an employee is active.
I want to fill a second combo box with only the functions from a specific
department code, of which the person selected in the first combo box is a
member.

I'm guessing that the first combo box should have an On Change event that
updates the 2nd combo box, but how do I query the right values and get them
put into that 2nd combo box?

 

[Guest]

Hi Alex

Yes you would need to use the 'Change' event of the employees combo box. I
would suggest some code on the lines of;

Dim strSQL as String

strSQL = "SELECT ID, Functionname"
strSQL = strSQL & " FROM Functions"
strSQL = strSQL & " WHERE Department_Code=" & Me!cboEmployee.Column(1) & ";"

Me!cboFunctions.RowSource = strSQL

This assumes of course that the department id is in the second column of the
employee combo box. If department isn't available then a slightly more
complex piece of SQL would be required joining Employee and Function on
department id, with the where clause selecting on employee id.

Hope this helps.