Combo box field not displaying some values

[Anna S]

I have a combo box listing staff names. The list is based
on a query from a Staff table and the staff member
numerical ID is stored in the combo box. There is a y/n
field in the Staff table indicating whether the staff
member is current or not. For data entry I want to
restrict the combo box list to current Staff members but
when I add the condition that the staff member should be
current to the combo box dropdown query, the field will no
longer display the staff member name in records already
entered if the staff member is no longer current.
Does anyone know a solution to this?
Thank you in advance
Anna

 

[Allen Browne]

Hi Anna

Yes, we all face this. If you filter out the inactive records in a combo
where the bound column is zero-width, nothing shows up for the old records.

One workaround is to sort the inactive records to the bottom of the list,
rather than eliminate them. The RowSource will be something like this:
SELECT StaffID, Surname & ", " & FirstName AS FullName, Inactive
FROM tblStaff
ORDER BY Inactive DESC, Surname, FirstName;

If you think it necessary, you could also use the BeforeUpdate (or
AfterUpdate) event of the combo to question or cancel the selection of an
inactive staff member:

Private Sub StaffID_AfterUpdate()
Dim strMsg As String

With Me.StaffID
If IsNull(.Value) Or (.Value = .OldValue) Then
'do nothing
Else
If .Column(2) = True Then
strMsg = .Column(1) & " is inactive. Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2) = vbNo Then
.Undo
End If
End If
End If
End With
End Sub

 

[Guest]

Thanks for your time Allen
That really helps
Anna

-----Original Message-----
Hi Anna

Yes, we all face this. If you filter out the inactive records in a combo
where the bound column is zero-width, nothing shows up for the old records.

One workaround is to sort the inactive records to the bottom of the list,
rather than eliminate them. The RowSource will be something like this:
SELECT StaffID, Surname & ", " & FirstName AS FullName, Inactive
FROM tblStaff
ORDER BY Inactive DESC, Surname, FirstName;

If you think it necessary, you could also use the BeforeUpdate (or
AfterUpdate) event of the combo to question or cancel the selection of an
inactive staff member:

Private Sub StaffID_AfterUpdate()
Dim strMsg As String

With Me.StaffID
If IsNull(.Value) Or (.Value = .OldValue) Then
'do nothing
Else
If .Column(2) = True Then
strMsg = .Column(1) & " is inactive. Continue anyway?"
If MsgBox(strMsg,

vbYesNo+vbDefaultButton2) = vbNo Then