Duplicate Record on Add

[leadadmin]

My table has two fields used as the key, ContactLastName and CompanyName. If the user attempts to add a duplicate record using my form I would like a custom error message to display and the record not added to the table.

I tried following advice given for a previously asked question in this forum and added code to the end of my BeforeUpdate procedure but got an error message indicating it was interpreting the value in ContactLastName as an expression or fieldname, which didn't fly.

I have code in that procedure that performs validation edits on required fields. I put the above described code after those edits. Was I on the right track? Can you help me with the code I need?

Much thanks.

 

[Remou]

Hi
After other checks sounds fine to me. What code are you using to find if the record is a duplicate?

 

[leadadmin]

Dim db As DAO.Database, rst As DAO.Recordset
Dim strMsg As String, Style As Integer, Title As String

If (Len(Me!ContactLastName & "") > 0) And _
(Len(Me!CompanyName & "") > 0) Then

Set db = CurrentDb()
Set rst = db.OpenRecordset("Customers", dbOpenDynaset)
Criteria = "([ContactLastName] = " & Me!ContactLastName & ") And " & _
"([CompanyName] = " & Me!CompanyName & ")"
rst.FindFirst Criteria

If Not rst.BOF Then
strMsg = "Dup Record; try again"
Style = vbCritical + vbOKOnly
Title = "Duplicate Key Error!"
MsgBox strMsg, Style, Title
Cancel = True
Me.Undo
End If

End If

 

[PHV]

Criteria = "ContactLastName=[tt]'"[/tt] & Me!ContactLastName & [tt]"'[/tt] And CompanyName=[tt]'"[/tt] & Me!CompanyName & [tt]"'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Remou]

Hi
I think you are missing some quotation marks:

 Set rst = db.OpenRecordset("Customers", dbOpenDynaset)
      Criteria = "([ContactLastName] = '" & Me!ContactLastName & "') And " & _
                 "([CompanyName] = '" & Me!CompanyName & "')"
      rst.FindFirst Criteria

 

[leadadmin]

Hooray! It works!

One little thing: all the data entered on the form is blanked out when the error appears. This is desired when it fails any of the other edits in this procedure, but I'd like the data to stay on the form for just this one. How can I do this?

 

[Remou]

Hi
Leave out the Cancel = True and Me.Undo, I think. You might like to put this check first, and exit the sub if it fails.

     If Not rst.BOF Then
         strMsg = "Dup Record; try again"
         Style = vbCritical + vbOKOnly
         Title = "Duplicate Key Error!"
         MsgBox strMsg, Style, Title
[Red]Exit Sub[/Red]
      End If

 

[Remou]

Sorry about the above reply. Please ignore it and use this corrected version.

Just leave out:
Me.Undo.

 

[leadadmin]

I thank you for all your help. But I just found that we're not done: my dup edit works too well. I get the dup error message on any new record I try to add, even if it is not a duplicate.
In my BeforeUpdate procedure I have a series of required field edits, which is followed now by the new code. Here is the last req'd fld edit and the dup edit code:

If Len(Nz(Me.Date, "")) = 0 Then
booBadRecord = True
strMsg = "Please enter today's date." & vbCrLf
Me.Date.SetFocus
If booBadRecord Then
MsgBox strMsg
Cancel = True
Exit Sub
End If
End If

If (Len(Me!ContactLastName & "") > 0) And _
(Len(Me!CompanyName & "") > 0) Then

Set db = CurrentDb()
Set rst = db.OpenRecordset("Customers", dbOpenDynaset)
Criteria = "([ContactLastName] = '" & Me!ContactLastName & "') And " & _
"([CompanyName] = '" & Me!CompanyName & "')"
rst.FindFirst Criteria

If Not rst.BOF Then
strMsg = "This Contact Last Name/Company Name combination is a Duplicate Record and cannot be added."
Me.ContactFirstName.SetFocus
Style = vbCritical + vbOKOnly
Title = "Duplicate Key Error"
MsgBox strMsg, Style, Title
Cancel = True
Me.Undo
End If

End If

End Sub

Any thoughts? Thank you very much.

 

[Remou]

Hi
I think I might do the recordset a little different:

If (Len(Me!ContactLastName & "") > 0) And _
      (Len(Me!CompanyName & "") > 0) Then
      
      Set db = CurrentDb()
'Select only records matching criteria. 
     Set rst = db.OpenRecordset("Select * from Customers" _ & "Where [ContactLastName] = '" & Me!ContactLastName _ & "' And [CompanyName] = '" & Me!CompanyName & "'"
      
      If Not rst.EOF Then
         strMsg = "This Contact Last Name/Company Name combination is a Duplicate Record and cannot be added."
         Me.ContactFirstName.SetFocus
         Style = vbCritical + vbOKOnly
         Title = "Duplicate Key Error"
         MsgBox strMsg, Style, Title
         Cancel = True
         Me.Undo
      End If
 'Code for if fields are not both greater than 0 eg:
Else
      If Trim(Me!CompanyName) & "" = ""
          'Do Something
      End If
      If Trim(Me!ContactLastName) & "" = ""
          'Do Something
      End If

End If

Bit sketchy, but might give you some ideas.