<<But you call the function from the query, so calling it
again later doesn't affect the query.>>
I see your point and I'm going to test it out!
<<If your invoice could possible go to more than one page and
you still need to fill out the last page, change the
function to use:
Items = DCount("*", "MyQuery") Mod 28>>
Won't usually work because pages after page 1 usually don't have the report
header so more than 28 total lines will fit on the page.
In my current case I knew there would never be more than one page. However
when there is a possibility of the report being two pages but not more, you
might use something like this in the BlankLines function:
Items = DCount("*", "MyQuery")
Select Case Items
Case <= 28
BlankLines = 28 - Items
Case Else
BlankLines = 70 - Items
End Select
Here's something else I picked up off
www.mvps.org/access that you might
want to use:
DoCmd.OpenReport "MyReport", acViewPreview
DoCmd.Maximize
Reports!MyReport.ZoomControl = 90
Your report will open at 90% magnification which is a perfect fit in the
report window.
Marsh,
I have always had a great respect for you from watching your responses to
posts. You have gained another big notch in my respect for you for your
courteous and professional response in this thread! I look forward to
continuing watching your answers to posts.
Steve
"Marshall Barton" <ma*********@wowway.com> wrote in message
news:nc********************************@4ax.com...
Continuing inline below
--
Marsh
MVP [MS Access]
PC Datasheet wrote:See answers to questions below ---
Marie wrote:
>Here's an outline of what I used ---------------
>Data for my report comes from a query. The query includes the primary
key >and five fields from a table.
>1. Create TblBlankLine:
> TblBlankLine
> ARow
> A1
> A2
> A3
> A4
> A5
> Set ARow data type to long integer and A1 to A5 data types to
matchthe >data types of the fields in the query. ARow is numbered 5000001 to
5000028 >2. I needed a total of 28 records and blank lines in my report. Put
the >following in a standard module:
>Function BlankLines()
>Dim Items As Integer
>Items = DCount("*", "MyQuery")
>BlankLines = 28 - Items
>End Function
>3. Union MyQuery with TblBlankLine and include the Where clause:
>WHERE TblBlankLine.ARow<=(BlankLines()+5000000)
>4. Put the following in the ReportHeader_Format event:
>Call BlankLines
>
>When the report opens, BlankLines is calculated. The Union query
returns28 >records; whatever number of records from MyQuery and the remainder as
blank >lines. Note that the fields in the report must show their borders.
"Marshall Barton" wrote Thanks for posting back with your results. I think that's a
better approach than either of the ones I was thinking of.
Two minor questions:
1 ) Why number the blank lines way up in the 5000000 range?
That's a reasonable way if the invoice details are sorted by
their key value (row number).
ARow matches up to InvoiceID in the Union query. So the Where clause
becomesa filter both to the InvoiceDetail records that are displayed as well as
theblank records from TblBlank records that are displayed. The 5000000 range
isinsurance that no InvoiceDetail records get missed. If for example ARow
inTblBlankLines was numbered 1 to 30 and the Where clause:
WHERE TblBlankLine.ARow<=(BlankLines()+0)
was used, any InvoiceDetail record with InvoiceID greater than
(BlankLines()+0) would never get printed.
Gotch ya.
2) Why do you call the BlankLines function in the Open
event? I don't see where this does anything!?
The BlankLines function is called in the ReportHeader_Format event. This
calculates the value of BlankLines() used in the Where clause of the
unionquery.
But you call the function from the query, so calling it
again later doesn't affect the query.
If your invoice could possible go to more than one page and
you still need to fill out the last page, change the
function to use:
Items = DCount("*", "MyQuery") Mod 28
You came up with a nice approach here Steve and I'm glad I
stumbled into it.
