[Last Call] Learn how to a build a cloud-first strategyRegister Now


Prevent Duplicate Entries ... using Multiple Fields on Form

Posted on 2009-02-16
Medium Priority
Last Modified: 2013-11-28
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
        End If
    End If

End Sub

Any Suggestions??????
Question by:snoopyswimer
  • 3
  • 2

Expert Comment

ID: 23653665
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

Author Comment

ID: 23653729
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?!?!?  

Accepted Solution

jpipkins earned 2000 total points
ID: 23653794
Use this instead of the other event
    Dim rcs As DAO.Recordset
    Dim sCriteria As String
    sCriteria = "[firstname] ='" & Me.firstname & "' and [lastname] ='" & Me.lastname & "' and [Last4ssn] = '" & Me.last4ssn & "'"
    If DCount("[firstname] & [lastname] & [last4ssn]", "Tlb_Emp_Names", sCriteria) > 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
            Set rcs = Me.RecordsetClone
            rcs.FindFirst (sCriteria)
            Me.Bookmark = rcs.Bookmark
        End If
    End If
    Set rcs = Nothing

Open in new window


Author Closing Comment

ID: 31547497
PERFECT ! ! ! ! ! ! ! ! ! !

Expert Comment

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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question