Using a Combo Box's NotInList event to add data

Using a Combo Box's NotInList event to add data

The idea of using a Combo Box in a Form is to limit the data that the user may select from. However, if the data isn't already present in the underlying Table, then you must present the user with an easy way to add new items. There are two different methods of doing this, depending on what other data is required.

Adding a single field

If the table only has two fields, for example the Primary Key and another field, then you may just want to use either a Recordset or a SQL Insert statement to add the new data, after the user has confirmed this (note that it is always a good idea to get the user to confirm this, as they might have spelt the data wrongly).

Private Sub cboCategoryID_NotInList(NewData As String, Response As Integer)
    Dim db As Database
    Dim strSQL As String
    If vbYes = MsgBox("'" & NewData & "' is not a current Category." & vbCrLf & "Do you wish to add it?", vbQuestion + vbYesNo, " ") Then
        Set db = DBEngine(0)(0)
        strSQL = "INSERT INTO [Categories] ([Category Name]) VALUES('" & NewData & "');"
        db.Execute strSQL
        Response = acDataErrAdded
        Set db = Nothing
    Else
        Response = acDataErrContinue
    End If
End Sub

Adding multiple fields

If the table has multiple fields that are needed to process the current action, then you will need to open a form to allow the user to add the data. This could be the same form that is used to add the data normally.

Private Sub cboSupplierID_NotInList(NewData As String, Response As Integer)
    Dim db As Database
    Dim rs As Recordset
    Dim lngSupplierID As Long
    If vbYes = MsgBox("'" & NewData & "' is not a current Supplier." & vbCrLf & "Do you wish to add it?", vbQuestion + vbYesNo, " ") Then
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset("SELECT * FROM [Suppliers] WHERE 1=2;")
    With rs
            .AddNew
            ![company name] = NewData
            lngSupplierID = ![Supplier ID]
            .Update
        End With
        rs.Close
        Set rs=Nothing
        Set db=Nothing
        DoCmd.OpenForm "Suppliers", , , "[Supplier ID]=" & lngSupplierID
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
End Sub

Note that in this example I use a Recordset to add the new data in the first form, and so am able to get the unique value for this record, SupplierID, to pass in the Where clause for the OpenForm method. In order to to limit the records that are returned by this Recordset, I use a where clause of 'WHERE 1=2' which will never return any records.

Top

Last modified at 06/06/2006 14:55:20