Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-06-2019, 11:10 PM   #1
Dio
Newly Registered User
 
Join Date: Jul 2019
Location: Philippines
Posts: 7
Thanks: 3
Thanked 0 Times in 0 Posts
Dio is on a distinguished road
[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 by Dio; 09-10-2019 at 08:17 PM.
Dio is offline   Reply With Quote
Old 07-07-2019, 03:49 AM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,271
Thanks: 527
Thanked 927 Times in 879 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Adding Blank Fields to a Report

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
__________________
Code:
                 |||||
               @(~‘^‘~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 07-07-2019, 03:51 AM   #3
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,271
Thanks: 527
Thanked 927 Times in 879 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Adding Blank Fields to a Report

Actually, reading your post again, I think you are already aware of this!

Sent from my Pixel 3a using Tapatalk

__________________
Code:
                 |||||
               @(~‘^‘~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 07-07-2019, 08:43 AM   #4
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,392
Thanks: 68
Thanked 2,699 Times in 2,584 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Adding Blank Fields to a Report

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!
Attached Files
File Type: zip db - Copy.zip (676.3 KB, 30 views)
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 07-07-2019, 10:03 AM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,579
Thanks: 50
Thanked 1,050 Times in 1,031 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Adding Blank Fields to a Report

Hi Dio. Welcome to the forum.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 07-07-2019, 04:59 PM   #6
Dio
Newly Registered User
 
Join Date: Jul 2019
Location: Philippines
Posts: 7
Thanks: 3
Thanked 0 Times in 0 Posts
Dio is on a distinguished road
Re: Adding Blank Fields to a Report

@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.
Dio is offline   Reply With Quote
Old 07-14-2019, 05:54 PM   #7
pekajo
Newly Registered User
 
Join Date: Jul 2011
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
pekajo is on a distinguished road
Re: Adding Blank Fields to a Report

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

pekajo is offline   Reply With Quote
Old 09-12-2019, 04:47 PM   #8
Dio
Newly Registered User
 
Join Date: Jul 2019
Location: Philippines
Posts: 7
Thanks: 3
Thanked 0 Times in 0 Posts
Dio is on a distinguished road
Re: Adding Blank Fields to a Report

@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.
Dio is offline   Reply With Quote
Old 09-12-2019, 10:33 PM   #9
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,392
Thanks: 68
Thanked 2,699 Times in 2,584 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Adding Blank Fields to a Report

if you can post the New db, I will have a look at it.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Dio (09-15-2019)
Old 09-13-2019, 08:34 AM   #10
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,369
Thanks: 21
Thanked 893 Times in 878 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: Adding Blank Fields to a Report

Maybe this will help...
https://www.access-diva.com/r8.html
__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp is offline   Reply With Quote
Old 09-15-2019, 05:59 AM   #11
Dio
Newly Registered User
 
Join Date: Jul 2019
Location: Philippines
Posts: 7
Thanks: 3
Thanked 0 Times in 0 Posts
Dio is on a distinguished road
Re: Adding Blank Fields to a Report

Quote:
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.
Attached Files
File Type: zip db - Copy.zip (1.21 MB, 9 views)
Dio is offline   Reply With Quote
Old 09-16-2019, 06:16 AM   #12
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,354
Thanks: 112
Thanked 2,843 Times in 2,593 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Adding Blank Fields to a Report

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 09-16-2019, 10:23 AM   #13
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,392
Thanks: 68
Thanked 2,699 Times in 2,584 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Adding Blank Fields to a Report

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.
Attached Files
File Type: zip db - Copy.zip (1.18 MB, 9 views)
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following 2 Users Say Thank You to arnelgp For This Useful Post:
Dio (09-20-2019), Tera (09-16-2019)
Old 09-20-2019, 04:12 AM   #14
Dio
Newly Registered User
 
Join Date: Jul 2019
Location: Philippines
Posts: 7
Thanks: 3
Thanked 0 Times in 0 Posts
Dio is on a distinguished road
Thumbs up Re: Adding Blank Fields to a Report

@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 by Dio; 09-28-2019 at 11:37 PM.
Dio is offline   Reply With Quote
Reply

Tags
arnelgp

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Subforms randomly adding blank records, not clearing fields SteveDataMan Forms 1 09-26-2013 04:18 PM
adding blank lines in a report irade92 Reports 0 03-26-2013 11:54 AM
Grouped report adding blank page at the end of the report MarcieFess Reports 0 01-10-2013 03:03 PM
Adding Data to blank fields in a table Sanjo Tables 1 03-25-2012 04:23 PM
Adding blank page to report roypython Reports 0 07-31-2006 06:50 PM




All times are GMT -8. The time now is 08:48 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World