Open new form to create New ID for customer

[mlrucci]

Good afternoon,
I am a bit of a novice to access and I am having difficulty creating a new customer when it doesn't exist. This is how I have it laid out. I have a form in which an employee enters a CustomerID. The field is one to many relationship. When the customerID is entered on the original form, there is an Dlookup for the rest of the customer information on the original form. If an employee enters the CustomerID on the original form, and the Dlookup is blank, this means the customer does not exist. I would like it to work that if the customer does not exist, to open a new form in order to create that customer and input the information needed. I want the customerID they inputted on the original form to auto populate the second form. There have been incidences where they have inputted the wrong number on the second form and I am trying to avoid this by auto populating the second form with the orginial form's customerID. The customerID on the table is the Primary key and is set to number not auto number. Any help would be appreciated. Thank you

[Micron]

First, how do you control the process so that a Customer ID typo doesn't result in a duplicate of what is really the same customer? Combo box or list box of customers? If it's not in the list, not only do you know that it doesn't exist, you pretty much prevent duplicates, plus it gives you a launching point for creating new. However, if you must use the current method, you can base your new customer form on tblCustomers and open the form in Data Entry mode. Either reference the still open 1st form that contains the id input, or if you're launching this from the 1st form, pass that value as an Open Args value (using the DoCmd.OpenForm method). You could also use variables that have the proper scope, but I'd shy from using variables that might be altered by subsequent actions in this case. That's just my preference. When the new cust form opens, I'd hide 1st form to prevent any altering of the id while 2nd form is open, then show 1st form when the customer record is saved. When the 2nd form is being unhidden, add a requery statement for the 1st form.

EDIT: if you have a customer id combo, you can allow list edits and use the Not In List event to handle adding an item to the list, but you might find that a bit buggy or convoluted.
Also, you might want to use the customer form Close event to save the record (via an intuitive command button such as Close & Save) so that this form can't be altered when you move back to the 1st form, which could happen if the customer form is still open. How are you handling customer data edits, such as change of address? Would think that you already have a customer form because of the possibility. If not,when you create one, use the same form for editing as you would for adding. You just have to not open it in Data Entry mode for edits.

[June7]

Use combobox NotInList event. Common topic. Review https://www.accessforums.net/showthr...ight=notinlist

DLookup is least efficient approach to display the related customer info. Better options:

1. multi-column combobox with textboxes referencing combobox columns

2. include the customer table in the form RecordSource (join type 'include all records from data table and only those from Customers that match'), textboxes bound to Customers fields set as Locked Yes and TabStop No.

[mlrucci]

Thank you for your responses, but I think I am more confused than when I started. I currently have a table with all the pertinent information in tblAudit. Within tblAudit, I have the CustomerID linked with one to many in a relationship. The PK is Auto Number in the tblAudit. I then have a table with the customer's information tblCustomer. The CustomerID in the tblCustomer is a PK with Number field set to no duplicates. This is how I do not come back with a duplicate. (Long story to explain the customer numbering system, but I cannot have the DB do an automatic numbering system. Just think of it like a Medical Record Number..Never duplicated) I am currently using the following VBA

Private Sub txtCustomerID_Exit(Cancel As Integer)
DoCmd.OpenForm "frmCustomer", , , "[CustomerID]=" & Me.txtCustomerID
End Sub

It will open the form, but will not place the CustomerID from form1 into form2 automatically. I currently have 25,000 customers, not appropriate to have a list or a combo. The Dlookup helps to show the information needed and if the CustomerID exists or needs to be created. I hope this clarifies what I am trying to do.

Thank you

[June7]

The code you show is constructing filter criteria. If this is a new ID, then there is nothing for the filter to work with because that ID does not yet exist in tblCustomer. And if CustomerID is a text type field, need apostrophe delimiters.

If IsNull(DLookup("CustomerID", "tblCustomer", "CustomerID='" & Me.txtCustomerID & "'")) Then
DoCmd.OpenForm "frmCustomer", , , , acFormAdd, acDialog, Me.txtCustomerID
End If

Then code in frmCustomer Current event:

If Me.NewRecord Then
Me!CustomerID = Me.OpenArgs
End If

And you might find of interest:
http://allenbrowne.com/ser-32.html
http://allenbrowne.com/AppFindAsUType.html

[mlrucci]

June7,
Thank you for your response. It makes a lot of sense about the filter. I reviewed the articles concerning the combo box, but would like to stick with the current format. I attempted to place the above code into my on exit from the CustomerID field in my main form. It appears that there were too many commas and was giving me an error. I fixed this error and placed the second code on the second form on current. When I attempted to exit the CustomerID on the first form, I received the run-time erro 2498. "An expression you entered is the wron data type for one of the arguments." I thought it might have been that I originally had the PK in tblCustomer Data Type set as number and the FK in the tblAudit Data Type was set as Text. I corrected the Data Type, but it did not fix my problem. Any suggestions. I am stuck. Thank you

[June7]

yes, sorry, one two many commas typo. Edited the post.

I would use AfterUpdate event, not Exit. Never have used Exit. Because what if user exits without entering a value?

So what data type did you change to? If it is number then remove the apostrophe delimiters.

[mlrucci]

June7,
First, I would like to thank you for your time and response. It is greatly appreciated! I changed the Data Type to Number. I am not using Dlookup for the CustomerID. When the employee places the CustomerID on the original form, there is a Dlookup for the Customer Information (First Name, Last Name, DateOfBirth). If the Dlookup is null then I want to open the CustomerAdd form. I have removed what I think is the

apostrophe delimiters and have tried to use one of two different vba codes and nothing happens. I added refresh on exit to see once a record is made, then it would make a difference. It does nothing! I know I am doing something wrong, just too new to understand where. Suggetions?

This is placed on the After Update on the form:

Private Sub Form_AfterUpdate()
If IsNull(DLookup("CustomerID", "tblCustomer", "CustomerID=" & Me.txtCustomerID & "")) Then
DoCmd.OpenForm "frmCustomerAdd", , , , acFormAdd, acDialog, Me.txtCustomerID
End If
End Sub

Private Sub Form_AfterUpdate()
If IsNull(DLookup("LastName", "tblCustomer", "CustomerID=" & Me.txtCustomerID & "")) Then
DoCmd.OpenForm "frmCustomerAdd", , , , acFormAdd, acDialog, Me.txtCustomerID
End If
End Sub

[June7]

No, textbox AfterUpdate.

And don't concatenate empty string.

If IsNull(DLookup("CustomerID", "tblCustomer", "CustomerID=" & Me.txtCustomerID)) Then

[mlrucci]

Thank you for all your help. I had multiple problems.
1. I had different Data Types in the tables. Corrected to be Numbers
2. I was trying to make this work on a form that had already been created. For this to work, I had to start with a new entry into the main form, and then it would open the secondary form, frmAddNewCustomer
3. I put the after update on the form and not on the txt box.

Thank you again for your help!