Checking for Duplicates

[Joshman]

How do I check for possible duplicate entries in two different fields (FirstName and LastName)?&nbsp;&nbsp;I don't want to prevent it totally, just check for it and ask if they want to add the entry or go with the existing one.&nbsp;&nbsp;(There are thousands of names, so duplication is inevitable.)<br><br>

 

[DougP]

Well this can be done several way's.<br>One way is to have 2 unbound text boxes on a form.<br>Key the firstname in one and the lastname in the other.<br>Press a Command button to serach for a match.<br>If one is found then bring that record up.<br>If none are found then create a new record with the first and last name they keyed in above.<br>to search a database in SQL which is the fastest way:<br><br>Private Sub Command12_Click()<br>&nbsp;&nbsp;&nbsp;&nbsp;On Error GoTo Err_Command12_Click<br><br>&nbsp;&nbsp;&nbsp;Dim FormName As String, SyncCriteria As String<br>&nbsp;&nbsp;&nbsp;Dim f As Form, rs As Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' form name to be syncronized<br>&nbsp;&nbsp;&nbsp;&nbsp;FormName = &quot;frm-People&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;'Define the from object and recordset object for the AutoCAD form<br>&nbsp;&nbsp;&nbsp;&nbsp;Set f = Forms(FormName)<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rs = f.RecordsetClone<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' define the criteria used for the sync<br>&nbsp;&nbsp;&nbsp;&nbsp;SyncCriteria = &quot;[FirstName] = '&quot; & Me![Text1] & &quot;' And LastName = '&quot; & Me![Text2] & &quot;'&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' find the corresponding record in the Parts table<br>&nbsp;&nbsp;&nbsp;&nbsp;rs.FindFirst SyncCriteria<br>&nbsp;&nbsp;&nbsp;&nbsp;If rs.NoMatch = True Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.GoToRecord , , acNewRec<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Me![FirstName] = Text1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Me![LastName] = Text2<br>&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;f.Bookmark = rs.Bookmark<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br><br><br>Exit_Command12_Click:<br>&nbsp;&nbsp;&nbsp;&nbsp;Exit Sub<br><br>Err_Command12_Click:<br>&nbsp;&nbsp;&nbsp;&nbsp;MsgBox Err.Number & &quot; &quot; & Err.Description<br>&nbsp;&nbsp;&nbsp;&nbsp;Resume Exit_Command12_Click<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>End Sub<br>-------------------<br>This works I juste tested it.<br><br><br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[dleewms]

I am trying to follow Doug's instructions, but am having some trouble understanding them.

The first set of instructions say that I can create a form with two unbound text boxes and then add a command button to do a search for a match. When I add the command button, which category contains the option to search the underlying table for a match. The category options are Record Navigations, Record Operations, Form Operations, Report Operations, Applications and Miscellaneous and none of these contain an option to do a search. I am at a loss here.

Then, my second question is that it seems like Doug was saying that another way to search the database was to use SQL. However, when I look at the SQL code, it looks to me like the procedure runs when Command12 is clicked. Is this the command button that is associated with the Form in option one or if not, what is it?

Sorry for so much confusion and thanks in advance for any assistance that you can give.

DLW

 

[Joshman]

The command button is a button you create with no wizard actions chosen (hit cancel). Instead you create an event procedure on the &quot;on click&quot; action. Copy and paste the code at the top here and change the field names to match yours. You will have to change the button name to match yours too (it will not be Command12 when you create it).

I have been using this code for over a year and it works great.

joshuab@musician.org
joshuaweb.htmlplanet.com

 

[dleewms]

O.K. I am still having problems. Let me explain what I have and see if you can point me in the right direction. I have created a test database with only one table named Main and it simply has FirstName, LastName, Address, Birthdate, City, State and Zip fields. I used the form wizard and created a simple form with all the same fields. This form is also named Main. I then created another form named Check and it only has the two unbound text boxes for the first and last name. I added a command button and assigned the code above to the OnClick property. I attempted to change the fields to match my own, but here is where I am running into the problem. When I click the command button to check the first and last name in the Check form with the entries in the Main table, I get a compile error &quot;Method or data member not found&quot;. Here is my code.

Private Sub CheckDup_Click()
On Error GoTo Err_CheckDup_Click

Dim FormName As String, SyncCriteria As String
Dim f As Form, rs As Recordset

' form name to be synchronized
FormName = &quot;Main&quot;

'Define the form object and recordset object for the AutoCAD form
Set f = Forms(FormName)
Set rs = f.RecordsetClone

' define the criteria used for the sync
SyncCriteria = &quot;[FirstName] = '&quot; & Me![Text1] & &quot;' And LastName = '&quot; & Me![Text2] & &quot;'&quot;

' find the corresponding record in the Main table
rs.Check SyncCriteria
If rs.NoMatch = True Then
DoCmd.GoToRecord , , acNewRec
Me![FirstName] = Text1
Me![LastName] = Text2
Else
f.Bookmark = rs.Bookmark
End If


Exit_CheckDup_Click:
Exit Sub

Err_CheckDup_Click:
MsgBox Err.Number & &quot; &quot; & Err.Description
Resume Exit_CheckDup_Click

End Sub

Thanks for any and all assistance.
DLW

 

[Joshman]

The problem with that code is that it is made to work only with one form. If you have another form opening like you do to check only, try this code out (I used your field names and form names.)
Before you do, you will have to have a primary key in the table (ContactID). Also, I use a macro to set the values of the Main form to the fields on the Check form in the instance where there is no duplicate. There is probably a better VBA way but I am not an expert programmer. The macro simply has two SetValue actions that set the value of the FirstName field on the Main form to equal the Text1 field of the Check form (and the same for LastName).


On Error GoTo Err_btnCheckDuplicates_Click

Dim FormName As String, SyncCriteria As String
Dim f As Object, rs As Object
Dim Response As Variant


' form name to be syncronized
FormName = &quot;Check&quot;

'Define the from object and recordset object for the Access form
Set f = Forms(FormName)
Set rs = f.RecordsetClone

' define the criteria used for the sync
SyncCriteria = &quot;[FName1] = '&quot; & Me![Text1] & &quot;' And [LName1] = '&quot; & Me![Text2] & &quot;'&quot;

' find the corresponding record in the Parts table
rs.FindFirst SyncCriteria
If rs.NoMatch = True Then
DoCmd.RunMacro &quot;mcrNewContact&quot;
Else
f.Bookmark = rs.Bookmark
Response = MsgBox(&quot;A record exists with that name. To use this record, simply press OK. To add a new record with that name, press CANCEL.&quot;, vbOKCancel)
If Response <> vbOK Then
DoCmd.RunMacro &quot;mcrNewContact&quot;
DoCmd.RunCommand acCmdSaveRecord
Else
DoCmd.OpenForm &quot;Main&quot;, acNormal, , &quot;[contactid] = forms![check]![contactid]&quot;, acFormEdit

DoCmd.GoToControl &quot;FName1&quot;

End If

End If

DoCmd.GoToControl &quot;FName1&quot;
DoCmd.Close acForm, &quot;Main&quot;, acSaveYes

Exit_btnCheckDuplicates_Click:
Exit Sub

Err_btnCheckDuplicates_Click:
MsgBox Err.Number & &quot; &quot; & Err.Description
Resume Exit_btnCheckDuplicates_Click

joshuab@musician.org
joshuaweb.htmlplanet.com