Not in list problem

[DanW]

I'm having a problem in exiting a combo box used for choosing the customer's record if they are not in the customer table.

The customer table has an autonumber and required fields for an alphanumeric ID, last name, first name, and date of birth.



The combo box is unbound and the row source is a query based on the above fields that is column bound to the autonumber and displays the alphanumeric ID, with the dropdown displaying the rest to make sure we get the right customer.

The problem is that if the desired customer is not in the table, I either have to pick another customer that is in the customer table before I can go to another form to add the new customer to the customer table, or I get the required data error messages for the combo box and the bound text boxes for LastName, FirstName and DOB.

What is the elegant (non-embarrassing) way to do this?

[RuralGuy]

Why not use the NotInList event of the ComboBox to bring up your "add a new Customer" form?

[DanW]

This is what I placed in the ComboBox NotInList event procedure code.

Private Sub cboCustomerID_NotInList(NewData As String, Response As Integer)
On Error GoTo MyErrorHandler
DoCmd.OpenForm "frmNewCustomer", acNormal
DoCmd.Close acForm, "frmCustomers", acSaveNo
Exit Sub
MyErrorHandler:
Dim Msg As String
Msg = Err.Number & ": " & Err.Description
MsgBox Msg
End Sub

This does take me to the frmNewCustomer, but doesn't close the frmCustomers with the following error message.

"2501: The Close action was canceled " When I click OK the frmCustomers closes and the following new error message appears.

"The text you entered isn't an item in the list. Select an item from the list or enter text that matches one of the listed items"

When I click the error message OK, the message box goes away and I am where I want to be with an open frmNewCustomer.

How do I change the code?

[RuralGuy]

The NotInList event is used when you want to add to the current selection in a ComboBox and continue on in the same form. It lets you "jump" to a form that can add your new data to a table and then close that form and have the new value selected in the first form. It sounds like you just want to go to a new form and abandon whatever changes you have made in the first form. Is that correct?

[DanW]

I would like to jump to an addnewdata form and then resume the first form, so you have me going in the right direction with a NotInList event. I had just gotten so frustrated with ignorant trial and error that I was settling for the latter abandon required field/orphan control approach.

We could deal with error messages as long as the tables don't get corrupted, this is for small business personal use. I just knew our other database software have made this work seamlessly, and adding a new customer is a very basic task, so that says a lot about me.

Thanks in advance.

[RuralGuy]

Try this code:

Private Sub cboCustomerID_NotInList(NewData As String, Response As Integer)
   On Error GoTo MyErrorHandler
   '-- We may need to add another Customer
   If MsgBox("[" & NewData & "] is not yet a Customer..." & vbCr & vbCr & _
             "Would you like to add a New Customer to your DataBase?", vbYesNo) = vbYes Then
      '-- Open the "Add a new Customer" form.
      DoCmd.OpenForm "frmNewCustomer", , , , , acDialog, NewData
      Response = acDataErrAdded
   Else
      Response = acDataErrContinue
   End If
Exit_cboCustomerID_NotInList:
   On Error Resume Next
   Exit Sub
MyErrorHandler:
   MsgBox Err.Number & " : " & Err.Description
   Resume Exit_cboCustomerID_NotInList
End Sub

If you get this running properly we can add code to the frmNewCustomer that puts the passed OpenArgs in the right control automatically.

[DanW]

and this code almost works. It just doesn't repopulate my bound controls. So if there is some additional code, I would appreciate it. I am trying to requery my other bound controls to required fields that were entered in the AddCustomer form and did arrive in tblCustomers.

