Exporting Report to PDF with VBA, second page is blank.

[kfraysur]

I used this thread:

MS Access auto/scheduled report export. | MrExcel Message Board

to learn how to schedule a report to be exported to a pdf file once per day. I have the report working, the macro works, and the batch file runs at the appropriate time. I actually set it up as the 'AutoExec' macro for this .accdb file and the vba code closes the database file. This report is the only thing done with this database file.

Public Function Report_Export()

'Creating two filename variables so they are easier to reference and modify in the future if necessary.
Dim FileNamePDF As String

FileNamePDF = "Filenamegoeshere.pdf" 'This is a network drive link

DoCmd.OutputTo acOutputReport, "CADTech Finishing Detail Report", acFormatPDF, FileNamePDF, False, "", , acExportQualityPrint

Application.Quit

End Function

This works when I run the code directly from the VBA editor. It also works when I run the macro. However, when I simply open the database file (or it's automatically opened by the task scheduler), the second page of the pdf that gets exported looks like this:

If you notice, the page count says '2 of 3'. However, the pdf only has 2 pages.

Any idea why this might happen/what a solution might be? I can run this manually and it works just fine, but the goal was to have this report run from a remote machine automatically.

 

[Micron]

What does the 1st page look like, and are you saying that the report should only be one page? You're saying it runs fine if you call the code somehow, yet also say it doesn't work when you open the db because the Autoexec calls the code?

If you shift bypass the macro when you open the db then execute the macro I gather it doesn't work. That would rule out a profile issue I guess. So whose profile is being used when TS opens the db and could it be a permissions issue?

 

[kfraysur]

What does the 1st page look like, and are you saying that the report should only be one page? You're saying it runs fine if you call the code somehow, yet also say it doesn't work when you open the db because the Autoexec calls the code?

If you shift bypass the macro when you open the db then execute the macro I gather it doesn't work. That would rule out a profile issue I guess. So whose profile is being used when TS opens the db and could it be a permissions issue?

The first page looks fine, it is page 2 where there's an error. In this instance, there should be 3 pages of the report, but only a 2 page pdf was generated. The first page appears as normal, the second appears like the image. There is no 3rd page at all in the pdf.

If I simply call the VBA caode (whether in the VBA editor or manually running the Autoexec macro) the report gets generated exactly as it should be. However, if I simply open the db file and let the Autoexec macro run itself, or if the db is opened due to my task scheduler batch file running, then I get the error I'm describing.

If I shift bypass and run manually, it does work. I'm not sure if you mean Windows profile or Office profile, but they are the same either way. The profile remains the same across all instances where the report generates correctly or incorrectly.

I have this setup in the task scheduler to run every morning at 6:40AM. Strangely, it worked without issue this morning, and generated a 3 page report as it's supposed to. I'm even more confused now, as I changed nothing in the code.

 

[Micron]

By profile I meant yours, in case you were not logged in when TS runs this.
I suspect each page is never identical because you've somehow broken up records - likely with page breaks or groups. Since it always prints page 1 all I can think of is that it's data realted but since I have no idea what pages 1 and 3 are supposed to look like, that's just a guess.

 

[kfraysur]

This report contains no groupings or page breaks, only some sorting by dates and customer names.

 

[JonXL]

What if you put a pause or wait at the start of your routine?

Also, where are the data table(s) stored?

 

[Micron]

A pause is a decent idea. In that code the only place that makes sense to be is between the Docmd and the Quit.

 

[JonXL]

A pause is a decent idea. In that code the only place that makes sense to be is between the Docmd and the Quit.

I was thinking maybe there's a delay in loading the data source and so the export doesn't have all the data available when it runs.

Wouldn't the time between the export and Application.Quit be the same when auto-running and running by hand? The time between the DB loading and report export would change, though, whether running automatically vs having to click around to get it to run...

Right?

I'm genuinely just guessing around.

 

[Micron]

Hmm. Maybe. I was thinking along the lines of a network delay between the two commands, and the db was closing too soon, but I like your thinking better.

 

[kfraysur]

I really appreciate the help.

I'm going to go ahead and mark JonXL's post as the solution. It looks like some kind of error was occurring with the report not able to fully load. Once I added a 2 second pause at the beginning of the VBA sequence, it appears to be working as intended under all circumstances, including running from the task scheduler.

I have another set of reports that I was working on automating, and the first report of 5 was showing a similar issue, but again, it was intermittent. I also added the pause to that code and it appears to be working as well.

For reference, this report is pulling data from a linked table from a SQL Server 2019 database.

Again, thanks for the help.