Save to PDF File Name AutoCreate

[Brilstern]

Q: How do I relate the desired filename to a field within the table of the datasource to "autoname" the file when saved.

In the below code the filename is NewReport. I want to change this to be the [Long Name] field of each record source when it saves. Reason being in the "autoname" function is because eventually I am going to create an archive button that will save a copy for each record to a monthly folder.

Private Sub cmdSavePDF_Click()
Dim strName As String
Dim strFilter As String
Dim strCurrentPath As String
Dim strReportFile As String
Dim strReport As String
strFilter = "[SSN] = '" & Me.MemberSelect & "'"
strName = Me.MemberSelect
strCurrentPath = Application.CurrentProject.Path
strReportFile = strCurrentPath & "\NewReport.pdf"
strReport = "rptECR"
 
   Call DoCmd.OpenReport(ReportName:="rptECR", View:=Access.AcView.acViewReport, WhereCondition:=strFilter)
   Debug.Print "Report and path: " & strReportFile
   DoCmd.OutputTo objecttype:=acOutputReport, objectname:=strReport, outputformat:=acFormatPDF, outputfile:=strReportFile
   DoCmd.Close acReport, "rptECR"
 
End Sub

Let me know if there is any other information that will help find a solution. Thx

Sgt B

[sierra7]

Hi again,
The answer is to include the identifier in strReportFile e.g.

strFilter = "[SSN] = '" & Me.MemberSelect & "'" 
strName = Me.MemberSelect 
strCurrentPath = Application.CurrentProject.Path 
strReportFile = strCurrentPath & "\NewReport" & strName & ".pdf" 
 

However, refereing back to your other question about looping through BandMembers, then this is a good example why you might want to use looping rather than have a continuous report (with page breaks) Each member would have a seperate report that could be emailed to him personally. The continuous report being more of a Control Document for management purposes.
S7

[NeoPa]

Try the following as a replacement for line #10 (I'm assuming the table name is [tblECR] - change if necessary) :

strReportFile = DLookup(Expr:="[Long Name]", _
                        Domain:="[tblECR]", _
                        Criteria:=strFilter)
strReportFile = Replace(strCurrentPath & "\ECR%N.PDF", "%N", strReportFile)

[Brilstern]

NeoPa,

That did the trick, I have never used the Dlookup function, new one to put into the toolbox. Thx

Sierra7,

Thx for the input, led exactly to NeoPa's example:)

Sgt B

[NeoPa]

It's also possible to use recordset code to access the same data, but generally a single access (to get one bit of data) can better be handled by the Domain Aggregate functions, of which one such is DLookup().

[Brilstern]

I see thank you both very much.