Cannot add new item to list

Leave what you have pasted in the not in list and cut everything after 'End Sub 'Auto Generated'

Then, in your 'ModMapping' module (or your new module, doesn't matter which) paste the following exactly as it is. (do not paste this line... it is an instruction to you)

'Add new to combo list

Public Function comboboxAdd(NewData As String, _

                strTable As String, strField As String, strtitle As String) _

                As Integer

Dim rs As DAO.Recordset

Dim StrSql As String

Dim StrMessage As String

Dim intResponse As Integer

    'Set up message box to confirm

    StrMessage = "'" & NewData & "' was not found " & _

                         Chr(10) & "Do you want to add this to the list?"

   ' strtitle = "Surname not Found"

    If MsgBox(StrMessage, vbYesNo + vbInformation, strtitle) = vbYes Then

        Set rs = CurrentDb.OpenRecordset(strTable, dbOpenDynaset)

        rs.AddNew

        rs.Fields(strField) = NewData

        rs.Update

        intResponse = acDataErrAdded

        ' Clean up

        rs.Close

        Set rs = Nothing

    Else

        ' response is no, so show list for another selection

        intResponse = acDataErrContinue

    End If

    comboboxAdd = intResponse

End Function

Not in list will look like:

Mod mapping function will now look like:

OK SMarshall, I tried doing exactly as you said. Here are the steps I followed:

Highlighted the locations field in my "Asset List" form. Went into "Form Design" mode. Clicked on the "event" tab in the Property Sheet. Now where it says "On Not In List" it says [Event Procedure]. I clicked on that and it opens VBA. This is what VBA now looks like:

I then clicked on "CTRL-S" to save my database. I go back into Access, click on "New Asset" and type in the location box something that is not in there and it says "The text you entered isn't an item in the list. Select an item from the list, or enter text that matches one of the listed items". 

So I'm obviously missing something here. I think I'm following your instructions very carefully but something is still not working right. 

At least I'm no longer getting the compile errors any more. That's progress, right? Should I just make my (OneDrive stored) database file shareable again and let you edit it yourself? I'd be willing to pay you for an hour of your time to make the necessary tweaks to make this work, hopefully you'd also be able to do what Ken is helping me with also, which is to add a "sold" checkbox to my table, modify my "Asset List" form to  make it easy for me to show just "sold" or "not sold" items as well. I'd be willing to pay you, say, $100 (in advance) to make these simple (for you) changes for me then send me the properly working database. I tired of beating my head against the wall and I'm starting to get a headache. :)

Ken, same offer to you. ;)

I'll try again with what I said in my last post, not last week, not some other part of a posting, but the very last post:

So.. if you highlight your locations combo on the Asset detail form, open the property form and click on the ellipses next to notinlist. Then past this exactly between the autogenerated code which will be:

Private Sub Location_NotInList(NewData As String, Response As Integer)  'Auto generated

Dim strTable As String

Dim strField As String

Dim strtitle As String

    strTable = "locations"

    strField = "location"

    strtitle = "This Location was not found"

    Response = comboboxAdd(NewData, strTable, strField, strtitle)

End sub  'Auto generated

Then, in your 'ModMapping'module paste the following exactly as it is.

'Add new to combo list

Public Function comboboxAdd(NewData As String, _

                strTable As String, strField As String, strtitle As String) _

                As Integer

Dim rs As DAO.Recordset

Dim StrSql As String

Dim StrMessage As String

Dim intResponse As Integer

    'Set up message box to confirm

    StrMessage = "'" & NewData & "' was not found " & _

                         Chr(10) & "Do you want to add this to the list?"

   ' strtitle = "Surname not Found"

    If MsgBox(StrMessage, vbYesNo + vbInformation, strtitle) = vbYes Then

        Set rs = CurrentDb.OpenRecordset(strTable, dbOpenDynaset)

        rs.AddNew

        rs.Fields(strField) = NewData

        rs.Update

        intResponse = acDataErrAdded

        ' Clean up

        rs.Close

        Set rs = Nothing

    Else

        ' response is no, so show list for another selection

        intResponse = acDataErrContinue

    End If

    comboboxAdd = intResponse

End Function

I know this works perfectly in your database if you follow the instructions. So, if you add it exactly as I have instructed with the function in the 'ModMapping' module module and the other part behind the locations combo not in list, on the 'Asset Details' form it will work. 

When you have pasted the code, save it and test it:

Open the form 'Asset details'

click on the locations combo and type in the name of a location you don't already have. when you lose focus (click somewhere else on the form) you will get a popup to say that location is not in the list, do you want to add it. Click yes and it will add the new location to the list. DONE.

The only compile error comes from the code you added previously behind cboMainCategory_NotInList which needs to be deleted:

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

As you are having so much trouble using the NotInList event procedure, have you tried the simpler solution I described earlier? viz:

………….but Access does provide a simpler, though more cumbersome for the user, solution.  First you need to create a form bound to the locations table and set its Data Entry property to True (Yes).  Then in the Data tab of the combo box's properties sheet enter the name of the new form as the List Items Edit Form property.  If the user types a new location name into the combo box, they'll be prompted as to whether they wish to edit the items in the list.  Answering yes will cause the new form to open at an empty new record, in which they can enter the new location and close the form.

For this you need no code whatsoever, so you can delete the satandard module you've tried to create, and whatever code you've entered into the combo box's NotInList event procedure.

Hi DennisWelch.

The code you are showing is for the main category combo not in list.

You need to highlight the location combo as you have done,  then on the property sheet go to the not in list event, click on the ellipses to open vba editor which will add 'private sub cboLocation_notInList etc etc.

You then add the code as you have for the category not in list. You will have to make some changes to the code though to insert into the correct table i.e. location table.

I don't normally do it that way.

If you add the following function you can then use it for all combo's (on the main access menu, click create, then 'module'. copy and paste this code to the module

'Add New item to Combo

Public Function combobox(NewData As String, _

                strTable As String, strField As String, StrMsgMessage As String, StrMsgTitle As String) _

                As Integer

Dim rs As DAO.Recordset

Dim strSQl As String

Dim intResponse As Integer

    'Set up message box to confirm

If MsgBox(StrMsgMessage, vbYesNo + vbInformation, StrMsgTitle) = vbYes Then

    Set rs = CurrentDb.OpenRecordset(strTable, dbOpenDynaset)

    rs.AddNew

    rs.Fields(strField) = NewData

    rs.Update

    intResponse = acDataErrAdded

    ' Clean up

    rs.Close

    Set rs = Nothing

Else

    ' response is no, so show list for another selection

    intResponse = acDataErrContinue

End If

    combobox = intResponse

End Function

then for each combo, click on the not in list event, ellipses, open vba editor (as you have previously) and add:

Private Sub cboEmpRank_NotInList(NewData As String, Response As Integer)    'this will be added automatically

Dim strTable As String

Dim strField As String

    strTable = "tblRank"          'change "tblRank" to your table name for the combo

    strField = "Rank"               'Change "Rank" to your field name 

    Response = combobox(NewData, strTable, strField)

End Sub         'also added automatically

OK so I created a module following your instructions and now when I click on open in the left column of my table I get the Macro error 2950 like before. Also if I click on "New Asset" I get a compile error. I didn't edit any of my forms or tables, I simply created a new module. I didn't compile anything, just copied and pasted your code into a new module and named it "AddNewAsset". Then exited VBA, went to my database and tried clicking "New Asset" and I get this:

So how do I resolve this issue? How does simply creating a new module mess everything up so bad if I haven't edited my form to even use the module? It should be obvious that I don'tknow what I'm doing and need more detailed instructions. 

And YES, I created a BACKUP before doing any of this! LOL!

Edit: So I renamed my corrupted database, then renamed the backup I made this morning and am using that. The errors went away, so it's obvious that creating a module as you described is causing the issues. I'm also getting (with my current working database) a message stating "Security Warning: some active content has been disabled". I never got this before. I haven't tried clicking on "enable content" yet.