VBA that exports selected record in form to PDF with specific name and file path

[gojets1721]

Hi I'm looking for a VBA for a button that when pressed in a form will export a report based on the selected form entry and open a prompt menu to save in a designated file path with a file name.

Essentially the form is a bunch of complaints and the form shows one complaint at a time. I have a report that basically serves as a printable version of each individual complaint. I want a button that will take whatever complaint you're on in the form and drop it into the report, and then export that report to a designated folder. But before doing so, there will be a prompt with a preset name (just the customer's name from the form) in case the user wants to save it elsewhere.

I currently use a macro and it does the job for now. It's just an open report macro with the following condition: "[qryComplaintReport]![ComplaintNumber]=[Forms]![ComplaintDetails]![ComplaintNumber]. This opens the right entry up in the report but it only brings it to a print preview. The user still needs to navigate through access to save it and there's no preset path or name.

Please let me know if I didn't give enough info. Thanks!!

 

[Gasman]

Again you need a where clause, this time with the report.

Edit: Sorry that post above was on my phone and I did not see the second paragraph.

Look at the OutputTo macro if you insist on using macros.

Personally I would go with learning VBA as there is so much out there, that you can google.?

You could get all your requirements using Google and VBA. I tend to program one task at a time, due to my inexperience. So I would get the report printed to a file location, then worry about the second part of giving choice to the user. However all that would still be with VBA and possibly already written if you look at Similar threads at the bottom of this thread.?

 

[theDBguy]

Have you seen this earlier discussion on a similar topic? Maybe it will give you some ideas.

Export report to separate PDF's

Hi, This discussion seems to have been covered several times in many places, so I'm sorry if I've out this in the wrong place! I've been trying for a few days but no matter what code I use I cannot get it to work. I am very unfamiliar with code as I only set this DB up as a favour for work...

www.access-programmers.co.uk

 

[Gasman]

Not having a good night tonight

Try https://www.access-programmers.co.u...-a-pdf-report-by-selecting-a-location.287075/

 

[gojets1721]

So I was able to find one guide and came up with the below:

Private Sub cmd_exportformPDF_Click()

    Dim reportName As String
    Dim fileName As String
    Dim criteria As String
    
    reportName = "CompletedForm"
    fileName = "F:\Documents\completedForm.pdf"
    criteria = "[qryComplaints]![ComplaintNumber]=[Forms]![Complaint Details - 2021]![ComplaintNumber]"
    
    DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
    DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
    DoCmd.Close acReport, reportName, acSaveNo

End Sub

However, this is a bit too static. It exports the report using the file name every time. Any idea how to code a dynamic name by pulling info from the form (such as "CustomerLastName, Customer Firstname Date of Complaint -> Smith, John 5.25.2021)?

 

[Gasman]

You concatenate that data as variables to make your filename.
FWIW you do not need the domain name for the criteria.?

EG:

strFolder = "F:\Documents\"
strFileName = [Forms]![Complaint Details - 2021]![LastName]" & "-" & [Forms]![Complaint Details - 2021]![FirstName]"

DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, strFolder & strFileName

that should give you the basics.

Use Debug.Print to make sure the string is correct.

Edit: That link in post 4 actually shows all this?

 

[gojets1721]

Edit: That link in post 4 actually shows all this?

Oh jeez I'm sorry. I didn't even see that link. I was only looking at your first comment

 

[gojets1721]

Here's my code. It doesn't seem to be implementing the criteria because every complaint is being exported for some reason. Any thoughts?

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

 

[Gasman]

Look at the syntax for the open report, you appear to have left a comma out?

 

[gojets1721]

Look at the syntax for the open report, you appear to have left a comma out?

After acHidden?

 

[Pat Hartman]

I would not hard-code the path. You can put the path in a table and create a form to modify the location. That works if the location is the same for all users. If you want a separate location for each user, add an additional column for UserID. But, the form gets more complicated because you don't want Joe to change the location for Sally so you need to verify the userID before you allow a change.

Also, I know that people like their dates as month day year or day month year but to have them sortable, the date in the file name should be year month day. You might also want to concatenate the ID of the complaint so you don't end up with duplicate file names.

 

[Gasman]

After acHidden?

No, before criteria, can be name of filter, then the where option. Use intellisense, that is what it is there for.

 

[Gasman]

Whilst you have stated you were crossposting in the other forum, you should let this forum know as well?
https://www.accessforums.net/showthread.php?t=83708

 

[bastanu]

As stated in the other forum I think the main problem is with this line:
criteria = "[qryComplaints]![ComplaintNumber]=[Forms]![ComplaintDetails - 2021]![ComplaintNumber]" should be:
criteria = "[ComplaintNumber]= " & [Forms]![ComplaintDetails - 2021]![ComplaintNumber] 'assuming the ComplaintNumber is numeric

Cheers,

 

[gojets1721]

Yes i was able to figure it out. Thank you!!