Using up Blank Records in an Invoicing Database

[Nod_Swerdna]

I have written an invoicing module for my program and some users in the office are dirtying a record and then leaving the module without completing the invoice. (This leaves behind an almost blank record with a period in one field or a space, etc). When someone else starts an invoice from another workstation it skips this almost blank invoice and starts a new one, skipping the almost blank one. Is there a way to check for these orphans automatically before using another invoice number. This happens once or twice a day so it is of concern to my bookkeeper who checks the sequence of invoices to make sure none are missing before posting them to the accounting program.


Any help would be appreciated - Don...

[ItsMe]

It is not an easy thing to address. One approach is to give the user the ability to delete a record if they decide to abandon and orphan said record. Of course, the design of your application will have to force them to choose between completing the transaction and deleting the transaction.

I think the first thing you should consider is sequential numbering. It is recommended not to use the Autonumber field for Invoice numbers. If you need to account for each Invoice number, the Autonumber field is not suitable. I would recommend a field of type long or double. This field would not be the PK for your table. This field would be a Business Key, a Key that is visible to Users but not necessarily used by a Query.

Many developers will use DMax to determine the next available invoice to use. It may be advisable to use a separate table to generate and log these sequential numbers. In a multiuser environment, you will need to approach it methodically and use additional code to maintain referential integrity (in the event of a record deletion, etc.).

[Nod_Swerdna]

Thank you for your very helpful suggestions. I can see that it was a mistake to use the Autonumber field. In lieu of this when a new invoice is needed I had thought to scan each record from the current day looking for a numbered record that has little or no information in the user fields and use that record first before creating a new record. I thought I might concatenate all the fields from a record and then calculate the size of the resulting string. If it is below a said number of characters then blank all the fields and use the record as the next invoice. Do you think this approach would work. I have other modules in the program where the staff is orphaning records as well and I don't want to rewrite too much code to fix this problem.
Thanks again

[ItsMe]

 I can see that it was a mistake to use the Autonumber field.

The Autonumber field is a very important data type within Access. However it has a specific purpose. Most everyone will use Autonumber as the Primary Key. In your case, I would use it as the PK and create another field for the invoice number everyone sees. This additional field would be considered a Business Key and the Autonumber would be the Primary Key.

 In lieu of this when a new invoice is needed I had thought to scan each record from the current day looking for a numbered record that has little or no information in the user fields and use that record first before creating a new record.

It is an interesting thought to avoid many voided records. However, what happens if someone orphans a number at 5PM? Your approach would not see this until the next time your validation code ran, the following day. You would still need additional code to explain orphans/voided records.

I thought I might concatenate all the fields from a record and then calculate the size of the resulting string.

I do not know if this is appropriate. Why not check to see if any of the required fields are complete? You can do this data validation at the time of data entry. If validation fails, refuse to close the form and give the option for the User to delete. Using your suggested approach seems to leave chance as the deciding factor whether or not to delete.

[orange]

You could check that each required field has a value before accepting the record and updating your database(table). Orphans as such should not be in your database.
As ItsMe said, autonumber has special uses and properties. It should not be used if sequential numbering is important to your design.

Here is more info on autonumber.