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.
The logic of preventing a duplicate customer name and address entry is to:
- Enter a customer name on the customer name field
- Enter an address on the address field
- Compare the customer name and address in the table tbl_Customer with the new entry.
- 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
- 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.
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.
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.
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.
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.