How to Prevent a Duplicated Data Entry

How to prevent a duplicated data entry (Related Video)

Related How To (Must See): 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 first then we may add a same customer in the table twice. In this HowTo, I will put the VBA code under the After Update Event Procedure for CustomerName field on the property sheet of the form design view.

The logic of preventing a duplicated customer name entry is to:

1. Enter a new customer name on the customer name field
2. Compare the customer name in the table tbl_Customer with the new entry name.
3. If a new customer name matches with a customer name in the table then show a message of having a duplicated customer name then Undo the process.
4. If no matching customer name then add the customer name to a customer table and go to new record ready to add a new customer

Put the following code from the logic above under the After Update Event Procedure:

Private Sub CustomerName_AfterUpdate()
Dim NewCustomer as string
Dim stLinkCriteria As String
Dim custNo As Integer
'Assign the entered customer name to a variable NewCustomer
NewCustomer = Me.CustomerName.Value
stLinkCriteria = "[CustomerName] = " & "'" & NewCustomer & "'"
If Me.CustomerName = DLookup("[CustomerName]", "tbl_customer", stLinkCriteria) Then
   MsgBox "This customer, " & NewCustomer & ", has already been entered in database." _
   & vbCr & vbCr & "Please check customer name again.", vbInformation, "Duplicate information"
   Me.Undo   'undo the process and clear all fields
End If
End Sub

For example, the alert message will pop-up after entering a new customer name “John Smith” which is already in the customer table.

If we want to show the record information of the matched customer from the customer table, we need to add Dlookup function and FindRecord command of the matched customer to the previous code follow the Me.Undo commmand as shown below:

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

If you want to show more customers that have a similar name, you can use the search keyword method to show the list of similar name. It was instructed on my another HowTo link here.

Related Video Link:

With these recent exploits for over a decade one expects both the countries to get a full member status in ICC so that they will get a proper recognition which they truly cialis levitra price deserve. Binge consuming disorder is widespread amongst lots of individuals prescription levitra who do not know they’ve this condition. The store wants to save your money and to make life even more buy levitra online http://www.devensec.com/development/Filming_Guidelines_6_3_18.pdf comfortable. Studies have consistently shown that generic tadalafil prices drivers in the age group of 40-45 tends to face the desired effects due to the imporper dosage pattern they take without proper consultation of the doctor.