Adding Blank records to query group to fill page on report

[Mr Wonderful]

I have a report that page breaks by group
In this example I have 3 groups
Group 1 contains 9 records
Group 2 contains 15 records
Group 3 contains 20 records
Each page can handle up to 40 records
When page 1 prints for group 1 I need the report to print the 9 records and then also print another 31 blank records to fill the page with lines and boxes formatted like the 10 it printed.
Page 2 would print 15 plus an additional 25
Page 3 = 20 + 20
and so on
The report is generated from a dynamic table that gets populated on each run by a series of queries.
The best scenario would be to then populate an additional needed number of blank records for each group based on the count of the existing records in the group.

 

[dhookom]

I wouldn't use a solution that adds any records to tables. You can use the Line method in the On Page event of the report to draw lines and boxes with code. Here is some code that I have used. It adds line numbers which you can remove.

Private Sub Report_Page()
    Dim intNumLines As Integer
    Dim intLineNumber As Integer
    Dim intTopMargin As Integer
    Dim ctl As Control
    Dim intLineHeight As Integer
    Dim intLineLeft As Integer
    Dim intLineWidth As Integer
    intNumLines = 20
    intLineLeft = 720 '1/2 inch from left margin
    intLineWidth = 1440 * 5 '5 inches
    intTopMargin = Me.Section(3).Height
    intLineHeight = Me.Section(0).Height
    Me.FontSize = 14
    For intLineNumber = 0 To intNumLines - 1
        Me.CurrentX = intLineLeft
        Me.CurrentY = intTopMargin + _
            (intLineNumber * intLineHeight)
        Me.Print intLineNumber + 1
        Me.Line (intLineLeft, intTopMargin + _
            (intLineNumber * intLineHeight)) _
            -Step(intLineWidth, 0)
    Next
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Mr Wonderful]

Thanks
Thank will work fine

 

[PHV]

Duane, what is the Step function ?

 

[dhookom]

The step is just a method for specifying the bottom left relative to the top right. Without this the points are plotted based on the section. With the Step, the points are "relative".

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I think there is a much easier approach to printing blank records, especially if you have lots of text boxes. The basic idea is that a report prints a record and then there is an event where it moves to the next record. Basically your codes keeps it from moving passed the next record and just continue to print the last record X times. For the X blank records just set the forecolor and back color to be the same and it appears blank. See Thread702-1653569. Basic gist:

public totalCount as integer

Public Sub printBlankRecords(numberBlanks As Integer)
  Dim recordCount As Integer
  recordCount = DCount("*", Me.RecordSource)
  TotalCount = TotalCount + 1
  
  If TotalCount = recordCount Then
    Me.NextRecord = False
    'once you get to the last record, stay on last record
  ElseIf TotalCount > recordCount And TotalCount < (recordCount + numberBlanks) Then
    Me.NextRecord = False
    'make the font and backcolor the same appearing to be empty record for each field
    Me.fldOne.ForeColor = Me.fldOne.BackColor
    Me.fldTwo.ForeColor = Me.fldTwo.BackColor
  End If
End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
  printBlankRecords getnumberofBlanks
End Sub

This example used a function to determine the amount of blank rows

 

[MajP]

Wrong thread listed. Thread702-1683569

 

[dhookom]

MajP,
Will your suggestion work if there are different numbers of records on each page with a need to fill the remainder of each page with "blank" records?

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I would think so, did not try. I used the record count as my determining factor, but If I was grouping then on format event of the group I could reset the count based on the count per group.

So it would be something like
public totalCount as integer
Public groupCount as integer

Public Sub printBlankRecords(numberBlanks As Integer)
TotalCount = TotalCount + 1

If TotalCount = groupCount Then
Me.NextRecord = False
'once you get to the last record, stay on last record
ElseIf TotalCount > groupCount And TotalCount < (groupCount + numberBlanks) Then
Me.NextRecord = False
'make the font and backcolor the same appearing to be empty record for each field
Me.fldOne.ForeColor = Me.fldOne.BackColor
Me.fldTwo.ForeColor = Me.fldTwo.BackColor
End If
End Sub

I think I then could cheat with a hidden field in the group header. Then in the format event of the header
groupCOunt = me.hiddenGroupSumTextBox
totalcount = 0

 

[MajP]

This one is probably worth a FAQ.
With a group header I put a hidden field "txtCount" with a control source of =count([some detail field])

This allows you to make different amount of blank rows per group to fill the page. This even allows you to define the total amount of rows per page (filled and blank). In this example the first page can have fewer total rows since it has a page and report header.

This works except the case where a group spans multiple pages. If each group is single page it will fill the page with blank rows. I can figure out how to determine the amount of records in a group, but not the amount of records on a page. If someone knows who to do that then it could be modified to do groups spanning a page.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
  Dim blanks As Integer
  blanks = getnumberofBlanks(Me)
  printBlankRecords Me, blanks
End Sub
Private Sub GroupHeader1_Print(Cancel As Integer, PrintCount As Integer)
  MakeDetailVisible Me
  TotalCount = 0
End Sub

public totalCount as integer
Public Function getnumberofBlanks(rpt As Access.Report) As Integer
  Dim totalRecordsPerPage As Integer
  Dim blanksPerPage As Integer
  Select Case Me.Page
    Case 1
      totalRecordsPerPage = 15
      blanksPerPage = totalRecordsPerPage - getVisibleRecordsPerPage(rpt)
    Case Else
      totalRecordsPerPage = 20
      blanksPerPage = totalRecordsPerPage - getVisibleRecordsPerPage(rpt)
  End Select
  Debug.Print "Page " & rpt.Page & " Visible " & getVisibleRecordsPerPage(rpt) & " blanks " & blanksPerPage
  getnumberofBlanks = blanksPerPage
End Function

Public Sub printBlankRecords(rpt As Access.Report, numberBlanks As Integer)
  Dim recordCount As Integer
  Dim visibleRecords As Integer
  
  TotalCount = TotalCount + 1
  visibleRecords = getVisibleRecordsPerPage(rpt)
 
  If TotalCount = visibleRecords Then
    Me.NextRecord = False
    'once you get to the last record, stay on last record
  ElseIf TotalCount > visibleRecords And TotalCount < (visibleRecords + numberBlanks) Then
    Me.NextRecord = False
    'make the font and backcolor the same appearing to be empty record
    MakeDetailBlank rpt
  End If
End Sub
Public Sub MakeDetailBlank(rpt As Access.Report)
  Dim ctrl As Access.Control
  For Each ctrl In rpt.Detail.Controls
    ctrl.ForeColor = ctrl.BackColor
  Next ctrl
End Sub
Public Sub MakeDetailVisible(rpt As Access.Report)
   Dim ctrl As Access.Control
  For Each ctrl In rpt.Detail.Controls
    ctrl.ForeColor = vbBlack
  Next ctrl
End Sub
Public Function getVisibleRecordsPerPage(rpt As Access.Report)
  getVisibleRecordsPerPage = rpt.txtCount
End Function