Home  Fees/Services  Access Examples  Tutorials  Access Download  Articles  Search  Forums  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.



Blue Claw Database Design is your premier source of Microsoft Access programming and database support. Both small business and department-level databases. Desktop installation as well as cloud/internet based solutions.

With our new partnership with Jay McCormick we have extensive programming support in SQL/Server database, MS Access software, Power BI applications, .Net web development, PHP, Excel & Outlook customization.


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"

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.

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:

Lease Contract Admin 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_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.

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

 









A Blue Claw Software Design Template:

Investment Account Management Software
 








A Blue Claw Database Design Article:

Repair Corrupted Access Table
 









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




Contact Information

Copyright 2000-2017 Blue Claw Database Design