Creating invoices

[Guest]

Hi.
I am develoing an invoicing system. I have created the tables that hold all
the data required for the invoices. I have a separate table that hold the
actual invoice data with invoice #, date, etc.

When running the report that creates new invoices, I would like to
automatically number the invoices, and update the table that hold the invoice
data.

The program should find the last (higest) invoice number already registred,
and start numbering the new invoices with the first free number. Then the
invoice number, client number, date, and content of the invoice should be
written back to the table holding the invoice data. Except for the invoice
number all the other data is created/collected with a query into the report
creating the invoice.

Can anybody give me an description on how to do this?

Regards Petterq

 

[Allen Browne]

This question might be bigger than you think. You will need some
understanding of working with recordsets programmatically to achieve this.

Typically an invoice consists of 2 tables: one for the header record
(invoice number, client id, invoice date, batch number), and one for the
detail records (line items on the invoices.) The line items will depend on
what other tables you have (such as Orders and Order Details), and those
tables must have some mechanism for identifying which records have already
been invoiced. Your interface also needs to prevent those order line items
from being changed or deleted after they have been invoiced.

The code to create the invoice will then perform these steps:
1. Open a transaction (so you can roll this whole thing back is something
does wrong.)

2. Get a new batch number (so you can identify the records created in this
run.)

3. Open a recordset using a total query that identifies the unique clients
who have uninvoiced order line items.

4. Open a recordset into the invoice header table.

5. Loop through the first recordset, AddNew to the 2nd one, and Update.

6. Within the loop, identify the new invoice number, execute an Append query
statement to create new records in the invoice details table for the order
detail records that need invoicing, and flag them as invoiced so they don't
get re-invoiced next time.

7. Commit the transaction.