Cannot add new item to list

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

It seems my code is not being run. I guess I don't know how to save my procedure and assign it to the combobox on my form.

Here is what my design mode of the form looks like. If I click on the ... elipsis it opens VBA and shows me the code I posted in the last message, but how do I save this code? Clicking "Save" from the file menu saves my database, not the procedure.

Can you show us the code you put in the NotInList event for the location combo/

Here is what I currently have: (I wish I would get email notifications that you responded! I've selected the box to notify me when someone responds and verified my email address is correct))

Can you show us the code you put in the NotInList event for the location combo/

Hi again Scottgem,

Well I'm getting closer! Now both forms pull from the "locations' table. However my "code" doesn't appear to be working because it tells me my choice isn't in the list and that I need to either choose an item from the list or type something that equals an item in the list. So I went into the "locations" table and added the new location. Closed my form and went back into it and now my new location is selectable. So it's not prompting me if I want to add a new location. No biggy but sure would be convenient if it works. And while I took a class in Junior college 5 years ago in VB programming it was a very tough course for me so I'm not sure I can figure out what's wrong. I basically copied and pasted from the website you gave me. 

I'll also try to figure out the "sold" items issue with you guys help. Thanks to both you and Ken for taking the time to help me customize this database to meet my needs. It's greatly appreciated!!