Exporting report to individual PDF's

[Velox]

Hi,

I've now been working on and off this issue for the last couple of days and i'm not getting any further on it. I wondered if anyone could possibly give me a hand?

I am most definitely not a programmer by any means although I have dabbled in the very basics of a few languages on a personal level in the past. This week I've been tasked with learning both Access and VBA to get this working so it's been a difficult couple of days! :banghead:

Basically we have a report that prints out our remote support for the past 30 days or so based on whether "Invoiced" is ticked on or off. If it's not ticked, it prints that page.

We're trying to get this report to export all pages to PDF's based on "Customer" name. Using various butchered code mangled together into Frankensteins monster, I think i'm kind of close-ish to achieving this goal.

I now have it exporting 30 odd pages of reports to 30 individually named PDF files. The only (rather large issue) is that each PDF contains all 30 pages of customers support instead of the one or two pages it should contain for that single customer!

This last step is proving a bit of a cliff face and nothing I am finding is helping me over it. Wondering if someone could possibly give me some pointers as to where I am going wrong?

I *think* I need to somehow have the below line look for the customer somehow (as per my botched up example) and only open that single page of the report. If I can do that then I think I am home and dry? I would then need to add something to say "don't print the invoice if [Invoice] = yes" but that sounds pretty simple to achieve compared to all the other hurdles I've crawled over so far. The most amusing was when I inadvertently removed "acViewPreview" which caused it to print 30 x 30 pages to my default printer. Oh how we laughed...

The below code was from an example which suggested I should be able to use a field reference from the report to print just that page but if I try manually typing in a customer name there to test, Access pops up a text type window and waits for me to enter something then proceeds to create that PDF but with nothing in it.

      DoCmd.OpenReport "Remote Support Report", acViewPreview, , "Customer = CUSTOMER NAME"

The below code has a few parts intentionally commented out. The loop seems unnecessary until I can get the first page to come out without all 30 pages attached to it. And I didn't really understand what the forms bits do and couldn't see that they were helping any so they are also commented out. I've left them there in case they do help in some way I don't understand.

Note that I do have a similar request up on Experts Exchange but other than a single response, it has been ignored. This board looks a lot more active so thought i'd give this a go. Hope this isn't a problem? I'm very happy to remove the experts Exchange post if it helps.

Sub Main()
    Dim rst As ADODB.Recordset
    Dim MyFileName As String
    Dim mypath As String
    
    mypath = "C:\Temp\Remote\"
    
    Set rst = New ADODB.Recordset

    rst.Open "Select distinct [Customer] From [Remote Support Query]", CurrentProject.Connection

'Do While Not rst.EOF
    
  '    Forms![Remote support].Customer.Value = 0
      
      MyFileName = rst("Customer") & ".pdf"
                        
  '    Forms![Remote support].Customer.Value = rst!Customer
                           
      DoCmd.OpenQuery "Remote Support Query"
      DoCmd.Close acQuery, "Remote Support Query", acSaveYes
      DoCmd.OpenReport "Remote Support Report", acViewPreview
      DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
      DoCmd.Close acReport, "Remote Support Report", acSaveYes
      DoEvents
      rst.MoveNext
        
   '   Loop
rst.Close
Set rst = Nothing
        
End Sub

 

[Ranman256]

it actually:
DoCmd.OpenReport "Remote Support Report", , , "Customer = '" & txtBox & "'"

(you dont want the acViewPreview, to print pdf)

you can also use a listbox, the list has all the user IDs to print to,
the query in the report, would look at the list,
select * from table where [CustomerID]=forms!myForm!lstBox

then scan thru the list and print 1 at a time:

sub btnPrintRpts_click()
dim vID, vDir , vFile<vCustName
dim i as integer

