CODEWITHSUNDEEP
vbams-accesscombobox
Digital_ecologist
Digital_ecologist

Reputation: 1

How do I allow the addition of a value to a table through a combo box in Microsoft Access?

I'm creating my fist Microsoft Access database. I need combo boxes that allow for selection from a list and also addition of values to the field that the combo box is pulling the selections from.

After a lot of googling, I found this example VBA code.

Private Sub cboMainCategory_NotInList(NewData As String, Response As Integer)

    On Error GoTo Error_Handler
    Dim intAnswer As Integer
    intAnswer = MsgBox("""" & NewData & """ is not an approved category. " & vbcrlf _
        & "Do you want to add it now?" _ vbYesNo + vbQuestion, "Invalid Category")

    Select Case intAnswer
        Case vbYes
            DoCmd.SetWarnings False
            DoCmd.RunSQL "INSERT INTO tlkpCategoryNotInList (Category) "
                & _ "Select """ & NewData & """;"
            DoCmd.SetWarnings True
            Response = acDataErrAdded
        Case vbNo
            MsgBox "Please select an item from the list.", _
                vbExclamation + vbOKOnly, "Invalid Entry"
            Response = acDataErrContinue

    End Select

    Exit_Procedure:
        DoCmd.SetWarnings True
        Exit Sub

    Error_Handler:
        MsgBox Err.Number & ", " & Error Description
        Resume Exit_Procedure
        Resume

End Sub

If I use the unaltered example, it doesn't throw an error, but doesn't let me enter a new value.

I tried to alter the code to fit my database but this block throws a syntax error (I've tried to troubleshoot, but I'm new to VBA, and can't find an appropriate linter).

Option Compare Database

Private Sub Combo26_NotInList(NewData As String, Response As Integer)

    On Error GoTo Error_Handler
    Dim intAnswer As Integer
    intAnswer = MsgBox("""" & NewData & """ is not in the database yet " & vbcrlf _
        & "Do you want to add it now?" _ vbYesNo + vbQuestion, "new chemical")

    Select Case intAnswer
        Case vbYes
            DoCmd.SetWarnings False
            DoCmd.RunSQL "INSERT INTO chemical_IDNotInList (chemical) "
                & _ "Select """ & NewData & """;"
            DoCmd.SetWarnings True
            Response = acDataErrAdded
        Case vbNo
            MsgBox "Please select an item from the list.", _
                vbExclamation + vbOKOnly, "Invalid Entry"
            Response = acDataErrContinue

    End Select

    Exit_Procedure:
        DoCmd.SetWarnings True
        Exit Sub

    Error_Handler:
        MsgBox Err.Number & ", " & Error Description
        Resume Exit_Procedure
        Resume

End Sub

I need a functioning combo box that both lets me select from existing values and add new ones.

Upvotes: 0

Views: 693

Answers (3)

Albert D. Kallal
Albert D. Kallal

Reputation: 48989

Your SQL insert command dont' look right.

Try this:

You can set the limit to yes = to yes.

The, for the no in list event, you can use the following code:

Private Sub Combo33_NotInList(NewData As String, Response As Integer)

   Dim strSql     As String
   If MsgBox(NewData & " not in list, add?", _
             vbYesNo + vbQuestion) = vbYes Then
      strSql = "insert into tblStudents (name) values(" & NewData & ")"
      CurrentDb.Execute strSql
      Response = acDataErrAdded
   End If

End Sub

The insert format is as above - there is no select command.

Note I used a table name of Students, and field name of Sname. So, just change the table name, and the field to whatever you used.

Upvotes: 0

ComputerVersteher
ComputerVersteher

Reputation: 2686

Line continuation is started by a blank in front of an underscore, at the end of the line, not at the beginning.

DoCmd.RunSQL "INSERT INTO tlkpCategoryNotInList (Category) " _
                  & "Select """ & NewData & """;"

or

DoCmd.RunSQL "INSERT INTO tlkpCategoryNotInList (Category) " & _
                  "Select """ & NewData & """;"

Upvotes: 0

June7
June7

Reputation: 21370

MsgBox syntax is missing a comma to separate arguments.

    intAnswer = MsgBox("""" & NewData & """ is not an approved category. " & vbcrlf _
        & "Do you want to add it now?", vbYesNo + vbQuestion, "Invalid Category")

Upvotes: 1

Related Questions