How can I check for duplicate entries after leaving a field?

[kelsita_05]

I'm pretty new to Access, so please overlook any stupid questions
I have a data entry form that has one field that cannot be a duplicate. So, in the table I have it Indexed with No duplicates.
The problem is that the form doesn't notify the user that they have entered duplicate information until they close the form. So the 10 minutes they spent filling it out is wasted.
So, after consulting various posts, I did a BeforeUpdate event with the following code:
(the field that must be unique is called "LS Number", in the form "Q and D" based on the table "Q and D Database")


Private Sub LS_Number_BeforeUpdate(Cancel As Integer)
On Error GoTo LS_Number_BeforeUpdate_Err

If (DLookup("[LS Number]", "Q and D Database", "[LS Number]=Forms![Q and D]![LS Number]")) Then
MsgBox "The LS Number you entered already exists. Enter a unique LS Number", vbInformation, "Duplicate LS Number"

End If

LS_Number_BeforeUpdate_Exit:
Exit Sub

LS_Number_BeforeUpdate_Err:
MsgBox Error$
Resume LS_Number_BeforeUpdate_Exit

End Sub


When I test this by entering a duplicate LS Number, it gives me the error "Type Mismatch." Unfortunately, I have no idea what this means, nor do I know how to fix it.

Please help, my head hurts!

 

[quest4]

It very simple means that the data types, between the table and the form are different. For example one is numeric and the other is text. Also check field sizes in the tables vs. control sizes in the form. I have not tryied to run DLookup or DCount against more than one table at a time, but it might work, but if it does not work for you, just run it against one table then the other, like two separate procedures. hth.

 

[kelsita_05]

Ok, so I checked and both are text. I cleaned up the code a little and it stopped giving me the error, but it doesn't do what I want it to. I can still enter duplicate LS Numbers.
This is what it looks like now:

Private Sub LS_Number_BeforeUpdate(Cancel As Integer)
On Error GoTo LS_Number_BeforeUpdate_Err

If (DLookup("[LS Number]", "Q and D Database", "[LS Number] =" & Forms![Q and D]![LS Number])) Then
MsgBox "The LS Number you entered already exists. Enter a unique LS Number", vbInformation, "Duplicate LS Number"

End If

LS_Number_BeforeUpdate_Exit:
Exit Sub

LS_Number_BeforeUpdate_Err:
MsgBox Error$
Resume LS_Number_BeforeUpdate_Exit

End Sub

 

[quest4]

Lets try a few changes here:
If (DLookup("[LS Number]", "[Q and D Database]", "[LS Number] =" & Me![LS Number])) Then
MsgBox "The LS Number you entered already exists. Enter a unique LS Number"
Me!![LS Number].SetFocus

End If
A quick suggestion, in the future, don't put any spaces in your names, like LSNumber, that way you won't need to use[] around the names. Nornally this is the event where you would put this, but sometime you have to try different event, like I have a check for blanks in the ONExit and in another dbase, it is in the LostFocus. Sometimes you have to think outside the box nad just try things and see what happens, but ALWAYS try it on a copy of the dbase. hth.