Cannot add new item to list

1.  Let's deal with the issue of adding a new item to the list of locations first.  This would normally be done by means of code in the combo box's NotInList event procedure, as we've described earlier, 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.  The new location will now be included in the combo box's list.  So, there is no need for you to keep the locations table open.

2. As regards the Sold column in the items table, the usual control for data entry into a Boolean (Yes/No) column is a check box.  The 'code' you are referring to is the SQL statement of a query.  This is entered in the query designer by switching to SQL view and typing in the SQL statement, or you can do it visually in the query design interface.  The SQL statement for a query to return only unsold items would be something like this:

SELECT *

FROM [Items]

WHERE NOT [Sold]

ORDER BY [ItemName];

Conversely, to return only sold items:

SELECT *

FROM [Items]

WHERE [Sold]

ORDER BY [ItemName];

And to return all items, whether sold or unsold:

SELECT *

FROM [Items]

ORDER BY [ItemName];

Note that if you switch back to query design view and save the query Access will change the SQL statement.  It will work just the same, however.  A query like one of the above can be used as a form's RecordSource property for data entry.

Well I've got some good news. I just closed all of the forms and tables I had open and exited Access and said no when it asked if I wanted to save the changes I made. Then went back in and everything seems to be working again. I'm back to the point where at least my original problem is solved. I still have to leave the separate table called "locations" open and add new location manually to it then go back to my form and I can then choose it as a location from the list. This is only a small inconvenience for me. Making this work seems to be more complex than I can handle. I obviously don't know what I'm doing and I am not a VB programmer, despite the class I took 5 years ago. I haven't used it since and so I've forgotten most of what I learned. Lesson learned that I need to make frequent backups of my database.

I've also purchased a book on Access 2016 (I assume that's the version I've got? There is no longer a Help/About menu option, and going under File/Account it says my Access is version 1911, but searching for a book called Access 365 yields books on Access 2016 so I assume that is what I have and need to get a book on, right? Anyway, I learned that the "backup database" option is under the File/Save As area. And that I can also backup individual forms and tables by using the usual CTRL-C and CTRL-V. Until I understand what I'm doing I'll start backing everything up. 

In the mean time I'll start reading my book on Access 2016 and fiddle around and make lots of backups. 

Adding a simple boolean yes/no column in the table for my "sold" option is even confusing for me. I've made a column raises questions such as "do I use a toggle button or a check box to indicate that an item has been sold"? Then what? Ken says I can make a query to show only sold items but then goes on to show me a bunch of code to make this happen but where this code goes and how do I add it to my form is pretty confusing for me. I obviously have a lot of studying to do. :(

Regardless of this, I currently have a usable database thanks to you guys and I'll continue to tweak it to meet my needs slowly and cautiously. Thanks a million for all the help guys!

As regards the error arising from the missing Retired Date column add the column back to the table in design view.  Having an unused column in the table won't do any harm, but hopefully should avoid the error arising again.

For the error with the embedded macro used as the OnClick event property of the txtOpen control, try changing the macro's WhereCondition argument to a fully qualified reference to the ID column:

   "[ID] = " & [Forms]![NameOfAssetListFormGoesHere]![ID]

I've assumed here that the ID column is a number data type.  If it's a text data type use:

   "[ID] = """ & [Forms]![NameOfAssetListFormGoesHere]![ID] & """"

As regards the NotInList event procedure, the following is an example of code for this, which is used to add a new employer.  By making the relevant amendments to the table and column names this should work in your present context.

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

    Dim ctrl As Control

    Dim strSQL As String, strMessage As String

    Set ctrl = Me.ActiveControl

    ' subject to user confirmation insert a new row into Employers table,

    ' inserting NewData value into Employer column

    strMessage = "Add " & NewData & " to list?"

    strSQL = "INSERT INTO Employers(Employer) VALUES(""" & _

            NewData & """)"

    If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then

        CurrentDb.Execute strSQL, dbFailOnError

        Response = acDataErrAdded

    Else

        Response = acDataErrContinue

        ctrl.Undo

    End If

End Sub

You'll find other examples of the use of the NotInList event procedure in a variety of contexts in my NotInList.zip demo  in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly. 

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

Adding a module doesn't do serious damage to any database, simply creates a module to add code.

What you asked to do was to be able to add a location or category using not in list on your location and category combo's, which the code I provided will do if you follow the instructions I gave. I don't believe I said to remove anything, just copy and past the code I provided into the module and put the other piece of code behind the not in list event of the combo, changing just the field and table name in that code.

The issues you have now are totally separate issues. If you remove the field then you also need to remove all references to it, such as that in your query.

The previous version is called a backup... if you have not made a backup then you have a problem. I make backups many times per day and it's a good habit to get into for this very reason.

I'm not sure what I did, but I created the module you described and I've caused serious damage to my database. I also removed a field that I did not use (the "retired" field) now when I try to open any of the items under "assets" such as "Assets by category" or "assets by location" they all give me a compile error referencing the now missing "retired date" that is now missing. I don't know if there is a way to go back to my previous version. 

I also tried adding another column to my form with a simple boolean "yes/no, true/false" for the sold category. You can see how that  looks here. Whenever I try to open an existing record I get the macro error message shown below.