Check for duplicates

[Jeff]

I have a form that I use to enter new clients. When I click the close
button I want the on click event to check for duplicates of the
ClientFirstName and ClientLastName. I ma trying to use a query that finds
duplicates...no good.
I am sure that I am making this harder than it is.

 

[Allen Browne]

You need to do this in the BeforeUpdate event of the form.
In your close button, when you force the save it will call
Form_BeforeUpdate, but it will also be called in other circumstances where
the save would occur.

Private Sub cmdClose_Click
If Me.Dirty Then
Me.Dirty = False
End If
DoCmd.Close acForm, Me.Name
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String

If IsNull(Me.ClientFirstName) Or IsNull(Me.ClientLastName) Or _
((Me.ClientFirstName = Me.ClientFirstName.OldValue) And _
(Me.ClientLastName = Me.ClientLastName.OldValue)) Then
'do nothing
Else
strWhere = "(ClientLastName = """ & Me.ClientLastName & _
""") AND (ClientFirstName = """ & Me.ClientFirstName & """)"
varResult = DLookup("ClientID", "ClientTable", strWhere)
If Not IsNull(varResult) Then
strMsg = "Client " & varResult & " has the same name." & _
vbCrLf & "Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2) <> vbYes Then
Cancel = True
'Me.Undo
End If
End If
End If
End Sub

 

[Jeff]

I figured it out myself. Sorry if anyone has given my problem any
attention!

If DLookup("[ClientID]", "[Clients]", "[ClientLastName] =
Form.[ClientLastName] ") And DLookup("[ClientID]", "[Clients]",
"[ClientfirstName] = Form.[ClientfirstName] ") Then
MsgBox "The client name entered is already in use", vbOKOnly
Exit Sub
End If