VBA Button To Export/Save/Print to PDF

[Welshgasman]

So just set the form recordsource again, that forces a fresh requery.
Or even the recordset.requery

[Gicu]

All of that when you could create a replica of your form as a report in 30 seconds.... and it would probably solve your other issues with rendering the white font if this is the same form referenced in your other post.

But in case you want to stick to your form maybe try something like this (a Me.Requery followed by the Docmd.FindRecord would probably also work instead of closing the form):

Dim FileName As String
Dim FilePath As String
Dim lRefNum as long  'assume ref number is a long integer
	
If MsgBox("Export Record To PDF?", vbYesNo, "Export") = vbYes Then
    FileName = "Counter_Log_" & Me.txtRefNum
    FilePath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "" & FileName & ".pdf"
    Me.Filter = "[Ref Num] = " & Me.[txtRefNum]
    Me.FilterOn = True
    lRefNum =Me.txtRefNum 'store the ref number to return to record
    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
    Application.Echo False
    DoCmd.Close
    DoCmd.OpenForm "Counter Log"
    Me.txtRefNum.SetFocus
    Docmd.FindRecord lRefNum
    Me.txtCustNum.SetFocus
    Application.Echo True
    '.DoCmd.GoToRecord , , acNewRec
Else
    Me.txtCustNum.SetFocus
End If

Cheers,

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

Was going to try your method but noticed that I cannot save a form as a report following your instructions. I open the form in design view then go to File then Save As and there are options to save it as a accdb, mdb, accdt, accde, etc... nothing to save as a report. I'm sure I am doing something wrong.

I invested a lot of time in doing the export to pdf from the form way and would like to see if I can complete my goal. The pdf looks really good with the existing code that I posted. However, there are a couple things happening which I need some help fixing. The big one right now is that I was going to apply this code to some fetch forms I have that open to a specific record when you click on the reference number (which is the primary key and I coded behind it on the click event and made it look like a hyperlink so users know they can click on it) in a split form which will open a fetch form of that exact record they clicked on. So I created a button to do the same thing for the export to pdf and it keeps loading pages when its trying to create the pdf file. So I assume that its because its trying to load the whole record set. I messed with the order of the filter lines and even took them out completely for testing and nothing seems to stop it from loading all the records when creating the pdf. Would you be able to look at the code and see if you know whats going wrong? Here is the code:

On Error Resume Next

If Me.Dirty Then
    If MsgBox("Before Export To PDF, You Must Save Your Changes." _
    & vbCrLf & vbCrLf & "Would You Like To Save Now?" _
    , vbYesNo, "Save Record") = vbYes Then
        If IsNull(Me.cmbSubject) Then
            MsgBox "Please Enter A Subject To Save The Record", vbInformation, "Requirements"
            Me.cmbSubject.SetFocus
            Exit Sub
        End If
        DoCmd.RunCommand acCmdSaveRecord
    Else
        Me.txtTabStopper.SetFocus
        Exit Sub
    End If
End If


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
Else
    Me.txtCustNum.SetFocus
End If

[data808]

All of that when you could create a replica of your form as a report in 30 seconds.... and it would probably solve your other issues with rendering the white font if this is the same form referenced in your other post.

But in case you want to stick to your form maybe try something like this (a Me.Requery followed by the Docmd.FindRecord would probably also work instead of closing the form):

Dim FileName As String
Dim FilePath As String
Dim lRefNum as long  'assume ref number is a long integer
    
If MsgBox("Export Record To PDF?", vbYesNo, "Export") = vbYes Then
    FileName = "Counter_Log_" & Me.txtRefNum
    FilePath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "" & FileName & ".pdf"
    Me.Filter = "[Ref Num] = " & Me.[txtRefNum]
    Me.FilterOn = True
    lRefNum =Me.txtRefNum 'store the ref number to return to record
    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
    Application.Echo False
    DoCmd.Close
    DoCmd.OpenForm "Counter Log"
    Me.txtRefNum.SetFocus
    Docmd.FindRecord lRefNum
    Me.txtCustNum.SetFocus
    Application.Echo True
    '.DoCmd.GoToRecord , , acNewRec
Else
    Me.txtCustNum.SetFocus
End If

Cheers,

Thank you very much for the suggestion. Just tested it and it's still going to a new record. I discovered something interesting. On the open event of the form I have it set to Docmd.GoToRecord,,acNewRec so that is the reason that's probably happening so I removed it and then the form loaded the first record in the recordset and thats it. No other records were available to view. So basically if you don't tell the form which record to go to, it will only load up the first record in the recordset. I think this behavior is also what is causing me to have problems with the fetch form. But not to get side tracked, would you know how to resolve this issue using your code that you posted now that we know the open event tells the form to open to a new record and that seems to be the only way to load the whole recordset into the form?

