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. Anonymous
    2019-12-14T20:54:45+00:00

    Ken, same offer to you. ;)

    OK, send me the file at kenwsheridan*<at>yahoo<dot>co<dot>*uk and I'll see what I can do.  But there'll be no charge.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2019-12-14T21:42:39+00:00

    Probably easier if Ken just fixes it for you, there are a lot more issues than just the not in list that should be addressed but it will still work if not changed.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2019-12-15T12:50:47+00:00

    I've posted an amended version of your file to my OneDrive folder at:

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

    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.

    In the table designs I've changed the DisplayControl property of the foreign key Location columns in the tables to Text Box.  Combo boxes should only be used in forms.  As users should never interface with the data directly in tables' raw datasheets using a combo box in tables serves no purpose.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2019-12-15T18:52:58+00:00

    I've posted an amended version of your file to my OneDrive folder at:

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

    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.

    In the table designs I've changed the DisplayControl property of the foreign key Location columns in the tables to Text Box.  Combo boxes should only be used in forms.  As users should never interface with the data directly in tables' raw datasheets using a combo box in tables serves no purpose.

    Thanks a million Ken, as well as SMarshall and Scottgem. I appreciate all of you guys taking the time to help me. While I ultimately resolved the issue by letting Ken edit my database directly, I still learned a lot and will keep playing with it. Next up is figuring out how to do the Sold/Not Sold feature. Of course I'll make frequent backups! LOL!

    Thanks again!

    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