Adding blank rows to a report

[T. McConnell]

Hello all,
I have a question, I am looking for a simple way to add blank rows to a report when it opens up. If I have a report that opens up and only has lets say 10 rows, but when printing I would like it to display at least 35 rows as some of the other reports being printed from the same button may contain up to 30+ rows. There is nothing too crazy with the report. I have seen some similar posts, but seems to be more than what I am needing. (maybe)
I will have two options to print a report one will be pulled from a dropdown list to generate a report based off of a particular truck number. Then another report which will pull all trucks and separated by the truck number on the report to print out.
My main thing is to find a way to populate the report to have extra lines in the print preview/output.
Attached are what I currently get and what I am trying to get it to look like.
The report name is TruckLog and the form the report generates from is PrepReport. The table housing the data is tblMain. I also have a query named TruckLogQry that pulls in the info from tblMain. Any help would be greatly appreciated.

 

[June7]

Review:

How to add blank lines to complete an Access report so that it matches a printed form? - Stack Overflow

How to Pad Access Reports With Blank Rows - vb123.com

 

[arnelgp]

Here is also an example.

 

[T. McConnell]

Here is also an example.

I opened up the report, but it still has only the 2 records in it and no extra lines, am I missing something simple?

 

[MajP]

This can be done simpler IMO using a variation of the link posted by @June7. The report has several handy features specifically for doing this

• MoveLayout: if False, prints on top of what was printed last;
• NextRecord: if False, prints the same record again;
• PrintSection: if False, doesn't print any data

So if you simply make a union query and create one blank row then you just print it multiple times by setting the nextrecord to false.

SELECT
  yourTable.ID,
  yourTable.OrderNumber,
  yourTable.StopNumber,
  yourTable.CustomerName,
  yourTable.PONumber,
  yourTable.ItemNumber,
  yourTable.ModelNumber,
  1 as Sort
FROM
  yourTable
UNION
Select
  null,
  Null,
  Null,
  Null,
  Null,
  Null,
  Null,
  2
From
  yourTable
ORDER BY
  8,
  1;

Code

Const MAX_LINES = 26
Private Counter As Integer
Private Total_records As Long

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
  Counter = Counter + 1
  If Counter >= Total_records And Counter < 27 Then
    Me.NextRecord = False
  Else
    Me.NextRecord = True
  End If

End Sub

Private Sub Report_Load()
      With CurrentDb.OpenRecordset(Me.RecordSource, dbOpenSnapshot)
        If Not (.BOF And .EOF) Then
            .MoveLast
            .MoveFirst
        End If
        Total_records = .RecordCount
    End With
End Sub

You must look at this in Print Preview. This may be the same as @arnelgp

The example posted by @June7 the author does not add the blank row. Instead they repeat printing the last row in the same manner but set the forecolor and backcolor of the controls to the same color making it appear to be empty. Same idea different technique.

 

[T. McConnell]

This can be done simpler IMO using a variation of the link posted by @June7. The report has several handy features specifically for doing this

• MoveLayout: if False, prints on top of what was printed last;
• NextRecord: if False, prints the same record again;
• PrintSection: if False, doesn't print any data

So if you simply make a union query and create one blank row then you just print it multiple times by setting the nextrecord to false.

SELECT
  yourTable.ID,
  yourTable.OrderNumber,
  yourTable.StopNumber,
  yourTable.CustomerName,
  yourTable.PONumber,
  yourTable.ItemNumber,
  yourTable.ModelNumber,
  1 as Sort
FROM
  yourTable
UNION
Select
  null,
  Null,
  Null,
  Null,
  Null,
  Null,
  Null,
  2
From
  yourTable
ORDER BY
  8,
  1;

Code

Const MAX_LINES = 26
Private Counter As Integer
Private Total_records As Long

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
  Counter = Counter + 1
  If Counter >= Total_records And Counter < 27 Then
    Me.NextRecord = False
  Else
    Me.NextRecord = True
  End If

End Sub

Private Sub Report_Load()
      With CurrentDb.OpenRecordset(Me.RecordSource, dbOpenSnapshot)
        If Not (.BOF And .EOF) Then
            .MoveLast
            .MoveFirst
        End If
        Total_records = .RecordCount
    End With
End Sub

You must look at this in Print Preview. This may be the same as @arnelgp

The example posted by @June7 the author does not add the blank row. Instead they repeat printing the last row in the same manner but set the forecolor and backcolor of the controls to the same color making it appear to be empty. Same idea different technique.

MajP,
I tried your code and when opening up your report it seems to work fine, when I copy your code over to mine, it seems to repeat the last record in the report till it fills up the max number of rows. I am attaching what I have now as a DB and basically you open up the Prep Report form, if you select 10/10/2022 for the date and Truck XDT06 it should show 2 records, but I can't get the additional lines to add to the report. Any suggestions? I did have some more columns to add than before.

 

[MajP]

MajP,
I tried your code and when opening up your report it seems to work fine, when I copy your code over to mine, it seems to repeat the last record in the report till it fills up the max number of rows. I am attaching what I have now as a DB and basically you open up the Prep Report form, if you select 10/10/2022 for the date and Truck XDT06 it should show 2 records, but I can't get the additional lines to add to the report. Any suggestions? I did have some more columns to add than before.

