I am trying to build a form that allows clients to enter new customer info to a customer table.
The form is based on a query that pulls info from 2 tables. One table id "Customers" and one table is "Key Box Information"
I have built a query, "Customers_Keybox Info" that pulls info from both tables. I have a one-to-many relationship between the 2 tables, so the query runs fine and allows updates/additions, no problem.
Here's what my form does so far:
My client enters what they think is a new Customer name in the Customer name field of the form. On the "on lost focus" event of customer name, I have set the following code to run:
Private Sub Customer__Name_LostFocus()
Dim vTruncate
Dim TestIt As Variant
vTruncate = 4
Forms![Add New Customer and Key Information]![Customer ID] = Left(Customer__Name, vTruncate)
End Sub
This code causes a 4-digit Customer ID to automatically be entered into the customer ID field of the form (and the table)
This code runs without a hitch, and puts the Customer ID in the Customer ID field.
The problem with my form arises when the customer ID is already in the Customers table. Say I already have a customer, Microsoft, and a client wants to add a new customer, Microtech. When they enter the new customer the same Customer ID, micr, is generated, resulting in a generic, duplicates error message, and Access says it cannot update the record, etc. What I need help with is generating some code that sees the duplicate problem AFTER the customer ID field has been updated and tells the client BEFORE giving a generic, duplicate error message, that the customer is possibly already in the table. Then Access should show the customer what it thinks is a duplicate entry (entries) and give the customer the chance to tell it if this is a duplicate (another MicroSOFT entry) or an actual new customer (a MicroTECH entry). If the customer decides to add the customer, Access should add a number to the existing 4-letter Customer ID field. Thus, making MicroTech have a customer ID of "micr1". Later, if a client decides to add a customer "Microinnovations" then Access should pop up "Microsoft" and "microtech" as possible duplicates, etc. See what I mean? And, if the client IS trying to add a duplicate customer, they should be able to choose not to add the customer after all and cancel the whole thing. This sounds like it could be a workable form to me. Can anyone help me with the code? Anyone have any other possible solutions?
The form is based on a query that pulls info from 2 tables. One table id "Customers" and one table is "Key Box Information"
I have built a query, "Customers_Keybox Info" that pulls info from both tables. I have a one-to-many relationship between the 2 tables, so the query runs fine and allows updates/additions, no problem.
Here's what my form does so far:
My client enters what they think is a new Customer name in the Customer name field of the form. On the "on lost focus" event of customer name, I have set the following code to run:
Private Sub Customer__Name_LostFocus()
Dim vTruncate
Dim TestIt As Variant
vTruncate = 4
Forms![Add New Customer and Key Information]![Customer ID] = Left(Customer__Name, vTruncate)
End Sub
This code causes a 4-digit Customer ID to automatically be entered into the customer ID field of the form (and the table)
This code runs without a hitch, and puts the Customer ID in the Customer ID field.
The problem with my form arises when the customer ID is already in the Customers table. Say I already have a customer, Microsoft, and a client wants to add a new customer, Microtech. When they enter the new customer the same Customer ID, micr, is generated, resulting in a generic, duplicates error message, and Access says it cannot update the record, etc. What I need help with is generating some code that sees the duplicate problem AFTER the customer ID field has been updated and tells the client BEFORE giving a generic, duplicate error message, that the customer is possibly already in the table. Then Access should show the customer what it thinks is a duplicate entry (entries) and give the customer the chance to tell it if this is a duplicate (another MicroSOFT entry) or an actual new customer (a MicroTECH entry). If the customer decides to add the customer, Access should add a number to the existing 4-letter Customer ID field. Thus, making MicroTech have a customer ID of "micr1". Later, if a client decides to add a customer "Microinnovations" then Access should pop up "Microsoft" and "microtech" as possible duplicates, etc. See what I mean? And, if the client IS trying to add a duplicate customer, they should be able to choose not to add the customer after all and cancel the whole thing. This sounds like it could be a workable form to me. Can anyone help me with the code? Anyone have any other possible solutions?