vDir = "c:\temp\"
for i = 0 to lstBox.listcount -1
    vID = lstBox.itemdata(i)      'get next item in listbox
    lstBox = vID		  'set the listbox to this item
    vCustName = lstBox.column(1)  'name in col2 (in vb, columns begin with zero)

        'get vitals off the listbox

      vFile = vDir & "Invoice_" & vCustName & ".pdf"
      docmd.OutputTo acOutputReport ,"rMyReport",acFormatPDF,vFile   
      'docmd.OpenReport "myReport",acViewReport,,"[CustomerID]=" & lstBox
next
end sub

 

[MarkK]

If you changed the RecordSource of the report to a parameter query, something like...

SELECT * FROM Table WHERE Customer = [Enter the customer name]

...which would typically prompt you for the value of Customer, then you could drive that parameter value into the report programmatically using DoCmd.SetParameter. Then open the filtered report and output it to pdf. Consider code like...

Sub Test91234701934()
    Const RN As String = "Remote Support Report"
    Const PATH As String = "C:\Temp\Remote\"
    Const SQL As String = _
        "SELECT DISTINCT Customer " & _
        "FROM [Remote Support Query] "
        
    With New ADODB.Recordset
        .Open SQL, CurrentProject.Connection
        Do While Not .EOF
[COLOR="green"]            ' set the value of the parameter the report will expect[/COLOR]
            DoCmd.SetParameter "[Enter the customer name]", !Customer
[COLOR="green"]            ' open the now filtered report in preview[/COLOR]
            DoCmd.OpenReport RN, acViewPreview
[COLOR="Green"]            ' output that open report to pdf[/COLOR]
            DoCmd.OutputTo acOutputReport, RN, acFormatPDF, PATH & !Customer & ".pdf"
[COLOR="Green"]            ' close the report and get ready to do it again[/COLOR]
            DoCmd.Close acReport, RN
            .MoveNext
        Loop
        .Close
    End With
        
End Sub

See what is happening there?
hth
Mark

 

[MarkK]

But that'll be flashing previewed reports all over the screen too, which isn't great. Another approach you could take is change the RecordSource of the report to leverage a TempVar, like...

SELECT * FROM Table WHERE Customer = TempVars!Customer

... and amend the code to...

Sub Test91234701935()
    Const RN As String = "Remote Support Report"
    Const PATH As String = "C:\Temp\Remote\"
    Const SQL As String = _
        "SELECT DISTINCT Customer " & _
        "FROM [Remote Support Query] "
        
    With CurrentDb.OpenRecordset(SQL)
        Do While Not .EOF
[COLOR="Green"]            ' set the value of the TempVar the report will expect[/COLOR]
            TempVars!Customer = !Customer
[COLOR="green"]            ' output the report to pdf[/COLOR]
            DoCmd.OutputTo acOutputReport, RN, acFormatPDF, PATH & !Customer & ".pdf"
            .MoveNext
        Loop
        .Close
    End With
        
End Sub

...which also uses a DAO.Recordset.
Cheers,
Mark

 

[Velox]

Wow thanks all! That's loads to go on! I'll have a play with them all in the morning and see where I get to. I'm not worried about it flashing previews up on the screen as it does that now and it's pretty fast even with the fact that it is writing 30 pages of data on each iteration instead of a single page or two.

However it does it, it'll be a hell of a lot better than the current way we do it which is to sit in front of it for half an hour every month manually printing each page then renaming by hand...

 

[Pat Hartman]

Markk's last solution solves the problem without having to open each report in preview. In addition to the annoying flashing, opening the reports in preview doubles the work that Access has to do because it has to run the report twice and although this might not be a problem in this case, it is poor technique and is not something you actually want in your repertoire. You might as well learn how to use a more efficient solution. So, start from the end of the suggestions and work backward so you get your solution right away.

 

[Velox]

So I've been playing around with this all morning. Predictably I'm not there yet!

I've tried and failed all the suggestions posted but i'm obviously most interested in the final set of code from MarkK given that it shouldn't flash previews up but i'm failing to understand if this is everything I need or if it should be integrated into some of my existing code.

