Use NotInList Event to Add a Record to Combo Box

Some time we would like to add new items into Combo Box while it does not previously exists.
There are several’s ways to do that :

1. Using Code to Add a Record to a Table:

With this you need to set LimitToList property of the combo box to Yes and writing into its OnNotInList event as below

Private Sub CustomerID_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_CustomerID_NotInList
If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr & "Do you want to add it?"

If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Please try again."
Else
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Customers", dbOpenDynaset)
'Ask the user to input a new Customer ID.
Msg = "Please enter a unique 5-character" & vbCr & "Customer ID."
NewID = InputBox(Msg)
Rs.FindFirst BuildCriteria("CustomerID", dbText, NewID)
' If the NewID already exists, ask for another new unique
' CustomerID
Do Until Rs.NoMatch
NewID = InputBox("Customer ID " & NewID & " already exists." & _ vbCr & vbCr & Msg, NewID & " Already Exists")
Rs.FindFirst BuildCriteria("CustomerID", dbText, NewID)
Loop
'Create a new record.
Rs.AddNew
'Assign the NewID to the CustomerID field.
Rs![CustomerID] = NewID
' Assign the NewData argument to the CompanyName field.
Rs![CompanyName] = NewData
' Save the record.
Rs.Update
' Set Response argument to indicate that new data is being added.
Response = acDataErrAdded
End If
Exit_CustomerID_NotInList:
Exit Sub

Err_CustomerID_NotInList:
'An unexpected error occurred, display the normal error message.
MsgBox Err.Description
'Set the Response argument to suppress an error message and undo
'changes.
Response = acDataErrContinue
End Sub

2. Using a Form to Add a New Record

You need to reprogram your OnNotInList event of this dropdown list as

Private Sub Des_NotInList(NewData As String, Response As Integer)
 Dim Result
 Dim Msg As String
 Dim CR As String
 CR = Chr$(13)


   ' Exit this subroutine if the combo box was cleared.
 If NewData = "" Then Exit Sub


 ' Ask the user if he or she wishes to add the new customer.
 Msg = "'" & NewData & "' is not in the list." & CR & CR
 Msg = Msg & "Do you want to add it?"
 If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
 ' If the user chose Yes, start the Customers form in data entry
 ' mode as a dialog form, passing the new company name in
 ' NewData to the OpenForm method's OpenArgs argument. The
 ' OpenArgs argument is used in Customer form's Form_Load event
 ' procedure.
DoCmd.OpenForm "frmDestination", , , , acAdd, acDialog, NewData
 Else
Me.Undo
 Response = acDataErrContinue
 Exit Sub
 End If


 ' Look for the customer the user created in the Customers form.
 Result = DLookup("[DesCode]", "dbo_tblDestination", "[DesCode]='" & NewData & "'")
 If IsNull(Result) Then
' If the customer was not created, set the Response argument
 ' to suppress an error message and undo changes.
 Response = acDataErrContinue
 ' Display a customized message.
 MsgBox "Please try again!"
 Else
 ' If the customer was created, set the Response argument to
 ' indicate that new data is being added.
 Response = acDataErrAdded
 End If
 End Sub

And in OnLoad event of the form frmDestination with

[wp_ad_camp_1]

Private Sub Form_Load()
 If Not IsNull(Me.OpenArgs) Then
 ' If form's OpenArgs property has a value, assign the contents
 ' of OpenArgs to the CompanyName field. OpenArgs will contain
 ' a company name if this form is opened using the OpenForm
 ' method with an OpenArgs argument, as done in the Orders
 ' form's CustomerID_NotInList event procedure.
 Me![DesCode] = Me.OpenArgs
 End If
 End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *