Beginning a NumberAuto function from a known starting value

  • Thread starter Thread starter Epilepsie Montreal Metropolitain
  • Start date Start date
E

Epilepsie Montreal Metropolitain

I need to start an NumberAuto function from a known starting
value and I'm wondering where I could find the code to do
this--if in fact I need to code it?
A suggestion on how to procede with this problem would be
much appreciated...

Dave H.
 
Epilepsie said:
I need to start an NumberAuto function from a known starting
value and I'm wondering where I could find the code to do
this--if in fact I need to code it?
A suggestion on how to procede with this problem would be
much appreciated...

Dave H.
Click to expand...


You can, of course, do this, as Audrey says but you should be aware of
the pitfalls of trying to use Autonumber to do something for which it
was not intended.

Autonumber is designed only to provide a unique identifier for records
in a table. This is used internally by Access in building relationships
between tables according to your design. The actual values that
Autonumber delivers are unimportant and should never be exposed to
users or have any external meaning at all. Depending on the design and
use of the database, Autonumber will develop gaps, become
random and go negative.

For any number series that you want to control or have available
externally, you must design your own. There are several schemes
available depending on what you want to do.

hth

Hugh
 
Hugh,

You raised my curiosity ---

When will an autonumber become random or go negative? I thought it would just
continue to increment by 1 ad infinitum!

Cassandra
 
An AutoNumber is just a Long Integer to which an incrementing scheme has
implied. A Long is large, a bit over 2 billion, but not infinite. <G>

There are certain circumstances that can cause the AutoNumber function to
skip sequences of numbers, not just one (we know that situation: someone
starts, but decides not to, add a new record, but the AutoNumber base has
been incremented). If you do something similar in a transaction or a query
fails, you can skip multiple numbers.

You just can't trust that they will be sequential, even though they are most
of the time, in the normal case. But, use them as intended, as surrogate
keys for internal joins, never show them to the user, and nobody'll know nor
care.

Larry Linson
Microsoft Access MVP
 
Cassandra,

Just to add to Larry's response, regarding reaching the limit of
2,147,483,647 which is when the expected behaviour of an autonumber is
to then go negative. You will notice also that in table design there
is a New Values property for an Autonumber field, which gives you the
choice of Increment or Random. In any case, if the database is
Replicated, all Autonumber fields will automatically become random.

- Steve Schapel, Microsoft Access MVP
 
If the autonumber ever went negative, does it go to -1 and decrease by -1 until
it reaches -2,147,483,647 ?
Would it then go positive again?

Hypothetical but I'm curious!!

Cassandra
 
I believe it goes from 2,147,483,647 to -2,147,483,648, and then starts
increasing by 1 (i.e.: -2,147,483,647, -2,147,483,646, -2,147,483,645)
until it reaches -1.
 
I appreciate your response Hugh, but I'm in need of using
the offset Autonumber as a receipt number on preprinted
sheets of receipt forms. If an autonumber is not the way
to go here, could you please elaborate about what the other
schemes should be...

Thanks for your time and help,
Dave H
 
Epilepsie said:
I appreciate your response Hugh, but I'm in need of using
the offset Autonumber as a receipt number on preprinted
sheets of receipt forms. If an autonumber is not the way
to go here, could you please elaborate about what the other
schemes should be...

Thanks for your time and help,
Dave H
Click to expand...


Here is some code to do this. It works for both single and multi-user
scenarii.

You will need a new separate Table (tblSeries) having just one Field
(NextNumber) of type Long Integer to store the incrementing number.
The Table will have no index and will not be related to any other Table.

Dim rst As Recordset, db As Database
Dim lngNextNumber As Long
Set db = CurrentDb

'Open tblSeries, lock, read next number, increment, update and unlock.
Set rst = db.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.MoveFirst
.Edit
lngNextNumber = ![NextNumber]
![NextNumber] = lngNextNumber + 1
.Update
End With
rst.Close
Set db = Nothing

This code is designed for Access 97. If you are using a later version,
you will need to use DAO.Recordset and DAO.Database and make sure you
have the appropriate reference set for the DAO Library.

The incrementing number is returned to you in the variable
lngNextNumber.

This code should be in the Click event of the command button you use
finally to commit your numbered record. This must happen after all
opportunities to abort your record, otherwise you could have gaps in
your numbering to explain to your auditors! After this code, you will
need to store the number value in your Table holding your records.

If, in your system, you need other unrelated number series, just add
another Field to tblSeries and manage it in a similar manner.

hth

Hugh
 
Back
Top