[Gicu]

I'm going to recommend you try to make it a report, as there are too many unknowns without seeing your db. When you open the form in design view and click on File\Save As you should get two options in the second column (leave it to MS for messing this up), choose Save Object then choose report:

Again. once you do that, open the new report in design view and change its record source to only include the current record in the form. Once that is done you just need the lines of code for the file name and the Docmd.OutputTo acOutputReport line to save it as PDF.

If you get stuck I will try to get you form printing tomorrow...

Cheers,

[data808]

I'm going to recommend you try to make it a report, as there are too many unknowns without seeing your db. When you open the form in design view and click on File\Save As you should get two options in the second column (leave it to MS for messing this up), choose Save Object then choose report:

Again. once you do that, open the new report in design view and change its record source to only include the current record in the form. Once that is done you just need the lines of code for the file name and the Docmd.OutputTo acOutputReport line to save it as PDF.

If you get stuck I will try to get you form printing tomorrow...

Cheers,

Ok I'm in the middle of testing right now. I created the report and it looks exactly like the form which is great. Where you lost me was changing the record source to only include the current record. There is record source in the properties under the Data tab however, here is where you would choose a table or query to pull data from. The other property that looks kind of what you are mentioning is in the Other tab in properties called Cycle. Here is where one of the options is Current Record. So I set that and the report still loads, what seems to be all records. I'll keep looking on Google to see if I can find an explanation of how to open a report with just one record that shows on a form. If I find anything I will let you know.

[Gicu]

PROGRESS!
Please open the newly created report in design view and on the recordscource property line (first one on the Data tab of the properties window of the form itself) you should click on the three dots (edit button) on the right and add criteria to the reports source to only load the current record from the form; this is my suggestion from back from post 11:
Select * From [Counter Log] Where [Ref Num] = Forms![Counter Log]![txtRefNum] ;
Cheers,

[data808]

PROGRESS!
Please open the newly created report in design view and on the recordscource property line (first one on the Data tab of the properties window of the form itself) you should click on the three dots (edit button) on the right and add criteria to the reports source to only load the current record from the form; this is my suggestion from back from post 11:
Select * From [Counter Log] Where [Ref Num] = Forms![Counter Log]![txtRefNum] ;
Cheers,

I think I'm getting closer. Could you take a look at this code I have. I am able to open the report with the one record that is showing on the form. Right now, when I run it, it will ask me if I want to export to PDF then after I click yes, it will flash some prompt windows but no PDF is saved to the desktop. Here is the code:

Dim FileName As StringDim FilePath As String


If MsgBox("Export Record To PDF?", vbYesNo, "Export") = vbYes Then
    FileName = "Counter_Log_" & Me.txtRefNum
    FilePath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & FileName & ".pdf"
    DoCmd.OpenReport "Copy of Counter Log", acViewPreview, , "[Ref Num] =" & Me.txtRefNum
    DoCmd.OutputTo acOutputReport, "Cop of Counter Log", acFormatPDF, FilePath
    MsgBox "PDF File Was Exported As (Counter_Log_Ref#.pdf) To Your Desktop", _
    vbInformation, "Notification"
    DoCmd.Close acReport, "Copy of Counter Log"
    Me.txtCustNum.SetFocus
Else
    Me.txtCustNum.SetFocus
End If

[data808]

PROGRESS!
Please open the newly created report in design view and on the recordscource property line (first one on the Data tab of the properties window of the form itself) you should click on the three dots (edit button) on the right and add criteria to the reports source to only load the current record from the form; this is my suggestion from back from post 11:
Select * From [Counter Log] Where [Ref Num] = Forms![Counter Log]![txtRefNum] ;
Cheers,

I got it working but need to fine tune it a bit. I'm going to work on it and get it to do what I want more smoothly with buttons and what not but I notice that the report looks good but once I export to PDF, the PDF will have the detail section of the report to be gray background color and the header and footer will reflect the background picture that is on the form. Would you know why that is happening?

[data808]

PROGRESS!
Please open the newly created report in design view and on the recordscource property line (first one on the Data tab of the properties window of the form itself) you should click on the three dots (edit button) on the right and add criteria to the reports source to only load the current record from the form; this is my suggestion from back from post 11:
Select * From [Counter Log] Where [Ref Num] = Forms![Counter Log]![txtRefNum] ;
Cheers,

Ok I've got it working great on the regular Counter Log form. However, I am still getting the same issue with my Counter Log Fetch form. When exporting to PDF from the fetch report (I had to make another copy for this fetch Counter Log because it has a slightly different scenario when pulling up records), it looks like its loading all the records even though the report only shows the one record that is pulled from the Fetch form. Here is a screenshot of the tiny prompt that pops up and it shows the current page count just keeps counting upwards never stopping until I cancel it. See attached picture. I tested a print to PDF and it worked just fine. It's only happening for export to PDF. Here is the export code:

