Automate Invoice Number Assignment

[Karl Burrows]

I have a very simple database (2003) that I use to input time and materials
for invoicing. I have queries and reports that generate invoices, but I
haven't figured out a way to have it autonumber an invoice "on the fly."
Here are the issues:

* There may be clients that are not billed in a given month, so you can't
generate an invoice number based on client.
* Somehow, based on the date of the time/materials billed, it will have to
pick those up within that period to include in the invoicing.
* Invoices are usually generated monthly, but there may be instances where
"special" invoices may be generated mid month.
* There may also be other invoices that may be outside of the normal
invoicing that could have a special invoice number (like 999).

Any thoughts on how to approach this so that as time is input, it is linked
to an invoice number that is generated from the database? Right now, the
query asks for the company name, a range of time (usually monthly) and then
an invoice number, which I track manually in a log, to place the invoice
number within the invoice. Then I save as a pdf and I am done. I just
worry I will duplicate invoices and it just takes time to sequence and
determine invoice numbers every month.

Thanks!

 

[Allen Browne]

Hi Karl

Presumably you have something like a Job table that you fill out when you do
some work for a client, and a JobDetail table which contains the line items
for the job. You could add an InvoiceDetailID field to the JobDetail table.
It is blank until an invoice has been created for that line item.

At the end of the month, you could then run some code to find all the
customers who have jobs that have items that have not been invoiced. The
code then creates an invoice for each customer, copies the JobDetail line
items into the InvoiceDetail for this invoice, and updates the JobDetail
record with the InvoiceDetailID so that next month these JobDetail line
items won't be invoiced again.

The end result is a fully verifiable history: you can see when each job
detail became part of an invoice. In a very simple system, it may even be
possible to use the JobDetail table as the InvoiceDetail table, i.e. the
same items end up being foreign keys to both your Job table and your Invoice
table.

The approach does require some ability with code, recordsets, and action
query statments. For any code like this that automatically creates records,
it is a good idea to assign a batch number. You can then reverse the
automated process if necessary, because you know which items were involved
in the batch.

HTH.