numbering invoices

[David b]

I am creating invoices for an app I am busy with.
The transactions for the invoice come from 2 tables which store Sales and Facilities Hire.

The current arrangement is that I create a temp table using append queries to get transactions from the 2 tables between selected dates. then draw these into a report grouped by the Sales and Facilities Hire
This all works fine.
However the customer requires invoices (reports) to have consecutive numbers and also I need to record the date when this batch of transactions were invoiced, and subsequently settled.

Trying to figure out the best way of making this happen.
Any thoughts
TIA
David B

 

[dan-cat]

Sounds to me like you need a new table 'tblInvoice' with the PK being the invoice number. The invoice number could be an auto-increment number. tblInvoice could store the total transaction values and date created. You could have a sub table to tblInvoice which stores which sales and facilities have been allocated to that invoice number.

If you want to autoincrement the invoice number - the value will have to be stored permanently somewhere.

 

[WayneRyan]

David,

Autonumbers will not give you consecutive Invoice numbers.
If a user starts entering a record, then cancels, the number
is no longer available.

There have been many posts here on generating invoice
numbers. Use the form's BeforeInsert event and use the
DMax function and add 1 to the result for your next number.

Use the Search facility here for Autonumber and Invoice.

Wayne

 

[dan-cat]

Wayne is right - if you use an autonumber field to generate a number, the generation of that number is taken out of your control. My apologies.

 

[WayneRyan]

David,

This link might help:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=31765&highlight=index

Wayne