Save Path Name In Table for editing - Saving & deleting files for email module

[Shaunk23]

I have outlook module that sends out forms... I have many different forms in the database.. in order to do what i needed i Save the Report as a PDF on my server, then delete file once attached to my email.. That being said as you see below i had to hard code the "save path" name in.. Is there a way for me to put this in a single table so it can be edited by admin in the sys_config section of my database? Here is the code..



Private Sub EmailDestinationRelease(Reportname As String)
On Error GoTo ErrorRoutine

Dim STRDOCNAME As String
Dim STRCRITERIA As String
Dim ReportNamesave As String
Dim SavePathName As String

On Error GoTo ErrorRoutine

ReportNamesave = Form_ExpBooking.UltimateCarrierRef & " Destination Release"
SavePathName = "\\Server\AecLogistics\TempFiles\ " & ReportNamesave & ".pdf"


STRDOCNAME = Reportname
STRCRITERIA = "ExpBooking.ID =" & Form_ExpBooking.ID
DoCmd.OpenReport STRDOCNAME, acViewPreview, , STRCRITERIA
DoCmd.OutputTo acOutputReport, Reportname, acFormatPDF, SavePathName, False, , , acExportQualityPrint
SendEmailMessage True, SavePathName
Kill SavePathName


ErrorRoutine:
Exit Sub
End Sub


The line in question is
SavePathName = "\\Server\AecLogistics\TempFiles\ " & ReportNamesave & ".pdf"

Could i write someway the "\\Server\AEClogistics\tempfiles\ " in a table and just reference that there?

 

[Addyman]

Re: Save Path Name In Table for editing - Saving & deleting files for email module

Yes you can. In case you need to be able to store more than one path I would create a table with an ID field (numeric - can be autonumber if you wish) and then a text field to store the path in.

Let's say you add a new record to this table and your ID is 1, you would then need to have this in your code:

SavePathName = DLookup("YourFieldName","tblYourTableName","ID = 1") & ReportNamesave & ".pdf"

Replace YourFieldName and tblYourTableName as appropriate and it should work fine.

I use this structure a lot for managing sequential numbers etc. for certain code strings I create. I can have multiple sequential numbers being stored in a table and I just DLookup using the relevant ID number as and when I need.

 

[Shaunk23]

Re: Save Path Name In Table for editing - Saving & deleting files for email module

Great thanks!

 

[Shaunk23]

Re: Save Path Name In Table for editing - Saving & deleting files for email module

How would i use " yes / no " for active path in the dlook up? Like i will set it to only have one active at a time.. so ill use the dlook to see which one is active and pull tha tone.

SavePathName = DLookup("ActualPath", "SysCon_SavePathDirectory", "ActivePath =" & "Yes") & ReportNamesave & ".pdf"