Identifying potential duplicates

[dunkt]

Hi guys,

I am not a VBA programmer, but am trying to help someone out with their database. They have a simple form to add contacts to their contacts table.

I have created the below code that will identify duplicates based on the first name and surname. Ideally, it should be the first initial and surname. I have suggested postcode, but they don't really use postcodes.

At the moment this code will match the first name and surname (if exact) and display the message and give the choice to add new, use existing or cancel and start again.

Except.. the message displays whether a match has been found or not. If a match is found, it will display the address - BUT - it only find one record. So if, for example, there are two or more John Smith's, it will only flag one of them. If you select the option to 'Use Existing' it may just pick another record. However, it doesn't display just how many John Smith's it might have found.

I guess what would be good would be to display a list of John Smith's, so the inputter can either pick one or add the new record anyway.

Equally, if no match was found, then there is no need for the display box.

My Ltd skills have got me this far but I can't seem to improve upon it. If anyone has any ideas, I'd be grateful to hear them.

As indicated, I have made a valiant attempt but a reaching the limit of my powers.

Many thanks for any help.

The code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
Dim strSQL As String
Dim iAns As Integer
If Me.NewRecord Then ' only check for new additions
     strSQL = "[Surname] = """ & Me!Surname _
         & """ And [First Name] = """ & Me![First Name] & """"
      Set rs = Me.RecordsetClone ' get the form's recordset
      rs.FindFirst strSQL ' find this person's name
      If rs.RecordCount > 0 Then
      iAns = MsgBox("There is a contact with this name already " _
     & "at " & rs![Address 1] & ", " & rs![Address 2] & ", " & rs!Town _
     & ", " & rs!Postcode _
     & ": Select Yes to add record anyway, No to use existing record, " _
     & "Cancel to erase and start over:", vbYesNoCancel)
    Select Case iAns
       Case vbYes
             ' do nothing
       Case vbNo
             ' jump to the found record
            Cancel = True
            Me.RecordsetClone.RecordCount "[First Name] = """ & Me![First Name] & """ AND [Surname] = """ & Me![Surname] & """"
            Me.Undo
            'see if record was found
            If Not Me.RecordsetClone.NoMatch Then
            'move to record
            Me.Bookmark = Me.RecordsetClone.Bookmark
            End If
       Case vbCancel
            Cancel = True
            Me.Undo
     End Select
  End If
End If
End Sub

[NeoPa]

I'm not sure what status RecordsetClone would be in at this stage. How about trying a DLookup() on record source of the form instead?

[Rabbit]

The reason the message always comes up is because you're not filtering the recordset. The find just moves the recordset pointer to that record, it doesn't change the count whatsoever.

[NeoPa]

I'm thoroughly embarrassed that I missed that :-(

Good spot rabbit.