Adding Blank Fields to a Report (1 Viewer)

Dio

Registered User.
Local time
Tomorrow, 05:31
Joined
Jul 7, 2019
Messages
10
[Solved] Adding Blank Fields to a Report

Hi.

I have grouped my records so that each group will be printed separately. However, some groups have fewer records than others. Other groups span two pages when printed. I would like to add blank fields to a report so that when printed. Each group have an equal number of rows, reaching down to the page footer which houses the page totals.

Attached is the condensed version of my still flat-file DB.

Thanks!

Dio
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 21:31
Joined
Jul 9, 2003
Messages
16,245
I don't have access to a PC at the moment so I cannot check my thoughts. I'm thinking that you can tackle this problem with the keep group together property of the actual report. This will force each group to start on a new page.

Sent from my Pixel 3a using Tapatalk
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 21:31
Joined
Jul 9, 2003
Messages
16,245
Actually, reading your post again, I think you are already aware of this!

Sent from my Pixel 3a using Tapatalk
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:31
Joined
May 7, 2009
Messages
19,169
Hi!
the db I made earlier is so simple.
there is no grouping on it.
besides, there is one comment that says
it shows the pad lines on the preview and
not on the print-out. which i verified to be
correct.
since then, i abandon that code and moved on
to investigate on using Union Query.
i think this is the best method, since it
prints the padded lines.
on your db's case, its different since you have
a lot of groupings there.

before anything else make a copy of your original db before
importing the objects I will mention below.
just in case anything goes wrong.

pwede ba ako mag tagalog, hirap na ko mag-ingles.
paki-translate na lang sa colleage mo.

in my test (as far as your sample db), it correctly
put the padded lines.
you may try it on bigger data.

the Objects you need to copy to your db are:

frmNav form (see changes i made and the code behind)
rptPCPP/Doc04A report (see the code behind)
tblCounter table (new, needed for Union Query)
zztblPCPP table (new, needed for Union Query).

goodluck sa project mo.
sana manalo si pacquiao!
 

Attachments

  • db - Copy.zip
    676.3 KB · Views: 195

theDBguy

I’m here to help
Staff member
Local time
Today, 14:31
Joined
Oct 29, 2018
Messages
21,358
Hi Dio. Welcome to the forum.
 

Dio

Registered User.
Local time
Tomorrow, 05:31
Joined
Jul 7, 2019
Messages
10
@Uncle Gizmo. Yes, Sir, I'm aware of it. I am using the page footer to calculate the page totals. The problem is when there are few records in a group, it leaves a huge blank between the last record and the page footer.

Now when I try to use the group footer for the total, some groups span multiple pages and it only appears on the last page. Thank you, Sir, for your time.
 

pekajo

Registered User.
Local time
Tomorrow, 08:31
Joined
Jul 25, 2011
Messages
132
Hi,

This may not help but I had the same issue so I copied all the records to a blank table. Did a count of now many records there were and looped to add (inserting a 'z' so that these records appear at the end of the report) the difference to the table always had the same number of records.
Then on the report I used the Conditional formatting to say if field = z then make the character white so as not to appear on the report.
I'm not a good programmer but code below:

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM Reports"
DoCmd.OpenQuery "Q_Reports2"
DoCmd.SetWarnings True
'************************* Add blank records ****************************
'Set rs1 = Me.Recordset
Set rs1 = CurrentDb.OpenRecordset("Reports")

aa = ""
rs1.MoveLast
rs1.MoveFirst
Dim idx As Integer

'*************************************************** Check for 1 page or 2 *******
'*************************** Fill page 1 or 2
If rs1.RecordCount < 72 Then

RecNum = 72 - rs1.RecordCount
Else
RecNum = 144 - rs1.RecordCount
End If

For idx = 1 To RecNum

DoCmd.GoToRecord , , acNewRec
rs1.AddNew
rs1![CSurname] = "z"
rs1![CourseID] = DLookup("[CourseID]", "Control")
rs1.Update
rs1.MoveNext
Next idx
Set rs1 = Nothing
 

Dio

Registered User.
Local time
Tomorrow, 05:31
Joined
Jul 7, 2019
Messages
10
@arnelgp, sir I studied the code and it turned out the problem is caused by one extra ",". It is fully working now. One thing I noticed at your code is that the footer data only displays when all the page rows are filled with data, but on pages in which some of the rows are empty the footer is blank. I hope you could help me if you know a workaround. Thanks.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:31
Joined
May 7, 2009
Messages
19,169
if you can post the New db, I will have a look at it.
 

Dio

Registered User.
Local time
Tomorrow, 05:31
Joined
Jul 7, 2019
Messages
10
if you can post the New db, I will have a look at it.
@arnelgp, sir I incorporated your code into my original database, so it shares the same code, Union Query, etc. as the one you sent. I am not able to post screenshots in this forum so I attached it in the ZIP file.

The db you sent shows that the Footer text box only displays data when all the columns in the Detail of a page are filled with data (1.png). However when a page contains only a few records, thus, the rest of the rows are filled with blank rows from the Union Query, then the text box in the Page Footer (2.png) is blank.

Another problem I encountered (although not that great of a deal to me) is that when I added a new sort/group in the report, especially the Membership Group (4.png) above the LastName Group (3.png), the blank rows is added to the to the first few rows instead of the last rows (5.png).

Thanks for all the help so far. I really appreciate it.
 

Attachments

  • db - Copy.zip
    1.2 MB · Views: 130

isladogs

MVP / VIP
Local time
Today, 21:31
Joined
Jan 14, 2017
Messages
18,186
The previous post was moderated. Posting this to trigger email notifications.

Until you have ten posts, you do have to zip any attached files.

I've deleted your duplicate post which was also moderated.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:31
Joined
May 7, 2009
Messages
19,169
if you are going to Sort by LastName, FirstName in Descending Order, open
table zztblPCPP and put on Firstname and LastName "0000000000".

if sorting by LastName, FirstName is Ascending, put "ZZZZZZZZ".

the Rule is you edit the report's sort order, edit the table also.
 

Attachments

  • db - Copy.zip
    1.2 MB · Views: 136

Dio

Registered User.
Local time
Tomorrow, 05:31
Joined
Jul 7, 2019
Messages
10
@arnelgp, thank you very much. That is one elegant solution to my problem. It took me quite the time to analyse your code and incorporate it to my DB, but it was worth it.
 
Last edited:

Users who are viewing this thread

Top Bottom