Limit combo box list, but not record display

[Guest]

I maintain a training records database. The main form is a description of
the training session, and the subform is a listing of attendees. There are
three tables of interest here: tblTrainingSession, tblEmployees (from which
the names of attendees are drawn), and a junction table which contains the
names of attendees. The junction table (tblEnrollment) has foreign keys that
relate to the primary keys from the other two tables. The main form is based
on tblTrainingSession, and the subform on tblEnrollment.
Names in the subform are selected from a combo box which has as its record
source a query based on tblEmployees. Sometimes employees leave. I would
like for their names not to appear in the combo box, but still appear in the
records of past training sessions. I store the EmployeeID (the PK field),
not the actual name in tblEnrollment. The trouble is that any way of
limiting the combo box list also limits the names that appear on the form
(although the records still appear to be in the table).
Any ideas on how to solve this? I would be OK with the names of former
employees on the list through the end of the current year, if that helps to
devise a solution.

 

[Rick Brandt]

I maintain a training records database. The main form is a description of
the training session, and the subform is a listing of attendees. There are
three tables of interest here: tblTrainingSession, tblEmployees (from which
the names of attendees are drawn), and a junction table which contains the
names of attendees. The junction table (tblEnrollment) has foreign keys that
relate to the primary keys from the other two tables. The main form is based
on tblTrainingSession, and the subform on tblEnrollment.
Names in the subform are selected from a combo box which has as its record
source a query based on tblEmployees. Sometimes employees leave. I would
like for their names not to appear in the combo box, but still appear in the
records of past training sessions. I store the EmployeeID (the PK field),
not the actual name in tblEnrollment. The trouble is that any way of
limiting the combo box list also limits the names that appear on the form
(although the records still appear to be in the table).
Any ideas on how to solve this? I would be OK with the names of former
employees on the list through the end of the current year, if that helps to
devise a solution.

You need to keep former employees in the source table and just have a
Yes/No field that indicates "Active" employees. Then the ComboBox
RowSource query would be set up along the lines of...

SELECT * FROM tblEmployees
WHERE Active = True
OR EmployeeID = Forms!NameOfForm!NameOfComboBox

That should cause the ComboBox to always include the name of the existing
entry regardless of whether they are active or not, but when making new
entries only active employees will be listed.

 

[Guest]

Thanks for taking the time to reply, but I have to tell you it didn't work.
The SQL statement I ended up with was:

SELECT tblEmployees.EmployeeID, [Last] & ", " & [First] AS [Full Name],
tblEmployees.Inactive
FROM tblEmployees
WHERE
(((tblEmployees.EmployeeID)=[Forms]![frmTrainingSession]![fsubEnrollment].[Form]![cboEmployeeID])) OR (((tblEmployees.Inactive)=No))
ORDER BY [Last] & ", " & [First];

I already had the Inactive (Yes/No) field in the table, so I reversed your
directions for an Active field. Also, I experimented with different means
for referencing the combo box. It seems to me the most likely way is to
reference the subform control as I have, but regardless of how I referenced
the control, the result is the same as if I had set Inactive = No as my only
criteria, which is to say Inactive employees don't appear anywhere.
I also experimented with placing directly over the combo box a text box
bound to Full Name. This almost works, except that the first record in the
subform, which automatically gets the focus when moving to a new record in
the main form, does not show the text box, which is a problem when the first
person in alphabetical order (which is how the subform is arranged) is an
inactive employee. In that case, the combo box takes the focus, and the name
does not appear since it is not on the combo box list. From past experience
I am pretty sure there is no way of having anything on top of a combo box
that has the focus. Efforts to change this by code, or to have the record
selector point to a new record and not the first record, have so far proved
futile. In any case it seems like an unwieldy approach.
If you can find the flaw in the supplied code I would much prefer that
approach, but I am OK with anything that works. Thanks again for your help.