Cannot add new item to list

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.

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

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. ;)

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 I followed your instructions exactly. Here are some screenshots to prove it:

(Not in List)

modMapping Module:

modMapping continued:

I saved this and tested adding a new location and I still get the message "the text you entered isn't an item in the list". 

Are you getting as frustrated as I am? 

Ok I think the issue is, looking at your screenshots, I added the Not in List code to the wrong form, Asset List rather than Asset Details. Let me try again because that's not the form I'm using to try to add a new record, you can't add one from there. :)

OK tried that, now it goes to Access and shows this when I type CTRL-S to save my code changes in VBA:

It also opened VBA which showed an error: