Auto Number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I review every post regarding the above subject, but I am not sure any of the
suggestions apply so I will post my issue.

I want my auto number column to format as follows: 200701, 200702,
200703... Seems easy, but the kicker is that next year, I want it to be
200801,200802,200803,...
 
The next value would be something like this:
Nz(DMax("MyID", "MyTable", "MyID >= " & 100 * Year(Date())), 100 *
Year(Date())) + 1

Assign that to your ID field in the BeforeUpdate event procedure of your
form, if it is a new record. I suggest Form_BeforeUpdate since this is the
last possible moment before the record is saved. This reduce the chance that
2 users are assigned the same number when adding records.
 
Dangerous.

First, if you are using a field that is an auto number, you have no control
over the number that is generated.
Second, do you plan to have more than 99 records per year? Your numbering
scheme appears to be limited to that.

I would split the field into two components. YearNumber and RecordCounter
and combine the two when needed to display.

Allen Browne has given you code to generate the number if you stick with
your current scheme.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
There is a brute force method to reset autonumber fields.
If you copy the source table to a new table name, say tmp, copying both
structure and data; then (in tmp) change autonumber field to long int and
save).

In tmp add one record that is one number below the desired new start, for
example add 200799 to tmp table.

(Of course, do back up the database before fritzing with all this.)

Anyway, now rename the original data table to something like MyData_BAK.

Now (I didn't say thsi was simple), copy structure only, MyData_BAK to MyData.

MyData is now empty table, but with original structure.

Run an append query, appending all the records in tmp to MyData.

MyData now contains all your data, still has autonumber field, and the last
autonumber is 200799.

Wait until one new record is added (200800), then go back and delete the
dummy record, 200799.

It's really less painful than it sounds, and it does work.
 
Back
Top