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?
Code:
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!