Checking for similar entries

[Minty]

I have a Customer record form (Bound). I am trying to prevent duplicate Customer entries at the point of new record insertion, and thought I would do the following;

1. Strip out any nasty characters
2. Lookup any similar names using a Dcount of *New Cust Name*
3. Display any matching names
4. Give the user the option to cancel or continue and add the new customer
5. If they continue and want to add the new customer, replace the name entered with my sanitised non-nasty character version.

Point 5 is where I fall over - all the rest works,I can't seem to continue and save the existing record, replace the value or otherwise...

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

I'm obviously missing some grey matter today or simply lacking coffee/beer/red wine...

 

[Ranman256]

for me, I'd have a pre-entry form.
a text box for First Name ,Last name, address, city

upon entry, a query filters the list of names of the existing entry for display..
if any exist then ,validate if its the same person
if not , create new record.

 

[Minty]

I have thought of this route but was trying to keep it within one form, just for simplicities sake.

What I've done as a work around is leave the Customer Name as entered then do all the tidying up after they enter the postcode which is as near mandatory as any part of the address can be!

 

[spikepl]

You might consider email or mobile phone as unique for a customer

 

[Minty]

In this case the customers are actually all Businesses. So for example IBM London, IBM Reading, IBM Bristol. Whilst we have a Site Address table we often deal with the sites as separate business areas so they need putting on as main accounts individually.

Also as a result we probably have a generic email for service at ibm.co.uk or sales at ibm.co.uk as made up examples...