asked on

Prevent Duplicate Entries ... using Multiple Fields on Form

I have found a couple of answer to my question ... but nothing seems to work.

I have a form (Frm_Main) linked to a table (Tlb_Emp_Names). There are 3 fields in the Table and 3 fields on the Form (FirstName, LastName, Last4SSN).

I need to know what code I should use to check the fields for a duplicate entry, and then if the employee already exists ... go to that entry.

Here is what I have tried ... I get a Run Time Error 3075:

Private Sub Last4SSN_BeforeUpdate(Cancel As Integer)
If DCount("[firstname] & [lastname] & [last4ssn]", "Tlb_Emp_Names", "[firstname] & [lastname] & [Last4ssn]='" & Me.FirstName & "' & '" & Me.LastName & Chr(34) & " & '" & Me.Last4SSN & "'") > 0 Then
If MsgBox("You already have this person on file with same..." _
& vbCr & "...first, last, and SSN..." _
& vbCr & vbCr & "Press Yes to clear out data entered..." _
& vbCr & "Press No to continue entering!!!", vbYesNo) = vbYes Then
Me.Undo
End If
End If

End Sub

Any Suggestions??????

jpipkins

Change your if dcount line to this:

If DCount("[firstname] & [lastname] & [last4ssn]", "Tlb_Emp_Names", "[firstname] ='" & Me.firstname & "' and [lastname] ='" & Me.lastname & "' and [Last4ssn] = '" & Me.last4ssn & "'") > 0 Then

snoopyswimer

ASKER

Ok ... That works!!!!

So ... is there a way to make it go to the previoulsy entered record? When I select "yes" is just clears out the fields?!?!?

ASKER CERTIFIED SOLUTION

jpipkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

snoopyswimer

ASKER

PERFECT ! ! ! ! ! ! ! ! ! !

jpipkins

You can remove one of the Me.Undo in there. Glad it works for you!