Print in pdf without opening the form (1 Viewer)

[Yuly17]

Hi, I have a program that prints several documents in pdf using a print button. Each report that gets printed, opens in and the close it. It take some time to do all the printing. Is there any way I can upgrade the code to make this faster and avoid opening the forms?

This is an example from one part of the documents printed. I did try to change acPreview to acNormal but it started printing all the records from the same report



MyFileName = Me.AssemblyPartCode & "-FittingsRecordSheet" & ".pdf"
DoCmd.OpenReport "FittingsRecordSheet", acPreview, , strWhere
DoCmd.OutputTo acOutputReport, "FittingsRecordSheet", acFormatPDF, MyPath & MyFileName, False
DoCmd.Close acReport, "FittingsRecordSheet"

MyFileName = Me.AssemblyPartCode & "-PickingList" & ".pdf"
DoCmd.OpenReport "PickingList", acPreview, , strWhere
DoCmd.OutputTo acOutputReport, "PickingList", acFormatPDF, MyPath & MyFileName, False
DoCmd.Close acReport, "PickingList"

MyFileName = Me.AssemblyPartCode & "-AssemblyTicket" & ".pdf"
DoCmd.OpenReport "AssemblyTicket", acPreview, , strWhere
DoCmd.OutputTo acOutputReport, "AssemblyTicket", acFormatPDF, MyPath & MyFileName, False
DoCmd.Close acReport, "AssemblyTicket"


Thanks in advance

 

[Gasman]

Not if the reports source has criteria that refers to form controls.

 

[Pat Hartman]

Yes. The problem is that the OutputTo does not support a where option the way the OpenReport does.

To handle this, I get my criteria from a form control so it is embedded in the Report's RecordSource querydef

Select ...
From ...
Where Somefield = Forms!yourform!txtSomeField

From what I see, you are printing three reports with the same criteria so that will be sufficient.

In the cases where I have to print reports with varying criteria, I use a query that selects the customers that have open invoices for example. Then I use a VBA loop to read through the recordset. For each record, I put the CustomerID into the form field (in this case the control on the form would usually be hidden since it is not specific to the form that is running the report) Then I use the OutputTo method to write one or more reports for the same customer. MoveNext to advance the loop and process the next customer record.

 

[Yuly17]

This is the complete code for save pdf button (attached). The idea is from one report include the data required and then opening each form, printing creating a name referring to a work order and form and saving in a location in the drive.

Opening each form takes time and makes printing one pack to last 3-5 min, this is why I'm trying to find a way to avoid opening each form to print.

 

[Pat Hartman]

Did you not understand my explanation of how to solve the problem? Are you looking for someone to build the solution for you?

 

[Gasman]

If the criteria is the same for all reports, open one form, set the criteria as tempvars, and output all the reports.

 

[Yuly17]

Did you not understand my explanation of how to solve the problem? Are you looking for someone to build the solution for you?

Sorry Pat,

Indeed I didn't understood your explanation. I'm quite new on this. I'm not looking for someone to build the solution but to give me some clarity if this is archivable. I did post the full code just to show there are not only 3 forms to print.

I will try to invest more time researching if there is a way to find a solution that doesn't need me to change the full code as I don't have all the experience required to do it at the moment.

 

[Pat Hartman]

It isn't code. It is just the select clause in the RecordSource query of your report. Then you can eliminate the code that opens and closes the report and just run the OutputTo. You still need to build the .pdf name.

Instead of using a form control, you can use a TempVar as Gasman suggested. I've been doing this for over 25 years. Long before Tempvars so I use the form that has the print/view option for the report to have the control that holds the value but Tempvars are fine. Since most of my forms provide the option to view, print, export to pdf, and some even export to excel, I always have a place to put the variable needed to control the selection.

Your table schema is not normalized. This is causing you to have to write significantly more code than you would otherwise need. You could also cut down code by creating some arrays to hold the variable part of the file name. Use different arrays for RAB, BIN, Coupling, and FSL. Or, better still a table with a type so you can select the type each time through the loop.

