Check for Duplicates

[mike60smart]

Hi
I appear to have a mental block on checking for Duplicates for a Members Data Entry Form.

The 2 fields are Forenames and Surname.

How would I code the AfterUpdate of the Surname Control to display the Duplicate warning?

Your help appreciated

 

[bastanu]

Use a dCount or dLookup in the source table to check if you already have an entry.

Cheers,

 

[cheekybuddha]

Something like:

If DCount( _
  "*", _
  "Contacts", _
  "FirstName='" & Replace(Me.txtFirstName & "", "'", "''")) & "' " & _
  "LastName='"& Replace(Me.txtLastName & "", "'", "''")) & "'" _
) > 0 Then
  MsgBox "Dupe"
End If

But perhaps you would be better off using BeforeUpdate:

Dim dupe As Boolean

dupe = DCount( _
  "*", _
  "Contacts", _
  "FirstName='" & Replace(Me.txtFirstName & "", "'", "''")) & "' " & _
  "LastName='"& Replace(Me.txtLastName & "", "'", "''")) & "'" _
) > 0

If dupe Then MsgBox "Dupe"
Cancel = dupe

 

[mike60smart]

Something like:

If DCount( _
  "*", _
  "Contacts", _
  "FirstName='" & Replace(Me.txtFirstName & "", "'", "''")) & "' " & _
  "LastName='"& Replace(Me.txtLastName & "", "'", "''")) & "'" _
) > 0 Then
  MsgBox "Dupe"
End If

But perhaps you would be better off using BeforeUpdate:

Dim dupe As Boolean

dupe = DCount( _
  "*", _
  "Contacts", _
  "FirstName='" & Replace(Me.txtFirstName & "", "'", "''")) & "' " & _
  "LastName='"& Replace(Me.txtLastName & "", "'", "''")) & "'" _
) > 0

If dupe Then MsgBox "Dupe"
Cancel = dupe

Hi David
How do we arrive at the field "Me.txtFirstName" & "Me.txtLastName" ?

I tried the following Code and get the error as shown

Private Sub Form_BeforeUpdate(Cancel As Integer)

10        On Error GoTo Form_BeforeUpdate_Error
      Dim dupe As Boolean

20    dupe = DCount("*", "[Member Details]", _
        "Forenames='" & Replace(Me.Forenames & "", "'", "''")) & "' " & _
        "Surname='" & Replace(Me.Surname & "", "'", "''") & "'" > 0

30    If dupe Then MsgBox "Dupe"
40    Cancel = dupe
          
50        On Error GoTo 0
60        Exit Sub

Form_BeforeUpdate_Error:

70        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_BeforeUpdate, line " & Erl & "."

End Sub

 

[bastanu]

Missing AND in the criteria clause of the dCount between the two fields).

 

[cheekybuddha]

So, do you have a field named 'Forenames' (plural) in table Member Details?

 

[mike60smart]

So, do you have a field named 'Forenames' (plural) in table Member Details?

Hi David

Yes

 

[cheekybuddha]

Missing AND in the criteria clause of the dCount between the two fields).

Thanks for picking that up, Vlad!

 

[cheekybuddha]

Try again with:

' ...
20    dupe = DCount("*", "[Member Details]", _
        "Forenames='" & Replace(Me.Forenames & "", "'", "''") & "' AND " & _
        "Surname='" & Replace(Me.Surname & "", "'", "''") & "'") > 0
' ...

 

[cheekybuddha]

There was also a mis-placed paren - hopefully corrected above in Post #9

 

[mike60smart]

There was also a mis-placed paren - hopefully corrected above in Post #9

Hi David

Tried that and the same error message

 

[cheekybuddha]

Please show your code again, Mike, as you have it now

 

[cheekybuddha]

No that still has the misplaced paren from before I edited post #9.

It should be this:

' ...
20    dupe = DCount("*", "[Member Details]", _
        "Forenames='" & Replace(Me.Forenames & "", "'", "''") & "' AND " & _
        "Surname='" & Replace(Me.Surname & "", "'", "''") & "'") > 0
' ...

 

[mike60smart]

No that still has the misplaced paren from before I edited post #9.

It should be this:

' ...
20    dupe = DCount("*", "[Member Details]", _
        "Forenames='" & Replace(Me.Forenames & "", "'", "''") & "' AND " & _
        "Surname='" & Replace(Me.Surname & "", "'", "''") & "'") > 0
' ...

Hi David
Many thanks yet again that works just great

 

[cheekybuddha]

Hi David
Many thanks yet again that works just great

You're welcome, Mike!

Glad we got there in the end - apologies for the shoddy code! And thanks to Vlad for spotting the main error!