Creating an invoice number

[Bentleybelle]

I have created an invoice. There may be several lines on an invoice. Each line item generates a log number in the underlying table, but obviously I want only one invoice number and I want Access to generate that number consecutively. I feel this should be (relatively) simple, yet I just happened to see a similar question on another forum and the experts suggested using FileMakerPro. But I don't believe Access can't handle this. Any suggestions please - thanks.

 

[ByteMyzer]

You are quite correct; Access is capable of handling this, and there are several methods of so doing, depending on the database architecture. Will this be a single-user or a multi-user application, front-end/back-end split? Would it be possible for you to post an attachment of your work so far?

 

[Pat Hartman]

You need an additional table. Just as in an order entry db you need an OrderHeader table as the parent for the OrderDetails, you need an InvoiceHeader table as the parent for the InvoiceDetails.

Search the archives here for the hundreds of posts on generating sequence numbers. Look for posts that contain autonumber and DMax().

My recomendation is to just go with an autonumber. It is more reliable and less work. Gaps should not cause any problem for you.

 

[tickes]

invoice header table

once you create the invoice header table, do you create this in your report by making it a sub-report? And how do you save this invoice??

 

[Pat Hartman]

once you create the invoice header table, do you create this in your report by making it a sub-report? And how do you save this invoice??

- There are many views on what an invoice is. It can be identical to an order in which case it doesn't need a separate invoice number. The order number can serve for both purposes. It can be all the orders for a period. It can be all unpaid orders, etc. It can include detail items from the order, it can summarize each order to a line item, it can be just one total amount.

Usually you don't need to copy order details and usually an invoice can encompass multiple orders. That would require two tables. An invoice header which is used to assign an invoice number and the date the invoice was created.
tblInvoice:
InvoiceID (autonumber primary key)
InvoiceDate

The second table is a relation table that connects all the orders that will appear on a given invoice.
tblInvoiceOrders:
InvoiceID (primary key fld1, foreign key to tblInvoice)
OrderID (primary key fld2, foreign key to tblOrder)

The normal process is to read the order table and determine what invoices need to be created. Create the tblInvoice row. Create the tblInvoiceOrders rows. The last step is reading the tblInvoiceOrders for today and printing the actual invoices.