Private Sub cmdExportToPDF_Click()


Dim FileName As String
Dim FilePath As String


If MsgBox("Export Record To PDF?", vbYesNo, "Export") = vbYes Then
    FileName = "Counter_Log_" & Me.txtRefNum
    FilePath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & FileName & ".pdf"
    DoCmd.OutputTo acOutputReport, "Counter_Log_Screenshot_report", acFormatPDF, FilePath
    MsgBox "PDF File Was Exported As (Counter_Log_Ref#.pdf) To Your Desktop", _
    vbInformation, "Notification"
    Me.txtRefNum.SetFocus
Else
    Me.txtRefNum.SetFocus
End If


End Sub

This does not happen when I go through my regular Counter Log form which loads the whole recordset then I open the report and it will only pull up the one record that is in the form. The export to PDF code is identical to what the Fetch form is using. It's just the Fetch form is not picking up just the one record showing in the report. It's picking up everything.


The way the Fetch form is loaded with the record is a big culprit in my opinion. It pulls the record from a split form when the user clicks on the Reference Number. It does a DLookup when the user clicks on the Reference Number on a split form. The records are listed in datasheet view on the bottom and some text boxes for filtering the datasheet down to minimize the results reflecting in the split form. Once the user sees the record they want, they can click the reference number and it will open the Counter Log Fetch form with that one record showing. Here is the code for the click event behind the reference number when the user clicks on it:

Private Sub txtRef_Num_Click()


Dim validCredentials As Long
Dim ID As Long
On Error GoTo ErrHandler:
validCredentials = DCount("[Ref Num]", "[tbl_Counter_Log]", "[Ref Num] = " & txtRef_Num & "")


If validCredentials = 1 Then
    ID = DLookup("[Ref Num]", "tbl_Counter_Log", "[Ref Num] = " & Me.txtRef_Num.Value & "")
    'The ID line above needs to be in this location for the code to work.
    'Originally had this line right under the validCredentials = DCount line.
    DoCmd.OpenForm "Counter Log Fetch", , , "[Ref Num] = " & ID
    Me.txtRef_Num.SetFocus
    Exit Sub
End If


ErrHandler:
MsgBox "Unable To Authenticate At This Time. Contact System Administrator For Help."
Me.txtCitNumFilter.SetFocus
Exit Sub


End Sub

So I think it's from this code that makes the Fetch form different because of how the record is loaded into it. Then from the Fetch form I have a button that will open up the report and when I run the same code to export to PDF, it doesn't work.

What are your thoughts on this? Is this beyond your expertise in figuring it out?

I just want to say thank you so much for taking the time to help me. I thought this report thing would definitely solve this issue as what you said made sense to me. I had so much confidence that I started developing for the Fetch section and did not save a master copy of the frontend before taking on that task. lol I hope we can get to the bottom of this. Or someone else knows what is happening.

[data808]

I got it working but need to fine tune it a bit. I'm going to work on it and get it to do what I want more smoothly with buttons and what not but I notice that the report looks good but once I export to PDF, the PDF will have the detail section of the report to be gray background color and the header and footer will reflect the background picture that is on the form. Would you know why that is happening?

Nevermind about this background color thing btw. I got it fixed.

[Welshgasman]

This is generally a big NO NO !

On Error Resume Next

Also do you have Option Explicit at the top of every module?

[Gicu]

Can you please show the record source for the fetch report? Does it have the criteria set to limit the records to only the one displayed in the fetch form?

Cheers,

[data808]

This is generally a big NO NO !

On Error Resume Next

Also do you have Option Explicit at the top of every module?

Should I remove all the On Error Resume's?

What does option explicit do?

[data808]

Can you please show the record source for the fetch report? Does it have the criteria set to limit the records to only the one displayed in the fetch form?

Cheers,

I thought that was what this line was for? Espcially the part in red:

DoCmd.OpenReport "Counter_Log_Screenshot_Fetch_report", acViewReport, , "[Ref Num] =" & Me.txtRefNum

It also works for the regular Counter Log way so that doesn't make sense to me. I tried adding criteria to the Fetch report in design view like you mentioned. I clicked the 3 dots then it has a pop saying something about "You invoked the query builder on a table. Do you want to create a query based on a table?" So I click yes. Then it takes me to a brand new query. Not sure what fields I should add so I did the asterisk thing which I think adds all fields to the query, then added another Ref Num field and set it to not show. I figure the Ref Num is what we will be pulling from. Then I put your suggested line (Select * From [Counter Log] Where [Ref Num] = Forms![Counter Log]![txtRefNum]) in the criteria and I get a prompt that says "The syntax of the subquery in this expression is incorrect. Check the subquery's syntax and enclose the subquery in parentheses."