Prevent a Duplicate Data Entry for Two Fields




How to Prevent a Duplicate Data Entry for Two Fields

After we open an Add New Customer form, we have no idea how many customers in the database. We will see only the blank field to add new information. We don’t know if this customer is already in the database. If we don’t have a function to check it first then we may add a same customer name and address in the table twice.

Related How To: How to Prevent a Duplicate Data Entry for Customer Name only

In this HowTo, I will allow to same customer name in database, but not both same customer name and address. That means we will prevent the duplicate entry by checking on both customer name and address. If this customer name with this address has already been in database then the alert message will pop-up.

new cust

The logic of preventing a duplicate customer name and address entry is to:

  1. Enter a customer name on the customer name field
  2. Enter an address on the address field
  3. Compare the customer name and address in the table tbl_Customer with the new entry.
  4. If the entered customer name and address matches with a customer name and address in the table then show a message of having duplicate information then Undo the process. And show the record information of matched customer name and address from the customer table
  5. If no matching customer name and address then add new customer name and address to customer table and go to new record ready to add a new customer

We will put the VBA code under the After Update Event Procedure under the Address field on the property sheet of form design view.

address after update

VBA Code

Private Sub Loc_Address_AfterUpdate()
Dim NewCustomer, NewAddress As String
Dim stLinkCriteria As String
Dim custNo As Integer

‘Assign the entered customer name and  address  to a variable
NewCustomer = Me.CustomerName.Value
NewAddress = Me.Address.Value
stLinkCriteria = "[customername] = " & "'" & NewCustomer & "' and [address]  = " & "'" & NewAddress & "'"
If Me.CustomerName = DLookup("[CustomerName]", "tbl_customer", stLinkCriteria) Then

   MsgBox "This customer, " & NewCustomer & ", has already been entered in database." _
              & vbCr & vbCr & "with addresss " & NewAddress & "" _
              & vbCr & vbCr & "Please check customer name and address again.", vbInformation, "Duplicate information"
     Me.Undo   'undo the process and clear all fields
    'show the record of matched customer name and address from the customer table
     custNo = DLookup("[customer_id]", "tbl_customer", stLinkCriteria)
     Me.DataEntry = False
     DoCmd.FindRecord custNo, , , , , acCurrent
End If
End Sub

For example, we have a customer name “John Vang” with address “1122 First St.” in our database.

new entry

We don’t know yet and want to enter same information again on the Add New Customer form.

There is no checking on a customer name since we allow a duplicate name, but not both name and address. The alert message will pop-up after entering a new address “1122 First St.” which is already in the customer table.

entry with duplicate

After click OK button on the alert message, it will undo or cancel new entry then redirect to the record of existing customer name and address from the customer table as shown in the picture below.

undo and go back

 

Acceptable Entry

We allow same customer name, but not both name and address. For example below, we already have customer “John Vang” with address 1122 First St. in our database, but we have a new customer “John Vang with address 1000 Main St.” So this customer is accepted to enter into our database.

no duplicate address no duplicate on table