Solved Adding blank rows to a report (2 Viewers)

Sun_Force

Member
Local time
Today, 20:31
Joined
Aug 29, 2020
Messages
228
I know that I can use a union query to add blank rows to a report to fill a printed a page.
This method works as far as there is no grouping and sorting in query.
As soon as I add a group to the report, the blank lines are printed on a separate page.
Obviously, because the rows that union query reads from a temporary table and adds to the record source of the report are blank.
And they are counted as a new group, hence a new page.

As a simple example, let's assume I have the following:

SQL:
SELECT 0 As Expr1,OrderedPartsPK, RecID, Ordered_Part, Quantity, CustomerFK
    FROM tblOrders WHERE Delivered=0
    Union All SELECT Top 2 Expr1 AS OrderedPartsPK,
    '', '', '', '' FROM tblReportDummy  Order By 1, 2

This works until there's no grouping in the report.

Now my question:
How can I add blank rows to a report that is grouped by one or two fields. (In my situation CustomerFK)


Any kind of advice is appreciated.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:31
Joined
Oct 29, 2018
Messages
15,590
How about adding the group value in your union query? In other words, instead of blank rows, those additional rows will have the grouping value.

Hope that makes sense.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:31
Joined
May 21, 2018
Messages
5,121
I am not sure where you want the blank but the on print method can help you do this.
OnPrint
The onPrint event can be cancelled which means that record does not get print. Also the onPrint has a printCount which gets updated for each time a section gets printed. So if you have groupings you can count each time a header is printed.

Code:
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
  Static Counter As Integer
  If Me.Names = "dada" Then
    If Counter < 1 Then
      'stay on the same record for 2 times
      Me.NextRecord = False
      Counter = Counter + 1
    Else
      Cancel = True
    End If
  End If
End Sub

In this simple example I look for a record "Dada". Then I stay on the record. Then the second time I cancel the print.

Blank.png


This trick can also be used to print certain records more than once. Just set the me.nextrecord to false for a specific count without the "cancel"

Twice.png
 

Sun_Force

Member
Local time
Today, 20:31
Joined
Aug 29, 2020
Messages
228
How about adding the group value in your union query? In other words, instead of blank rows, those additional rows will have the grouping value.

Hope that makes sense.
@theDBguy

At present I have written a function, this function is called on every report's on-open event and receives the name of a table and a filter. Based on these parameters, it creates a union sql and set this sql as the record source of the report. Simple and effective.

But your solution, if I understand it as you mean, needs a lot of hard coding.
I have to calculate the data for each data in the group (in this case for each CustomerFK), and add the appropriate count of blank rows with the same CustomerFK field to a temp table, then join main table and the temp table and print the data.

If I've misunderstood you, a more detailed explanation, or even a very simple test database is much appreciated. I'm new to this whole database, I'm learning and I have plenty of time. So I don't want to make you rush. Anytime you're free, even a month later, is OK with me.

Thanks again for your time.
 
Last edited:

Sun_Force

Member
Local time
Today, 20:31
Joined
Aug 29, 2020
Messages
228
I am not sure where you want the blank
@MajP
Seems that I wasn't clear enough about what I'm thinking of.

The reports that MS-Access prints, are not something like a pre-printed forms. They end in the middle of the page and the rest (bottom) of page is empty. The following is the normal results of a printed report in a sample database.

2020-11-20_13-54-01.jpg



As you can see, the first half is filled with data, but the rest of page remains blank.
I want to print the result on a page, filled with the cells, like the following.


2020-11-20_13-54-45.jpg


As you see I have the cells filled the whole page, just like the data is printed on a pre-printed cells.
The union query does its job as long as the report has no grouping.
I'm looking for a way to have the same result after adding a group to the report.
It means that if I add CustomerFK as a group, I want to have the same result for the orders from every customer on separate pages.

Thanks for any further advice.
 

arnelgp

once i caught a fish alive...
Local time
Today, 19:31
Joined
May 7, 2009
Messages
13,267
It means that if I add CustomerFK as a group, I want to have the same result for the orders from every customer on separate pages.
see if this is what you need.
 

Attachments

  • GroupInReport.accdb
    544 KB · Views: 123

Sun_Force

Member
Local time
Today, 20:31
Joined
Aug 29, 2020
Messages
228
see if this is what you need.
@arnelgp
I appreciate your try, but unfortunately no. It's already near to what I was trying to do, but still the goal seems to be far away.
Each group should start from a new page. The more data I add, the more difference I can track. Check the following images of print preview.

I may be able to fine tune your try, but tomorrow have a hard exam. I have to study tonight. After the exam is over, I'll try to check your code and hopefully I may find a way to make it work.

Thanks for your time.

2020-11-20_17-06-58.jpg





2020-11-20_17-16-45.jpg
 
Last edited:

arnelgp

once i caught a fish alive...
Local time
Today, 19:31
Joined
May 7, 2009
Messages
13,267
yes, i believe i have a Constant number of lines per page.
you need to Count the number of lines per page then
substitute that to my Constant variable. i have 27 while
the report you showed has 28.
 

Sun_Force

Member
Local time
Today, 20:31
Joined
Aug 29, 2020
Messages
228
@arnelgp
You're a genius. I didn't know I can use multiple unions this way.
I appreciate your time and help.

Thank you.
 

Users who are viewing this thread

Top Bottom