Print all records on a report ONLY IF Invoice NO is same

[Assistance]

How do I get all the detail records for the same invoice no. to appear on the
same page?

 

[Allen Browne]

Presumably you have 2 tables, such as:
- tblInvoice (the header record), with fields:
o InvoiceID primary key
o ClientID relates to tblClient.Client
o InvoiceDate Date/Time
- tblInvoiceDetail (the line items), with fields:
o InvoiceID relates to tblIvoice.InvoiceID
o ProductID relates to tblProduct.ProductID
o Quantity Number
o PriceEach Currency
etc.

To create a report that looks like an invoice:
1. Create a query that uses both tables, along with tblClient and
tblProduct.

2. Create a report based on this query. Output all the fields you will need
for the report from the 4 tables. Also add a column for any calculated
fields, e.g.:
Amount: [Quantity] * [PriceEach]

3. In report design view, open the Sorting And Grouping dialog (View menu.)

4. In the first row of the dialog, choose the InvoiceID field, and in the
lower pane of the dialog choose Yes for both Group Header and Group Footer.
Access adds 2 new sections to the report - the InvoiceID Group Header (above
the Detail), and the InvoiceID group Footer below.

5. Right-click the InvoiceID Group Header (the grey bar), and in the
Properties box (Format tab), set the Force New Page property to Before. This
ensures each invoice begins on a new page.

6. Increase the height of the InvoiceID Group Header to about 3 inches. In
this section, add the fields for the invoice number, invoice number, and the
address panel (customer name and address lines, placed where they will print
correctly for a window-faced envelope.)

7. In the Detail section, add the fields for the line items. Cut their
labels (Ctrl+X) and paste them into the InvoiceID Group Header. Place them
in a row across the bottom of the section (below the address panel.)

8. Line up the fields in the Detail section side-by-side across the report,
each one under its label. Access will print the InvoiceID header section
once at the top of the invoice, and will then print as many Detail rows are
needed for the invoice.

9. In the InvoiceID Group Footer (below the detail section), add a text box
to show the total. For example:
=Sum([Amount])

 

[Assistance]

Thank you very much Mr. Browne. This was very detailed and helpful. However,
my problem is that I have Invoice no. 8251 which has 3 detail records. Yet
each record is appearing on a different page and the sum is totaling all the
records for all the invoices. I need all the records for invoice 8251 or any
other invoice which has multiple records to appear on the same page and sum
for that invoice no. only. Is this possible?

 

[Allen Browne]

Okay, you have missed a step somewhere.

Possible examples:

a) In the upper pane of query design, make sure the 2 tables are joined
(i.e. there is a line from one to the other, on the matching fields
(probably InvoiceID.))

b) In the report, make sure you have the InvoiceID header.

c) If you are using a subreport, remove it and use tthe query with the 2
tables instead.

d) Make sure your total is in the InvoiceID Group Footer, not in the Report
Footer.

e) Check that you are correctly applying the WhereCondition.

and others.

 

[Assistance]

Thank you!!! I have reviewed my steps and applied them per instructions. I
even have a nice looking Form with a subform for the Details. But it seems I
do not know where to apply the WHERE conditions and others you mention below
in e).

Do I apply the WHERE condition in the Report Design on a print - event
procedure to get all details for the same invoice number whether it's 806221
or 8251?

And would this syntax be appropriate?

Select * (all the records )
From Invoice detail table,
Where invoice DetailID = invoice Id.invoice header table?

 

[Allen Browne]

The suggestion is to leave the report so that it shows all records when
opened from the Database window.

Instead, the Click event of the button on your form sets the WhereCondition.
So if the button is opened from the form where you have a record selected,
it shows only that record. But if it is opened from the Database window, it
shows all records.

The linked article shows how to set up the WhereCondition for OpenReport in
the Click event of your button. Here it is again:
http://allenbrowne.com/casu-15.html