Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76

    Unanswered: 2 x Auto Numbers

    Hi,
    I have a table which includes shipment details. Shipment ID is the primary key and set to AutoNumber. I want another field within the table to show the supplier reference which we set. This wants to start at 3456 and increase by 1 each time. Access does not allow two autonumbers and so I do not know how to set this automatically.

    e.g
    ShipmentID SupplierReference
    1 3456
    2 3457
    3 3458

    Has anyone any ideas?
    Thanks
    Simon

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There shouldn't be any reason to need two autonumbers.
    What other table relate to this table?

  3. #3
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76
    You are right, I can get away with using just one autonumber. I did not even consider that! Is there a way to make the autonumber start at 3456 instead of 1?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes - but you need to do it via code. Hang on...

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

  6. #6
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76
    My table is tblYTShipments and column is YTID

    ALTER tblYTShipments ALTER ytid AUTOINCREMENT (3456, 1)

    When I run this, I get a syntax error message. Where have I gone wrong?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Jabber View Post
    I get a syntax error message.
    Is that all it says? No details?

  8. #8
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76
    Syntax error in ALTER TABLE statement

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There's no "ALTER TABLE" in your ALTER TABLE statement - that'll be the error!

  10. #10
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76
    I get the same SYNTAX error for:
    ALTER Table tblYTShipments ALTER AutonumberCol ytid AUTOINCREMENT (3456, 1)
    and
    ALTER Table tblYTShipments ALTER AutonumberCol ytid (3456, 1)

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    remove "AutonumberCol" - that is a place holder for the name of your column

  12. #12
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76
    ALTER Table tblYTShipments ALTER ytid (3456, 1)

    Tried this and I now get "Syntax error in field definition"

  13. #13
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76
    ALTER Table tblYTShipments ALTER ytid AUTOINCREMENT (3456, 1)

    This works. Thank you for your patience.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •