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:
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:
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
Comment