Ok, this is a tough one
I apologize for knowing next to nothing about VB code, but I really need some help here. This is the situation:
I have a table - Customers
I have another table - Key Box Information
They both contain a field called - Customer ID
They are linked by this field
key box info does not have a customer name field, only the customer ID field, and each customer has "key/lock" info attached to their ID
The 'customers' table contains the customer name field
I am having no problems with the relationship between the 2 tables, everything is linked up just fine
Now, what i need to do is create a form called "Add Customer"
When a client clicks on the form, it should pop-up with the Customer Name field from "customers" table and the customer ID field from the "key box information" table. It should also contain the other fields from the Key box info table. I want the client to be able to add a new customer and key info from this form. However, I do not want them to be able to add the customer if the customer already exists in the table. The primary key for the "customers" table is the "customer ID" field. It uses @ the first 5 letters of the customer name field as the ID. I would like the client to be able to type in the "new" customer name. Then, the code should lookup the first 5 letters of said name in the customer ID field of the customer table. If there are no duplicates, the form should allow the customer name to be added to the customers table and use the first 5 letters of it as the customer ID and also add it to the cusotmers table (also filling it in for them in the form). If there is a duplicate, I guess Access should pop-up the customer name(s) with the dupicate customer id value and ask the client if they still want to add the customer name or not. If yes, Access could maybe add a "1" to the first 5 letters or something and still allow the client to continue. If no, Access should close the form. I may be going about this all wrong, and I know this is a novel I'm writing here. I really need some help/advise from anyone with code experience! Help!
I apologize for knowing next to nothing about VB code, but I really need some help here. This is the situation:
I have a table - Customers
I have another table - Key Box Information
They both contain a field called - Customer ID
They are linked by this field
key box info does not have a customer name field, only the customer ID field, and each customer has "key/lock" info attached to their ID
The 'customers' table contains the customer name field
I am having no problems with the relationship between the 2 tables, everything is linked up just fine
Now, what i need to do is create a form called "Add Customer"
When a client clicks on the form, it should pop-up with the Customer Name field from "customers" table and the customer ID field from the "key box information" table. It should also contain the other fields from the Key box info table. I want the client to be able to add a new customer and key info from this form. However, I do not want them to be able to add the customer if the customer already exists in the table. The primary key for the "customers" table is the "customer ID" field. It uses @ the first 5 letters of the customer name field as the ID. I would like the client to be able to type in the "new" customer name. Then, the code should lookup the first 5 letters of said name in the customer ID field of the customer table. If there are no duplicates, the form should allow the customer name to be added to the customers table and use the first 5 letters of it as the customer ID and also add it to the cusotmers table (also filling it in for them in the form). If there is a duplicate, I guess Access should pop-up the customer name(s) with the dupicate customer id value and ask the client if they still want to add the customer name or not. If yes, Access could maybe add a "1" to the first 5 letters or something and still allow the client to continue. If no, Access should close the form. I may be going about this all wrong, and I know this is a novel I'm writing here. I really need some help/advise from anyone with code experience! Help!
