Access Diva Print Constant Number of Lines

The difficult I do immediately, the impossible takes a little bit longer.

Reports

Tips (Main)

Home

Print Full or Partial Sheet of Same Labels

Print One or More Labels for One or More Clients (Records)

Multiple copies of 1(one) Report using a Cartesian Product query

Keep Main Report/Subreport Records Together

Multicolumn Report with Images

Filtering Reports

Ranking Reports Using Shapes

Custom Page Header

Send Report (or Labels) to Specific Printer

'From https://www.access-diva.com/tips.html
Global intTotalCount As Integer
Global ctl As Control
 
Function fPrintLines(r As Report, lngTotalGroup As Long, lngHowManyRows As Long)
'7.27.2019 rtw modified to make more generic
'For reports with Groups
'Call the fSetCount() function from the Group Header section
'On_Print property place: =fSetCount([Reports]![rptPrintConstantNumberOfLines])
 
'Call the fPrintLines() function from the detail section
'On_Print property place: =fPrintLines([Reports]![rptPrintConstantNumberOfLines],[txtTotalGroup], 20)
 
    intTotalCount = intTotalCount + 1
 
    If intTotalCount = lngTotalGroup Then
        r.NextRecord = False
    ElseIf intTotalCount > lngTotalGroup And intTotalCount < lngHowManyRows Then
        r.NextRecord = False
        fMakeBlank r
    End If
 
End Function
 
Function fSetCount(r As Report)
'7.27.2019 rtw modified to make more generic
 
    intTotalCount = 0
    fMakeVisible r
 
End Function
 
Function fMakeBlank(r As Report)
'7.27.2019 rtw modified to make more generic
 
    For Each ctl In r.Section(0).Controls
        Select Case ctl.ControlType
            Case acTextBox
                ctl.ForeColor = vbWhite
            Case acLabel
                ctl.ForeColor = vbWhite
            Case acComboBox
                ctl.Visible = False
            Case acCheckBox
                ctl.Visible = False
            Case acImage
                ctl.Visible = False
        End Select
  Next ctl
 
End Function
 
Function fMakeVisible(r As Report)
'7.27.2019 rtw modified to make more generic
 
    For Each ctl In r.Section(0).Controls
        Select Case ctl.ControlType
            Case acTextBox
                ctl.ForeColor = vbBlack
            Case acLabel
                ctl.ForeColor = vbBlack
            Case acComboBox
                ctl.Visible = True
            Case acCheckBox
                ctl.Visible = True
            Case acImage
                ctl.Visible = True
        End Select
  Next ctl
 
End Function
 
Function fPrintBlankRecords(r As Report, lngTotalRecords As Long, lngHowManyRows As Long)
'7.27.2019 rtw modified to make more generic
'For reports without Groups
'Call the fSetCount() function from the Group Header section
'On_Print property place: =fSetCount([Reports]![rptPrintConstantNumberOfLines])
 
'Call the fPrintBlankRecords() function from the detail section
'On_Print property place: =fPrintBlankRecords([Reports]![rptPrintConstantNumberOfLines],[txtCount], 20)
 
    intTotalCount = intTotalCount + 1
 
    If intTotalCount = lngTotalRecords Then
        r.NextRecord = False
    ElseIf intTotalCount > lngTotalRecords And intTotalCount < lngHowManyRows Then
        r.NextRecord = False
        fMakeBlank r
    End If
 
End Function
 
Function fAddBlankRecords(r As Report, lngTotalRecords As Long, lngHowManyRows As Long)
'7.27.2019 rtw modified to make more generic
'For reports without Groups
'Call the fSetCount() function from the Group Header section
'On_Print property place: =fSetCount([Reports]![rptPrintConstantNumberOfLines])
 
'Call the fAddBlankRecords() function from the detail section
'On_Print property place: =fAddBlankRecords([Reports]![rptPrintConstantNumberOfLines],[txtCount], 20)
 
    intTotalCount = intTotalCount + 1
 
    If intTotalCount = lngTotalRecords Then
        r.NextRecord = False
    ElseIf intTotalCount > lngTotalRecords And intTotalCount < (lngTotalRecords + lngHowManyRows) Then
        r.NextRecord = False
        fMakeBlank r
    End If
 
End Function

Copy and paste the below into a new Module and save. (Remember don't name the Module the same as any of the Function names.)

1. Add a Constant Number of Blank Lines per Group

a. In the Group Header place a Text Box named txtTotalGroup and put =Count(*) as the Control Source.

b. Place =fSetCount([Reports]![YourReportNameGoesHere]) on the On_Print section of the Group Header, see example to the left.

c. Place =fPrintLines([Reports]![YourReportNameGoesHere], [txtTotalGroup], 20) on the On_Print section of the Detail section, see example below.

Note, change the *20* to constant number of rows you want per Group.

2. Add a Constant Number of Blank Lines to the end of the Report (no Groups)

a. In the Detail section place a Text Box named txtCount and put =Count([ControlThatContainsData]) as the Control Source.

b. Place =fPrintBlankRecords([Reports]![YourReportNameGoesHere], [txtCount], 20) on the On_Print section of the Detail section, see example to the right.

Note, change the *20* to constant number of rows you want per Group.

3. Add a Constant Number of Blank Lines to the Group.

a. In the Group Header place a Text Box named txtTotalGroup and put =Count(*) as the Control Source.

b. Place =fSetCount([Reports]![YourReportNameGoesHere]) on the On_Print section of the Group Header, see example to the left.

c. Place =fAddBlankRecords([Reports]![YourReportNameGoesHere], [txtTotalGroup], 20) on the On_Print section of the Detail section, see example above.

Note, change the *20* to constant number of rows you want per Group.

Images are for code line placement only. See details for which Function to use for desired results.

Print Constant Number of Lines (or Add a Constant Number of Lines)...

This topic comes up from time to time in Forums and the answer usually involves a Temp Table or adding blank records or digging up that Microsoft Report Utilities, How to Print a Constant Number of Lines per Group, sample which involved adding the controls you wanted to *hide* to the Function. I thought why not make something a little more flexible and without the need for Temp Tables, adding a bunch of blank records or adjusting the code each time.

Below are three different Functions...

1. Add a Constant Number of Blank Lines per Group. Group will always have defined number of lines.

2. Add a Constant Number of Blank Lines to the end of the Report (no Groups)

3. Add a Constant Number of Blank Lines to the Group. Group will always have defined number of blank lines

irrelevant of the Groups size. So, if you want 10 extra lines each Group will always have 10 extra blank lines.