autonumber messes up with me.undo

  • Thread starter Thread starter Angi
  • Start date Start date
A

Angi

I have a Coid that's set to autonumber. The company entry form's
BeforeUpdate event doesn't save the record if certain things aren't
met. That works fine. The problem is it still takes the next number
messing the autonumber sequence. How do I make the Coid 1 + the last
record number rather than using the autonumber? I don't know how to
reset the autonumber without compacting the db. If there's a better
way, please let me know. TIA!
 
Angi said:
I have a Coid that's set to autonumber. The company entry form's
BeforeUpdate event doesn't save the record if certain things aren't
met. That works fine. The problem is it still takes the next number
messing the autonumber sequence. How do I make the Coid 1 + the last
record number rather than using the autonumber? I don't know how to
reset the autonumber without compacting the db. If there's a better
way, please let me know. TIA!
Click to expand...

If you want to ebsure that your CoIDs are generated in sequence with no
gaps, don't use an autonumber. Autonumbers exist to generate unique
keys and for no other purpose, so if you care what the number actually
is, you shouldn't use an autonumber.

There are various schemes for generating sequential numbers, from simple
to complex. In a single-user environment, you can just use code like
this in your form's BeforeInsert event:

Me!CoID = Nz(DMax("CoID", "tblCompanies"), 0) + 1

In a multi-user database, your best bet is to have a one-record table in
which you store the next number to be assigned. In the form's
BeforeUpdate event, if it's a new record being saved, you open a
recordset on that table, locking it, get the number from the record,
update the record with the next higher number, and close the recordset
to release the lock.
 
Autonumber (as mentioned many mnay times in these groups) is used to create
a unique key for your data. It should not be used if the numbers are to
have meaning or will be used for any other purpose. Autonumber is not
designed to keep a list of continuous ordered numbers that never skip.

Please do a search. Your question is very common and you should find
hundreds of similar questions and responses in the history of these
newsgroups. I'd probably look in the table design, or getting started
groups.

Rick B
 
Or, if you wanted to find hundreds of examples of a guy being a jerk, you
could do a search for "Rick B".
 
Back
Top