Some titles in a report are missing when exporting to PDF (1 Viewer)

hjc076

New member
Local time
Today, 05:39
Joined
Dec 4, 2019
Messages
4
Hi all, kind of frustrated here trying to figure out how to solve this issue..:banghead:
I am exporting more than 100 reports (with a lot of parameters) to PDF using VBA, everything works fine excepting for one detail, in many reports some titles are tied to parameters values (start and end period for example), using a textbox with control source property = [parametername]. This was done in that way for the person who made all the reports.
If I run the report manually and export it to PDF, then titles are shown correctly, but if I run the VBA process titles in PDF files are empty.
Is there any workaround you could recommend to solve this?

I am using (inside a loop that is working fine, not relevant to this issue):

DoCmd.OpenReport CurrentRep, acViewPreview, , , acHidden
DoCmd.OutputTo objecttype:=acOutputReport, objectName:=CurrentRep, outputformat:=acFormatPDF, outputFile:=filepath
DoCmd.Close acReport, CurrentRep, acSaveNo

I would really appreciate any help.
Thanks!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:39
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF! My guess would be there's a timing issue. Perhaps try adding a delay between printing/exporting each report to PDF.
 

Micron

AWF VIP
Local time
Today, 05:39
Joined
Oct 20, 2018
Messages
3,476
Not sure if TDBG meant to add the delay between the closing and opening or between the output and closing. I'd put the pause in the latter position because the controls probably don't have time to calculate before the report is closed. We both seem to think that the loop IS the issue...
 

isladogs

MVP / VIP
Local time
Today, 09:39
Joined
Jan 14, 2017
Messages
18,186
I agree about the need to pause the code, having had exactly the same issue with 17 lengthy reports run in turn and exported to PDF. Start by adding the line DoEvents after the OutputTo line
 

hjc076

New member
Local time
Today, 05:39
Joined
Dec 4, 2019
Messages
4
Not sure if TDBG meant to add the delay between the closing and opening or between the output and closing. I'd put the pause in the latter position because the controls probably don't have time to calculate before the report is closed. We both seem to think that the loop IS the issue...

Thanks guys for your answers.. I just tried it using delays from 1 up to an exaggerated 15 secs but it didn't work. I am running the delay statement once the report is open, so the export to PDF will not start the delay time ends.
 

Micron

AWF VIP
Local time
Today, 05:39
Joined
Oct 20, 2018
Messages
3,476
The inference is that the parameter might not get passed when opening in vba, but that's just a guess without seeing any code. Did you step through that code and verify that any variables or parameters are not missing values? Or does the code have no bearing on the parameters?
 

isladogs

MVP / VIP
Local time
Today, 09:39
Joined
Jan 14, 2017
Messages
18,186
Over the years, I've used various methods of pausing code including DoEvents, dbIdle.RefreshCache and the Sleep API.
In fact I ran some detailed tests on these methods http://www.mendipdatasystems.co.uk/speed-comparison-tests-3/4594428909

In this case, I disagree with the conclusions from the FMS article.
All of them can work well but setting a fixed pause using Sleep may add unnecessary extra delay.
Using dbIdle.RefreshCache is often the smallest delay but doesn't always allow code to complete. DoEvents is in my experience usually successful without noticeable performance issues
 

Micron

AWF VIP
Local time
Today, 05:39
Joined
Oct 20, 2018
Messages
3,476
Not disagreeing with your tests or their results, but the article isn't about speed, it's about processor load, yes? Maybe things have progressed to the point where it's no longer an issue.
 

isladogs

MVP / VIP
Local time
Today, 09:39
Joined
Jan 14, 2017
Messages
18,186
I'd seen the FMS article before running my own tests and ran similar tests to those described by Luke Chung. Using it after each record in a Do Loop really isn't wise (as one of my own tests showed) but I didn't see the significant CPU spike he described.

My article wasn't just about speed either. It focused on effectiveness of the code pause and partly illustrated the effect of each using a progress bar and accompanying caption to detect whether sufficient time was allowed to complete code. DbIdle.RefreshCache is faster but less effective.

As the OP wants to run several steps in sequence without looping, DoEvents should work perfectly well. That is if pausing code really is the issue … which from the last reply may not be the case.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:39
Joined
Oct 29, 2018
Messages
21,357
Thanks guys for your answers.. I just tried it using delays from 1 up to an exaggerated 15 secs but it didn't work. I am running the delay statement once the report is open, so the export to PDF will not start the delay time ends.
Hi. Just as a FYI, proper placement of the delay code may also be vital to your success. So, I would say experiment a little by moving the delay in different points in your code. For example, if you are not doing it already, I would say try putting it just before the export to PDF line, after all the other stuff you have to do with the report are done. Just a thought...
 

hjc076

New member
Local time
Today, 05:39
Joined
Dec 4, 2019
Messages
4
Thanks everyone for your advises and suggestions. I tried placing the delay in different steps of the code (before and after the export) ,but it is still not working.

DoCmd.OpenReport CurrentRep, acViewPreview, , , acHidden
'DoCmd.Minimize

DoCmd.OutputTo objecttype:=acOutputReport, objectName:=CurrentRep, outputformat:=acFormatPDF, outputFile:=filepath

Call WaitFor(30)

DoCmd.Close acReport, CurrentRep, acSaveNo
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:39
Joined
Oct 29, 2018
Messages
21,357
Thanks everyone for your advises and suggestions. I tried placing the delay in different steps of the code (before and after the export) ,but it is still not working.

DoCmd.OpenReport CurrentRep, acViewPreview, , , acHidden
'DoCmd.Minimize

DoCmd.OutputTo objecttype:=acOutputReport, objectName:=CurrentRep, outputformat:=acFormatPDF, outputFile:=filepath

Call WaitFor(30)

DoCmd.Close acReport, CurrentRep, acSaveNo
Oh well, I think it was worth a try anyway. Otherwise, we wouldn't know for sure. Unfortunately, it would be hard to offer any other suggestions without being able to see the problem in action. Are you able to post a demo version of your db?
 

sxschech

Registered User.
Local time
Today, 02:39
Joined
Mar 2, 2010
Messages
791
A random thought. If the timer doesn't work well. What if you have the vba run a query or some other process between reports?
 

Micron

AWF VIP
Local time
Today, 05:39
Joined
Oct 20, 2018
Messages
3,476
what about the suggestion in post 7? Not feasible or applicable?
I would also suggest Debug.Print the parameters, run it until it fails and look at the printouts to be sure that the values are there.
 

hjc076

New member
Local time
Today, 05:39
Joined
Dec 4, 2019
Messages
4
Thanks everyone for your answers and ideas, based on some of your comments I finally found the error, I wasn't passing the string properly to the parameters, using delimiter characters (i.e: 'MM' instead of MM). Even when the queries are using the parameter and the data is fine, text boxes in the reports don't recognize the value as valid if it is not enclosed in ' ' .
Thanks again for your help!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:39
Joined
Oct 29, 2018
Messages
21,357
Thanks everyone for your answers and ideas, based on some of your comments I finally found the error, I wasn't passing the string properly to the parameters, using delimiter characters (i.e: 'MM' instead of MM). Even when the queries are using the parameter and the data is fine, text boxes in the reports don't recognize the value as valid if it is not enclosed in ' ' .
Thanks again for your help!
Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Micron

AWF VIP
Local time
Today, 05:39
Joined
Oct 20, 2018
Messages
3,476
Too bad it took so much effort, because that was suggested in post 7.
Thanks for posting the solution for everyone.
 

Users who are viewing this thread

Top Bottom