From the comment on the bottom that it uses a DAO.recordset I don't understand if that means I need to open the recordset first as per previous attempts similar to:

    Dim rst As DAO.Recordset
    Dim db As DAO.Database
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT * FROM [Remote Support] WHERE [Customer] = TempVars!Customer")

Or if everything I need is here. if I do need to open it as per above, I don't understand how i'd then use those strings in the main body of code so I am presuming this is wrong and everything I need is in the code you posted.

That being the case, I get an error of "TempVars can only store data, they cannot store objects" and the debug points to this line:

            TempVars!Customer = !Customer

I have tried to learn how the 'Bang' works and what this line does to get an understanding of what might be going wrong here but am failing to understand it so far. I have yet to spot any examples where the bang is used twice though in code such as this, so...

....if I remove the second bang from that line, it moves to the next line but then errors with "Microsoft Access cannot find the object '|1'" so I guess removing the second bang just breaks the whole thing.

Any thoughts?

Thanks!

 

[MarkK]

The code should run fine as posted, but the problem is that !Customer is a field in a recordset, and the TempVar thinks you are trying to save the whole field in the TempVar. You need to amend that line as follows...

TempVars!Customer = !Customer.Value

...so you only try to save the Value of the field, not the field itself.

But it is also crucial that you understand you need to modify the RecordSource of the report correctly, so it correctly consumes the value you are storing in that TempVar.

SELECT * FROM Table WHERE Customer = TempVars!Customer

Those are the two important chunks.
hth
Mark

 

[Velox]

But it is also crucial that you understand you need to modify the RecordSource of the report correctly, so it correctly consumes the value you are storing in that TempVar.

SELECT * FROM Table WHERE Customer = TempVars!Customer

Those are the two important chunks.
hth
Mark

Thanks MarkK, I wasn't understanding this but did some research and watched the video linked in the zip (post count to low to post links or images!) which showed me how to (hopefully) correctly set the record source of the report to:

SELECT * FROM [Remote Support] WHERE Customer = TempVars!Customer;

where [Remote Support] is the main table.

Now if I click the three dots (build button?) on the record source properties, the query builder opens and shows me the image in the zip which suggests it is correct?

As a side note, if I open the query builder and then hit save, it updates the recordsource to:

SELECT * FROM [Remote Support] WHERE ((([Remote Support].[Customer])=[TempVars]![Customer]));

- I am presuming I dont need all those additional brackets so I have removed them. When I saved the document with them in, it immediately crashed Access!

Unfortunately I still get the same error as previously - "Run-time error '2059': Microsoft Access cannot find the object '|1'" which debugs to

DoCmd.OutputTo acOutputReport, RN, acFormatPDF, PATH & !Customer & ".pdf"

I had a friend who knows a little VB take a look. He thought perhaps it should be

DoCmd.OutputTo acOutputReport, RN, acFormatPDF, PATH & TempVars!Customer & ".pdf"

but that still gives the same error.

Below is the is code I am working from now. Any thoughts? Sorry if I'm being dim

Option Compare Database

Sub PDF_Export()
    Const RN As String = "Remote Support Report"
    Const PATH As String = "C:\Temp\Remote\"
    Const SQL As String = _
        "SELECT DISTINCT Customer " & _
        "FROM [Remote Support Query] "
        
    With CurrentDb.OpenRecordset(SQL)
        Do While Not .EOF
            ' set the value of the TempVar the report will expect
            TempVars!Customer = !Customer.Value
            ' output the report to pdf
            DoCmd.OutputTo acOutputReport, RN, acFormatPDF, PATH & !Customer & ".pdf"
            .MoveNext
        Loop
        .Close
    End With
        
End Sub

 

[MarkK]

I don't see a problem with the code. Yeah, the Microsoft query writer does a crap job with all its *^&%&^ parenthesis. These two lines of code...

DoCmd.OutputTo acOutputReport, RN, acFormatPDF, PATH & !Customer & ".pdf"
DoCmd.OutputTo acOutputReport, RN, acFormatPDF, PATH & TempVars!Customer & ".pdf"

