X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Combo Box Code, Don't Understand Errors..., Access 2016    
post Mar 6 2018, 03:37 PM

Posts: 1
Joined: 5-March 18

I need to be able to add a new record to a combo list if it doesn't exist in the current list. The idea is to add the new item to the data's original table when it is typed into the combo box and not found in the list.

I did a search for sample code and found this, but I am getting errors when it tries to run. I attached the code as an image below. What is wrong with it?

Any help and suggestions are welcome!

This is also part of the code, I think. I do not understand VBA. AT ALL.

On Error GoTo Err_Append2Table
' Purpose:   Append NotInList value to combo's recordset.
' Assumes:   ControlSource of combo has the same name as the foreign key field.
' Return:    acDataErrAdded if added, else acDataErrContinue
' Usage:     Add this line to the combo's NotInList event procedure:
'                Response = Append2Table(Me.MyCombo, NewData)
    Dim rstDynaset As Recordset
    Dim sMsg As String
    Dim vField As Variant      ' Name of the field to append to.

    Append2Table = acDataErrContinue
    vField = cbo.ControlSource
    If Not (IsNull(vField) Or IsNull(NewData)) Then
        sMsg = "Do you wish to add the entry " & NewData & " for " & cbo.Name & "?"
        If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK Then
            Set rst = CurrentDb.OpenRecordset(cbo.RowSource)
                rst(vField) = NewData
            Append2Table = acDataErrAdded
        End If
    End If

    Set rst = Nothing
    Exit Function

    MsgBox "Error " & Err.Number & ": " & Err.Description, vbInformation, "Append2Table()"
    Resume Exit_Append2Table
End Function

I think the error is coming from this....

Attached File  2018_03_06.png ( 42.92K )Number of downloads: 8

This post has been edited by gabbie747: Mar 6 2018, 03:41 PM
Go to the top of the page
post Mar 6 2018, 03:52 PM

UtterAccess Editor
Posts: 18,201
Joined: 29-March 05
From: Wisconsin



Your screenshot is definitely showing the problem. If you had copied THAT code into your post instead of taking a screenshot, it would have been easier to do this, but here goes....

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

Notice how you are adding semicolons where they don't belong? Also, the underscore is meant to be a line continuation mark, which comes in handy so one very long line of code doesn't go off the right side of your monitor. These two things are exactly the same:

    strTest = "The quick brown fox jumps " & "over the lazy dog."

    strTest = "The quick brown fox jumps " & _
              "over the lazy dog."

Also, I'd recommend you take a look at our Wiki article on the Combobox NotInList event, which shows (in my opinion) a nice an simple way to achieve the same thing, in the first example, but it doesn't use a Recordset.

Hope this helps,


(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    19th March 2018 - 01:36 PM