Change report source and text box in VBA

[jay c]

I haven't had a lot of exposure to Access and VBA for around ten years, so I'm practically a newbie. I remember a little bit of VBA, and I know some powershell and vbscript. I apologize in advance for my ignorance and stumbling.

I need to print a series of weekly reports for different departments. I don't want to maintain a different report for each department, so I'd like to be able to change the report header and query source for one report using VBA.

Here's how I was thinking of doing it:

1. Get the name of the first department from a table.
2. Get the appropriate query name from the department name. They are all named "qryDepartment".
3. Open the report (rptWeekly) in design mode.
4. Change the report record source to qryDepartment.
5. Change the report header (Label26) to "Department Weekly Report".
6. Export the report to PDF.
7. Go back to #1 for the next dept.

Except I can't remember how to do most of this. I can export the report to PDF, but how do I loop through the department names table, and how do I change the record source and header before exporting it?


If there's a better way to do this, I'm open to that too.


Thank you!

 

[boblarson]

For the Header, just put a text box on the header and format it to look like a label. Then you can either have a form for running the reports from, one department at a time and use a combo box or list box to get the names from (the control source on the report could look like

= [Forms]![FormNameHere]![ControlNameHere].[Column](1) & " Weekly Report"

And then open the report filtering it in the code to open it:

DoCmd.OpenReport "rptWeekly", acViewPreview, , "[DepartmentID]=" & Me.ControlNameHere

 

[jay c]

Thanks, Bob. That makes sense. I might not need to change that header at all, though. Here's what I'm thinking now after doing a little more research:

• Instead of pointing the report at a different query for each department, I can just filter the data as you suggested.
• Instead of changing the header in the report, I can make the header generic and just change the output filename according to department. It will very rarely be printed, so I don't think this will matter much.

Unfortunately, I have 40+ departments, so running them one at a time isn't practical. So the big thing I have left to figure out is how to loop through the list of department names.

 

[jay c]

Another question:

Is there a way to filter the DoCmd.OutputTo method?

or

Is there a way to automatically export the report to a PDF file using DoCmd.OpenReport method?

 

[boblarson]

To iterate through the departments you can just do something like

Dim rst As DAO.Recordset
 
Set rst = CurrentDb.OpenRecordset("Select Distinct DepartmentID From TableName Order By DepartmentID", , dbOpenForwardOnly)
 
Do Until rst.EOF
   'do the printing code here
 
Loop

As for the OutputTo, no you can't filter but in order to use that you could change the query underlying the report by using a QueryDef object and some cool SQL code by Access MVP Armen Stein which is found here:
http://www.jstreettech.com/files/basJStreetSQLTools.zip

The ReplaceWhereClause function there is great. Just unzip the code in that link and then copy all of it into a blank standard module.

 

[jay c]

Hi Bob. I really appreciate your help, but to be honest, I can't make heads or tails of the code in the zip file. I'm sure I could if I had a lot of time to spend on it--I wish I did!--but I don't.

I'm just going to have to do it the hard way for now (separate query and report for each department) and come back to it when I have time to figure it out.

Thanks anyway!

 

[boblarson]

You don't need to understand the code AT ALL. Just paste it into a new module and then when wanting to replace the criteria for a query you can do this:

Dim qdf As DAO.QueryDef
Dim strWHERE As String
 
Set qdf = CurrentDb.QueryDefs("QueryNameHere")
 
strWHERE = "WHERE [FieldX]='Something' And [FieldY]>=#" & Date & "#"
 
qdf.SQL = ReplaceWhereClause(qdf.SQL, strWHERE)
qdf.Close
 
Set qdf = Nothing

Then you can open the report. Actually very simple.

 

[jay c]

No offense intended, Bob. The idea of running code I found on the Internet without understanding exactly what it does makes my hair stand on end.

Here's what I was able to come up with:

Dim db As DAO.Database
Set db = CurrentDb

Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs("qryGlobal")

Dim rsDepartments As Recordset
Set rsDepartments = db.OpenRecordset("tblDepts")

strQryPart1 = "SELECT <blah blah blah blah> FROM tblRawData WHERE "
strQryPart2 = " ORDER BY tblRawData.[<blah>];"

Do Until rsDepartments.EOF

    strDepartment = rsDepartments!Department
    strQuery = strQryPart1 & rsDepartments!OUQuery & strQryPart2
    strReportPath = "\\servername\reports\" & strDepartment & " Data.pdf"
    qdf.SQL = strQuery
    DoCmd.OutputTo acOutputReport, "rptTemplate", "PDFFormat(*.pdf)", strReportPath, False, "", , acExportQualityPrint
    
    rsDepartments.MoveNext
     
Loop

Works perfectly.