How to enforce a autonumber field starting from some number(2000)?

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

Guest

Hello,

I setup a Purchase Order table and form to keep the Purchse Order database
for accouting. And we want the system to assign the PO number as autonumber
but starting from 2000 not 1(because 1-1999 already used and want to keep
reality consistent), how can I do that?

Any comments or solutions appreciated! Thanks!
 
Sorry I did not touch VBA before, could you please tell me where should I
put/call the function SetAutoNumber()?

By the way, can I just copy the whole code and run it for my purpose?

Thanks for your help!
 
Yes. Copy the fucnction from the article.
Open a new module (Modules tab of your Database window).
Paste into the module.
Save with a name like "Module1".

Then press Ctrl+G to open the Immediate window.
Type:
? SetAutoNumber("Table1", 2000)
and press Enter. That example sets Table1 to start at 2000.

If you compact the database again before adding a record, it will be reset
back to 1 again.
 
more question, regarding "If you compact the database again before adding a
record, it will be reset back to 1 again."

Does that mean if I add some records first then comapct the database
sometime later the autnumber won't be reset back to 1 and it will be
consistent assigned?

I am going to change a primary key in PO table from "Number" to AutoNumber
and I know that I need delete the records in that table in order to change
the design.
Actually this database is a shared database. Do I really need compact it
after deleting the record?(to improve the performance?)

How could I maintain this shared database after it is used? Do I need a
regularly compact the database?
 
Thanks, Allen! Your code is great !!!

Also, I just done the testing for compacting my database. After insert some
records to the table(primary key is autonumber starting from 2000 using the
procedure Allen published).

After compacting the database, the autonumber did not reset back to 1, which
is good for me.

Allen, sorry I need ask more, if I split the DB(a user level security DB),
does the data(tables) is secured too? If not, do I need do anything
additional to secure the data too?

Thanks again for your help and patience!
 
If it does not reset on compact, you probably need to download the service
pack for JET 4. Although it is behaving as you want in this particular case,
there are *many* bugs that have been addressed in the service packs, and you
really need at least SP7, preferably SP8.

To check what version you currently have installed, locate the file
msjet40.dll (typically in \windows\system32). Right-click and choose
Properties. On the Version tab, you should see 4.0.8xxx.0. If the minor
version number does not start with a 7 or 8, this is important enough that I
would refuse to do any paid work for a client until they updated.

If you split the database, the security goes on the back end primarily. You
generally release an MDE file to the users, and that stops them from messing
with the front end.
 
Back
Top