Searching/Updating multiple tables at once

[vangogh228]

I have encountered a database that has an occasional duplication because existing customers are occasionally entered as new. Sales and Service activity on the new record's related tables needs to be moved to the original main record and the new erroneous one deleted.

The customer records and data are related by a customer number field. Currently, the database operator is going to each of the underlying tables and manually changing the entry in the customer number field (the related field) to reflect the original number, then she deletes the new main record manually.

1. Is there a way of have the system recognize duplicates based on matching entries in multiple fields and, perhaps, stop entry? For example, could it look at First, Middle and Last names plus the Zip Code to eliminate duplication?

2. If that is too difficult, can I at least automate their current process, considering they have 5 underlying tables?

Thanks so much for any help!!

 

[RV]

Duplicate Customers

How do you define "duplicate customers"?

In practice, "duplicate customers" in fact are not duplicate when you look at the data which was entered.
But they are duplicate 'cause the data entered actually represents the one and same customer.

It's hard to "automate" customers not being entered duplicate.
One suggestion is first to look up existing customers and check whether a customer (most propably) already exists.

Another possibility is to define an Before Update event in your Customer form which checks whether a Customer exists in your table Customer having the same Firstname, Middlename, Lastname AND Zipcode.

The basic code looks like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If NewRecord = True Then
If DCount("*", "Customer", "Lastname = '" & Forms!Customer!Lastname & "' AND Firstname = '" _
& Forms!Customer!Firstname & "' AND Zipcode = '" _
& Forms!Customer!Zipcode & "' AND Middlename = '" _
& Forms!Customer!Middlename & "'") <> 0 Then
MsgBox "Customer already exists"
Me.Undo
End If
End If
End Sub

RV

 

[vangogh228]

RV: I think I understand the principle, but I'm not familiar with DCount. What is the syntax for that command? I am intrigued by the "*" portion.

Thanks so much for the help!!!

Tom