Solved

how can I dynamically specify the output location of an MS ACCESS report

Posted on 2013-05-21
5
265 Views
Last Modified: 2013-06-07
I have a system of about 30 programs - ACCDBs.
They all output reports as PDFs to a fixed location:
C:\myapp\output

How can relocate the myapp folder and have the reports output to the new location such as
h:\myhomedirectory\myapp\output

thanks
Phil
0
Comment
Question by:philkryder
  • 2
  • 2
5 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39186520
The output location would have been saved when you created the reports. If the output is generated in VBA, you need to edit it there in each case, if in a macro (possibly in the form you create the report from, edit it there.


Kelvin
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39186935
Along the lines of what Kelvin has posted, our databases have a table called "tblPaths" to store paths like this that are needed in various places in the code.  The fieldnames show the different "types" of paths, and there is a single record containing the actual paths... so the table might look like this:

tblPaths:

FieldName                    Data
---------------------------------------
PDFReportPath             h:\myhomedirectory\myapp\output
webPath                        www.mysite.com
WorkOrderInputFiles   h:\myhomedirectory\myapp\workorders
ImagesPath                  h:\myhomedirectory\myapp\Images

When needed, these paths can be looked up in the code... for example:

Dim strOutputPath as String
Dim strFile as string
strOutputPath = DLookup("PDFReportPath", "tblPaths")
strFile = strOutputPath & "\" & Format(Date,"yyyy_mm_dd_") & "myReport.pdf" 
' Output a report...
DoCmd.OutputTo acOutputReport, "rptMyReportName", acFormatPDF, strFile, True

Open in new window


This allows you to easily change the path for the reports -- just by editing the path as stored in the table.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39187152
no points please.  Minor correction to mbizup's code:

strOutputPath = DLookup("Data", "tblPaths", "[FieldName] = 'PDFReportPath'")
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39187181
Dale,

My code was correct... but the explanation may not have been clear.  I used that format because of a lack of horizontal space.  It might be clearer in a code snippet.

tblPaths contains a single record.  The FieldNames in my previous post are the actual columns in that table.  So pictured another way, the DATA in tblPaths is:

PDFReportPath                             webPath                       WorkOrderInputFiles           ImagesPath  
_______________________________________________________________________________________________________________________     

h:\myhomedirectory\myapp\output         www.mysite.com           h:\myhomedirectory\myapp\workorders        h:\myhomedirectory\myapp\Images       

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39187193
Miriam,

I get it, your Paths table contains a single record and multiple fields, very "non-normal" of you.  I should have figured that out when I saw the "FieldName" header on your list.

;-)
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This collection of functions covers all the normal rounding methods of just about any numeric value.
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now