Add empty rows in a report (1 Viewer)

tamraght

New member
Local time
Today, 10:43
Joined
Jun 14, 2021
Messages
9
Hello Friends,

I have a report that prints out the attendance sheet with a list of students enrolled in that class. In many cases, students enroll after the class already started. So, we would have to print another sheet and copy the previous attendance data manually. I was looking for a solution to have extra empty rows, so anytime I have new student I would use the same sheet and add new names on it manually, so I don't have to copy the all the attendance data which usually takes time specially when a groupe has many classes already.

I've looking around for a solution for this issue for weeks and still haven't found one. Here is my last hope as I saw some similar issues but couldn't figure out how to apply them in my example.

By the way, I am still a beginner in Access programming.

Thanks lot.
 

Minty

AWF VIP
Local time
Today, 09:43
Joined
Jul 26, 2013
Messages
10,353
If the attendance records were entered into the database, you could simply print the existing attendance records with the new students on the end (or the middle, or the start)?

It doesn't sound as if you are recording the attendance, simply printing a student roster?
 

bastanu

AWF VIP
Local time
Today, 02:43
Joined
Apr 13, 2010
Messages
1,401
Probably the easiest would be to create a small local table that has the same structure as the report's source (as in the student names) add your desired number of empty records then create a union query:

Select qryYourReportSource.* From qryYourReportSource
Union All
Select tblEmptyList.* from tblEmptyList;

Use this new query as the report's record source and you'll have the empty rows.

Cheers,
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:43
Joined
May 7, 2009
Messages
19,169
this will only work on Print Preview.
see attendanceSheet report.
 

Attachments

  • enrollees.accdb
    492 KB · Views: 544

bastanu

AWF VIP
Local time
Today, 02:43
Joined
Apr 13, 2010
Messages
1,401
Please also see copy of attendanceSheet without any VBA. And a copy of the same report printed (as PDF).
Cheers,
 

Attachments

  • enrolleesVlad.zip
    423.1 KB · Views: 235

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:43
Joined
May 7, 2009
Messages
19,169
Please also see copy of attendanceSheet without any VBA. And a copy of the same report printed (as PDF
i already counted the Total lines ("full" page) of the report, it is 33.
on your query you are only adding 10?
so, if there are already 30 students + 10 (on your union),
first page will get 33 ("full" page), while the second page on 7?

using vba both page 1 and page 2 will have 33 lines ("full").
 

tamraght

New member
Local time
Today, 10:43
Joined
Jun 14, 2021
Messages
9
Thank you all for your replies and all the attachements you sent.
Using the Union All method I was having this error as it is shown in this screenshot, and I don't know why.

Sans titre.png

Here is the SQL Code:

Code:
SELECT PRINT_LIST_UNION1.*  FROM PRINT_LIST_UNION1
UNION ALL SELECT TOP 25 PRINT_LIST_UNION2.* FROM PRINT_LIST_UNION2
ORDER BY ID_ADMISSION;
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:43
Joined
Oct 29, 2018
Messages
21,357
Thank you all for your replies and all the attachements you sent.
Using the Union All method I was having this error as it is shown in this screenshot, and I don't know why.

View attachment 92380
Here is the SQL Code:

Code:
SELECT PRINT_LIST_UNION1.*  FROM PRINT_LIST_UNION1
UNION ALL SELECT TOP 25 PRINT_LIST_UNION2.* FROM PRINT_LIST_UNION2
ORDER BY ID_ADMISSION;
Hi. Welcome to AWF!

Reports ignore the ORDER BY clause in the Query or Record Source. Try using the Sorting and Grouping facility to assign your record order.

Hope that helps...
 

Sun_Force

Active member
Local time
Today, 18:43
Joined
Aug 29, 2020
Messages
396
@arnelgp @bastanu
I've been working on this lately. But I wasn't able to use your method (using union query with a dummy table) if the report has grouped.

Imagine I want to print a report of all incoming orders for this month and the report is grouped by CustomerID.
Each page should have 20 rows.
So if I have orders from 5 customers, and each customer has let's say 10 orders, then I need to print 5 pages, each page is filled with orders from a customer and 10 empty lines to fill the page.
Can your union method do this job?

At present I'm using vba and count the orders for each customer, add necessary empty rows to a dummy.
thank you.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:43
Joined
Oct 29, 2018
Messages
21,357
@arnelgp @bastanu
I've been working on this lately. But I wasn't able to use your method (using union query with a dummy table) if the report has grouped.

Imagine I want to print a report of all incoming orders for this month and the report is grouped by CustomerID.
Each page should have 20 rows.
So if I have orders from 5 customers, and each customer has let's say 10 orders, then I need to print 5 pages, each page is filled with orders from a customer and 10 empty lines to fill the page.
Can your union method do this job?

At present I'm using vba and count the orders for each customer, add necessary empty rows to a dummy.
thank you.
Just thinking out loud, but I think so. Can you post a sample db for testing?

Sent from phone...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:43
Joined
May 7, 2009
Messages
19,169
sample, see orderReport in Print Preview.
 

Attachments

  • order_report_padded.accdb
    1.4 MB · Views: 547

Sun_Force

Active member
Local time
Today, 18:43
Joined
Aug 29, 2020
Messages
396
@arnelgp @theDBguy
Thanks for your time. I have a test this coming Monday and I have to study hard to pass it.
I'll get back to you on Monday evening. I'm really sorry to ask a question and not being able to be here.
 

tamraght

New member
Local time
Today, 10:43
Joined
Jun 14, 2021
Messages
9
this will only work on Print Preview.
see attendanceSheet report.
Thank you so much for your effort and your time. I'm still a beginner in VBA. I tried to apply your code in my report, but I was getting an error. So I just used the Union All Method And I think so far it does what I need it to do.

Thanks again,
 

tamraght

New member
Local time
Today, 10:43
Joined
Jun 14, 2021
Messages
9
Hi. Welcome to AWF!

Reports ignore the ORDER BY clause in the Query or Record Source. Try using the Sorting and Grouping facility to assign your record order.

Hope that helps...
Yes It did, thank you so much and also to @bastanu and @arnelgp for the help. You guys are Amazing.
 

Sun_Force

Active member
Local time
Today, 18:43
Joined
Aug 29, 2020
Messages
396
sample, see orderReport in Print Preview.

@arnelgp thanks for the sample file. It seems that you've used a sub report for this purpose. I'm working on it to see if I can format the report and its sub report to print what I need.

Thanks for the sample file


Thank you.
 

pekajo

Registered User.
Local time
Today, 20:43
Joined
Jul 25, 2011
Messages
132
Hi,
What I do is add the number of records required and in the main sort field add either aa or zz depending how you want it sorted (top or bottom) and then on the report in conditional formatting if sort field = aa or zz change the text to white.
 

Users who are viewing this thread

Top Bottom