[AccessD] Prevent Duplicates

Oops, just read Paul's post.  Missed the number.  Paul is right on.

************************
 If Not IsNull(Me.ctlVenNumber) Then
    If DCount("VenNumber", "tbl_Vendor", "VenNumber = '" & me.ctlVenNumber
& "'" & ) > 1 Then   
    	MsgBox "You have entered a Vendor Number that already exists"
      Me.ctlVenNumber.SetFocus
      Cancel = True
    End If
 End If
************************

Steve

-----Stephen Pickering's Original Message-----

Virginia,

I would set this in the properties of the field, not validate on a form,
unless I had some compelling reason for allowing duplicates via some other
process.

If you still want to validate in the form, I would put your code in the
BeforeUpdate event, not the AfterUpdate, and yes, you need the quotes.

I would also change my control names from being the same as the field
names.  "ctlVenNumber" instead of "VenNumber" for the text box, for
example.

************************
 If Not IsNull(Me.ctlVenNumber) Then
    If DCount("VenNumber", "tbl_Vendor", "VenNumber = '" & me.ctlVenNumber
& "'" & ) > 0 Then   
    	MsgBox "You have entered a Vendor Number that already exists"
      Me.ctlVenNumber.SetFocus
      Cancel = True
    End If
 End If
************************

HTH,

Steve

-----Virginia Hollis' Original Message-----

I need a way to check if a vendor number is a duplicate entry. On the
AfterUpdate of VenNumber I placed the below code, but it produces an error
about not finding the Automation Object and gives the entry I made in the
VenNumber field. VenNumber is a text field - do I have the quotes or
parenthesis wrong?

Virginia

************************
 If Not IsNull(VenNumber) Then
    If DCount("VenNumber", "tbl_Vendor", "VenNumber = " & [VenNumber]) > 0
Then   
        MsgBox "You have entered a Vendor Number that already exists"
        VenNumber.SetFocus
        VenNumber.Undo
End If
    End If
--