Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-16-2018, 11:49 AM   #1
earls
Newly Registered User
 
Join Date: Mar 2018
Posts: 21
Thanks: 6
Thanked 0 Times in 0 Posts
earls is on a distinguished road
print only pages with data

I have a report (image below) that is printed for employees to have signed after each leg of a trip. A job can have two legs (two pages to be signed) or a job can be a one-way trip (one page to sign). How can I make it if a job has one leg one page prints (not two), two legs two signature pages… currently I’m using a page break and it defaults to two pages always. Unfortunately, the table is based on a spread sheet we receive nightly and imported into the DB with all legs in one record.

tblJobs
ID
CustomerName
DOB
Homephone…
jobnumberA
jobnumberB
PickupaddressA
PickupaddressB
DestinationAddressA
DestinationAddressB

Report
page one= A info
page two= B info (again record doesn’t always have B info)
Attached Files
File Type: pdf 172.pdf (70.1 KB, 28 views)

earls is offline   Reply With Quote
Old 05-16-2018, 12:20 PM   #2
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 498
Thanks: 0
Thanked 113 Times in 113 Posts
June7 will become famous soon enough
Re: print only pages with data

My, this looks familiar.

Problem is the 'legs' are not separate records. How is it known that there is only one leg required - empty fields?

Your report design is repeating controls/fields to replicate a multi-record, multi-page report, but it is still just a single record output.

You could use a UNION query to rearrange the 'legs' to individual records and base your report on that query.

Then the report design would not repeat controls for two 'pages'. The pages would generate based on existence of data.

Last edited by June7; 05-16-2018 at 12:26 PM.
June7 is offline   Reply With Quote
Old 05-16-2018, 12:48 PM   #3
earls
Newly Registered User
 
Join Date: Mar 2018
Posts: 21
Thanks: 6
Thanked 0 Times in 0 Posts
earls is on a distinguished road
Re: print only pages with data

I have a form that I mark jobs to save as individual pdf’s (than print at different offices), code is below. I don’t want to mess with a union query as everything else is running perfect and it stores values in query, i don't think a union query can store values. I was really hoping to accomplish task within report if possible.


Code:
Public Sub cmdSaveAsPDF_Click()

Dim qdf As DAO.QueryDef
Dim strPathName As String
Dim rs As Recordset
Dim stDocName As String
Dim strSavedSQL As String
If Me.Dirty Then Me.Dirty = False
stDocName = "rptJobs"
Set rs = CurrentDb.OpenRecordset("SELECT orderid, tripname FROM Orders WHERE SelectedPrint;", dbOpenSnapshot)
If rs.EOF Then
    MsgBox "Nothing found to process", vbCritical, "Error"
Else
    'CreateFolder CurrentProject.Path & "\orders"
    ' store the current SQL
    Set qdf = CurrentDb.QueryDefs("qryjob")
    strSavedSQL = qdf.SQL
    While Not rs.EOF
        qdf.SQL = Left(strSavedSQL, InStr(strSavedSQL, ";") - 1) & " and (orderid = " & rs!OrderID & ");"
        ' put in the same folder as the database
        strPathName = "c:\Jobs\" & rs!Tripname & ".pdf"
        DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, strPathName

        rs.MoveNext
    Wend
    ' restore the SQL
    qdf.SQL = strSavedSQL
    qdf.Close
    Set qdf = Nothing
End If
rs.Close
Set rs = Nothing
End Sub

earls is offline   Reply With Quote
Old 05-16-2018, 01:25 PM   #4
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 498
Thanks: 0
Thanked 113 Times in 113 Posts
June7 will become famous soon enough
Re: print only pages with data

No, queries don't store values, tables store values. Queries manipulate stored data. Queries can do calcs but those calcs are not 'stored'. A UNION is just a series of SELECT statements (up to 50) and those SELECT statements can include calcs. Can even reference another query in the SELECT statements. Or use the UNION as a source for another query and do calcs there. Or do calcs in textboxes on report.

Using UNION for report has no bearing on how the data is input.

Only way I know to suppress print of a page is with code in page section Format event. Since your second page is generated by a page break control, not sure code can manage.


Last edited by June7; 05-16-2018 at 01:59 PM.
June7 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Pages breaks - so that data isn't spread over 2 pages Maranna Forms 3 06-22-2009 09:06 AM
SQL and Data Access Pages and ASPX pages CyrusMacsen ASP and ASP.NET 2 12-13-2005 09:09 AM
Print these pages polygonking Modules & VBA 0 09-27-2004 01:48 PM
Print only the first 2 pages Pappy Reports 3 06-26-2003 10:28 AM
Print Odd and Even Pages whb52ford Reports 1 08-13-2001 10:58 PM




All times are GMT -8. The time now is 03:30 AM.


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

Sponsored Links

How to advertise

Media Kit


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