Pass a parameter into an access report programmatically

Question

I've got an existing Access MDB. I'm adding a command button to an existing Form that runs an existing report. The change being made is that this button needs to pass in a parameter containing the ID of the record being reported on - currently the report runs on every record in the MDB.

I've altered the Query that the report runs on to use a parameter for the ID value, so that now when the button is clicked Access prompts for the record ID to report on, and the report displays like it should.

However, I can't for the life of me figure out how to pass a parameter into the report for the query to use. How can I do this?

Answer 1

The DoCmd.OpenReport method has various arguments, one of which is a Where statement:

DoCmd.OpenReport"rptReport", acViewPreview,,"ID=" & Me.ID

That is

expression.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)

Answer 2

My general approach to this type of problem is to save the criteria in the database, typically a control table that has one row. Then to reference your criteria you put a query in paranthesis that returns one value, of the criteria you want. In your case, it would be something like:

(select reportID from control)

The advantage of this techinque is that the control table remembers the settings for the next time you run the report. Of course, ReportID would be tied to a field in a form. I also like the fact that your queries are isolated from forms; they can be run independently of forms.

Answer 3

The Where clause of the docmd.openreport is a string that uses the same format as the where clause in a SQL statement.

The reason to put parameterize you query at the docmd instead of the RecordSource of the report is flexibility. You may have a need to open the report without any paremeter/return all the records or have the ability to filter on different fields.