Add To Table From Combobox (DAO) (1 Viewer)

Status
Not open for further replies.

Mile-O

Back once again...
Local time
Today, 11:15
Joined
Dec 10, 2002
Messages
11,316
Summary: DAO method for adding an unknown item in a combobox's list to the underlying table

N.B. This article applies to users of the Data Access Objects (DAO) data access method. It is assumed that you are using MS Access 97 to follow this article. If you would like to use the DAO method with MS Access 2000 - or greater - then you may wish to refer to this thread. An ActiveX Data Objects (ADO) method can be found on this thread. Should you have a "User Defined Type not defined" error then refer to this thread.

On occasions whereby you have a set selection displayed within a form's combobox but would allow for the user to add a new option to the underlying table then you can make use of the combobox's NotInList event.

The NotInList event occurs at the moment when the user enters some data into the combobox that isn't already in said control's current list.

Some points to note:
  • This event is only triggered if the combobox's LimitToList property is set to 'Yes';
  • The combobox's RowSourceType must be set to 'Table/Query';
  • The combobox must have a RowSource set to either a table or a query.

Assuming that we have on our form a combobox called MyCombo, the following code will offer the user the option to add the new value into the underlying table and - if accepted - add said value.

Code:
Private Sub MyCombo_NotInList(NewData As String, Response As Integer)
    
    On Error GoTo Err_ErrorHandler
    
    ' provide text constants to reduce text later and allow for faster execution
    ' due to added speed from the compilation of constants
    Const Message1 = "The data you have entered is not in the current selection."
    Const Message2 = "Would you like to add it?"
    Const Title = "Unknown entry..."
    Const NL = vbCrLf & vbCrLf
    
    ' database and recordset object variables
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    ' show message box and evaluate if the user has selected Yes or No
    If MsgBox(Message1 & NL & Message2, vbQuestion + vbYesNo, Title) = vbYes Then
        ' open a connection to the current database
        Set db = CurrentDb
        Set rs = db.OpenRecordset("MyTable")
        ' using the recordset object
        With rs
            .AddNew ' prepare to add a new record
            .Fields("MyField") = NewData ' add unfound data into field
            .Update ' update the table
            .Close ' close the recordset object
        End With
        Response = acDataErrAdded ' confirm record added
    Else
        Me.MyCombo.Undo ' clear the entry in the combobox
        Response = acDataErrContinue ' confirm the record is not allowed
    End If
    
Exit_ErrorHandler:
    ' de-initialise our object variables
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
    
Err_ErrorHandler:
    ' display error message and error number
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_ErrorHandler
    
End Sub
 
Last edited:
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom