Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004

    Unanswered: Are multiple Autonumber formats possible?

    Is it possible to generate an autonumber for a table and allow the user to choose between multiple autonumber formats?

    For example, if cars and bikes were to be entered onto the same table can an autonumber choice be offered such as CAR001, or BIK001?

    Or is it just as good to create another field with a drop-down list to display CAR, or BIK?

  2. #2
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    Autonumbers are intended to be used for one purpose and only one purpose, to provide a unique identifier for each record. Here's a post I've archived from a gentleman named John Vinson, MVP, explaining how autonumbers work:

    Quote Originally Posted by John Vinson
    When using Autonumber, do be aware that there will be gaps in the numbering - any record that's deleted will leave a gap; hitting <Esc> after starting a record will leave a gap; adding records using an Append query may leave a gap, often a huge one; replicating the database will make your invoice numbers random.

    In short... it's best NOT to use Autonumbers for human consumption, and particularly not for consumption by accountants and auditors. Invoice sequences like 319, 321, 322, 385, 386, 221841246, -1083225152 make such people get very nervous.
    If you want to your PO numbers to be consecutive you'll have to redefine the datatype of the field to Text or Number/Integer (despite the "numbers" it contains, it really should be Text)and develop a hack for generating an auto-incrementing your ID or Serial or whatever "number."

    If you search here or elsewhere using auto-incrementing or Access and auto-incrementing you should get a gazillion hits on the subject. Generating a "number" with multiple prefixes will complicate the job, but it is doable. Using a combobox to pick the prefix might be the way to get started.

    Linq ;0)>
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    an auto generated column is usually reserved for internal use only, so the user should not place any reliance or trust on that sequence number, if you must have relaince or trust on the sequence number then you will have to develop your own, or find on t'net a function that does that for you.

    using prefixes such as 'CAR' or 'BIKE in my books are not a smart move.

    you should have another table which classifies what type of vehicle it is and then use that in the vehicle table.

    ...why, well what happens if someone decides they want to add a new type of vehicle... say trucks
    or if they want to subtype an existing vehicle eg Bikes into say "Standards", "Sportsbikes", "Trikes" and "Sheds (Cruisers)"

    I'd argue your main table should be say 'Vehicles' and you have a value in there which is a foreign key to VehicleTypes. you coudl use the prefix as the key eg CARS,BIKES,
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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