Automatic Report Parameters

[the_utmost]

Hi:

I am creating an Access application that will automate the printing of 10 reports. Each of these reports requires that I enter in a Start_Date and and End_Date. I have written a function so that they generate the first and last date of any given month. I need to know how to pass in the first and last date of a given month as parameters.

Note: I cannot alter the queries in the said reports.

 

[Pat Hartman]

The most efficient method is to modify the queries to add selection criteria. If these queries are used for another purpose, perhaps you should create new ones just for the report. A less efficient method is to use the where argument of the OpenReport method to filter the report.

 

[the_utmost]

I cannot alter the reports at all for several reasons. There are too many reports to alter and the queries are built off of a main query, and they cannot be changed.

Can I send in parameters with the WHERE clause in the DoCmd.OpenReport command? Can you give me an example on how to do this? Will I be able to do this if my reports are built of off queries that use their own WHERE clause?

 

[Pat Hartman]

Can I send in parameters with the WHERE clause in the DoCmd.OpenReport command?

yes.

Can you give me an example on how to do this?

Search the archives here. There are lots of examples.

Will I be able to do this if my reports are built of off queries that use their own WHERE clause?

The Where argument of the OpenForm Method applies a filter to a recordset. That is why it is less efficient than modifying the underlying where clause.

 

[the_utmost]

I tried the OpenForm method but I am sending parameters to reports, not to forms. It choked when I tried to do this, telling me that my report was not in the forms collection.

 

[Pat Hartman]

I said OpenReport twice and then misspoke in my last statement as I was explaining why this method was inferior and said OpenForm. Couldn't you work that out? Use the OpenReport method to open a report and the OpenForm method to open a Form.

 

[the_utmost]

My problem goes deeper than that. I need to use the WHERE clause but the reports are generated off of a query that accepts parameters. All I need is the syntax for the WHERE clause to get this to fly. I currently have something like this:

strWhereSQL = "RPT.POST_DT BETWEEN '" & StartDate & "' And " & "'" & EndDate & "'"

AccessApp.DoCmd.OpenReport rsRunningReports!report_name, acPreview, , strWhereSQL

I still get prompted for the date values at this point.

 

[Pat Hartman]

To stop the prompts, you are going to have to either:
1. Remove the prompts from the query and rely entirely on the where argument of the OpenReport Method.
or
2. Modify the query's where clause to reference form fields rather than prompting for input.

If as you say, you "can't" change the queries, you are SOL since both methods require some modification to the queries.

 

[the_utmost]

It looks like I will have to change the queries but this was not my first choice. It will not be a difficult thing to do, but there are a fairly large number of reports to change and the thing is, we will add more reports in the future, so we wanted to avoid that. I have posted this problem in several area's and on different forums and everyone agrees with you. Thanks for your help, I will follow your advice.

 

[sportsguy]

The best answer is to have each report linked to its own query. Then at report run time, using code, you can change the report query to add as many where clauses as you want, save the query using the QueryDef procedure, and then open the report.

sportsguy

 

[the_utmost]

It is more complicated then that. There is one main pass-through query that runs off of an Oracle DB and then many Access subqueries. The parameters are in the subqueries. I got the OK to take out the parameters and therefore will use the WHERE clause when I open the report. But as far as changing the queries while the app runs, it would be more work then I need to do.

 

[Pat Hartman]

Your Oracle table must be relatively small. To make the most efficient use of ODBC databases, the underlying query is where the parameters belong. You want to limit the amount of data you are retrieving from the back end db. Your current structure returns all rows and then filters them locally.

BTW, a pass-through query that runs a stored procedure is slightly more efficient than a standard Access query against a linked table but not much. Jet makes every effort to "pass through" ALL queries to the back end db engine.

Stored procedures against RDBMS' are to dynamic SQL as Querydefs against Jet tables are to dynamic SQL. They are both "compiled" ahead of time and therefore save processing at runtime.

I would make separate queries with parameters as the base queries to limit the number of rows returned from the Oracle db. Then choose which query to use as the RecordSource for the form/report.

 

[the_utmost]

The way that you descibed it (several queries on for the reports) is how the situation currently is. The problem is that I am automating the printing of all the reports and they want me to have no user input. So I think that I want to change the queries and pass in parameters with the WHERE clause of the OpenReport method. I have tried everything else that I can think of. Thanks for your help. I appreciate it.

 

[Pat Hartman]

The best way to pass parameters to a report's query is by using a form. So, rather than prompting, the criteria is obtained directly from the form that runs the report.

Where someField = Forms!YourForm!SomeField

 

[the_utmost]

I'm Just About Done!

I am trying to, for the most part, to bypass using a form. For instance, a person will select "May" and hit "Run" and the app will print all 10 reports for May. I have a function that will get the first and last date of the given month. Now I am trying to open the report with the OpenReport method using the dates in the Where clause. No luck so far.