Then instead of multiple sections to build and write, you use a loop and fill the values from the array.

Reference controls using Me.controlName rather than Me!controlName.Value. It takes less typing and gives you intellisense.

The whole IfElse that starts with "If Ret2 = True is unnecessary. Instead of setting a value using the code above, just display the error. I don't see the code for IsFileOpen() but it looks like it returns true or false. Therefore, you would combine the two sets of code this way:

MyFileName = "PickingList" & ".pdf"
If IsFileOpen(MyPath & MyFileName2) Then
    Msgbox MyfileName & " is open.  Close the .pdf and start again.", vbOKOnly
    GoTo LastLine
End if
MyFileName = "ProcessCheckList_RAB" & ".pdf"
If ....

There is no reason to build variables you are never going to use. You build the same variables later when you are exporting the reports.

That is only the short, long method. There are even better ways to reduce this code to just a few lines.

I know you're new at this so here's something to think about. If you are writing long "duplicate" code procedures like this, you are doing something wrong. There is always a better way. Usually it is a table that you read in a loop that controls a process. Sometimes it is an array as i suggested above.

The lack of normalization is what is preventing you from completely controlling this output using one table but you can still reduce it substantially by eliminating redundancy.

 

[Gasman]

Admittedly I am using a tablet, but the strWhere does not appear to change?
So open the reports hidden and output to pdf.

 

[Pat Hartman]

Opening the reports hidden still takes a lot of time when you are opening this many reports at once. Much better to be able to use a where clause in the report and just use the OutputTo. The only issue then is if each report might be opened from multiple procedures. In that case, you would need to change those procedures as well to populate the TempVar or the form control depending on which method is used.

 

[Pat Hartman]

I couldn't stand it so I spent a half hour cleaning up the code without making any structural changes to reduce it further.
I removed almost half of the code. It's down to 179 lines from 349. If you can normalize your tables, You can drop another 3/4's of the code.

Consistency is your friend. ALWAYS properly indent your code as you write it. If you move it and that changes its indentation, fix it then. Here's a picture of my VBA toolbar. You can add useful controls to it. Compare this picture to your own version. Then go and add these controls to help with editing. The four to the right of the compile button help with formatting and comment and uncomment code.

Also part of consistency is using proper names for your controls and other objects. combo146 is meaningless. ALWAYS change the Name property of the control if you added it manually. Access uses the name of the ControlSource as the Name property of the Control but you have to do this yourself when you add controls.

The first repetitive section of the code where you are checking to see if the report exists can be reduced to a loop with a single repeat if you put the names of

And finally, if you don't have Option Explicit in every procedure, set it now. Also set declare variables as the default so Access will add it automatically for new modules.

Option Compare Database
Option Explicit

Private Sub SavePDF_Click()

