More on Access Accounting Database

J

Ju Chao

Just following on from the recent question I had about sample MS
Access Accounting Systems, one particular query I had was how people
handled the Autonumber generation.... I use it for Invoices etc but
then if a user aborts an invoice it leaves a blank or gap between two
invoice numbers which does not look good for accounting/audit
purposes... I have heard people say not to use Autonumber for fields
such as Invoice Number, but for multi user environments it's quite
handy in helping ensure everyone has a unique number... do I really
have to code a save routine which only then locks/updates the invoice
number? What a pain if thats the case (or am I seeing it as more
difficult than it should be?)

Many thanks

Ju Chao
 
M

Mike Painter

Ju said:
Just following on from the recent question I had about sample MS
Access Accounting Systems, one particular query I had was how people
handled the Autonumber generation.... I use it for Invoices etc but
then if a user aborts an invoice it leaves a blank or gap between two
invoice numbers which does not look good for accounting/audit
purposes... I have heard people say not to use Autonumber for fields
such as Invoice Number, but for multi user environments it's quite
handy in helping ensure everyone has a unique number... do I really
have to code a save routine which only then locks/updates the invoice
number? What a pain if thats the case (or am I seeing it as more
difficult than it should be?)
Click to expand...

Assuming the autonumber maintains a proper count, something not guearneted
then it is an *excellent* tool for auditing.
Anything created should never be deleted because there is no way to tell why
it was deleted.
Once invoice number 1234 is created it should be marked as void and kept in
the audit trail.
 
A

Arvin Meyer

Traditional accounting systems have the same problem. Suppose you spill
coffee on, or otherwise ruin a pre-printed invoice or PO. You lose the
number, don't you? Is it the end of the world? No, life goes on.
Unfortunately, there is no easy solution. You can create your own numbering
scheme, but it is still possible to lose a number if you allow a record to
ever be deleted or discarded in any way.

The simplest answer is to not worry about it. Or you can never discard or
delete a record. Instead, mark it deleted and include " where not deleted"
in all your queries. Failing that, you will need to use only unbound forms,
code everything, and allow no deletions. (even more complex than you
thought).
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Top