[AccessD] Prevent Duplicates

oh well, we got there between us in the end....





Message date : Apr 22 2004, 03:51 PM
>From : "Pickering, Stephen" 
To : "'Access Developers discussion and problem solving'" 
Copy to : 
Subject : RE: [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
-- 
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
Join the UK's number one for the internet 
www.freeserve.com/time