1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

MS Access 2002 / Record Numbers

Discussion in 'Business Applications' started by AntiFusion, Sep 23, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. AntiFusion

    AntiFusion Thread Starter

    Joined:
    Sep 23, 2004
    Messages:
    6
    Is there a method which can be used to cause the MS Access-generated "record number" to appear in a cell in the record, so that it can be used as the primary key?
     
  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    The record number generally is the PK. You can have any value that is in a table show up on a form.
     
  3. SJ McAbney

    SJ McAbney

    Joined:
    Sep 24, 2004
    Messages:
    28
    There is no such thing although I believe you are referring to the Autonumber field type.

    As Rockn has said, you would normally use this Autonumber as the primary key of your table.

    If you are talking about using it within another field in the same record then this serves no purpose. Or are you referring to having the Autonumber appearing on a form within a textbox? As it stands your question reads as if you are using the database's tables directly.
     
  4. AntiFusion

    AntiFusion Thread Starter

    Joined:
    Sep 23, 2004
    Messages:
    6
    There is no such thing although I believe you are referring to the Autonumber field type.

    Actually, no, I'm not.
    I'm referring to the record number, as indicated in the record selector navigation bar at the bottom of forms. It typically corresponds to the row number in a data table.

    The reason I want to use this, instead of using a cell in each row which is an autonumber, is that if you delete a record (row) the autonumber no longer necessarily corresponds to the (record number / row number) because Access does not adjust the autonumber cells.

    For instance, if I have ten records (rows), each with an auto number cell, 1--10, everything is kosher. If I delete row 5 (record 5), the record with autonumber 5 simply ceases to exist. However, in the record navigation bar, at the bottom of a form which is looking at that particular data table, "5" will return the record with autonumber "6" in it as primary key.
     
  5. SJ McAbney

    SJ McAbney

    Joined:
    Sep 24, 2004
    Messages:
    28
    It has no reason to. The autonumber is simply a unique value providing a meaningless key for connecting data related via different tables within your database. If you are trying to use this field as any sort of displayed index for your data then I'd suggest creating your own autonumber field.

    Such a method, however, would only be viable with forms (which you should use) as you can't call functions or run routines from any activity within a table.

    Yes; that's logical. If you've related the record with autonumber of 5 as a foreign key in another table and then delete the record, do you really expect the other table to know to change 6 to 5 etc. ? As I've said already, it is a meaningless number used only for relating records. And this updating between related tables is why you shouldn't be using a changing value as the primary key within a table.
     
  6. AntiFusion

    AntiFusion Thread Starter

    Joined:
    Sep 23, 2004
    Messages:
    6
    I'd suggest creating your own autonumber field.

    When I'm using the term "autonumber", that is precisely what I am talking about: A cell, within a row of the data table, which has its format set to "Auto Number", and which number sequentially, as you browse downward in the table.

    As we've both pointed out, this does not adjust for deleted rows.

    This is a minor inconvenience, in this case, because it is the primary key, AND I would like to have at least one cell (and corresponsing field in the form we use for input and viewing ) which is actually sequential with the "record number" seen in the navigation bar.

    ............unless there is a reasonably easy way to make your own navigation bar, to browse from one autonumber primary key to another...........
     
  7. SJ McAbney

    SJ McAbney

    Joined:
    Sep 24, 2004
    Messages:
    28
    How's this?
     

    Attached Files:

  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/277162

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice