VBA Button To Export/Save/Print to PDF

[data808]

Does anyone have an VBA examples if you have a button on a form and want to export/save/print to a PDF? What would actually be great is if the user could click on the button and have a SaveAs window pop up and then they can choose for where the PDF will be saved. Basically, the PDF will just be a screenshot of the form. Is this possible? If so, could you show me some examples? Thanks.



I just tried this line but it didn't do anything:

DoCmd.OutputTo acOutputForm, "Counter Log", acFormatPDF, Counter_Log.pdf

[data808]

I found this online and feel like it's close to what I would want but it's not quite working. When I click the button to run it, it will have a little prompt window that pops up, similar to when you print something. Then you can see it say something about now outputting Counter Log with the correct reference number of that one record I had showing on the form. Then at the bottom of that tiny prompt you see current pages and the page number just keeps going up as it's processing it and doesn't seem to stop so I cancel it. I'm thinking this form should only be one page as it's not very big. Here is the code:

Dim FileName As String
Dim FilePath As String
FileName = "Counter_Log_" & Me.txtRefNum
FilePath = "C:\Users\JS\Desktop" & FileName & ".pdf"
Forms("Counter Log").Filter = "[Ref Num]='" & Me![Ref Num] & "'"
Forms("Counter Log").FilterOn = True
DoCmd.OutputTo acOutputForm, "Counter Log", acFormatPDF, FilePath

Does anyone know why this is not working? Ultimately, I would just want the one record showing on the form to turn into a screenshot PDF. If possible, I would like the SaveAs window popup for the user to choose where to save the PDF but if not then having it to save to anyone's desktop. Not specifically mine. Is it something like C:\%userprofile%\Desktop for the filepath?

[Welshgasman]

No, but I know a search engne that will likely have some?

https://www.google.com/search?q=expo...hrome&ie=UTF-8

[data808]

No, but I know a search engne that will likely have some?

https://www.google.com/search?q=expo...hrome&ie=UTF-8

Yup I'm on google right now which is how I found that one example but its not working. Looking for more but so far haven't found anything that has worked.

[ArviLaanemets]

We use for years now CutePDF Writer (it is freeware) for this. Users downolad it, and install as printer. Whenever user wants to print any document (Word, Excel, Text file, Access Report, ...) as PDF, he/she selects the CutePDF printer, then the dialog window opens to determine to where and to with name to save the printout, and when printed, the PDF-file is saved.

[Welshgasman]

Yup I'm on google right now which is how I found that one example but its not working. Looking for more but so far haven't found anything that has worked.

You likely need to to acSelection. Lookup the syntax with MS. Default is acPrintAll
I have never printed forms, (other than PrtScrn) so not sure whether the filter would take effect?

If you wanted to default to the user's desktop, then you would need to concatenate the user's UserProfile environment variable into the file path

[data808]

We use for years now CutePDF Writer (it is freeware) for this. Users downolad it, and install as printer. Whenever user wants to print any document (Word, Excel, Text file, Access Report, ...) as PDF, he/she selects the CutePDF printer, then the dialog window opens to determine to where and to with name to save the printout, and when printed, the PDF-file is saved.

Thanks for the suggestion but I think I would rather use VBA than installing 3rd party software to do the job.

[data808]

You likely need to to acSelection. Lookup the syntax with MS. Default is acPrintAll
I have never printed forms, (other than PrtScrn) so not sure whether the filter would take effect?

If you wanted to default to the user's desktop, then you would need to concatenate the user's UserProfile environment variable into the file path

Thanks. I got it to work somewhat. This code filters the form down to the current record that's reflecting in the form. So basically eliminates all other records in the table from being outputted to the PDF:

Dim FileName As String
Dim FilePath As String
FileName = "Counter_Log_" & Me.txtRefNum
FilePath = "C:\Users\JS\Desktop" & FileName & ".pdf"
Me.Filter = "[Ref Num] = " & Me.[txtRefNum]
Me.FilterOn = True
DoCmd.OutputTo acOutputForm, "Counter Log", acFormatPDF, FilePath

