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-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
  2. 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
  3. 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
  4. 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
  5. Anonymous
    2019-12-14T20:48:16+00:00

    Ok I followed your instructions exactly. Here are some screenshots to prove it:

    (Not in List)

    modMapping Module:

    modMapping continued:

    I saved this and tested adding a new location and I still get the message "the text you entered isn't an item in the list". 

    Are you getting as frustrated as I am? 

    Ok I think the issue is, looking at your screenshots, I added the Not in List code to the wrong form, Asset List rather than Asset Details. Let me try again because that's not the form I'm using to try to add a new record, you can't add one from there. :)

    OK tried that, now it goes to Access and shows this when I type CTRL-S to save my code changes in VBA:

    It also opened VBA which showed an error:

    Was this answer helpful?

    0 comments