Record Level Duplicate Prevention / Validation with VBA

[Guest]

Hello All,

I have a table with 9 fields that is populated through a form, I am looking
to prevent duplicate records using VBA. This code will need to execute when
the save command button is clicked and trigger a msgbox if all 8 of the
fields are an exact match with the 8 fields in another record. The ninth
field is an autonumber used as a unique id and does not need to be considered.

Form name: input
Table name: records
Fields:
RecordID (autonumber) - this field does not need to be checked
ProjectID (Number)
TypeID (Number)
Box (Number)
Number (Text)
Revision (Text)
Title (Text)
CompanyID (Number)
StartDate (Date/Time)

Any and all help is much appreciated, Philip

 

[Douglas J. Steele]

Why not create a unique index on the 8 fields?

 

[Guest]

Thanks, guess I was looking at the problem the wrong way. Perhaps you could
tell me how to make the message displayed when an attempt to save a duplicate
record is made more user friendly.

 

[Douglas J. Steele]

You'll get an error 3022 (I believe). Have error handling that intercepts
that particular error, and display your own message instead:

Select Case Err.Number
Case 3022
MsgBox "Sorry, someone's already beaten you to that combination"
Case Else
MsgBox Err.Number & ": " & Err.Descriptiong
End Select

You could also put code in the form's Error event:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3022 Then
MsgBox "Sorry, someone's already beaten you to that combination"
Me.Undo
Response = acDataErrContinue
End If
End Sub

 

[Guest]

I used the error handling below both in "Private Sub Form_Error" and
"cmbSave_Click", the sub used to save the record. The error number was 0 or
no error if I read that correctly - I found this with the code in
cmbSave_Click. Have I made an error in setting this up or does the attempted
violation of a unique index produce something other than an error? Thank you
for all the help so far