Combo Box in a subform

[Ed Dolikian]

I am stumped...

I've got a combo box in a subform (Say Employee). The
combo box does not display the key field (e.g.
Employee_ID) but displays the Employee Name. I want to
filter the employee list based on another field in the
subform (e.g. Department), thus the rowsource would
be "Select * from [Employees] where [dept] = '" forms!
[mainform]![subform].form![Dept] & "';"

It seems that whenever I move focus to a different record,
only the Employee names for the "current" department show
up in the other records. I'd like to show the Employee
Name in all records but only the Employee in the correct
Dept if I open the combo box.

If I remove the filter from the rowsouce, names appear but
my list includes all employees.

Any ideas?

 

[John Vinson]

It seems that whenever I move focus to a different record,
only the Employee names for the "current" department show
up in the other records. I'd like to show the Employee
Name in all records but only the Employee in the correct
Dept if I open the combo box.

This is annoyingly tricky. Changing the Rowsource query of a combo
changes it for all repetitions of the combo on the continuous form;
one getaround is to put a Textbox on the form, carefully superimposing
it over the text area of the combo box. Base the Subform on a Query
joining its table to the Employee table by EmployeeID and use the
EmployeeName as the control source of the textbox.

Set the textbox's Enabled property to No, Locked to Yes, and Tab Stop
to No so the user can't affect it; selecting the combo box will make
it drop down normally and hide the textbox.