Me.Detail.ForceNewPage Format

[steve21nj]

The code runs to the fifth record and moves the additional records to a second page. But it pushes my footer to page 2, which I would like to remain on page 1 and appear on page 2.

I would like to take it further by carrying everything over from the first page to the second including the Report Header, Page Header, Detail, and Report Footer. On page two, it would display the remaining records under the same format as page 1.

Is this possible? Would I have to do each On Format for the Report Header, Page Header, etc.?

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If [TxtCount] Mod 5 = 0 Then
        Me.Detail.ForceNewPage = 5
    Else
        Me.Detail.ForceNewPage = 0
End If
End Sub

 

[boblarson]

Report footer will be on the last page. Page footer will be on every page but you can use the On Format event of the Footer section to display it only on one page by using something like:

If [Page]<>1 Then
  ' your code to hide stuff here
End If

 

[steve21nj]

Thanks Bob. That works and I moved info from the report header and footer to the page header....thank god its friday!

I have a separate question involving the same report VBA.

In my code I have it limited to 5 records before moving to the next page. The report is similar to an excel sheet where it has the gridlines. I always want the display 5 rows in the report, even if there is only 2. So the 3 additional rows would be blank, which is fine, but it would maintain the integrity of the form.

Pg1 shows with 5 records
Pg2 shows with 1 record, but I’d like to keep the gridlines down to the 5th record.

Is this possible?

 

[boblarson]

Hmm, I'm not sure about this one.

 

[steve21nj]

I was reading random google pages and found something about intNumLines = 5 but nothing on the forum about it. Have you ever used that?

http://www.pcreview.co.uk/forums/possible-force-number-rows-detail-section-t1658880.html

Attached is a picture of the details section. Highlighted is the custom lines. I'd like to simply replicate the entire details section, 5 times, even if the ID only returns 1 value.

The page site talks about drawing the lines in a very elaborate code, I just want to insert the rows using the same text boxes/labels...

 

[boblarson]

Haven't tried it at all.

 

[steve21nj]

I wanted to share this code in case anyone was searching how to insert blank rows…It works well!

Copy and paste the following code to the On_Page event of the report. Basically if you are looking for an excel style setup to display a report, invoice, log, or something similar, this will draw the vertical and horizontal lines in the Detail portion of the report.

Private Sub Report_Page()
Dim intNumLines As Integer
    Dim intLineNumber As Integer
    Dim intTopMargin As Integer
    Dim ctl As Control
    Dim intLineHeight As Integer
    'intNumLines = the number of rows you want, in my case it is 13
    intNumLines = 13
    intTopMargin = Me.Section(3).Height
    intLineHeight = Me.Section(0).Height
    For Each ctl In Me.Section(0).Controls
        For intLineNumber = 0 To intNumLines - 1
            Me.Line (ctl.Left, intTopMargin + _
                (intLineNumber * intLineHeight)) _
                -Step(ctl.Width, intLineHeight), , B
        Next
    Next
End Sub

 

[boblarson]

Thanks for posting back with the solution.