Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2013-05-21
5
Medium Priority
?
311 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 2000 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 49

Expert Comment

by:Dale Fye
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 49

Expert Comment

by:Dale Fye
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

580 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