Dim strType As String, strWhere As String
strtype = NewData
strWhere = "[CustomerID] = """ & strType & """"
If vbYes = MsgBox("CustomerID " & NewData & " is not in the customer list. " & "Do you want to add this Customer ID?", vbYesNo + vbQuestion + vbDefaultButton2, gstrAppTitle) Then
DoCmd.OpenForm "frmAddCustomer", DataMode:=acFormAdd, WindowMode:=acDialog, OpenArgs:strType
If IsNull (DLookup("CustomerID", "tblCustomers", strWhere)) Then
MsgBox "you failed to add a new Customer that matched what you entered." & "Please try again.", vbInformation, gstrAppTitle
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
Else
Response = acDataErrDisplay
End If
End Sub

I also added the following code to the form Load event for frmAddCustomer

Private Sub Form_Load()
Me.CustomerID = Me.OpenArgs
End Sub

After this beautiful for me code runs, I am left in frmCustomers with the right CustomerID in the ComboBox, but the previous customers LastName, FirstName, and DateOfBirth. The tblCustomers does have the LastName, FirstName and DateOfBirth for the new added Customer.

I have to close frmCustomers, reopen it and key in the added CustomerID to get the LastName, FirstName, and DateOfBirth controls to populate correctly.

I have tried requering the text boxes without help.

This is so close, and very elegant, if I could just get the bound controls to populate.

Hope you can help. Thanks in advance.

Dan
After the code has run

[DanW]

Sorry RG

Thanks for your code and post while I was entering mine slowly from my laptop to desktop while working and didn't check for your post before submitting mine.

Will substitute and post back

Dan

[DanW]

Eureka!!

But actually you found it. Your code populates the bound controls

I'm past the bottleneck

This thread is solved

Thanks so much

Dan

[RuralGuy]

Excellent! Glad we could help.

[DanW]

RG's NotInList code added the new cboCustomerID(value is a PK autonumber; displays CustomerID number), txtLastName, txtFirstName, and txtBirthday values from what I entered in dialog box of frmNewCustomer to my main frmCustomers, both forms recordsourced to tblCustomers.

However, I've just realized that it was replacing the data from my oldest record (first autonumber) for those fields rather than starting a new record. This was obvious when I added data to other nonrequired textboxes and the same data displayed for a new customer and tblCustomers showed the first record was modified with the new ID number, name and DoB.

So I added to frmNewCustomer:

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec

which forces the new data to a new record, but when the NoInList code is finished, my cboCustomerID box has the new ID, and the new customers names and DoB data is present in my tblCustomers, but the frmCustomers txtLastName, txtFirstName, and txtDoB controls are empty, even though they are bound to those filled fields in tblCustomers. So I'm kind of back to the beginning with bound controls populating.

Interestingly, I can get all old customers to populate the text boxes correctly when I enter and select their ID from cboCustomerID, but I cannot get the recently added new customers data to populate the text boxes, only cboCustomerID will have data.

I have found two ways to get the bound text boxes to populate for the new Customer. I either have to close frmCustomers and reopen it and enter the ID in cboCustomerID and select it, or I have to add another new customer, which means that the previous new customer gets treated like an old customer and will populate the text boxes properly.

It seems that I need code in the frmCustomers cboCustomerID NotInList event to close frmCustomers and reopen it with the new record added in frmNewCustomer. My attempts have failed and most led to loops in a dialog box.

Appreciate your help and your code, Dan

[RuralGuy]

You can make the frmNewCustomer go to a new record every time by simply making the following change to the code I posted:

Private Sub cboCustomerID_NotInList(NewData As String, Response As Integer)
   On Error GoTo MyErrorHandler
   '-- We may need to add another Customer
   If MsgBox("[" & NewData & "] is not yet a Customer..." & vbCr & vbCr & _
             "Would you like to add a New Customer to your DataBase?", vbYesNo) = vbYes Then
      '-- Open the "Add a new Customer" form.
      DoCmd.OpenForm "frmNewCustomer", , , , acFormAdd, acDialog, NewData
      Response = acDataErrAdded
   Else
      Response = acDataErrContinue
   End If
Exit_cboCustomerID_NotInList:
   On Error Resume Next
   Exit Sub
MyErrorHandler:
   MsgBox Err.Number & " : " & Err.Description
   Resume Exit_cboCustomerID_NotInList
End Sub

[DanW]

That does work to put the new customer ID in a new record.

Is there any code to allow my main form to start working with the new customer data immediately? When I am done with the NotInList event I have the cboCustomerID control filled, but cannot get txtLastName, txtFirstName, and txtDoB to populate no matter how many times I reenter the new customer's ID number. To get it to work with the new customer, I have to either close the main form, reopen it and then enter the new customer ID, or to go throught the NotInList event again with another customer. Then the previous new customer can be selected and populate the form.

Seems like I need code to close and reopen my main form (or an equivalent save event) and select the record associated with the NewData string of the NotInList event.

Is that the right way to go? If so, I would appreciate your help with the code. I cannot get the NotInList event to do the close/reopen.

Thanks for your help RG. I wouldn't be this far tidying up loose ends without your help.

Dan

[RuralGuy]

Are you closing the frmNewCustomer to get back to the first form? The code I supplied will requery the ComboBox when it get control which should satisfy your first form. You should be able to work with the new record immediately.

[RuralGuy]

Maybe it is time to post your db so we can look at it; removing any sensitive data of course.