Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Importing / Linking / August 2005

Tip: Looking for answers? Try searching our database.

Export reports  to MS Word using VBA

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Tony White - 09 Aug 2005 18:57 GMT
Hello Experts and Power Users

I need your help. I administer a mystery shop program and I do monthly
reporting. Each month I have to run one report for each region shopped. There
are 40 regions therefore I run the report 40 times and name/export to rtf 40
times. I created a query that gives me the current regions shopped.

Question 1: Can I use VBA to run the report for each regionID in result
query records? I use an input box to enter the valid regionID for the time
period

Question 2: Can I use VBA to export the report as a rtf file? Currently I
'plug-n-chug' the export/naming process.

Hopefully this is possible. Thanks for your help in advance!
Signature

Anthony White

Roger Carlson - 09 Aug 2005 19:58 GMT
Sure.  You should create a table with each region in a separate record.
Call it tblRegions.  You could have the region name or whatever in it, but
one field it must have is RegionID.  Base your report on a query called
NewQuery (actually, it can be anything you want, but in the example below,
it's called NewQuery).  Then you'll need some code to create your query over
again, once for each region, using the values in the tblRegion table.  Once
you've created the query, you Output your report to a file in the RTF
format.  The code would look something like this:

'*********************************
Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim rsCriteria As Recordset

Set db = CurrentDb
Set rsCriteria = db.OpenRecordset("tblRegions", dbOpenDynaset)

'*** the first record in the Criteria table ***
rsCriteria.MoveFirst

'*** loop to move through the records in Criteria table
Do Until rsCriteria.EOF
   '*** create the Select query based on
   '    the first record in the Criteria table
   strSQL = "SELECT * FROM MainTable WHERE "
   strSQL = strSQL & "[RegionID] = " & rsCriteria![RegionID]

   '*** delete the previous query
   db.QueryDefs.Delete "NewQuery"
   Set qdf = db.CreateQueryDef("NewQuery", strSQL)
   DoCmd.OutputTo acReport, "rptGLTable", "RichTextFormat(*.rtf)",
"C:\GLTable.rtf", False
   rsCriteria.MoveNext
Loop
'**************************
Of course, you will substitute your actual query SQL statement, query name,
table name, file name, and so forth into the above code, but that's the gist
of it.

Signature

--Roger Carlson
 Access Database Samples: www.rogersaccesslibrary.com
 Want answers to your Access questions in your Email?
 Free subscription:
 http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

> Hello Experts and Power Users
>
[quoted text clipped - 11 lines]
>
> Hopefully this is possible. Thanks for your help in advance!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.