Re: Making an autonumber field start at a set number

RASTUS wrote:
I am constructing an order number and a Job number based database and I want
the order numbers to automatically increment to the next number, I currently
have used a combined key to achieve this but don't know how to make both
parts of the key display on the form and don't know how to make the number
increment starting from a given number say 550000, can anyone help, possibly
I should not use the key field to achieve the desired effect?


Personally, I like autonumber for table keys. And with autonumbers I don't care if they are sequential. The reason? It is so easy to get out of sequence and have holes using autonumbers.

Try this. Open a table with an autonumber key field. Start entering some data then press ESC a couple of times to undo the changes and close the table. Open again and enter some data. The autonumber will increment but now there's a space.

What some folks do is get the max number of the table and add 1 to it to create a sequential number. Others may open a table that holds key numbers, add 1 to a key, update the table and save that number to the field.

You can still end up with "holes" with this method too. Let's say you create the key number when something is added to the record. Someone else adds a record 1 higher than yours. Then you escape out of the record.

You may find it best to create the number when the record is being added to the table to avoid holes. Or else create the number right away but if you don't want to save it, set a flag to ignore it.
.

Relevant Pages

  • Re: ACCESS: Can I make Autonumber field start with 582 rather than
    ... reliable algorithm for generating Sequence numbers. ... Note the OP used the term 'autonumber' in the ... autonumber works i.e. when MAX + INCREMENT goes out of the value range ... A better approach in SQL is to have a ready-rolled table of ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Autonum field for Relationships and Replication
    ... > that due to the PK being a single autonumber field, ... that's risking your database because it depends on the ... enforce your definition of uniqueness. ... there's no reason to program your sequence. ...
    (microsoft.public.access.replication)
  • Re: Renumbering records
    ... use a two-column sequence instead, ... In fact, I don't really want true replication, but ... started getting erratic results in my autonumber field. ... Then the next time you copy an Access database to ...
    (microsoft.public.access.replication)
  • Re: Order of evaluation of function arguments
    ... it schedules an increment of "i", ... also will occur by the next sequence point, ... The order of evaluation of the function designator, ... int f, g, h; ...
    (comp.lang.c)
  • Re: Making an autonumber field start at a set number
    ... Dim 1ngID As Long ... advice that I should not use a key autonumber field to create JOB ... increment by one and start at 550000. ... Dim lngID As Long ...
    (comp.databases.ms-access)