I am not at my computer so I can not check. My code does print the last record multiple times, but I created a blank record with the union query. Did you do the same? If not you will get the last record to repeat and it will show.

 

[T. McConnell]

I am not at my computer so I can not check. My code does print the last record multiple times, but I created a blank record with the union query. Did you do the same? If not you will get the last record to repeat and it will show.

Which part of the code specifically drives that piece? I have tried modifying different parts of the code since I added more fields to the report. So I may have messed something up and not sure where to fix it now. Sorry I know the general basics of some things, but not enough apparently.

 

[MajP]

It is not code, it is the union query. The union query creates a row at the end with no values except on a field to sort by.

 

[T. McConnell]

It is not code, it is the union query. The union query creates a row at the end with no values except on a field to sort by.

Here is my Union Query code

SELECT tblMain.DateText, tblMain.UniqueID, tblMain.OrderNumber, tblMain.DeliveryDate, tblMain.TruckID, tblMain.StopNumber, tblMain.CustomerName, tblMain.PONumber, tblMain.ItemNumber, tblMain.ModelNumber, tblMain.Qty, tblMain.EToteNumber, tblMain.Trucks, tblMain.EnterToteHere, 1 as Sort
FROM tblMain
UNION Select Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, 2
From tblMain
ORDER BY 15, 1;

Here is the output still when selecting either all records currently or just by a specific truck. (One screen shot of just the truck outputs only the two lines still with no extra spaces. The other screenshot shows the last entry with multiples still showing.

Here is the full code I have for all the events for the report.

Option Compare Database
Option Explicit

Const MAX_LINES = 30
Private Counter As Integer
Private Total_records As Long


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
  Counter = Counter + 1
  If Counter >= Total_records And Counter < 31 Then
    Me.NextRecord = False
  Else
    Me.NextRecord = True
  End If
End Sub

Private Sub Report_Load()
With CurrentDb.OpenRecordset(Me.RecordSource, dbOpenSnapshot)
        If Not (.BOF And .EOF) Then
            .MoveLast
            .MoveFirst
        End If
        Total_records = .RecordCount
    End With
End Sub

I appreciate all of your all's help on this. I truly do, Thank You!

 

[MajP]

Notice in your image that the blank row is at the top of the page. To make this work the blank row has to be sorted last. Although your query was correct you had Sorting and Grouping sort by ID not by the "Sort" field. So the code was doing the correct thing and repeating the last row, but you had the incorrect last row.

 

[arnelgp]

see TruckLog2 report in Print Preview.

 

[T. McConnell]

@MajP and @arnelgp
Thank you both so so so much, as always this forum has been so helpful. And thanks to everyone in this thread for their assistance, this is finally what I was trying to accomplish.

 

[T. McConnell]

One last thing, I noticed when I use the main form to open the report filtered by a specific date and truck, it only displays the 2 records again and not the one with the lines, if I open up the report normally, it displays the additional lines correctly. Any suggestions?

 

[MajP]

In my approach the additional lines will only happen in Print mode or Print Preview, since it is not really adding records but only forcing multiple print events to occur. When you open from the form are you specifying acViewPreview as the argument for the view?

 

[MajP]

If that does not work, you may want to adapts @arnelgp method since that method is adding "real" blank rows using a temp table of blank rows.

 

[T. McConnell]

In my approach the additional lines will only happen in Print mode or Print Preview, since it is not really adding records but only forcing multiple print events to occur. When you open from the form are you specifying acViewPreview as the argument for the view?

Yes, it is setup to open as acViewPreview on the button.

 

[MajP]

I will have to try to demo this. In all the methods proposed the codes counts the number of records by using the recordsource of the report. But you are passing in a filter which happens after the fact. So it will have to determine how many records are in the filtered set.

 

[MajP]

This is a little tricky based on how and when a report applies the filter.
You use the docmd.openreport and apply a Where condition. Example

DoCmd.OpenReport "TruckLog", acViewPreview, , "TruckID = 'xdt11'"

This does not change the recordsource. It sets the reports Filter property to "TruckID = 'xdt11'" and sets the FilterOn property to true. What happens next I do not know in how it actually applies the filter without changing the recordsource.

But if you apply the above Where condition it will also filter out the one blank row in the Union query. You will have to modify your form to pass the Where condition to include the blank row. Something like this

DoCmd.OpenReport "TruckLog", acViewPreview, , "TruckID = 'xdt11' OR OrderNumber is NULL"

Now your filter will include all records with the TruckID filter and the one blank row.

Then to get the correct recordcount your code has to account for the filter.

Private Sub Report_Open(Cancel As Integer)
Dim strSql As String

If Me.Filter = "" Or Me.FilterOn = False Then
  strSql = "Select * from MainMasterUnion order by Sort, OrderNumber"
Else
  strSql = "Select * from MainMasterUnion WHERE " & Me.Filter & " ORDER BY SORT, OrderNumber"
End If
Debug.Print strSql
Me.RecordSource = strSql
With CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
        If Not (.BOF And .EOF) Then
            .MoveLast
            .MoveFirst
        End If
        Total_records = .RecordCount
    End With
    MsgBox Total_records
End Sub

 

[arnelgp]

here also with Filter.
you can run form1 for this demo that will filter the report on
DeliveryDate = Oct 13, 2022.

you can create any filter on it.