Tables
Customers - stores customer name and address - don't worry about phone, email etc
Products - stores product SKU, description, and price - don't worry about supplier etc
Invoices - stores invoice header info, namely, invoice number, date, customer name and address (* see below)
InvoiceDetails - stores product SKU, product description, product price
Obviously there are other fields that belong in these tables (eg, such as a LineID or ItemID in the InvoiceDetails table) but I just want to outline the essentials without complicating the description.
Forms
Invoices - which
- automatically generates an invoice number,
- allows you to select the customer from a drop down and populates customer name and address fields,
- has a subform that is laid out as a table or grid where you can select the product sku in a drop down field in the column and have it populate the product description and price fields for that product in that row
Just one to print the invoice.
If it can be done without macros that would be the preferred solution but if macros are necessary so be it.
As I said, this is not intended to be a production system but to help learn some of the basics. As a former tech writer, if someone can produce such a barebones invoice system, I am happy to write a How To on it and flesh it out to make it easier for others who may be going through the same learning curve as I am.
Thanks in advance.
* Edit to explain why I am storing Customer name and address in invoice header table. It's because customers may change address and even names so storing it in this table means I can view the invoice as it was when it was created. Handy if the customer calls and wants a reprint of an invoice that the lost in a move.
