print only pages with data (1 Viewer)

earls

Registered User.
Local time
Today, 09:01
Joined
Mar 27, 2018
Messages
21
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)
 

Attachments

  • 172.pdf
    70.1 KB · Views: 146

June7

AWF VIP
Local time
Today, 05:01
Joined
Mar 9, 2014
Messages
5,425
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:

earls

Registered User.
Local time
Today, 09:01
Joined
Mar 27, 2018
Messages
21
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
 

June7

AWF VIP
Local time
Today, 05:01
Joined
Mar 9, 2014
Messages
5,425
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:

Users who are viewing this thread

Top Bottom