Checking for similar entries (1 Viewer)

Private Sub txtCustomer_BeforeUpdate(Cancel As Integer)

    Dim dbs As Database
    Dim rs As Recordset
    Dim qdf As QueryDef
    Dim sSql As String
    Dim LookupRes As Long
    Dim sNewCust As String
    Dim iResp As Integer
    Dim sMsg As String

   'On Error GoTo customer_BeforeUpdate_Error

sNewCust = Trim(Me.TXTCustomer)

sNewCust = fStripIllegal(sNewCust)

LookupRes = DCount("CUSTOMER", "Customers", "Customer like '*" & sNewCust & "*'")
Debug.Print LookupRes

If LookupRes > 0 And IsNull(Me.POST_CODE) Then ' Checks it isn't an existing record (new one won't have a post code)
    
    Me.txtEnteredByEmpID = iCurrentEmp     'Who is adding the record
        
    Set dbs = CurrentDb()
    
    sSql = "SELECT Account_No, Customer, Address_1 , Address_2 , Address_3 ,Address_4 , Country1 , Post_Code, Tel From [Customers] WHERE [Customers].Customer like '*" & sNewCust & "*' ;"
    
    Set rs = dbs.OpenRecordset(sSql, dbOpenSnapshot)
    
    With dbs
        Set qdf = .CreateQueryDef("tmpCustInfo", sSql)
        DoCmd.OpenQuery "tmpCustInfo"
        .QueryDefs.Delete "tmpCustinfo"
    End With
    
    dbs.Close
    qdf.Close
    
    sMsg = "This customer may already exist - please check the list displayed to avoid entering a duplicate." & vbCrLf
    sMsg = sMsg & "If you want continue to create the new record please press OK or Cancel the action."
    iResp = MsgBox(sMsg, vbOKCancel, "Duplicate Customer ?")
    
    If iResp = vbCancel Then
        Me.Undo
        Cancel = True
        DoCmd.Close acQuery, "tmpCustInfo"
    Else
        DoCmd.Close acQuery, "tmpCustInfo"
        Call FieldColours(1)[COLOR="Red"]
        If Me.Dirty Then
 this or               DoCmd.RunCommand acCmdSaveRecord
 this                  Me.TXTCustomer = sNewCust
doesn't work
[/COLOR]
        End If
                         
    End If
    

     
End If


   On Error GoTo 0
   Exit Sub

customer_BeforeUpdate_Error:

    With dbs
    .QueryDefs.Delete "tmpCustinfo"
    End With
    dbs.Close
    qdf.Close
    
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure customer_BeforeUpdate of VBA Document Form_Customer Record"

End Sub