duplicates records and custom error messages.

[Guest]

First, I have to determine if a record already exist in a SQL database based
on two key fields. If duplicate records exist, how do I create a Custom
Error Message and void out the system meesage to notify a user that the
record is a duplicate?

 

[John Nurick]

Hi Sharon,

Assuming you're still working with your form and textboxes, and that the
records are in a linked table, the general idea is to use something like
this in the appropriate BeforeUpdate events.

I'll assume the two key fields are Field1 and Field2, that they are text
fields, and that they are bound to txtField1 and txtField2:

If DCount(1, "MyTable", _
"(Field1='" & Me.Controls("txtField1").Value & "') And (" _
& "Field2='" & Me.Controls("txtField1").Value & "')" ) Then
Cancel = True
MsBox "A record with these values already exists", blah blah
End If

If either field is a number field rather than a text field, remove the
apostrophes '' from around its value. For a date field, it's best to
replace the apostrophes with hash signs so today would look like this:
#02/25/2005#