Add Blank Fields To A Report (1 Viewer)

Derrick T. Davidson

Registered User.
Local time
Tomorrow, 02:32
Joined
Jan 31, 2016
Messages
15
I have a report which derives its information from a query. The report is a summary of cargo going out per day, and works fine. My problem is there are 20 entries per sheet and if I have less than twenty the report cuts off after the last entry.

Is there a way (besides adding extra zero entries) to show blank fields up to twenty lines.

I know this sounds crazy but it is an official form and can not be changed, simple in excel but unsure if it is possible in access??
 

Ranman256

Well-known member
Local time
Today, 14:32
Joined
Apr 9, 2015
Messages
4,339
you MUST have some data to report, but blank lines are not data, so...
Add a field that creates records, but doesnt have to show on the report.
I usu add an extra virtual query like (select "myRpt" as Title) then add this query into my main data query, BUT NOT JOIN THEM. This will create additional records.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:32
Joined
May 7, 2009
Messages
19,169
lucky for you there is a fix, but i need your db, so i can paste the code. i have a hard time explaining.
 

Derrick T. Davidson

Registered User.
Local time
Tomorrow, 02:32
Joined
Jan 31, 2016
Messages
15
Do you need the complete database or just the query and report? The database is rather big, the report is rough (in the design stage).

I found the code below on the internet but it stalls at "Count" not the correct code?

Code:
 Private Sub Report_Open(Cancel As Integer)
RecordNumber = Count([tblDetail]![detOrigin]) 'it counts the number of records in the form/report
FixedNumber = 20 '(e.g.. you want a 10  filled or empty lines long report)
Counter = FixedNumber - RecordNumber
Do While Counter > 0
DoCmd.OpenQuery "AddBlankRecord" 'run a query that add a blank record
Counter = Counter - 1 'substract one to the counter
Loop
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:32
Joined
May 7, 2009
Messages
19,169
here is your report, kabayan. thjis will only work in Print preview, but not in Report view.
 

Attachments

  • Mail - Copy.accdb.zip
    359.8 KB · Views: 362

sneuberg

AWF VIP
Local time
Today, 11:32
Joined
Oct 17, 2014
Messages
3,506
I haven’t looked at arnelgp’s solution yet, but here how I’d do it. I’d first simplify the record source of the report eliminating unused field. (I didn’t this time, but I would if I did it over) Then create a table, let’s say tblBlanks that matches the record source. With some copying you can do this with a Make Table query. Then add a numeric field to the end of that table. Let’s say BlankID. Then add twenty records to this table with BlankID going from 1 to 20. We are going to make a UNION ALL query with the record source of the report and this table so in the record source query of the record you want to add this additional BlankID to the SELECT part of the query. Just add 0 as BlankID. This is so the queries have matching columns for the UNION ALL query. Then make a query from the tblBlanks. Copy and paste the SQL of this to Notepad and Add UNION ALL after it. Then copy and paste the SQL from the report record source query after the UNION ALL. Create a union query with the SQL from the notepad. Now change the reports source query to the union query you made. The code will select the appropriate number of records from the tblBlanks. Here’s the code

Code:
Dim RecordCount As Long
Dim FixNumber As Long
Dim BlankRecords As Long
Dim strCriteria As String
RecordCount = DCount("*", "qryPickMaster")
Fixednumber = 20 '(e.g.. you want a 10  filled or empty lines long report)
BlankRecords = (Fixednumber - RecordCount) Mod Fixednumber
If BlankRecords < 0 Then BlankRecords = 0
strCriteria = "BlankID <= " & BlankRecords
Debug.Print "strcriteria = " & strCriteria
DoCmd.OpenReport "rptCN38Master", acViewReport, , strCriteria, acWindowNormal
This code is run from the frmMaster in the attached database. I had to change the sort of the report to get this blank records on the bottom so you will want to do that too.
 

Attachments

  • MailwithBlanks.zip
    380.9 KB · Views: 318
Last edited:

daschrislydon

New member
Local time
Today, 18:32
Joined
Mar 5, 2017
Messages
4
here is your report, kabayan. thjis will only work in Print preview, but not in Report view.

Hello

Thanks for posting this example - it works great.

But... I Just tried printing... and the blank lines don't print?

