Cannot add new item to list

Anonymous
2019-12-05T04:03:45+00:00

Hi guys,

OK I have been using the bundled database "Asset Tracking" to keep track of one of my collections. I have an Office 365 Home subscription. I've been successfully using this for a few months and currently have over 500 assets in the database.

As I add a new item to this database there is a field called "location". Every time I add a new location that's not already in the list it asks me if I want to add it, I say "yes", then I add it and go about my merry way.

This no longer works. 

Now when I type in the new list item and hit TAB (as I've done 100 times before) when I get to the point where I click on OK it tells me again that the location is not in the list and do I want to add it? 

So let's say that I just added "Belfast" it shows me the list of locations that are already in the list with Belfast at the bottom, then just above it is an entry called "B". Nothing I do will let me actually successfully add a new Location. I delete Belfast and B from the list (by dropping down the list, right clicking and select "edit list items") which does seem to delete both items, but when I go to add it again the exact same thing happens. I have a the same issue no matter which city I try to add so it's not a B or a Belfast thing. 

Tech support won't help me and my database is now useless if I can't keep adding new locations to choose from when adding assets. 

I've tried "Compact and Repair Database" to no avail. Tech support via chat was useless and suggested I try an online repair which just downloaded and reinstalled the program. This hasn't helped. Same problem. It seems the location field has somehow gotten corrupted.

Any help would be greatly appreciated!

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

44 answers

Sort by: Oldest
Most helpful Newest Oldest
  1. ScottGem 68,840 Reputation points Volunteer Moderator
    2019-12-07T23:02:48+00:00

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

    Was this answer helpful?

    0 comments
  2. Anonymous
    2019-12-09T18:13:26+00:00

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

    Was this answer helpful?

    0 comments
  3. Anonymous
    2019-12-09T20:33:25+00:00

    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.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2019-12-10T00:08:38+00:00

    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

    Was this answer helpful?

    0 comments
  5. Anonymous
    2019-12-11T05:55:04+00:00

    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.

    Was this answer helpful?

    0 comments