Duplicate Name Alert

[Seth]

I have a query [qryRecords] with field names [strFirstName] and
[strLastName].
When I access any given record in form "test", I want the user to be
alerted if there is more than one record with the same first name and
last name.
This is different from most posts I've read in that this doesn't happen
upon data entry since my data is imported and already verified.
Duplicate first and last names do legitmately exist in this application
and therefore I simply want to be notified when I access a record that
is such.

I've tried Dlookup and various other code, but it ends up flagging
every record or nothing at all...

Private Sub Form_Current()
On Error GoTo Err_cmdNoDup

If DCount("strLastName" & "strFirstName", "qryRecords") > 1 Then
MsgBox Me![strLastName] & ", " & Me![strFirstName] & " is a Name
Alert."
Else
End If

Exit_cmdNoDup
Exit Sub

Err_cmdNoDup:
MsgBox Err.Description
Resume Exit_cmdNoDup

End Sub

[Br]

Seth wrote:

I have a query [qryRecords] with field names [strFirstName] and
[strLastName].
When I access any given record in form "test", I want the user to be
alerted if there is more than one record with the same first name and
last name.
This is different from most posts I've read in that this doesn't
happen upon data entry since my data is imported and already verified.
Duplicate first and last names do legitmately exist in this
application and therefore I simply want to be notified when I access
a record that is such.

I've tried Dlookup and various other code, but it ends up flagging
every record or nothing at all...

Private Sub Form_Current()
On Error GoTo Err_cmdNoDup

If DCount("strLastName" & "strFirstName", "qryRecords") > 1 Then
MsgBox Me![strLastName] & ", " & Me![strFirstName] & " is a Name
Alert."
Else
End If

Exit_cmdNoDup
Exit Sub

Err_cmdNoDup:
MsgBox Err.Description
Resume Exit_cmdNoDup

End Sub

I went a little further and alerted the user if there was anyone

- same names
- same first name and same first letter of last name
- same last name and same first letter of first name

... in case of spelling mistakes.

I also added a Name Comment field that would be displayed next to the
name in all dropdown selections so people of the same name could be
differenciated.
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Allen Browne]

This example searches the table for other clients with the same name.
"Other" means a different primary key, assumed to be a numeric field named
ClientID in this example.

Private Sub Form_Current()
Dim strWhere As String
Dim lngCount as Long

If Not (IsNull(Me.strLastName) Or IsNull(Me.strFirstName) Or _
(IsNull(Me.ClientID)) Then
strWhere = "(strLastName = """ & Me.strLastname & _
""") AND (strFirstName = """ & Me.strFirstName & _
""") AND (ClientID <> " & Me.ClientID & ")"
lngCount = DCount("*", "CustomerTable", strWhere)
If lngCount > 0 Then
MsdgBox lngCount & " other client(s) with the same name."
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Seth" <sa******@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

I have a query [qryRecords] with field names [strFirstName] and
[strLastName].
When I access any given record in form "test", I want the user to be
alerted if there is more than one record with the same first name and
last name.
This is different from most posts I've read in that this doesn't happen
upon data entry since my data is imported and already verified.
Duplicate first and last names do legitmately exist in this application
and therefore I simply want to be notified when I access a record that
is such.

I've tried Dlookup and various other code, but it ends up flagging
every record or nothing at all...

Private Sub Form_Current()
On Error GoTo Err_cmdNoDup

If DCount("strLastName" & "strFirstName", "qryRecords") > 1 Then
MsgBox Me![strLastName] & ", " & Me![strFirstName] & " is a Name
Alert."
Else
End If

Exit_cmdNoDup
Exit Sub

Err_cmdNoDup:
MsgBox Err.Description
Resume Exit_cmdNoDup

End Sub