I have a subform that display requisition information. One of the
fields in the subform is a combo box that shows who requested the
requisition. The users can change who requested the requisition and I
would like to only have them be able to choose active employees. So,
I have written the query for the rowsource for this combo box, which
is based on an employees table, to look at all active employees and
the employee that is currently chosen in case that employee is
inactive. The query looks like this:
SELECT tblEmployee.EmpoyeeID, [LNAME] & IIf(IsNull([LNAME]),"",", ") &
[FNAME] AS FULLNAME
FROM tblEmployee
WHERE (((tblEmployee.STATUS)="A")) OR
(((tblEmployee.EmpoyeeID)=[FORMS]![frmrequisition]![FRMREQSUBFORM]![cboRequestedby]))
ORDER BY tblEmployee.LNAME, tblEmployee.FNAME;
Properties of the combo box:
Limit to list = Yes
Number of columns = 2
bound column = 1
column widths = 0;1
The problem comes when the subform shows several requisitions and some
of the employees who requested the requisitions are inactive. What
happens is if I am on the current record where the employee that
requested it is inactive the employee is displayed but on another
record that has a different employee that is inactive shows up blank
because it is not in the drop down list. It is fine if the list of
requisitions being displayed all has the same person that requested it
but is confusing to the users when they don't see a name in the
requested by field but then go and click on it and the name appears.
Is there any way to get around this?
Kelly