Limit combo box list only

[Guest]

Some while ago I thought this was a relatively simple problem. Now I know it
is not. I have a training records database. The training session
information is in a table (tblSession, with SessionID as the PK). Attendance
information is in another table (tblEnrollment) in which SessionID is the FK.
These are presented on screen by means of form/subform
(frmSession/fsubEnrollment).
Names in in tblEnrollment (fsubEnrollment) are selected from a combo box
(cboName), which has as its record source the query qryEmployee, drawn from
tblEmployee. The database works as expected, but there is something I would
like to change if it is possible. The names of former employees need to show
up on old training records, but not in cboName. I can add an Active (Y/N)
field to tblEmployee and use it to limit the results of qryEmployee. This
restricts the combo box list to active employees only, but it also prevents
the names of former employees from being displayed in cboName (although the
EmployeeID is still stored in the underlying table). I posted this question
a while age. the only response was to suggest as the combo box row source:
SELECT * FROM tblEmployees WHERE Active = True OR EmployeeID =
Forms!frmSession!cboName, but that didn't work.
I have attempted to place a text box on top of the text area of the combo
box. The text box contains the employee name from the query. It almost
works, except that autocomplete no longer works for the combo box. The
drop-down list works, but since the text box is on top of the combo box
autocomplete is also buried. I have tried various ways of getting that the
text-box-on-top-of-combo-box system to work, but the more I try the more I
conclude that it is the wrong approach.
Is there a way to solve this problem?

 

[Ken Snell [MVP]]

You want to display all employees but prevent the selection of an "old"
employee?

Assuming you can train your users to understand how the form will work, go
ahead and put all employees into the query that is the row source of the
combo box. Include in the visible columns in the dropdown list the 'status'
of the employee so that users can see the status.

In the BeforeUpdate event of the combo box, test if the selected employee
has the right status; if not, cancel the event and display a message to the
user that this employee cannot be selected.

Not the best of all worlds, but it's one way.

Another way is to not do any editing on your current form. Instead, do
editing via a popup dialog form that is opened by a command button that the
user specifically clicks to do editing. Then you can separate the list of
employees -- one for display (which of course would not need to be a combo
box any way), and one for editing.

 

[Guest]

Thanks for your reply. As it is people are not likely to select a past
employee. The problem is that the list will get cluttered with names that
shouldn't be there. Periodically archiving records of former employees is
probably the simplest solution. Reports can still show the names. A
separate data entry form, apart from the display, is an approach worth
considering, although retraining users gives me pause. I had hoped a simple
solution was eluding me. Thanks again.

 

[Ken Snell [MVP]]

Although not straightforward, and I haven't used it at all, I have read
posts from other MVPs where they use the textbox on top of a combo box
approach that you mentioned in your first post.

This would be the only other way to do what you want, but it involves some
programming and such to work correctly.

Here are some previous posts (Google search) about this (and there are many
others) - watch for line-wrapping, as each URL is meant to be "one line"):


http://groups-beta.google.com/group..._doneTitle=Back+to+Search&&d#b1f499a68166afe9


http://groups-beta.google.com/group..._doneTitle=Back+to+Search&&d#1fbfad4e900baf8d


http://groups-beta.google.com/group..._doneTitle=Back+to+Search&&d#b2e4994605ec439f


--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

Thanks again for your reply, and for the links. I had attempted the text box
trick, but kept getting run-time error 2110 when I tried to set the focus to
the text box containing the name. The way it works is that attendance at a
training session is stored in tblEnrollment (which is a junction table
between tblSession and tblEmployees). A query (qryEnrollment) based on
tblEnrollment is the record source for a subform (fsubEnrollment), which is a
continuous form. The main form is based on training session information
contained in tblSession. The PK from tblSession is a FK in tblEnrollment.
The combo box (cboEmployee) row source is a select query derived from the
Employees table, limited to active employees. One of the fields in
qryEnrollment is a calculated field [Name], which is a concatenation of
FirstName and LastName.
So fsubEnrollment contains cboEmployee. EmployeeID (the PK from
tblEmployee) is the stored value. The visible value is a concatenated field.
The text box txtName sits on top of the combo box (cboEmployee). Its record
source is the field [Name], also concatenated, but from a different query
than the combo box uses. The problem, as I mentioned, is that any attempt to
set the focus to, make visible, or otherwise make txtName the visible control
leads eventually to error 2110. I say eventually because it sometimes seems
to work for a while until I click controls in the wrong order or something
like that. I wondered if Name is a reserved word, so I changed it, but to no
effect.
What I finally did was to place code in the subform's On Current event:

If Forms!frmSession!txtXofY = "New Record" Then
Me.cboEmployee.SetFocus
Else: Me.txtName.SetFocus
End If

txtXofY displays "5 of 25" or whatever unless it is a new record, in which
case that is displayed. For some reason I can set the focus to txtName in
this code. A new record shows the combo box (with AutoExpand enabled). Old
records show txtName, with former employees' names displayed as needed. If
somebody attempts to change what is in the text box they receive a message
box that they need to use the down arrow. Clicking the combo box's down
arrow sets the focus to the combo box, which is fine because it can't display
the names of former employees. It seems to be a stable solution, and does
what I need it to do with no extra training needed.