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: 5
Thanks: 1
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
Attached Files
File Type: accdb db - Copy.accdb (1.58 MB, 6 views)


Last edited by Dio; 07-20-2019 at 03:54 AM.
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,089
Thanks: 476
Thanked 893 Times in 848 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,089
Thanks: 476
Thanked 893 Times in 848 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
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,203
Thanks: 57
Thanked 2,313 Times in 2,219 Posts
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, 9 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: 3,063
Thanks: 36
Thanked 729 Times in 712 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
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 offline   Reply With Quote
Old 07-07-2019, 04:59 PM   #6
Dio
Newly Registered User
 
Join Date: Jul 2019
Location: Philippines
Posts: 5
Thanks: 1
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, 04:52 AM   #7
Dio
Newly Registered User
 
Join Date: Jul 2019
Location: Philippines
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
Dio is on a distinguished road
Re: Adding Blank Fields to a Report

Hi Sir arnelgp,

It been a week since I have touched my laptop. I have incorporated your code to my form. It works perfectly well but when I added some columns to the table. It returned the "Run-time error '3307': The number of columns in the two selected tables or queries of a union query do not match." Maybe I missed something. I'm pretty sure I have updated all the tables, forms and the record source for the report. I'm not really good with Union Queries. Could you please look at it one last time sir. Thanks a lot.

Dio is offline   Reply With Quote
Old 07-14-2019, 05:54 PM   #8
pekajo
Newly Registered User
 
Join Date: Jul 2011
Posts: 19
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
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 02:32 PM.


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