Before you design the report, you need to design the tables to store the
data.
Typically this involves 2 tables:
a) Invoice table (for the invoice header), with fields such as:
- InvoiceID Autonumber primary key
- ClientID Number relates to your client table
- InvoiceDate Date/Time
- HeadNote Memo any comments to print above detail lines
- FootNote Memo any comments to print below detail lines
- Terms Text statment of terms (e.g. 'Strictly 30
days')
b) InvoiceDetail table (for the line items), fields:
- InvoiceDetailID AutoNumber primary key
- InvoiceID Number which invoice this line belongs to
- ProductID Number what product is on this line
- Quantity Nubmer how many of this product
- PriceEach Currency dollars per quantity
Storing the invoices like this solves the problem of the invoice number.
(You can't just assign the 'next available number', as it won't be
consistent. For example, if someone deletes the last 2 invoices, you really
don't want to reissue those numbers to someone else.)
So, you can now create a query that uses both tables, along with other
tables you need (e.g. for clients and products.) Now you can create a report
based on that query. Use the Sorting And Grouping pane to create an
InvoiceID Header section, and an InvoiceID Footer section. Use this header
for any letterhead info you need and the address panel. Use the footer for
the totals, terms etc. Use the Detail section of the report for the fields
that come from the InvoiceDetail table.
You can lay this report out so it matches your current printed report, using
an Image control if you need to print a logo (rather than merely print to
pre-printed letterhead.) Make sure you set the ForceNewPage property of the
InvoiceID Header section to 'before...', so each invoice starts on a new
page.