...do exactly the same thing since the previous line was...

TempVars!Customer = !Customer.Value

Maybe you can post a Db that demonstrates this problem, because I don't see it from what you posted.
hth
Mark

 

[MarkK]

It could be your filename is not value, if the Customer name contains any illegal filename characters in Windows.
Mark

 

[Velox]

Cool that'd be awesome if you could! Should be attached. I removed all of our data and ran a compress & repair to hopefully remove any dreggs of it. If i've missed anything, it's pretty low level stuff - just company names. It's not like it's medical records or anything...

I populated it with three lines of dummy data. The code should also be in there already.

Cheers!

 

[MarkK]

There is no report in the database called 'Remote Support Report.' That is why this line fails...

    Const RN As String = "Remote Support Report"
    ...
    DoCmd.OutputTo acOutputReport, RN, acFormatPDF, PATH & !Customer & ".pdf"

Mark

 

[Velox]

Oh really? That's weird as when I look in the list of reports I see "Remote Support report".

As I say, i'm new to both Access and VBA but I presumed the name in the report list would be the absolute name of it. I'm researching where to find the actual name of reports now as it doesn't seem to be obvious. It says "Report" in the drop-down on the right & I thought it might be that, but when I try renaming it to "Report" I get an error that it doesn't exist.

I will also discuss with my colleague who created the database originally to see if he knows. Thanks for the pointer!

(*edit yay 10 posts! I can post images!)

 

[isladogs]

The only report in the database you posted is called Remote support query

 

[Velox]

Ohhh so the record source is the name of the report? We have a query named "report support query" and I presumed it meant it was pulling data from that query. Well that confuses things nicely if the report and the query are named the same....

Thanks I think I have plenty to go on now. I'll play around with the report & query tomorrow and see if I can get the names straight. I tried setting everything to "Remote Support Query" but got an error that it doesn't exist...

(I'm guessing report names when used in VBA aren't case sensitive?)

 

[MarkK]

Ohhh so the record source is the name of the report?

No. This line of code...

    DoCmd.OutputTo acOutputReport, RN, acFormatPDF, PATH & !Customer & ".pdf"

...attempts to open and output a report named "Remote Support Report, (as specified by the constant RN, defined earlier in the code block) but that report simply does not exist in the database you posted. There is only one report in that database, and it is called "Remote Support Query." That line of code will fail on that account. Nothing to do with RecordSource properties of anything.
hth
Mark

 

[Velox]

Ok but you can presumably understand my confusion given that I'm going to the section in Access clearly marked as "Reports" and referencing what is presumably a report (give that it is in the reports section) and that seems to be named "Remote Support Report" even when I view it in the design view?

You are seeing what is in the screenshot below in the reports section of the database I posted? I haven't somehow deleted it have I? I'll double check when i'm back on my work PC tomorrow. I'm an idiot if so.

Otherwise I just need to research and understand tomorrow what a report is if the thing listed under Reports isn't actually a report or if what looks like the name of it isn't actually the name of it...! :banghead:

(get this report into PDF's they said, it'll be easy they said...)

 

[isladogs]

I haven't somehow deleted it have I?

Yes you have. This is the entire list of database objects

 

[Velox]

You need a "shooting yourself in the head" smiley

At some point whilst I was working on it, I switched from the version I had to a copy of the main version that is in use (to ensure there were no nasty surprises later lol) and I did not spot that the reports had different names as I thought all versions were identical. I must have taken that screenshot from the old version which was still on my desktop - I thought the report names were the same in all versions.

I am a monumental idiot. Thanks so much for pointing out the issue & apologies if I sounded narky in the previous post but I just wasn't seeing the correlation between the report names

It seems to work perfectly now. All I need to do is add a "while not" type statement so it only prints out the non-invoiced stuff but i'm going to be very embarrassed if I can't even do that.

Thanks for your patience on this everyone!