By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,654 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Save to PDF File Name AutoCreate

Brilstern
100+
P: 207
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.


Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSavePDF_Click()
  2. Dim strName As String
  3. Dim strFilter As String
  4. Dim strCurrentPath As String
  5. Dim strReportFile As String
  6. Dim strReport As String
  7. strFilter = "[SSN] = '" & Me.MemberSelect & "'"
  8. strName = Me.MemberSelect
  9. strCurrentPath = Application.CurrentProject.Path
  10. strReportFile = strCurrentPath & "\NewReport.pdf"
  11. strReport = "rptECR"
  12.  
  13.    Call DoCmd.OpenReport(ReportName:="rptECR", View:=Access.AcView.acViewReport, WhereCondition:=strFilter)
  14.    Debug.Print "Report and path: " & strReportFile
  15.    DoCmd.OutputTo objecttype:=acOutputReport, objectname:=strReport, outputformat:=acFormatPDF, outputfile:=strReportFile
  16.    DoCmd.Close acReport, "rptECR"
  17.  
  18. End Sub
Let me know if there is any other information that will help find a solution. Thx

Sgt B
Jan 30 '12 #1

✓ answered by NeoPa

Try the following as a replacement for line #10 (I'm assuming the table name is [tblECR] - change if necessary) :
Expand|Select|Wrap|Line Numbers
  1. strReportFile = DLookup(Expr:="[Long Name]", _
  2.                         Domain:="[tblECR]", _
  3.                         Criteria:=strFilter)
  4. strReportFile = Replace(strCurrentPath & "\ECR%N.PDF", "%N", strReportFile)

Share this Question
Share on Google+
5 Replies


Expert 100+
P: 446
Hi again,
The answer is to include the identifier in strReportFile e.g.
Expand|Select|Wrap|Line Numbers
  1. strFilter = "[SSN] = '" & Me.MemberSelect & "'" 
  2. strName = Me.MemberSelect 
  3. strCurrentPath = Application.CurrentProject.Path 
  4. strReportFile = strCurrentPath & "\NewReport" & strName & ".pdf" 
  5.  
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
Jan 30 '12 #2

NeoPa
Expert Mod 15k+
P: 31,308
Try the following as a replacement for line #10 (I'm assuming the table name is [tblECR] - change if necessary) :
Expand|Select|Wrap|Line Numbers
  1. strReportFile = DLookup(Expr:="[Long Name]", _
  2.                         Domain:="[tblECR]", _
  3.                         Criteria:=strFilter)
  4. strReportFile = Replace(strCurrentPath & "\ECR%N.PDF", "%N", strReportFile)
Jan 30 '12 #3

Brilstern
100+
P: 207
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
Jan 30 '12 #4

NeoPa
Expert Mod 15k+
P: 31,308
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().
Jan 30 '12 #5

Brilstern
100+
P: 207
I see thank you both very much.
Jan 30 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.