Checking for duplicates

[bdhtexas]

I have set up a database that doesn't allow Duplicate Claim#'s and when our users are entering new Claim#'s in their forms, it doesn't notify them that they are Duplicate Claim#'s until they have completed filling in all of the form. This is wasting valuable production time. I want to know HOW I can have this message displayed as soon as they tab out of the Claim# field. Please help!

 

[Mile-O]

on the BeforeUpdate event of your ClaimNumber textbox

Private Sub txtClaimNumber_BeforeUpdate(Cancel As Integer)
    If DCount("ClaimNumber", "tblClaims", "ClaimNumber = """ & Me.txtClaimNumber & """") > 0 Then
        MsgBox "That number is already on the system.", vbExclamation, "Duplicate Claim Number"
        Cancel = True
    End If
End Sub

 

[bdhtexas]

on the BeforeUpdate event of your ClaimNumber textbox

Private Sub txtClaimNumber_BeforeUpdate(Cancel As Integer)
    If DCount("ClaimNumber", "tblClaims", "ClaimNumber = """ & Me.txtClaimNumber & """") > 0 Then
        MsgBox "That number is already on the system.", vbExclamation, "Duplicate Claim Number"
        Cancel = True
    End If
End Sub

I get a Compile error: Expected list separator or )
This happens at "tblClaims"
How exact do I have to be with the spaces and commas and quotation marks?

 

[Johnthelleader]

Checking for repeat numbers

I have a similar issue as illustrated in this topic.

I have a text box called Invoice Number in a Data Entry form called add new invoice. This is stored in a table called Invoices.

What I would like is when a user enters in an Invoice number, and that invoice has already been entered in, a warning is displayed.

I appreciate that I need to add some code in the before update section of the control, but not too confident on how that could be done, so grateful for any assistance.