The problem I am having now is that the form is no longer able to navigate/scroll through other records. I would like it to somehow refresh/requery so that the user can continue looking at other records in the form. The only way to do this right now is to close the from then reopen it. This will bring it back to it's normal state. However, it also goes back to the last record entered so I actually would like it to refresh/requery while staying on the current record they just exported to PDF. I tried the Me.Requery and Me.Refresh but that didn't do anything.

Oh and what is the code for making the filepath to save the PDF to anyone's desktop and not just my own?

[data808]

Ok got the save to desktop regardless of user:

Dim FileName As String
Dim FilePath As String
FileName = "Counter_Log_" & Me.txtRefNum
FilePath = CreateObject("WScript.Shell").SpecialFolders("Desk top") & "" & FileName & ".pdf"
Me.Filter = "[Ref Num] = " & Me.[txtRefNum]
Me.FilterOn = True
DoCmd.OutputTo acOutputForm, "Counter Log", acFormatPDF, FilePath

Now just for the issue with the refresh or requery to make it so that the user can still navigate and look at other records while being in the form. Right now this code works as far as saving the PDF to the desktop but because it filtered everything out except for the one record that we exported to PDF, there is no way to click the next or back buttons to look at other records. The only way is to close and reopen the form then it will load all records.

How do I do this without having to close and reopen form?

[Welshgasman]

Just remove the filter after the print?

[Gicu]

Report are much easier and better for printing; open the form in design view, go to File\Save As and save it as a report (rptCounterLog - notice there are no spaces in the object name). Now open the report in design view and change its recordsource to only include the current record in your form (something like Select * From [Counter Log] Where [Ref Num] = Forms![Counter Log]![txtRefNum] ;")

Now you get the file name and use Docmd.OutputTo acOutputReport....

Cheers,

[data808]

Just remove the filter after the print?

I already tried Me.FilterOn = False and that just takes the form to the first record with no other records loaded. So basically only able to view that first record. I even tried adding Me.Refresh and Me.Requery to the Me.FilterOn = False and that didn't do anything.

[data808]

Report are much easier and better for printing; open the form in design view, go to File\Save As and save it as a report (rptCounterLog - notice there are no spaces in the object name). Now open the report in design view and change its recordsource to only include the current record in your form (something like Select * From [Counter Log] Where [Ref Num] = Forms![Counter Log]![txtRefNum] ;")

Now you get the file name and use Docmd.OutputTo acOutputReport....

Cheers,

Thanks. Yeah I keep reading that reports are the way to go. I just like the idea of having a screen shot of the form so it looks identical to how its viewed when the user enters the record. I guess its possible to design a report that looks just like the form but I was hoping for a quick solution as designing a report would take some time getting it done properly.

I will try your method and see what the results are and get back to you. Thanks again for the suggestion and keep them coming if you have anymore.

[Welshgasman]

I have always set the filter to "" as well?

[data808]

I have always set the filter to "" as well?

lol yeah I saw that post online somewhere. I think it was:

Me.Filter = ""
Me.FilterOn = False

That didn't work either. I have come up with an unsatisfying solution for now. It closes the form then reopens it and then all the records load. The thing I don't like about it is that it makes the form flash because its closing and reopening so it looks horrible and also when it reopens, I don't know how to open it back up to the record that it was on right before it closed. So the right now it just reopens to a new record. The code looks like this:

If MsgBox("Export Record To PDF?", vbYesNo, "Export") = vbYes Then
    Dim FileName As String
    Dim FilePath As String


    FileName = "Counter_Log_" & Me.txtRefNum
    FilePath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "" & FileName & ".pdf"
    Me.Filter = "[Ref Num] = " & Me.[txtRefNum]
    Me.FilterOn = True
    DoCmd.OutputTo acOutputForm, "Counter Log", acFormatPDF, FilePath
    MsgBox "PDF File Was Exported As (Counter_Log_Ref#.pdf) To Your Desktop", _
    vbInformation, "Notification"
    Me.txtCustNum.SetFocus
    DoCmd.Close
    DoCmd.OpenForm "Counter Log"
    DoCmd.GoToRecord , , acNewRec
Else
    Me.txtCustNum.SetFocus
End If