Results 1 to 11 of 11
  1. #1
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103

    Unwanted "shaded" space on Form


    I created a monthly inspection form used by my company which has 2 pages. I then create a single PDF for all the inspections done for the month and these are then printed out for inclusion in monthly billing.
    My first problem:
    How do I prevent the appearance of a “shaded” area withing the margins which shows up on the 2nd page (please see pic)?

    My second problem: is there a way within Access to create individual PDFs (for each customer) from the single PDF created in the first place? This would be based on using the page break set up in the form (2 pages per customer?
    Click image for larger version. 

Name:	FORM shade.JPG 
Views:	30 
Size:	49.0 KB 
ID:	41436

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    1) you might have row shading turned on in the report, but that's just a guess because you only show 1 record.
    2) if you are using Access OutputTo to create the report you would need to open the report filtered to 1 customer, create/send, then close and reopen to the next customer. Usually one writes code to loop through a recordset of what in your case would be customer records.

    I think there is an alternate page color option also; look for it in report design view.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    That looks like an "Alternate Row Color", look in the format tab and change try changing the Alternate Row Color.

    As for splitting the pdf document you could use external shell commands and a command line tool like pdftk to split the single PDF. But I would think it would be easier to write some vba that will loop through each customer's report and save it as an individual pdf.

  4. #4
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    @Micron and @kd2017

    Great, that was the issue (alternate page color), problem 1 solved!

    Regarding problem 2, I am thinking of going to Adobe Pro to divide up the "bulk PDF" once it is generated in Access.But, not sure if Adobe Pro will be able to re-name each individual PDF, though...

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    #2 that seems like a last resort option! If you need help with vba to create a loop to save individual pdf's let us know. What's your experience with vba? Have you tried it yet?

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Why would you not just create multiple PDFs, one for each customer?

  7. #7
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    @davegri
    Great question. I know of only one way doing it by filtering in each customer one by one and then running the PDF function. It's OK for one or two, but going through this for 100+ would be a painful exercise. Is there a better way that I have yet to discover?

  8. #8
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by zkrucz View Post
    Is there a better way that I have yet to discover?
    Yes!

    You would "do it [automatically] by filtering each customer one by one and then running the PDF function" using the power of VBA!

    What's your experience level with VBA? We can help you with the coding, can you post the SQL used to generate the report?

  9. #9
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    @kd2017

    OK, please see below. I would like to be able to name the individual PDFs as Follows: TABLE1.PWS_Name followed by TABLE1.Date_of_Inspection

    SELECT TABLE1.PWS_Name, TABLE1.City_Town, TABLE1.[PWS_ID#], TABLE1.Date_of_Inspection, TABLE1.Arrival_Time, TABLE1.Departure_Time, TABLE1.Primary_Operator_Name, TABLE1.[License_#], TABLE1.Expiration_Date, TABLE1.Person_Conducting_Inspection, TABLE1.Owner_Responsible_Party, TABLE1.Designated_Staff, TABLE1.Present_at_Inspection_1, TABLE1.Present_at_Inspection_2, TABLE1.Zone_1_Inspected, TABLE1.Wellhead_Pit_Inspected, TABLE1.Comments_Wellhead, TABLE1.[Storage_Tanks_ Inspected], TABLE1.Chemical_Addition_Inspected, TABLE1.Water_Quality_Emergency, TABLE1.Water_Loss_Emergency, TABLE1.Cross_Connection_Inspection_Testing, TABLE1.Third_Party_Repair_Calibration, TABLE1.Sample_Collection, TABLE1.Paperwork_Emergency_response_etc, TABLE1.Certification, TABLE1.Operator_Signature, TABLE1.A2, TABLE1.Date_form_provided_to_owner, TABLE1.Meter_Readings_Inspected, TABLE1.Equipment_Calibration_Inspected, TABLE1.Reporting, [ACCOUNT ADDRESS_LIST].[ABC_-SEQ], [ACCOUNT ADDRESS_LIST].INSPECT_ACCT, [ACCOUNT ADDRESS_LIST].BACKFLOW_ACCT, TABLE1.Wellhead_Cap_Secured, TABLE1.Storage_Facilities_Screen, TABLE1.Master_Meter_Reading, TABLE1.Comments_Readings, TABLE1.Filters, TABLE1.[UV_Bulb_Life_%], TABLE1.Hour_Meter, TABLE1.Day_Tank_Level, TABLE1.pH_Reading, TABLE1.Residual, TABLE1.Comments_Water, TABLE1.Distribution_Piping_Facility, TABLE1.Treatment, TABLE1.Filters_Changed, TABLE1.[Well_#1_Reading], TABLE1.[Well_#1_Unit_#1_Reading], TABLE1.[Well_#1_Unit_#2_Reading], TABLE1.[Well_#2_Wellhead_Cap_Secured], TABLE1.[Well_#2_Wellhead_Pit_Inspected], TABLE1.[Well_#2_Reading], TABLE1.[Comments _Well_#2], TABLE1.[Well_#3_Reading], TABLE1.[Well_#2_Zone_1_Inspected], TABLE1.[Well_#3_Zone_1_Inspected], TABLE1.[Well#3_Wellhead_Cap_Secured], TABLE1.[Well_#3_Wellhead_Pit_Inspected], TABLE1.[Comments_Well_#3], TABLE1.[Well_#4_Reading], TABLE1.[Well_#4_Zone_1_Inspected], TABLE1.[Well#_4_Wellhead_Cap_Secured], TABLE1.[Well_#4_Wellhead_Pit_Inspected], TABLE1.[Comments_Well_#4], TABLE1.A1, TABLE1.A3, TABLE1.Sample_Number, TABLE1.Sample_Collected, TABLE1.Type_of_Sample, TABLE1.Comments_Samples, TABLE1.Well_Pump_Pumps_Hour_Meter_1, TABLE1.[Well_Pump_Pumps_Hour-Meter_2], TABLE1.Boosters_Online, TABLE1.System_Pressure, TABLE1.Generator, TABLE1.Generator_Hours, TABLE1.Generator_Oil_Water_Belts, TABLE1.[Propane_%], TABLE1.Diesel_Propane, TABLE1.Storage, TABLE1.Cross_Connection_Inspection_Testing_Date_, TABLE1.Geostamp
    FROM [ACCOUNT ADDRESS_LIST] INNER JOIN TABLE1 ON [ACCOUNT ADDRESS_LIST].PWS_ID = TABLE1.[PWS_ID#];

  10. #10
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Here is some example code for you to try. You will need to edit the outDir and reportName variables to match your situation. I made some assumptions, the biggest assumption is that you want an individual pdf for each record in that query you posted.

    *warning* this could take a while to finish depending on how many records it has to go through. It would be better if you only ran the report on those that havn't already been saved to pdf. It would be trivial to add code to below to first check for an existing pdf that already has the same name and skip outputting that record, though without knowing your situation I'm not sure if that's ideal.

    Code:
    Private Sub Command0_Click()
    On Error GoTo ErrHandler
    
        Dim reportName As String
        Dim outDir As String
        Dim outPath As String
        
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        
        outDir = "C:\report_output\"
        reportName = "Report1"
        
        
        Set db = CurrentDb
        Set rs = db.OpenRecordset("SELECT [pws_id#], pws_name, date_of_inspection FROM table1;", dbOpenSnapshot)
            If Not (rs.BOF And rs.EOF) Then
                rs.MoveFirst
                Do While Not rs.EOF
                    outPath = outDir & rs!pws_name & "_" & Format(rs!date_of_inspection, "yyyymmdd") & ".pdf"
                    DoCmd.OpenReport reportName, acViewPreview, , "table1.[pws_id#]=" & rs![pws_id#], acHidden
                    DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, outPath
                    DoCmd.Close acReport, reportName, acSaveNo
                    rs.MoveNext
                Loop
            End If
        rs.Close
    
    ExitHandler:
        Set rs = Nothing
        Set db = Nothing
        Exit Sub
    
    ErrHandler:
        MsgBox Err.Description, , Err.Number
        Resume ExitHandler
    End Sub

  11. #11
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    @kid2017

    OK, let me give it a whirl. Thank you.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  2. Replies: 3
    Last Post: 02-08-2015, 02:43 PM
  3. Unwanted "Enter Parameter Value" Box
    By molly13 in forum Access
    Replies: 3
    Last Post: 10-23-2014, 01:35 PM
  4. Replies: 4
    Last Post: 01-06-2011, 10:52 AM
  5. Replies: 3
    Last Post: 05-30-2009, 12:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums