Access 2000 - Sequential numbering

G

Glynn

I need to sequentially number accounting Journal entries, under strict
machine control.
I am using the AutoNumber(Appended) which works, except when a Journal entry
is deleted - which also deletes the AutoNumber, leaving an unacceptable gap
in the numbering sequence.

Help
 
A

Arvin Meyer

Glynn said:
I need to sequentially number accounting Journal entries, under strict
machine control.
I am using the AutoNumber(Appended) which works, except when a Journal entry
is deleted - which also deletes the AutoNumber, leaving an unacceptable gap
in the numbering sequence.
Click to expand...

The only solution is to never delete. If you use a custom sequential number:

=DMax(["MyField", "MyTable") + 1

You will be able to delete and repeat only the very last used number. What
would happen if you accidently spilled coffee on a bunch of paper forms? Or
needed to delete a spoiled or cancelled order? The very same "problem".
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
G

Guest

hi,
compacting your database should reset your autonumber
system.
tools>database utilities>comact database.

it's a good idea to compact after each deletion.
it's also a good idea not to delete but instead mark the
entry someway so that it can be eliminated from queries.
 
A

Arvin Meyer

hi,
compacting your database should reset your autonumber
system.
tools>database utilities>comact database.
Click to expand...

Actually for Access 2000 and later, there was a bug in autonumbering that
was fixed in JET SP4. Anyone running SP4 or later (we are now up to SP8)
will no longer be able to reset their autonumber without deleting all the
records in the table. The following KB article describes how to reset an
autonumber:

http://support.microsoft.com/default.aspx?scid=kb;en-us;812718

Resetting the autonumber is not the problem though. Glynn is worried about
the gaps after a record is deleted. I don't think he should be.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
G

Glynn

Thanks - I will use DMax which meets my needs.

Another one if I may impose on you:
I have a Table of Client names and details.
When I make changes to the client data, I need to keep a record of the
previous detail.
How do I create records of 'before' and 'after'

Glynn
 
Top