Dim crId As Integer
Dim strWhere As String
Dim dirDefault As String
Dim MyFileName As String
Dim ShortName As String

    dirDefault = "C:\Users\yc6586\Desktop\GESUK HAD\JC test\"
 
    strWhere = "whatever code you have in some other place"
 
    If Me.ABS_Check = 0 And Me.DNV_Check = 0 And IsNull(Me.Other_Check) = True And Me.NA_Check = 0 Then
        MsgBox ("Please choose the ThirdParty test witness")
        GoTo LastLine
    Else
        If Combo164.ListIndex < 0 And Left(Me.InternalCode.Value, 2) = "HF" Then
            MsgBox ("Please choose a FRAC FAT procedure")
            GoTo LastLine
        End If
        blnsave = MsgBox("Are you sure you want to save this record?", vbQuestion + vbYesNo, "Save Confirmation")
        Me.ChangeBy = Environ("UserName")
        Me.ChangeDT = Now()
        crId = Me.Form.CurrentRecord
        Me.Refresh
        Me.Requery
        DoCmd.GoToRecord acDataForm, "E&S Assembly Forms", acGoTo, crId
        strWhere = "[AssemblyWO]='" & Me!AssemblyWO & "'"
        MyPath = dirDefault & Me.AssemblyWO & "-" & Me.AssemblyPartCode & "\"
        If Dir(dirDefault & Me.AssemblyWO & "-" & Me.AssemblyPartCode, vbDirectory) = "" Then
               MkDir (dirDefault & Me.AssemblyWO & "-" & Me.AssemblyPartCode)
        End If
         'Check if files are already open
   
        MyFileName = Me.AssemblyPartCode & "-PickingList" & ".pdf"
        GoSub CheckFile
        MyFileName = Me.AssemblyPartCode & "-ProcessCheckList_RAB" & ".pdf"
        GoSub CheckFile
        MyFileName = Me.AssemblyPartCode & "-RABDrawing" & ".pdf"
        GoSub CheckFile
        MyFileName = Me.AssemblyPartCode & "Skive And Cut Length Drawing - RAB" & ".pdf"
        GoSub CheckFile
        MyFileName = Me.AssemblyPartCode & "-RABAssemblyDrawing" & ".pdf"
        GoSub CheckFile
        MyFileName = Me.AssemblyPartCode & "-ProcessCheckList_BIN" & ".pdf"
        GoSub CheckFile
        MyFileName = Me.AssemblyPartCode & "-BINDrawing" & ".pdf"
        GoSub CheckFile
        MyFileName = Me.AssemblyPartCode & "-BINAssemblyDrawing" & ".pdf"
        GoSub CheckFile
        MyFileName = Me.AssemblyPartCode & "-CouplingDrawing" & ".pdf"
        GoSub CheckFile
        MyFileName = Me.AssemblyPartCode & "-CrimpDrawing" & ".pdf"
        GoSub CheckFile
        MyFileName = Me.AssemblyPartCode & "-BendRestrictor" & ".pdf"
        GoSub CheckFile
        MyFileName = Me.AssemblyPartCode & "-BendRestrictor-MACH1" & ".pdf"
        GoSub CheckFile
        MyFileName = Me.AssemblyPartCode & "-FAT" & ".pdf"
        GoSub CheckFile
        MyFileName = Me.AssemblyPartCode & "-API 7K - P1,P2" & ".pdf"
        GoSub CheckFile
        MyFileName = Me.AssemblyPartCode & "-API 7K - P3" & ".pdf"
        GoSub CheckFile
        MyFileName = Me.AssemblyPartCode & "-16C Gas" & ".pdf"
        GoSub CheckFile
        MyFileName = Me.AssemblyPartCode & "-API 16C FT" & ".pdf"
        GoSub CheckFile
        MyFileName = Me.AssemblyPartCode & "-API 16D FT" & ".pdf"
        GoSub CheckFile
        MyFileName = Me.AssemblyPartCode & "-FittingsRecordSheet" & ".pdf"
        GoSub CheckFile
        MyFileName = Me.AssemblyPartCode & "AssemblyTicket" & ".pdf"
        GoSub CheckFile
        MyFileName = Me.AssemblyPartCode & "-TestWitnessCheckList" & ".pdf"
        GoSub CheckFile
   Else
 
