Results 1 to 8 of 8
  1. #1
    Barb Murrin is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    8

    ID Number and Record Number Differ

    I don't know what happened to my database, but suddenly the ID number and record number differ. They used to be the same. Any time I typed in a record number in the search field at the bottom of my form, Access would take me to the correct record; now it takes me to a different record. I have attached a screenshot to show the differing numbers (form collapsed to hide most information).



    What has caused this after weeks of flawless function, and how do I fix it?

    Barb

    P.S. My attachment doesn't show! Trying again.
    Last edited by Barb Murrin; 04-25-2017 at 09:25 AM. Reason: Attachment does not show

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    You have to remember that in MS Access there is no such thing as a "record number", at least not one that is permanent. The "record number" you see at the bottom of the screen is totally dependant on the sorting and filtering you have applied to the form, and any correlation between it and the ID Number is essentially coincidental.

    Without seeing it, I think the most likely cause for the differences you see is that ID number is an autonumber, and that somewhere along the line you either deleted a record, or cancelled out of the form while you were entering a new record. An autonumber is generated as soon as you enter the first character in a record, and it will not be reused, even if you don't save the record.

    PS - I see this is your first post - welcome to the forum.
    Last edited by John_G; 04-25-2017 at 09:44 AM. Reason: Add a greeting

  3. #3
    Barb Murrin is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    8
    Yes, this is my first post. Thanks for the welcome!

    I have not deleted any records, and the "record number" that displays at the bottom of the form doesn't behave as though I have. (Did that in a previous life and got the numbers off by one.) However, this differs dramatically. Record 1 shows ID 196; record 2 = ID 86; record 3 = ID 1. It's just completely weird!

    How do I attach a screenshot? I dragged it onto the area indicated, and it showed when I clicked the attachment icon. Was I supposed to do something else to make it stick?

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Record 1 shows ID 196; record 2 = ID 86; record 3 = ID 1. It's just completely weird!
    It means your form is sorted on some field other than the ID Number, or is not sorted at all.

    try this:
    With the form open, click in the ID Number field. Then in the ribbon, click the "Home" tab, then click "Ascending" in the Sort and Filter group.

    The Record Number and ID should now match.

    Using the Record number is not a reliable way to find records, as you have seen. A better way is to click in the ID Number field, click "Find" in the ribbon, and enter the ID Number of the record you want to find. You will get an error message if there is no record with the requested ID Number.

    You can use "Find" with any field, not just numbers.

  5. #5
    Barb Murrin is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    8
    Blessings on you! That fixed the problem.

    RESOLVED.
    Last edited by Barb Murrin; 04-25-2017 at 12:11 PM. Reason: New question posted in new thread

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If I could ask another question...why don't all my queries show up as available sources for mail merges?
    It is usually best to ask totally new question in a new thread. That way, if the person who was helping you with the previous question does not know the answer to the new one, it will appear as a new unanswered question (because it is in a thread with no replies), and is more likely to be seen by others.

    I remember having this same problem years ago. I never knew why it was, but I found I was still able to use those queries, by manually typing them in (even if you don't see them on the selection list).

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    why don't all my queries show up as available sources for mail merges?
    Sorry, I can't help with that one, as I don't do mail merges. The only thing I can think of is that some of the queries are not select queries.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You might want to read this about autonumbers:

    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/Autonumbers


    Use Autonumbers properly
    http://access.mvps.org/access/general/gen0025.htm



    This is why an autonumber PK field should never be visible to a user.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 11-30-2014, 12:46 PM
  2. Replies: 6
    Last Post: 09-22-2014, 02:45 AM
  3. Replies: 16
    Last Post: 06-20-2013, 09:25 AM
  4. Replies: 6
    Last Post: 01-24-2013, 10:02 PM
  5. Making New Record Number Next Numerical Number
    By jhillbrown in forum Access
    Replies: 1
    Last Post: 03-10-2010, 11:06 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums