Validation Rule DLookup

[kstump]

I have a very simple question . . . This is the first time I have tried to do a DLookup. I have a field CustomerNumber. When the value is entered, I want to make sure this is not a duplicate number in a table LastCustomer. I have been trying to make this work as an AfterUpdate code, but so far it won't work. Could someone give me the code for this? Thank you.

 

[ghudson]

Try something like this if the customer number is a text field.

    If DCount("[CustomerNumber]", "tblCustomers", "[CustomerNumber] = '" & [Forms]![YourForm]!CustomerNumber "'") > 0 Then
        MsgBox "That customer number already exists.", vbInformation, "User ID Already Exists"
        Exit Sub
    Else
        'for testing if false
    End If

 

[kstump]

It didn't work.
The CustomerNumber feild is a number . . . the form name is frmLastCustomer

 

[ghudson]

Try this...

    If DCount("[CustomerNumber]", "YourTableNameHere", "[CustomerNumber] = " & [Forms]![frmLastCustomer]!CustomerNumber") > 0 Then
        MsgBox "That customer number already exists.", vbInformation, "Customer Number Already Exists"
        Exit Sub
    Else
        'for testing if false
    End If