Look Up existing Record or Add New using Combo Box

[JTSC]

Hi All,

I've got a form for my users to enter customer call reports. The form has a combo box to allow the user to select an existing customer (from a customers table), or to enter a new customer (and presuambly have that customer and customer ID entered into the customer table).

If the user can't find the customer in the como box list (i.e., they will need to add a new customer), how do I have another form open for entering a new customer to the table (I have created a form for this purpose, but can't figure out how to have the combo box open it, if the customer does not already exist int the table.)

Thanks,
JTSC

[ADezii]

@JTSC
The typical approach to adding an Item to the RowSource of a Combo Box is to use the NotInList() Event in conjunction with either DAO or ADO. No Form interface is usually needed, and the Limit To List Property of the Combo Box must be set to 'Yes'. I'll post the code and should you have any questions, please feel free to ask.

Private Sub cboTest_NotInList(NewData As String, Response As Integer)
Dim strMsg As String
Dim MyDB As DAO.Database
Dim rstCustomers As DAO.Recordset
 
strMsg = "'" & NewData & "' is not in the list.  "
strMsg = strMsg & "Would you like to add a New Customer?"
 
If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, "New Customer") Then
  Response = acDataErrDisplay 'Access displays its standard Error Message
Else
  'Decided to Add the New Customer
  Set MyDB = CurrentDb
  Set rstCustomers = MyDB.OpenRecordset("tblCustomers", dbOpenDynaset)
    rstCustomers.AddNew
      rstCustomers("Name") = NewData
    rstCustomers.Update
 
    Response = acDataErrAdded   'Item added to underlying Recordset and the Combo
                                'Box is Requeried, Item added to List
    rstCustomers.Close
    Set rstCustomers = Nothing
End If
End Sub

[JTSC]

Hi ADezii,
Thanks. I put the code in as suggested. Now when i put an entry in the combo box that does not match the list, I do get the prompt "Would you like to add a new customer? If I answer yes, I get the "standard" message "The text you entered isn't an item in the list. Select an item from the list or enter the text that matches one of the listed items".

Here's the code that I entered:

Private Sub Customer_ID_NotInList(NewData As String, Response As Integer)
Dim strMsg As String
Dim MyDB As DAO.Database
Dim rstCustomers As DAO.Recordset
 
strMsg = "'" & NewData & "' is not in the list.  "
strMsg = strMsg & "Would you like to add a New Customer?"
 
If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, "New Customer") Then
  Response = acDataErrDisplay 'Access displays its standard Error Message Else
  'Decided to Add the New Customer
  Set MyDB = CurrentDb
  Set rstCustomers = MyDB.OpenRecordset("Customers", dbOpenDynaset)
    rstCustomers.AddNew
      rstCustomers("CustomerName") = NewData
    rstCustomers.Update
 
    Response = acDataErrAdded   'Item added to underlying Recordset and the Combo
                                'Box is Requeried, Item added to List
    rstCustomers.Close
    Set rstCustomers = Nothing
End If
 
End Sub

I'm not sure what to do next....

Thanks

[ADezii]

@JTSC
Looks like you forgot an 'Else' between Lines 10 and 11.

[NeoPa]

While the concept (with provided code) that ADezii suggests can work well with small, uncomplicated tables where the whole record is included in a ComboBox, it is often necessary to provide a more complete facility from which to add new records. This would be done, as you originally suggested, using a separate form.

ADezii's concept is not so different from this. Essentially, when an item triggers the NotInList event, instead of adding the new record with code from data already entered into the ComboBox, you would open a data entry form from within your code passing any data already entered into the ComboBox. A .Requery would be necessary after the form closes (not after the call to open it).

** Aside **
I'm glad I started this as I have literally just implemented something like this and I put the .Requery after the call to open the form. Seeing this I realised I had to kick myself and fix the bug. Luckily I missed with the kick.

Anyway, moving on. I will include some of the code I used to ensure the Requery was done.

Option Compare Database
Option Explicit
 
'strFrom will contain the name of the form, and optionally, the name of the
'control (if required) separated by a comma (,).
Private strFrom As String
 
Private Sub Form_Open(Cancel As Integer)
    strFrom = Nz(OpenArgs, "")
End Sub
 
...
 
Private Sub Form_Close()
    'Counterintuitively, setting .Dirty to False saves the changes.
    Me.Dirty = False
    'Requery the calling form or control.
    If strFrom = "" Then Exit Sub
    On Error Resume Next
    With Forms(Split(strFrom, ",")(0))
        If UBound(Split(strFrom, ",")) = 0 Then
            Call .Requery
        Else
            Call .Controls(Split(strFrom, ",")(1)).Requery
        End If
    End With
    On Error GoTo 0
End Sub

Welcome to Bytes!