How do I save a report to PDF in folder matching values of listbox

[Delta729]

First of all, I barely use Access and I'm very much a novice so I hope I explain this correctly so it makes sense.

I have a very small database created just for the purpose of easy formatting a report and printing the report to PDF for management. I have everything set up and everything is working great, except I would like to be able to route the report when saving to a folder based on the values of list boxes.

I have a form with 3 list boxes that cascade or depend on values on the preceding one. The first box is "lstDivision", when selected it drills down and shows the values for "lstDivRep" and 3rd is "lstAssociate". I currently have all of the files so they will save to PDF in a local folder. What I'd like to do is set this up so it will route to sub-folders based on the values selected in the first two list boxes. (Hope this makes sense). The "lstAssociate" is multi-selection list box.

Here is the code I have now:

Private Sub btnSaveToPDF_Click()



Dim sReportName As String
sReportName = "IDP" '' name of the predefined report

Dim vItm As Variant
Dim sSQL As String

For Each vItm In [Forms]![IDP]![lstAssociate].ItemsSelected
sSQL = "[Associate] = '"
sSQL = sSQL & [Forms]![IDP]![lstAssociate].ItemData(vItm) & "'"

DoCmd.OpenReport sReportName, acViewPreview, , sSQL

DoCmd.OutputTo acOutputReport, "", acFormatPDF, "C:\Users\Daniel\Desktop\backup copy\New Output\" & [Forms]![IDP]![lstAssociate].ItemData(vItm) & " - " & [Forms]![IDP]![lstDivision].ItemData(vItm) & " - Updated (" & Format(Date, "mm-dd-yyyy") & ")" & ".PDF", False

DoCmd.Close acReport, sReportName
Next vItm

End Sub


Any help would be greatly appreciated because I've been searching off and on for 3 days.

Thanks, Dan

[June7]

What is the issue? What happens when the code runs - error message, wrong results, nothing?

Do the folders at each level already exist? If not, will have to run code that creates them.

[Delta729]

Hi June,

The code run's perfectly and puts all of the PDFs into one folder New Output. Basically, there are about 200 associates and the way this works now putting all the PDFs into one folder, I'd have to manually move them after they've been saved to the New Output folder. What I'd like to be able to do is to automatically look at the value selected in the first list box "lstDivision" and the second list box "lstDivRep" and put these into existing sub-folders in the folder called New Output. I hope that makes sense.

[June7]

If the folders already exist then modify your path concatenation to include the listbox values for those folders.

[Delta729]

That is where I'm completely lost. I should have said I'm a complete novice "idiot" at Access & especially VBA. It took me over a week to get this code working that I had found on the web. Do I need to declare the listbox for their selected values? Like the Dim sReportName As String (or variant) is on the code right now?

[June7]

No, listbox is a variable, just refer to the listbox. Actually looks like your code is already doing that except need to concatenate "\" between each folder variable and the filename. Replace " - " with "\".