steven wrote:
I am creating some financial reports in Access 2000, and I am having
problem that I can't resolve.
The report sorts information first by vendor, then by budget line
number. It generates the information I need accurately, but I am
having problems with the layout.
-The budget line header contains information about the vendor--it looks
great.
-The budget line footer contains the sum, and I am not having any
problems there.
-The budget line detail is where I am having problems. Its size varies
from 1 line to several lines, based on the number of invoices that a
particular vendor has. Is there a way to lock the detail section so
that it always contains 15 lines and so the necessary details fall into
the appropriate fields?
I would appreciate any insights that you might have. I need to resolve
this issue because the reports have to look identical to the original
that is approved by the state.
Thanks for your help,
Steven
Here's a response I gave last year.
If you are a programmer utilizing VBA, please look at
MoveLayout/MoveNext/PrintSecion in on-line help.
In my example code below, I was getting the number of lines that will be
printed in a memo field and the number of detail lines. I wanted to
start printing at a certain position on the page, about 1/2 way down,
depending on the memo and detail line count.
You might want to create a running sum variable that is reset on page
breaks or something like that. Increment the variable for each detail
line. Then run a loop (in the GroupHeader0 band). Ex:
Dim intLineCnt As Integer
Here is the example code. It will not do what you want but it should
provide most of the info to get you started. On the report, I
created/dropped a field called MoveCount and it is in the GroupHeader0 band.
Private Sub GroupHeader0_Format(Cancel As Integer, _
FormatCount As Integer)
'intCount is a global variable for the report
If Me.MoveCount < intLineCnt Then
'I want to move to the next line until
'I can start printing. This report prints
'from the middle of the page to the bottom
'so I need to calc when to start printing
'but not overflow to the next page.
Me.MoveLayout = True
Me.PrintSection = False
Me.NextRecord = False
'the printer has moved 1 more row. now inc the
'counter for this loop. No need to DoWhile/Loop
'here since this header code is the loop.
Me.MoveCount = Nz(Me.MoveCount, 0) + 1
Else
'it's found when to start printing. Let it rip
Me.MoveLayout = True
Me.PrintSection = True
Me.NextRecord = True
End If
End Sub
Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer)
'reset MoveCount on the new page. PageHeader will be executed
'before the GroupHeader.
Me.MoveCount = 0
Dim rst As Recordset
Dim strSQL As String
Dim lngWidth As Long
Dim lngHeight As Long
Dim lngTotalLines As Long
Dim lngRet As Long
'calls a routine to get the number of detail records
'to print on this page
intRecCnt = GetRecCount()
'calls a routine to determine the number of lines in a memo field
If Me.Notes > "" Then
'get the number of lines it will take to
'print a memo field. This is a Stephan
'Lebans routine found at his web page. Find
'a message from him in this newsgroup for
'a link to his web site.
lngRet = fTextHeight(Me.Notes, , _
lngHeight, lngWidth, lngTotalLines)
End If
'used to calc where to start printing on a page....31 is 1/2 way
'way down the page in my example. lngTotalLines returned in
'the call to fTextHeight
intLineCnt = 31 - lngTotalLines - (intRecCnt + 1)
End Sub