' Output reports
        MyFileName = Me.AssemblyPartCode & "-FittingsRecordSheet" & ".pdf"
        DoCmd.OutputTo acOutputReport, "FittingsRecordSheet", acFormatPDF, MyPath & MyFileName, False
     
        MyFileName = Me.AssemblyPartCode & "-PickingList" & ".pdf"
        DoCmd.OutputTo acOutputReport, "PickingList", acFormatPDF, MyPath & MyFileName, False
     
        MyFileName = Me.AssemblyPartCode & "-AssemblyTicket" & ".pdf"
        DoCmd.OutputTo acOutputReport, "AssemblyTicket", acFormatPDF, MyPath & MyFileName, False

        'RAB Drawings
        If Me.HoseBodyId = "RAB" Then
            MyFileName = Me.AssemblyPartCode & "-ProcessCheckList_RAB" & ".pdf"
            DoCmd.OutputTo acOutputReport, "ProcessCheckList_RAB", acFormatPDF, MyPath & MyFileName, False
         
            MyFileName = Me.AssemblyPartCode & "-RABDrawing" & ".pdf"
            DoCmd.OutputTo acOutputReport, "RABDrawing", acFormatPDF, MyPath & MyFileName, False
         
            MyFileName = Me.AssemblyPartCode & "-Skive And Cut Length Drawing - RAB" & ".pdf"
            DoCmd.OutputTo acOutputReport, "Skive And Cut Length Drawing - RAB", acFormatPDF, MyPath & MyFileName, False
         
            MyFileName = Me.AssemblyPartCode & "-RABAssemblyDrawing" & ".pdf"
            DoCmd.OutputTo acOutputReport, "RABAssemblyDrawing", acFormatPDF, MyPath & MyFileName, False
         End If

        'BIN Drawings
        If Me.HoseBodyId = "BIN" Then
            MyFileName = Me.AssemblyPartCode & "-ProcessCheckList_BIN" & ".pdf"
            DoCmd.OutputTo acOutputReport, "ProcessCheckList_BIN", acFormatPDF, MyPath & MyFileName, False
         
            MyFileName = Me.AssemblyPartCode & "-BINDrawing" & ".pdf"
            DoCmd.OutputTo acOutputReport, "BINDrawing", acFormatPDF, MyPath & MyFileName, False
         
            MyFileName = Me.AssemblyPartCode & "-BINAssemblyDrawing" & ".pdf"
            DoCmd.OutputTo acOutputReport, "BINAssemblyDrawing", acFormatPDF, MyPath & MyFileName, False
         End If

        'Coupling Drawing
        If (Me.HoseBodyId = "RAB" Or Me!HoseBodyId.Value = "BIN") Then
            MyFileName = Me.AssemblyPartCode & "-CouplingDrawing" & ".pdf"
            DoCmd.OutputTo acOutputReport, "CouplingDrawing", acFormatPDF, MyPath & MyFileName, False
        End If

        'Crimp Drawings
        If Me!HoseBodyId.Value = "Crimp" Then
            MyFileName = Me.AssemblyPartCode & "-CrimpDrawing" & ".pdf"
            DoCmd.OutputTo acOutputReport, "CrimpDrawing", acFormatPDF, MyPath & MyFileName, False
         End If

        'Save QC Froms
        If Me.BendRestrictor = True Then
            MyFileName = Me.AssemblyPartCode & "-BendRestrictor" & ".pdf"
            DoCmd.OutputTo acOutputReport, "BendRestrictor", acFormatPDF, MyPath & MyFileName, False
        End If
        If Me.BendRestrictorMACH1 = True Then
            MyFileName = Me.AssemblyPartCode & "-BendRestrictor-MACH1" & ".pdf"
            DoCmd.OutputTo acOutputReport, "BendRestrictor-MACH1", acFormatPDF, MyPath & MyFileName, False
        End If

        MyFileName = Me.AssemblyPartCode & "-FAT" & ".pdf"
        DoCmd.OutputTo acOutputReport, "FAT", acFormatPDF, MyPath & MyFileName, False
     
        Reportcode = Left(Me.HoseBodySteps, 2)  '''  what is this line of code doing here??????????????
     
        If Me.FSL <> "N/A" Then
            MyFileName = Me.AssemblyPartCode & "-API 7K - P1,P2" & ".pdf"
            DoCmd.OutputTo acOutputReport, "API 7K - P1,P2", acFormatPDF, MyPath & MyFileName, False
         
            MyFileName = Me.AssemblyPartCode & "-API 7K - P3" & ".pdf"
            DoCmd.OutputTo acOutputReport, "API 7K - P3", acFormatPDF, MyPath & MyFileName, False
         
            MyFileName = Me.AssemblyPartCode & "-TestWitnessCheckList" & ".pdf"
            DoCmd.OutputTo acOutputReport, "TestWitnessCheckList", acFormatPDF, MyPath & MyFileName, False
        End If
        If Me.GasTest = True Then
            MyFileName = Me.AssemblyPartCode & "-API 16C Gas" & ".pdf"
            DoCmd.OutputTo acOutputReport, "API 16C Gas", acFormatPDF, MyPath & MyFileName, False
        End If
        If Me.FireTest = True Then
            MyFileName = Me.AssemblyPartCode & "-API 16C FT" & ".pdf"
            DoCmd.OutputTo acOutputReport, "API 16C FT", acFormatPDF, MyPath & MyFileName, False
        End If
        If Me.API16D = True Then
            MyFileName = Me.AssemblyPartCode & "-API 16D FT" & ".pdf"
            DoCmd.OutputTo acOutputReport, "API 16D FT", acFormatPDF, MyPath & MyFileName, False
        End If
    End If
End If

LastLine:
    Exit Sub
CheckFile:
    ShortName = Left(InStrRev(MyFileName, "-") + 1)
    If IsFileOpen(MyPath & MyFileName) Then
       MsgBox ShortName & " file is open. Close the PDF and start again"
       GoTo LastLine
    End If
End Sub

 

[Gasman]

Opening the reports hidden still takes a lot of time when you are opening this many reports at once. Much better to be able to use a where clause in the report and just use the OutputTo. The only issue then is if each report might be opened from multiple procedures. In that case, you would need to change those procedures as well to populate the TempVar or the form control depending on which method is used.

Pat,
O/P is using a Where, strWhere

 

[Yuly17]

It isn't code. It is just the select clause in the RecordSource query of your report. Then you can eliminate the code that opens and closes the report and just run the OutputTo. You still need to build the .pdf name.

Instead of using a form control, you can use a TempVar as Gasman suggested. I've been doing this for over 25 years. Long before Tempvars so I use the form that has the print/view option for the report to have the control that holds the value but Tempvars are fine. Since most of my forms provide the option to view, print, export to pdf, and some even export to excel, I always have a place to put the variable needed to control the selection.

Your table schema is not normalized. This is causing you to have to write significantly more code than you would otherwise need. You could also cut down code by creating some arrays to hold the variable part of the file name. Use different arrays for RAB, BIN, Coupling, and FSL. Or, better still a table with a type so you can select the type each time through the loop.

Then instead of multiple sections to build and write, you use a loop and fill the values from the array.

Reference controls using Me.controlName rather than Me!controlName.Value. It takes less typing and gives you intellisense.

The whole IfElse that starts with "If Ret2 = True is unnecessary. Instead of setting a value using the code above, just display the error. I don't see the code for IsFileOpen() but it looks like it returns true or false. Therefore, you would combine the two sets of code this way:

MyFileName = "PickingList" & ".pdf"
If IsFileOpen(MyPath & MyFileName2) Then
    Msgbox MyfileName & " is open.  Close the .pdf and start again.", vbOKOnly
    GoTo LastLine
End if
MyFileName = "ProcessCheckList_RAB" & ".pdf"
If ....

There is no reason to build variables you are never going to use. You build the same variables later when you are exporting the reports.

That is only the short, long method. There are even better ways to reduce this code to just a few lines.

I know you're new at this so here's something to think about. If you are writing long "duplicate" code procedures like this, you are doing something wrong. There is always a better way. Usually it is a table that you read in a loop that controls a process. Sometimes it is an array as i suggested above.

The lack of normalization is what is preventing you from completely controlling this output using one table but you can still reduce it substantially by eliminating redundancy.

Thank you very much. I didn't write this code but i have been pushed to learn and add or remove things from it. I will take all your suggestions.

Thabks for your time as well.

Would you recommend a course or where I can learn more about access?

 

[Pat Hartman]

O/P is using a Where, strWhere

I see that but it is not defined nor is it filled in the procedure he posted. Option explicit isn't invoked either.

I didn't write this code

If you didn't write the code, who did? Please don't think they can teach you anything.

You might want to get a copy of the Access Cookbook version 2. It is pretty old but then VBA hasn't changed much over the years. Just a few new functions. Some of the examples won't make any sense because Access has, over the years, fixed the problems they were intended to solve. But it is a good way to learn VBA by example.

Learning to program is different from learning a natural language because it is not just vocabulary and syntax. You need to learn to think differently. Computers are dumb as rocks but very fast so humans think they're smart. But they're only as smart as the programs that are written for them to run. The type of business programs we write with VBA make decisions and move stuff around and do arithmetic. You can write them in a clunky manner as your original code or you can make use of the VBA language and it's feature set to create loops and reuse code as with gosubs. Clunky isn't wrong, it is just wasteful of resources and time consuming and prone to error not to mention dreadfully tedious.

 

[Pat Hartman]

forgot the picture

 

[Gasman]

In post 4 in the text file

strWhere = "[AssemblyWO]='" & Me!AssemblyWO & "'"

but not Dimmed.

However I believe that is the original author's awful code not the O/P.

 

[Yuly17]

I see that but it is not defined nor is it filled in the procedure he posted. Option explicit isn't invoked either.

If you didn't write the code, who did? Please don't think they can teach you anything.

You might want to get a copy of the Access Cookbook version 2. It is pretty old but then VBA hasn't changed much over the years. Just a few new functions. Some of the examples won't make any sense because Access has, over the years, fixed the problems they were intended to solve. But it is a good way to learn VBA by example.

Learning to program is different from learning a natural language because it is not just vocabulary and syntax. You need to learn to think differently. Computers are dumb as rocks but very fast so humans think they're smart. But they're only as smart as the programs that are written for them to run. The type of business programs we write with VBA make decisions and move stuff around and do arithmetic. You can write them in a clunky manner as your original code or you can make use of the VBA language and it's feature set to create loops and reuse code as with gosubs. Clunky isn't wrong, it is just wasteful of resources and time consuming and prone to error not to mention dreadfully tedious.

Was an ex coworker in the office, so now he left and I am traying to take what he left and change as the office need to be due some production changes... So, I'm new to this, learning, and trying to understand from this code he wrote and add things required on top of my normal workload, I'm a mechanical engineer working in mechanical design field, and I'm passionate to learn new things and go an step forward. Thanks for the time and the effort of explain all this to me.

 

[Gasman]

Always hard to take over someone else's code, even when they are a professional developer.
Even worse if they are not.

You have come to the right place though.

 

[Pat Hartman]

OK, start by replacing your old code with the new version. Don't forget to test. Then get rid of the statement with the question because someone pointed out that the code I didn't see does exist somewhere.

Add option Explicit. If it wasn't there before, you will now get compile errors which you need to fix. It is very poor practice to not Dim variables before you use them. It is only a single line of code and it can save you from sooo many embarrassing errors or even dangerous errors.

What if in part of the code you use
namex = namea + namec
but you really meant
nameb = namea + namec

When you reference nameb later, it will not have a value but it should. And NO error ever gets raised because VBA assumes you meant to do this.

Not requiring variables to be dim'd before use is the worst "feature" of VBA and the most dangerous for novices. Take the hit now and fix the crap your co-worker left you.

 

[Gasman]

Add option Explicit. If it wasn't there before

To that end (and this will only help you for further modules), go into the VBA window and Tools/Options/ and make sure Require Variable Declarations is ticked.

You will need to add Option Explicit to all existing modules, forms/reports, where it does not exist. Then fix all the compiler complaints where something is not declared.