Combo Box Validation 1

[tis9700]

Merry Christmas Everyone!

Have a question for you.

I have a Combo Box on a subform in datasheet view that allows the user to select a SSN of an Assistant who participated in a class.

There are no limits to the amount of Assistants a user can enter for a class but I do not want them to be able to select the same Assistant more than once per class.

The Row Source is...
SELECT DISTINCT SelectHspInstructorQry.InstID, SelectHspInstructorQry.InstSSN, SelectHspInstructorQry.FULLNAME
FROM SelectHspInstructorQry
ORDER BY SelectHspInstructorQry.InstSSN;

And the Combo Box is bound to the InstID field of the ASSISTANTS table

I tried to prevent it by using the Before Update event ...

Private Sub cboSelectAssistant_BeforeUpdate(Cancel As Integer)

If Me.cboSelectAssistant.Value = Me.Parent.Parent!InstID Then
MsgBox "Primary Instructor can not be an Assistant!", vbInformation, "Selection Error"
Cancel = True
Me.Undo
ElseIf Me.cboSelectAssistant.Value = DLookup("InstID", "ASSISTANTS", "ClassID = " & Me!ClassID) Then
MsgBox " This Assistant has already been selected for this class!"
Cancel = True
Me.Undo
End If


End Sub

It works unless the user chooses an Assistant then chooses the same Assistant right after. But If they choose an Assistant, Choose another Assistant then choose the first Assistant again it works.

I tried to requery the combo box on the Got Focus event with no luck.
What am I not seeing?

Thanks and Happy New Year!

 

[PHV]

ElseIf Me.cboSelectAssistant.Value = DLookup("InstID", "ASSISTANTS", "ClassID = " & Me!ClassID[!] & " AND InstID = " & Me!cboSelectAssistant.Value[/!]) Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tis9700]

Perfect! Thanks for the Christmas present. Gold Star for you PHV!