Check for duplicates

  • Thread starter Thread starter Jeff
  • Start date Start date
J

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.
 
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
 
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
 
Back
Top