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-14T09:34:48+00:00

    I modified the code for the not in list to add the title behind the combo rather than in the module code.

    So.. if you highlight your locations combo on the Asset detail form, open the property form and click on the ellipses next to notinlist. Then past this exactly between the autogenerated code which will be:

    Private Sub Location_NotInList(NewData As String, Response As Integer)  'Auto generated

    Dim strTable As String

    Dim strField As String

    Dim strtitle As String

        strTable = "locations"

        strField = "location"

        strtitle = "This Location was not found"

        Response = comboboxAdd(NewData, strTable, strField, strtitle)

    End sub  'Auto generated

    Then, in your 'ModMapping'module paste the following exactly as it is.

    'Add new to combo list

    Public Function comboboxAdd(NewData As String, _

                    strTable As String, strField As String, strtitle As String) _

                    As Integer

    Dim rs As DAO.Recordset

    Dim StrSql As String

    Dim StrMessage As String

    Dim intResponse As Integer

        'Set up message box to confirm

        StrMessage = "'" & NewData & "' was not found " & _

                             Chr(10) & "Do you want to add this to the list?"

       ' strtitle = "Surname not Found"

        If MsgBox(StrMessage, vbYesNo + vbInformation, strtitle) = 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

        comboboxAdd = intResponse

    End Function

    There are a lot of other things that should be modified but try this first.

    oh, and two words for you BACKUP first

    Was this answer helpful?

    0 comments
  2. Anonymous
    2019-12-14T09:00:06+00:00

    No worries, one problems solved.

    Now if you copy and paste the code I posted previously (for the module) into a new module or just put it into the 'ModMapping' module you already have and put the other few lines of code behind your not in list for each combo you will solve that too.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2019-12-14T08:12:24+00:00

    It is saving to Copy of Assets

    You're absolutely right. I figured out where it was pointing to the "copy of assets" table (the assets detail form) and changed it to save to "Assets" instead. It now seems to be working properly. Whew! THANKS!

    Was this answer helpful?

    0 comments
  4. Anonymous
    2019-12-14T05:28:22+00:00

    It is saving to Copy of Assets

    Was this answer helpful?

    0 comments
  5. Anonymous
    2019-12-14T03:35:19+00:00

    Well I clicked on the Dec 4th edition of my database, downloaded it, then opened that version. At least I thought I did, but an entry I manually entered into my table this morning is in there! How is that possible if the database I opened is from 9 days ago? Then I tried adding a new record using my "new asset" form but it's still not updating the table with a new record. I'll go ahead and post a link here to the database file on OneDrive. There is no sensitive data in there (other than the obscene amount of money I've spent over the last 6 months on Hard Rock Cafe Pins)  :)

    https://1drv.ms/u/s!Am0C6KvA\_BxKhdsokABvR2lYHtWjCQ?e=ggyyvT

    Thanks for any help you can give. Hopefully it's something simple.

    Was this answer helpful?

    0 comments