Print in pdf without opening the form

[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.

 

[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.

 

[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.

 

[Gasman]

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

 

[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?

 

[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.

 

[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.