I would like some input/thoughts. The application I'm developing has an order entry function. After an order is input, it needs to create a customer invoice. No big deal. My Invoice Report has a section where the customer name, address, state, etc is shown and a Detail section where the product purchased, price, discount, etc. is placed. No big deal. The issue I would appreciate help/your thoughts on is that the customer wants at any time in the future to pull up a customer and click on a button to see previously created invoices. I could use a continuous sub-form to show invoice dates but I'm struggling with how to reconstruct the detail of every previously created invoice that currently is generated "on the fly." Please give me your thoughts on how to do this.
You need to store the data used to create an invoice in a separate table or set of tables and base the invoice report on it. If you use the main data tables for generating invoices, any change in one of the "living" data tables (e.g. change in the address of a customer) would mean that a later re-print of a former invoice for this customer would produce a document that would be different from the original invoice.
This is a legal obligation in many places and is mandatory for being SOX compliant.
Thanks, that was the strategy I was tentatively considering. Now it will get a little more complicated if the user ever needs to reprint the invoice I need to make sure I don't "recreate/add the same data" in the two invoice tables and only add new data there, i.e. if they make a partial payment. I need to think through the conditions to add data to the invoice tables. I appreciate your help.
One simple solution consists in adding a boolean column [Invoiced] to the [Sales] table. The function or SQL procedure that creates the invoice must then test that column and only process rows in [Sales] for: WHERE [Invoiced] = False.
A more complex solution (one I actually use) has a Long Integer column [Invoice_ID] in the [Sales] table to store a copy of the primary key of the [Invoices] table. The rule for creating invoices then includes: WHERE [Invoice_ID] Is Null. This method allows you to later retrieve the details of every invoice using its primary key to extract the details from the [Sales] table.