How do I pass a parameter into an access report programatically?

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. Help?

(I'm a SQL / VB.net guy with no Access experience, so apologies up front for the "send me teh codes" style question. Also, google is kinda letting me down on this one. Too basic a question? I guess so.)

Answer 1

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

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

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

Why everyone wants to make this so complicated, I don't know.

1. save your report's recordsource without parameters.

2. as suggested by Remou, pass the criteria in the appropriate argument of DoCmd.OpenReport.

Trying to do it any other way is going to be a matter of resisting the natural methods for accomplishing tasks in Access.

--
David W. Fenton
David Fenton Associates

Answer 4

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.

Answer 5

Would Remou's solution work though, if the form you are want to pass the parameter to's data source is an ado dataset, in the "code" section of the form? I don't think so, and I couldn't find any other way to pass a generic parameter to another form(which could in turn, be used as a parameter in a query in your VB Code). It would be nice if you could do that in Access.

I think Knox's suggestion is good for that case. When the user clicks on the button or link to go to another form, you write the parameter to the database, then form then opens and reads that value....you may of course, run into problems if you have many people using the reports at the same time though.

Answer 6

I know this is an old post but this took me a bit. Error was "Invalid use of parren" however the issue was the space in the field name. I was creating a report from a db that someone did the common mistake, spaces.

To pass a param to a query through the where clause when the database field has a space use this example:

DoCmd.OpenReport "rptByRegionalOffice", acViewPreview, , "[" & "Regional Office" & "]" & "=" & "'" & cmboOffices.Value & "'"

If you think about this you can see that this will produce where [Regional Office]='string value' just as you would expect in access sql.