Access 2000 - Sequential numbering

[Guest]

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

 

[Arvin Meyer]

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.

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

 

[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.

 

[Joseph Meehan]

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

I suggest you may not want to use Autonumber for that use. Autonumbers are
designed to provide unique numbers. It in not designed to provide numbers
in order and for a number of reasons may not do so. As a result using them
in any application where the user sees the numbers is likely to end up with
confusion.

There are other ways of providing the numbers you want depending on the
particual application. You may need to increment your numbers using VBA
code. I suspect someone will respond with a sample of some workable code.
I know the basic code, but I have never needed such numbers so I have not
worked with it so I would rather not suggest something I have not tested.

 

[Arvin Meyer]

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

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

 

[Guest]

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

 

[Joseph Meehan]

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

I see Van already answered your question.