Dynamic File name based on field in the report

[Bancell]

I am trying to figure out how to have a file that is being exported via a macro to automatically use the field "Group Of Technician" from the "partsUsuageReport" to a specific location. I have been able to get the file to export and save to the correct location but have not been able to figure out how to get the file name correct. From everything I have read I will need to use VBA for this. Unfortunately I dont understand VBA at all at this point. I am working on learning it but the light hasnt clicked on yet. A lot of the code I am seeing online doesnt look anything like what I have here so I'm not sure what Im doing wrong.

Any suggestions?






'------------------------------------------------------------
' OpenReport_Click
'
'------------------------------------------------------------
Private Sub OpenReport_Click()
On Error GoTo OpenReport_Click_Err

DoCmd.OpenReport "PartsUsuageReport", acViewReport, "", "", acNormal
DoCmd.OutputTo acOutputReport, "PartsUsuageReport", "PDFFormat(*.pdf)", "T:\Brad&Bill\Tech Inventories\Parts Usauge Reports\Parts Usauge Report.pdf", False, "", , acExportQualityPrint


OpenReport_Click_Exit:
Exit Sub

OpenReport_Click_Err:
MsgBox Error$
Resume OpenReport_Click_Exit

End Sub

 

[welshgasman]

I tend to define a base path lets say called strPath with a value of "T:\Brad&Bill\Tech Inventories\Parts Usauge Reports\"
I would also parameterise the report name
strReport = "PartsUsuageReport"

Then you would use something like

DoCmd.OutputTo acOutputReport, strReport, "PDFFormat(*.pdf)", strPath & strReport & "-" & [Group Of Technician] & ".pdf", False, "", , acExportQualityPrint

HTH

 

[Bancell]

Thank you for the quick reply!


Ok so this is what I have now. I am now getting the following error when I try to run the report. "Microsoft Access can't find the field '|1' referred to in your expression"




'------------------------------------------------------------
' OpenReport_Click
'
'------------------------------------------------------------
Private Sub OpenReport_Click()
On Error GoTo OpenReport_Click_Err

DoCmd.OpenReport "PartsUsuageReport", acViewReport, "", "", acNormal
strPath = "T:\Brad&Bill\Tech Inventories\Parts Usauge Reports"
strReport = "PartsUsuageReport"
DoCmd.OutputTo acOutputReport, strReport, "PDFFormat(*.pdf)", strPath & strReport & "-" & [Group Of Technician] & ".pdf", False, "", , acExportQualityPrint



OpenReport_Click_Exit:
Exit Sub

OpenReport_Click_Err:
MsgBox Error$
Resume OpenReport_Click_Exit

End Sub

 

[Bancell]

I got it to work. I just had to put in [reports]![PartsUsuageReport]![group of technician] and it worked!!

Thank you for the assistance!!