Force consistent number of lines, even if only a few lines of data

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Hi,

Task: Print invoice to replicate current manual forms.

Question: How can the report be forced to always have 40 lines per form,
even if there are only 2 lines of data? (ie want to keep the cell borders
of all 40 lines)

Do I need to somehow programically add the 38 blank lines to the query
(report recordset), or can this be done via report commands.

Thanks in advance,

Brian
 
Three alternatives:

1. Explain to whoever wants this that they are way out of touch with the
real world, that such paper invoices were created before there were any
computers, and people had no idea how many lines they were going to need on
an invoice. Printing the desired number of lines followed immediately by the
total is much safer.

2. Use the Line method to programmatically draw all the lines directly onto
the page, exactly where you want them, in the Page event of the report.

3. Programmatically keep repeating the last row of the report (but without
the text) by setting NextRecord to False and repeating until the Top
property of the report indicates you are far enough down the page.
 
You can create underlying query for the report so it
always returns multiples of 40 records. The condition is
that your invoice items are numbered 1,2,3... N for each
invoice and that you have a table tblNumbers (Filed
TheNumber, int) with exactly 40 records, containing
numbers 1 to 40.

If your tblInvoiceDetail contains field DetailNum
(1,2,3,4..) then you can write something like this:

SELECT I.InvoiceID, N.TheNumber, I.Qty, I.price,
I.Qty*I.Price
FROM tblNumbers AS N
LEFT OUTTER JOIN tblInvoiceDetails AS I ON
N.TheNumber=I.DetailNum
ORDER BY I.InvoiceID,N.TheNumber

The query returns exactly 40 lines per invoice, with
Qty,Price information where it exists and NULL values for
the rest of the records.

If you anticipate multipage invoices, then the query gets
a bit more complex. You need to calculate how many
multiplies of 40 you will need, and table tblNumbers
needs to have sevaral multiplies of 40 records.

If you find this too complicated, stick with Allen's
solution, just skip the telling anything bad to your
client.

:-)
 
Back
Top