They appear in the print preview (and not in report view, as you say)

How do we get them to print?
(They also don't appear on a PDF export...)

Thanks once again!

Chris
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:32
Joined
Sep 21, 2011
Messages
14,046
I would have thought that you would need a hidden field in the detail line that will always contain a value?, then a *blank* detail line would be printed?

The *blank* detail line would be the rest of the fields?

Admittedly have not tried that yet, as no requirement for me (yet), but would be keen to know how this would be done, if my idea does not work?
 

daschrislydon

New member
Local time
Today, 18:32
Joined
Mar 5, 2017
Messages
4
I would have thought that you would need a hidden field in the detail line that will always contain a value?, then a *blank* detail line would be printed?

The *blank* detail line would be the rest of the fields?

Admittedly have not tried that yet, as no requirement for me (yet), but would be keen to know how this would be done, if my idea does not work?

The example db that arnelgp posted does what we need display wise.... it fills the rest of the page with blank detail rows, which displays on screen in print preview mode fine.

But when you come to actually print it, the blank rows aren't there anymore.

In fact, with a bit more experimenting, when you come to PRINT the report, the rows that get printed are just the real live data rows (the ones in the table) - the code for recolouring the Forecolor actually runs when you push print... but the bit of code that gives you the extra lines doesn't run...

Any insights?

Thanks
 

daschrislydon

New member
Local time
Today, 18:32
Joined
Mar 5, 2017
Messages
4
Ah. A bit of digging reveals that the iLine variable keeps on counting up after the report has print previewed. (So when printing the iLine starts at 37...... hence things getting messed up...)
Don't know if I have enough knowledge to solve it, but I think that's the key....!
 

daschrislydon

New member
Local time
Today, 18:32
Joined
Mar 5, 2017
Messages
4
OK, seemed to come up with a solution, in case it's useful to others!

Add this to the declaration at top of report code:
Dim blnPreview As Boolean

In the ReportHeader_Format section:
If blnPreview Then
' Print preview mode
Else
iLine = 0
' Print mode
End If

blnPreview = Not blnPreview


Also had to add code into each section of the blanking out bit to RESTORE the black colour to the forecolor (in the If iLine < iTotal Then section and the ElseIf iLine = iTotal Then section)

Seems to work for me.......
 

Dio

Registered User.
Local time
Tomorrow, 02:32
Joined
Jul 7, 2019
Messages
10
Hello Sir arnelgp.

Could you please explain how that works. I have been wanting to do that. Searched the internet the whole week but the common suggestions are either to use union query or adding blank records. I tried downloading the raw file and the one you edited but I couldn't get mine to work.

Thanks,

Dio
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:32
Joined
May 7, 2009
Messages
19,169
this is an old thread.
you should make new one.
anyway here is another example.
if you are unable to make it, upload your db together with the report, the table/query of the source of report.
goodluck, my new friend.
 

Attachments

  • padline.zip
    22.2 KB · Views: 207

Dio

Registered User.
Local time
Tomorrow, 02:32
Joined
Jul 7, 2019
Messages
10
Hi, Sir arnelgp,

I have created a new thread (access-programmers.co.uk/forums/showthread.php?p=1629813#post1629813) (I'm not allowed to post links yet), as you suggested and attached a condensed version of my DB. I tried copying the code from your padline but doesn't work. Maybe because I used Navigation Subform. Something must be wrong with my code.

Regards,

Dio
 

Attachments

  • db - Copy.accdb
    1.6 MB · Views: 203

pmxpo

New member
Local time
Today, 18:32
Joined
Jul 8, 2019
Messages
4
Hi, Sir arnelgp,

I have created a new thread (access-programmers.co.uk/forums/showthread.php?p=1629813#post1629813) (I'm not allowed to post links yet), as you suggested and attached a condensed version of my DB. I tried copying the code from your padline but doesn't work. Maybe because I used Navigation Subform. Something must be wrong with my code.

Regards,

Dio

I would really be glad if can assist me with the following report please.

See attached.
 

Attachments

  • June 2019 FAAC Report.xlsx
    295.7 KB · Views: 167
  • Database.xlsx
    151.9 KB · Views: 207

Users who are viewing this thread

Top Bottom