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

Status
Not open for further replies.

Mile-O

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

N.B. This article applies to users of the ActiveX Data Objects (ADO) data access method. It is assumed that you are using MS Access 2000 - or greater - to follow this article. Users of MS Access 97 are advised to use the Data Access Objects (DAO) method described 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
    
    ' conenction and recordset object variables
    Dim cn As Connection
    Dim rs As ADODB.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 connection object
        Set cn = CurrentProject.Connection
        ' initialise the recordset object
        Set rs = New ADODB.Recordset
        ' using the recordset object
        With rs
            .Open "MyTable", cn, adOpenStatic, adLockPessimistic ' open it
            .AddNew ' prepare to add a new record
            .Fields("MyField") = NewData ' add unfound data into field
            .Update ' update the table
            .Close ' close the recordset connection
        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 cn = 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