preventing duplications

[Guest]

I am creating a form for my coworkers to use to enter data into a database.
It's a basic contact management db. The form starts in the last name field,
then tabs to the first name field onward. I would like for the form to
autofill the last name with existing, matching records so that should someone
go to enter a contact that is already there, access will pull up that record
before they try to enter a new one. This would probably require some
coordination of what is entered in the lastname field and the first name
field. Any guidance on how to do this? Any way to help prevent users from
entering the same record multiple times simply because they do not realize it
is already there. THANKS!

 

[Guest]

One way to do this would be to use the DLookup function to see if the first
name, last name combination already exists. It would not preclude
misspellings or differences in format, but would be a good starting point:

If Not IsNull(DLookup("[LastName]","ContactTableName", _
& "[LastName] = '" & Me.txtLname & "' and [FirstName] = '" _
& Me.txtFname & "'") Then
If MsgBox(Me.Lname & ", " & Me.Fname & " is already in the Database " _
& vbNewLine & "Click OK to Edit this Record or Cancel to Start
Over", _
& vbOkCancel,"Duplicate Entry") = vbOk Then
'pull the record up
Else
'Cancel the record
End If
End If

You may want to also consider checking some other fields that could be
duplicates like address, phone number, email, or whatever in the form's
before update event and take whatever action is necessary.