Passing Parameters to Queries for Report Generation

[drizzo]

I'm trying to create a subroutine that will run a report for me, and also pass the report a date range in which to use. Using the qdf.Parameter option I am able to set the values of the date parameters in the queries, but when I try and export the report it prompts me to enter the parameters. Below is the code I have come up with thus far.

 
Sub testSub()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
 
Set db = CurrentDb
Set qdf = db.QueryDefs("qry_EMP_GROUP_CURRENT")
qdf.Parameters("startDate") = "2/1/2011"
qdf.Parameters("endDate") = "2/17/2011"
qdf.Parameters("empAssignmentDate") = "2/17/2011"
Set rst = qdf.OpenRecordset
DoCmd.OpenReport "Tech Support Summary Report"
End Sub

[orange]

See this for an alternative approach
http://www.fontstuff.com/access/acctut08.htm


Here's a sample
http://www.pcreview.co.uk/forums/cre...-t2464130.html

[drizzo]

Thanks for the info Orange. I read the articles you linked to, and I used that as a starter. Below is what I came up with. I can then call this sub from VBScript which then I can schedule to run daily.

 
Sub exportReport()
Dim currentDate As String
Dim stringVar1 As String
 
currentDate = Format(Date, "yyyymmdd")
stringVar1 = "\\sfnt\adduser\Supervisors\TS\Reports\Tech Support Summary Report " & currentDate & ".pdf"
 
DoCmd.OpenForm "dateSelect"
Forms!dateSelect!txtStartDate.Value = #1/1/2011#
Forms!dateSelect!txtEndDate.Value = Date - 1
Forms!dateSelect!txtEmpAssignmentDate.Value = Forms!dateSelect!txtEndDate.Value
DoCmd.SetWarnings False
DoCmd.OutputTo acOutputReport, "Tech Support Summary Report", "PDFFormat(*.pdf)", stringVar1, False, "", , acExportQualityScreen
DoCmd.SetWarnings True
End Sub

[orange]

Glad you got it working!!!