AccessApp.DoCmd.OpenReport ReportName:=rsRunningReports!report_name, View:=acViewNormal, WhereCondition:="StartDate = " & tmpStartDate & " And EndDate = " & tmpEndDate & "

I will work on the Where condition and try to use "between" in the SQL.

 

[Pat Hartman]

Either use Between date1 and date2 or use >= and <=. You are only selecting records for the two end dates.

You shouldn't have separate reports for "May". You should have one set of reports that take a parameter and there is no better place to supply that parameter than a form.

Allowing the users access to the container window so they can run reports themselves is not good practice. You should provide a form controlled interface and never let them at the database container at all.

 

[the_utmost]

You are right about using 'beween'. That was what I was planning on doing. You're also correct in your thinking that there should be one set of reports that take in a parm. I probably didn't explain that very good but that's how it is set up. On that note, my report prints off every record instead of using the WHERE clause. Here is my code: (this is a very simple example)

strWhereSQL = "[ID] = 1"

If Not rsRunningReports.RecordCount = 0 Then

For i = 1 To rsRunningReports.RecordCount
'* Print report
Set AccessApp = CreateObject("access.application")
AccessApp.OpenCurrentDatabase (strPath)
AccessApp.Visible = False
AccessApp.DoCmd.OpenReport rsRunningReports!report_name, acViewNormal, strWhereSQL
DoEvents '* Allow report to be sent to the printer
DoCmd.Close acReport, rsRunningReports!report_name, acSaveNo '* The user can save the data in the last parameter but here we turn it off
AccessApp.Quit
Set AccessApp = Nothing

Next i
Else
MsgBox "No reports in print que.", vbInformation, "No Reports to Print"
End If


Also, I *do* use a form to select the month but I have to use a function to find out the last day of that very month. The first and the last day of that month will be the parms. I have an interface for the user and I plan to make an .mde file once it is completed, so that people will not muck with the code and queries. On that note, I *really* prefer .exe's but such is life. Thanks very much for your note.

 

[Pat Hartman]

You are missing a coma in the statement:
AccessApp.DoCmd.OpenReport rsRunningReports!report_name, acViewNormal,, strWhereSQL

There is no reason to close the report since you didn't open it in preview mode it just went to the printer so remove the close statement.

An Access database can't be an .exe because it is a "container" file. It contains various other files that define objects or contain data. Data can NEVER be converted to an .exe so don't hold your breath waiting for that feature. Once an .exe is built, it doesn't change.

 

[the_utmost]

Deadly! Works beautifully. Couldn't have done it without your help. Thanks a lot.

 

[the_utmost]

Getting Propted for Values

That worked for a simple examle: ID = 1 but now I am working in the real code.

I basically have 3 parameters: date, group number and Admin company. I build an SQL WHERE clause to open the report by querying a table that is like a print que. It returns one record and I use variables to create the SQL string. It is prompting me for the date when I open it up with this:

AccessApp.DoCmd.OpenReport rsRunningReports!report_name, acViewNormal, , strWhereSQL

I think that I am just overlooking some simple syntax. Here is my SQL WHERE string function:

'* ParmGroupNumber is the parameter used for Group Number
'* ParmAdminComp is the parameter in the print que table for Admin Company
'* intID is the report ID number. Is the primary key of the table
'* Post_Dt is the date the report was posted. I am using BETWEEN in the ' WHERE clause
'* dt_StartDate and dt_EndDate are the start and end date of the reporting period. Entered in from the User Interface.

strReportName = rsRunningReports!report_name
intID = rsRunningReports.Fields!ID

strSQL = ""
strSQL = "SELECT Post_Dt, ParmGroupNumber, ParmAdminComp "
strSQL = strSQL & "FROM tblReport_Details "
strSQL = strSQL & "WHERE ID = " & intID & " "
strSQL = strSQL & "AND Report_Name = '" & strReportName & "'"

Set rsWHERESql = CurrentDb.OpenRecordset(strSQL)
rsWHERESql.MoveLast
rsWHERESql.MoveFirst

strWhereSQL = ""

If rsWHERESql.RecordCount = 0 Then
MsgBox "Error building SQL statement.", vbCritical, "Critical Error"
Else

If Not IsNull(rsWHERESql.Fields!Post_Dt) Then
strWhereSQL = "[Post_Dt] BETWEEN #" & dt_StartDate & "# AND #" & dt_EndDate & "# "
End If

If Not IsNull(rsWHERESql.Fields!ParmGroupnumber) Then
intGroupNo = rsWHERESql.Fields!ParmGroupnumber
strWhereSQL = strWhereSQL & "AND [ParmGroupnumber] = " & intGroupNo & " "
Else
strWhereSQL = strWhereSQL & "AND [ParmGroupnumber] = *"
End If

If Not IsNull(rsWHERESql.Fields!ParmAdminComp) Then
strAdminComp = rsWHERESql.Fields!ParmAdminComp
Else
strAdminComp = "*"
End If

strWhereSQL = strWhereSQL & "AND [ParmAdminComp] = '" & strAdminComp & "'"

End If