no undo or going back (i.e. invoice numbers)

[Chrisopia]

In the real world, we have a set of numbered invoices.

No 2 invoices have the same number, and no invoice goes missing or thrown out. If it's cancelled it is labelled so.

So on to the database version of the invoice -
Currently my invoice number is generated by the very rough

Max([invoiceNumber]) +1

i need some advice on how to prevent invoice numbers being deleted one way or another, or being rewritten / overwritten to protect them and keep their order.

 

[NigelShaw]

Hi,

couple of things.

you can sort your invoices into numerical order without having them stored as numerical order. example-

1
6
5
8
10
4
7

can be sorted in a query by ascending order.

your data should be stored in a Back End anyway so password protect it and connect to it through the front end and restrict other user access.
i guess one way to prevent overwriting could be to create a popup that displays what you need but have all of the controls locked to prevent editing. This way it cant be edited.

lastly as an observation, you would never cancel an invoice would you? if an invoice is raised, it needs to be credited i thought? maybe im wrong


cheers


Nidge

 

[DavidAtWork]

You could make the Invoice number the primary key for that table as Access won't allow you to delete just the invoice number as PK's can't be null, and if there are other table records linked to the invoice number, referential integrity won't allow you to delete records.
David

 

[NigelShaw]

There is a downside to that though David. If the user started a new invoice process but then bailed, I don't think the PK ID comes back as the next increment rather, the next number from the last PK generated which would leave holes in the invoice increment?

Nidge

 

[vbaInet]

From the scenario you described, there will be holes if you use AutoNumber, there won't be if the OP is using DMax() in a Number field. Record deletion will obviously leave holes.

However, Primary Keys are meaningless and should not be seen by the user. If the InvoiceID gets deleted it should reflect this which is why numbers don't get refactored.

If you don't want to delete the records then use a Flag to indicate that the record has been deleted or not.

 

[Galaxiom]

However, Primary Keys are meaningless and should not be seen by the user.

No so. If the PK is produced by an autonumber then it is advised that the field should not be exposed because it has no real meaning and cannot be controlled.

However that does not mean a PK is necessarily meaningless. A PK can simply be a natural key such as an allocated invoice number.

 

[vbaInet]

I wasn't explicit. Meaningless to the user, meaningful to the database for enforcing referencial integrity and keeping "close tabs" on connected data. Where in this case an Invoice Number shouldn't necessarily be relying on PKs. But then again, every developer to his own.

 

[DavidAtWork]

NS, I wasn't suggesting using the autonumber data type, using your Max +1 is fine, as long as it's unique you can make that field the PK. As suggested above, you should have a way of cancelling an invoice as I imagine there could be several reasons why an invoice would be cancelled, even days after creation. Better for an audit trail to show cancelled invoices rather than delete.
David

 

[Lightwave]

I would probably just use the Autonumber as the Invoice make it PK and prevent anyone from ever deleting from the Invoice Table the actual invoice numbers. As others have stated you can flag the Invoice as Invalid / Deleted but never ever allow anyone to remove it.

 

[tehNellie]

There is a downside to that though David. If the user started a new invoice process but then bailed, I don't think the PK ID comes back as the next increment rather, the next number from the last PK generated which would leave holes in the invoice increment?

Nidge

Dont expose the Invoice table to a form. Populate it with a query, preferably a parametised one and then it doesn't matter if they bail halfway through, the query was never completed so it wasn't sent to the database.

Likewise, if you don't provide a DELETE query to the invoice table then records cannot be deleted only UPDATE to a cancelled/completed status.

 

[vbaInet]

I would probably just use the Autonumber as the Invoice make it PK and prevent anyone from ever deleting from the Invoice Table the actual invoice numbers. As others have stated you can flag the Invoice as Invalid / Deleted but never ever allow anyone to remove it.

The "problem" with AutoNumber is if you're entering a new record and suddenly you wish to cancel, the number doesn't roll back.

 

[Lightwave]

The "problem" with AutoNumber is if you're entering a new record and suddenly you wish to cancel, the number doesn't roll back.

More of an accountancy point...
I don't personally see it as a particular importance to have consecutive invoice numbers as presumably if you have a good bank reconcilliation process there shouldn't be unknown amounts going in or out of the bank account. Similarly if you are getting people to pay into a bank account as long as they register the invoice number in the account it really will be viewed independently of any other invoice numbers and the uniqueness is the main protection factor in matching it to the sales ledger. Yes every now and then the consecutive autonumber may be broken but it is probably going to be rare so you are left with an almost consecutive number system that is very easy to implement and maintain and still should ensure good quality corporate governance.

But I take your point I'm not answering the OP question.

 

[vbaInet]

Yes, I agree with you. Pointless to care about the holes really.

I've been hammering the same point since my first post. I was just saying if the OP wants to have less holes, then AutoNumber won't be the way to go in the scenario I gave.

 

[Galaxiom]

Auditors like to see consecutive invoice numbers. It eliminates the possibility of fake invoices being issued.

Cancelling invoices is unusual, particularly "days later". Once the point of sale batch has been closed it messes with the figures to cancel an invoice. Normally a credit note is issued to reverse an invoice.

 

[Chrisopia]

thanks for all the replies guys! Every little helps...

I think perhaps I used the wrong terminology - supposedly you issue a proforma to give credit and until it is paid it becomes an invoice - a receipt as it were.

My company use duplicate pads at the moment, so they're called invoices in general. An issue occurs when an invoice has began, been created, or sent,
and through companies going under (bad debt), or mistakes to an order, or customer dissatisfaction the invoice remains unpaid, and is therefore cancelled. Usually a separate invoice is made out with the relative amendments.

I've already had discussions about Autonumber in many posts, and it's not guaranteed to count upwards, plus I'm using certain scripts to add a prefix to the numbers, a code to resemble which branch the invoice belongs to. So in effect, for example there are 3 branches, so there will be 3 invoice number 1, number 2, and 3... but with the prefix they are still unique numbers

We use a triplicate pad for our invoices... one is the customer copy, one is ordered by date, and one is ordered by number, so it's important to have a seamless count of invoices.


I know if it has related tables, it prevents you from deleting the invoice, but what if the related tables have nothing attached to it yet?
Also I need the archive of the invoices on the database to remain constant, e.g. once they're paid the data cannot be changed etc. this goes for things like the billing address of that particular invoice - even if its no longer valid,

I guess the paper free office dream is long gone now!

 

[gemma-the-husky]

for something like invoice numbers - i prefer to use a control table to store the next invoice number, rather than use dmax.

if you need a new sequence, you can just change the value in the control table