Need help exporting criteria driven report via VBA

[templeowls]

Cross post from here. Just trying to get more exposure for this question. I've found a few guides online but none so far that are exporting based on a criteria



I'm looking to have a button in a form which, when pressed, will pull the current form entry that the user is viewing into a preset report and then export that report to a preset folder. All without prompting the user to do anything I also want the file to have a dynamic name based upon the entry that is being exported. I have the below code which doesn't seem to be applying the criteria. So it's exporting every entry rather than the singular one that the user is viewing. Any ideas? I'm not fluent in VBA so this could be completely incorrectly coded...just as a heads up

Private Sub cmd_exportformPDF_Click()
    Dim reportName As String
    Dim criteria As String
    Dim strfolder As String
    Dim strfilename As String
    
    reportName = "CompletedForm"
    criteria = "[qryComplaints]![ComplaintNumber]=[Forms]![ComplaintDetails - 2021]![ComplaintNumber]"
    strfolder = "F:\Documents"
    strfilename = Me.ComplaintLastName & Me.ComplaintFirstName & "-" & Day(Now) & "-" & Month(Now) & "-" & Year(Now) & ".pdf"
    
    DoCmd.OpenReport reportName, acViewPreview, criteria, acHidden
    DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, strfolder & strfilename
    DoCmd.Close acReport, reportName, acSaveNo
 End Sub

[ranman256]

the query of the report looks at the combo box (or other control) as criteria, and only
shows data for that person.

the report can then be saved to target folder & name:

sub btnPrintPdf_Click()
vDir = "\\server\folder\"     '(or get it from a table)
vName = "rInvoice_" & cboBox & ".pdf"
vFile = vDir & vName
docmd.OutputTo acOutputReport ,"rInvoice1Person",acformatpdf,vFile
end sub

[templeowls]

I'm not sure what you mean. The query itself for the report does not utilize any criteria. Should it?

[ranman256]

yes. the combo would pick the client/person.
and the query would make a report for only that person.

select * from table where [clientID]=forms!fMyForm!cboBox

make another button to output ALL persons if needed (a different report) ,then the combo is not needed.
But you still get a report of all , or N reports for 1 person.

[templeowls]

I'm filtering by ComplaintNumber since that is always unique. That would work right?

So basically, [ComplaintNumber]=forms!MyForm!ComplaintNumber
?

[templeowls]

So the code would be:

sub btnPrintPdf_Click()
vDir = "F:\Documents"
vName = "PatientLastName" & "PatientFirstname" & ".pdf"
vFile = vDir & vName
docmd.OutputTo acOutputReport ,"ComplaintReport",acformatpdf,vFile 
end sub

I'm honestly unsure of where my components fall into your example. I just want the file name to be "Last name, first name"

[Gicu]

Can you please try this amended code (choose the line you need for the criteria based on the data type of the complaint number)? You need to isolate the form variable otherwise it is passed as the literal text

Private Sub cmd_exportformPDF_Click()
    Dim reportName As String
    Dim criteria As String
    Dim strfolder As String
    Dim strfilename As String
    
    reportName = "CompletedForm"
    criteria = "[ComplaintNumber]= " & [Forms]![ComplaintDetails - 2021]![ComplaintNumber] 'assumes complaint number is numeric
    'criteria = "[ComplaintNumber]= '" & [Forms]![ComplaintDetails - 2021]![ComplaintNumber] & "'" 'assumes complaint number is text
    strfolder = "F:\Documents"
    strfilename = Me.ComplaintLastName & Me.ComplaintFirstName & "-" & Day(Now) & "-" & Month(Now) & "-" & Year(Now) & ".pdf"
    
    DoCmd.OpenReport reportName, acViewPreview, criteria, acHidden
    DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, strfolder & strfilename
    DoCmd.Close acReport, reportName, acSaveNo
 End Sub

Cheers,

[templeowls]

That worked!! Thank you so much

[Gicu]

You're very welcome!

[Welshgasman]

Care to mark it solved in both forums?