Home Code

VBA > Form > combo box NotInList

Add a choice to a combo box list by adding a record to the table for the rowsource in the NotInList event of the combo box.

VBA for combo box NotInList event to add record to a table with new data.

video tutorial

Explains how the code works

YouTube: Add New Data with NotInList VBA for Combo box (7:24)

Code

Code behind form that calls combo_NotInList

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ CategID_NotInList
Private Sub CategID_NotInList( _ 
   NewData As String,_ 
   Response As Integer) 
'200718 strive4peace, 200916

   'call combo_NotInList
   'Pass table name, field name, NewData, and Response   
   
   Call combo_NotInList( _ 
      "Category", "Category", NewData, Response) 
   
End Sub 

Standard Module

'  module: mod_combo_NotInList_s4p
'*************** Code Start *****************************************************
' Purpose  : Called by combo NotInList event to add record to table
' Author   : crystal (strive4peace)
' License  : below code
' Code List: www.MsAccessGurus.com/code.htm
'          : http://msaccessgurus.com/VBA/Code/Combo_NotInList.htm
'-------------------------------------------------------------------------------
'                              combo_NotInList
'-------------------------------------------------------------------------------
Public Sub combo_NotInList( _ 
   ByVal psTablename As String _ 
   ,ByVal psFieldname As String _ 
   ,ByVal NewData As String _ 
   ,ByRef Response As Integer) 
'200718 strive4peace, 200904,15
   
   'set up Error Handler
   On Error GoTo Proc_Err 
   
   Dim sSQL As String _ 
   ,sMsg As String 

   'initialize response to error
   Response = acDataErrContinue 
   
   'Ask if user wants to add a new item
   sMsg =  """" & NewData _ 
      &  """ is not in the current list. " _ 
      & vbCrLf & vbCrLf _ 
      &  "Do you want to add it? " _ 
   
   'if the user didn't click Yes, then exit
   'so user can change whatever they typed
   If MsgBox(sMsg,vbYesNo, "Add New Data") <> vbYes Then 
      GoTo Proc_Exit 
   End If 

   'SQL statement to add record to psTablename
   'set psFieldname = "NewData"
   sSQL =  "INSERT INTO [" & psTablename &  "] " _ 
      &  "([" & psFieldname &  "])" _ 
      &  " SELECT """ & NewData &  """;"

Debug.Print sSQL  'comment or remove later
   
   With CurrentDb 
      'run the SQL statement
      .Execute sSQL 
      'if a record was added, set Response
      If .RecordsAffected > 0 Then 
         'set response to data added
         Response = acDataErrAdded 
      End If 
   End With 
   
Proc_Exit: 
   Exit Sub 
   
Proc_Err: 
   MsgBox Err.Description,,_ 
     "ERROR " & Err.Number _ 
     &  "   combo_NotInList"
   Resume Proc_Exit 
   Resume 
End Sub 
' LICENSE
'   You may freely use and share this code
'     provided this license notice and comment lines are not changed;
'     code may be modified provided you clearly note your changes.
'   You may not sell this code alone, or as part of a collection,
'     without my handwritten permission.
'   All ownership rights reserved. Use at your own risk.
'   ~ crystal (strive4peace)  www.MsAccessGurus.com
'*************** Code End *******************************************************

Keyword and comments in code were colored with this free Color Code add-in

Goto Top