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: Newest
Most helpful Newest Oldest
  1. Anonymous
    2019-12-17T02:55:45+00:00

    You guys both have the same version, I only posted a shareable link to it once. I think you guys are talking about 2 different things. Ken says I was using a combobox set to "Value List" in my TABLE, not the forms. I had it working but needed to leave the table open and add new entries to it manually. Then go back into my form to add a new record that uses the new location.

    The problem I was having was that when entering a record in the form it wouldn't prompt me "do you want to add a new location" it would tell me I had to use a location that is already in the list. Now when adding an item and I enter a location that's not in the "locations" table it will prompt me do I want to add it, thus saving me from having to leave the table open, add a new location to it, then go back to my form and finish entering the new item. 

    After Ken got this working for me he continued working on my database for me so I could mark something as sold and then filter by sold/unsold items. 

    Hope this resolves any confusion. 

    Thanks a million once again Ken for all of your help. It's sincerely appreciated!!!

    Was this answer helpful?

    0 comments
  2. Anonymous
    2019-12-16T00:47:04+00:00

    Locations combo is definitely not 'Value List" in the version I received and I tested the code in the database before I posted it, worked exactly as intended.

    Looks like we are all working on different versions.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2019-12-16T00:16:16+00:00

    ..................but the initial question RE: locations combo, has RowSourceType property of table/query which is correct

    Not in the file I received from the OP.  It was set to 'Value List', and the RowSource property was such a delimited string expression, hence the failure of the NotInList event procedure to execute as intended.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2019-12-15T23:46:26+00:00

    "The problem was that the RowSourceType property of the combo box was a Value List.  Changing this to Table/Query and setting its RowSource property to an SQL statement, allowed standard code to be entered into the control's NotInList event procedure."

    Yes, the problem for the category combo would have been that RowSourceType property of the combo box was a Value List but the initial question RE: locations combo, has RowSourceType property of table/query which is correct

    Was this answer helpful?

    0 comments
  5. Anonymous
    2019-12-15T19:20:04+00:00

    I've updated the file in my OneDrive folder by changing the Assets List form's RecordSource property to:

        SELECT * FROM Assets WHERE NOT Sold

    This shows only unsold items in the form by default.  To show only sold items I've added an unbound Show Sold checkbox to the form's header with the following code in its AfterUpdate event procedure:

        If Me.chkSold Then

            Me.RecordSource = "SELECT * FROM Assets WHERE SOLD"

        Else

            Me.RecordSource = "SELECT * FROM Assets WHERE NOT Sold"

        End If

    The form can be toggled between unsold and sold items with the check box.

    Was this answer helpful?

    0 comments