Home  Fees/Services  Access Examples  Tutorials  Access Download  Articles  Search  Contact  Privacy  Links

Access Form Programming Tutorial
Not In List Event

Form Subtotal Fields
Master/Detail Forms
NotInList Event
Speed Up Forms
Unbound Form
Change Textbox Height
Get Website Data
Access Audit Trail

Form Query Parameter

Not In List Example

Access Visual Basic Not In List Event Code Example

Here are explained several examples of simple and complex combo box not in list event handling.

Microsoft Access is a fully capable database and it's primary limitations is the number of concurrent users it will support. The number of users supported depends on the transaction load and the amount of data but generally we recommend a limit of 10 to 15 users.

We create databases large and small. Some of our databases help run entire small businesses including travel agencies and airline reservation systems. We also handle unique businesses such as fly rod manufacturing. Did you know there are 11 steps in the making of a fly rod?

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:

Not In List

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

NotInList dropdown control example

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

Access VBA Not In List Example

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"


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.

Not In List Event Programming

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

Access notinlist code example

A Blue Claw Database Design Template:

Student Grant Program Administration Software

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_ID = Me.Account_ID
Form_F_Trds_Unbnd.Accnt_Name_Entry = Me.Account_ID
Form_F_Trds_Unbnd.Accnt_Number_Combo = Me.Account_ID
Exit Sub

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.

Access NotInList Combo Box

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 = DLookup("Drug_id", "L_Drugs", "Drug='" & newdata & "'")
DoCmd.GoToControl "Dosage"


A Blue Claw Software Design Template:

Airline Reservations Database Template

A Blue Claw Database Design Article:

Learn How To Select A Consultant

Microsoft Office Forms:
 MS Access 2003
 Access 2007
 Access 2010
 Access 2013

Contact Information

Copyright 2000-2017 Blue Claw Database Design