Not In List Example

Here are explained several examples of simple and complex combo box not in list event handling. There are times when you'll need more advanced techniques to handle lists that are not a single-column dropdown list or combo box.

In our NotInList example we have a bond trading data entry application and we want to be able to add new customer records on-the-fly.  Here is the setup for the not in list event example:

We are about to enter a new accnt name that is not in the dropdown list:

Amce Bonds Inc is the new acct and, as you can see, isn't in our lookup list.  The NotInList event is triggered below:

The code that pops up the Add New Account confirmation and handles the response is show below: Note that there really isn't any reason why we have this split into two subroutines... just how it got coded.

Private Sub Acct_Name_Entry_NotInList(newdata As String, Response As Integer)
Response = acDataErrContinue
Call Acct_Name_Not_Found(newdata)
End Sub

Public Sub Acct_Name_Not_Found(newdata)
Dim ans As Variant
' new acct
gbl_exit_name = False

ans = MsgBox("Do you want to add this acct?", _
vbYesNo, "Add New acct?")

If ans = vbNo Then
Me.Acct_Name_Entry = Null
DoCmd.GoToControl "acct_name_entry"
GoTo exit_it
End If

' add acct
DoCmd.OpenForm ("f_accts_add")
Form_F_accts_Add.acct_Name = newdata

Me.acct_Name_Entry = Null

DoCmd.GoToControl "acct_number"

exit_it:

End Sub

Below is the little popup that has the three fields that we need to enter for a new account - Accnt #, Accnt Name (automatically filled in by the VB code), and Coverage.

Below is our filled-in form opened with the not in list VBA code ready to press the Save Account button.

Here is the VBA code behind the Save Account button.  You can see how it save the record; requeries the combo boxes on the main form; fills in the newly entered values in the the main form and finally sets the focus to the Asset Class field.

Private Sub Command8_Click()
On Error GoTo Err_Command8_Click

DoCmd.RunCommand acCmdSaveRecord
Form_F_Trds_Unbnd.Accnt_Name_Entry.Requery
Form_F_Trds_Unbnd.Accnt_Number_Combo.Requery
'
Form_F_Trds_Unbnd.Accnt_ID = Me.Account_ID
Form_F_Trds_Unbnd.Accnt_Name_Entry = Me.Account_ID
Form_F_Trds_Unbnd.Accnt_Number_Combo = Me.Account_ID
DoCmd.Close
Form_F_Trds_Unbnd.Asset_class_Combo.SetFocus
Exit_Command8_Click:
Exit Sub

Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click

End Sub

Here is the main form again ready to continue data entry after handling the not in list event.

Below is the visual basic code used to immediately add the new list item to the lookup table:

Private Sub Combo20_NotInList(newdata As String, Response As Integer)
Response = acDataErrContinue
Call Drug_Not_Found(newdata)
End Sub

Public Sub Drug_Not_Found(newdata)
Dim ans As Variant
' new drug name
gbl_exit_name = False

ans = MsgBox("Do you want to add this drug?", _
vbYesNo, "Add New drug?")

If ans = vbNo Then
  Me.Combo20 = Null
  DoCmd.GoToControl "dosage"
  GoTo exit_it
End If

' add drug name
DoCmd.RunSQL ("INSERT INTO L_Drugs ( Drug ) SELECT '" & newdata & "'")
Me.Combo20 = Null
Me.Combo20.Requery
Me.Combo20 = DLookup("Drug_id", "L_Drugs", "Drug='" & newdata & "'")
DoCmd.GoToControl "Dosage"
exit_it: