Find duplicate record via data entry form ALWAYS fires

[Gina Maylone]

Good morning all! Sorry to be so needy lately!

I have a function that looks for an existing address in my database after "City" is entered. It fires on every record even when there are NO records in the table. Do I need an Else statement?

Private Sub City_AfterUpdate()
On Error GoTo errHandler
Dim fulladdress As String
Dim strCriteria As String
Dim rs As DAO.Recordset

Me.fulladdress = Me.[Address] & " " & Me.[Street Name] & " " & Me.[City] & " " & Nz(Me.[State]) & " " & Nz(Me.[Zip])
DoCmd.RunCommand acCmdSaveRecord

    Set rs = Me.RecordsetClone

    fulladdress = Me.fulladdress.Value
    
      strCriteria = "[fulladdress]=" & "'" & fulladdress & "'"
    Debug.Print strCriteria
    If DCount("fulladdress", "contacts", _
              strCriteria) > 0 Then
 
            
    Debug.Print strCriteria
     
    End If
'Message box warning of duplication
Select Case MsgBox("This address already exists." & vbNewLine & vbNewLine & _
"Click 'Yes' to view the existing contact record," & vbNewLine & vbNewLine & _
"Click 'No' to add a duplicate contact record," & vbNewLine & vbNewLine & _
"Click 'Cancel' to discard all changes.", vbYesNoCancel + vbExclamation)
Case vbYes
'Go to record of original record'
DoCmd.OpenForm "contacts", acNormal, , strCriteria

Case vbNo
'Allow save
'cancel = False
Forms!CONTACTS.State.SetFocus

Case vbCancel
'Stop the save and undo the form
Me.Undo

End Select


    Set rs = Nothing

Exit Sub
errHandler:
  MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"


End Sub

Thanks in advance!

[ItsMe]

I would probably do something like

Set rs = Me.RecordsetClone
If rs.recordcount < 1 then
set rs = nothing
exit sub
end if

[John_G]

Follow the logic in your code - there is no way to avoid executing the Select Case.... statement.

It's an easy fix -

First, change

if DCount("fulladdress", "contacts", strCriteria) > 0 Then

to

if DCount("fulladdress", "contacts", strCriteria) = 0 Then


and put Exit Sub right after the Debug.Print strCriteria statement.

What that does is exit the procedure if the address is NOT found, and you will not get the message.

[Gina Maylone]

This did it! Thank you John_G!!