VBA that exports selected record in form to PDF with specific name and file path (1 Viewer)

templeowls

Registered User.
Local time
Yesterday, 21:26
Joined
Jun 11, 2019
Messages
112
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

Enthusiastic Amateur
Local time
Today, 05:26
Joined
Sep 21, 2011
Messages
8,826
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.?
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:26
Joined
Oct 29, 2018
Messages
16,216
Have you seen this earlier discussion on a similar topic? Maybe it will give you some ideas.

 

templeowls

Registered User.
Local time
Yesterday, 21:26
Joined
Jun 11, 2019
Messages
112
So I was able to find one guide and came up with the below:

Code:
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

Enthusiastic Amateur
Local time
Today, 05:26
Joined
Sep 21, 2011
Messages
8,826
You concatenate that data as variables to make your filename.
FWIW you do not need the domain name for the criteria.?

EG:
Code:
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? :(
 

templeowls

Registered User.
Local time
Yesterday, 21:26
Joined
Jun 11, 2019
Messages
112
Here's my code. It doesn't seem to be implementing the criteria because every complaint is being exported for some reason. Any thoughts?

Code:
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

Enthusiastic Amateur
Local time
Today, 05:26
Joined
Sep 21, 2011
Messages
8,826
Look at the syntax for the open report, you appear to have left a comma out?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 00:26
Joined
Feb 19, 2002
Messages
32,908
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.
 

bastanu

AWF VIP
Local time
Yesterday, 21:26
Joined
Apr 13, 2010
Messages
862
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,
 

Users who are viewing this thread

Top Bottom