I can't prevent two or more people from having the same name
It happens very infrequently in our case. The secretaries might not
always notice and not ask for an additional middle name or such.
The records are safe-guarded however because each student has their own
unique identifying number.
I was just thinking of making a secondary lookup available for admin,
one based on name rather than student number, as name is much easier to
work with.
Having a color code is not really that important. I suppose I could
include the student nos. to the right of the names, in a combo box.
That way they could note the numbers should dups surface in their
search (and they notice them).
Ahh, that's different from what I thought you might be
doing. This one is a tougher task and I can see where a
color highlight might be useful.
It think displaying the ID number is a good idea, but
there's no guarantee your users will pay attention to it
without some kind of additional nudge.
First things first, you can identify the duplicate names by
changing the combo box's RowSource query to be more like:
SELECT S.sname, S.snumber,
(SELECT Count(*)
FROM table As X
WHERE X.sname = S.sname) As DupCnt
FROM table As S
Now you can test a selection in the combo box to see if it
is a duplicate in its BeforeUpdate event:
If Me.combo.Column(2) > 1 Then
If MsgBox("Be sure to select by ID number", _
vbOKCancel, "Duplicate Name") = vbCancel Then
Cancel = True
End If
End If
You could even go so far as to skip the check if they click
in the ID column (instead of the name column) by using the
MouseUp event to capture the X coordinate of the mouse.
It may be more work than you want to get into, but it's
possible to simulate a combo box with a continuous subform.
A continuous form can use Conditional Formatting to color
code entries based on the values of the DupCnt field.