MS Access: How to filter report on non-report field?

2
votes

I haven't worked with Access in 8 years -- I believe I've forgotten more than I ever knew. Someone asked me to modify a report so that a list of drawings, which currently displays all drawings, will list drawings for a specific customer (or for all customers).

I located the nested queries used by the report, and I modified the inner query to accept a parameter (first mistake?). Then I added a new form to display the customer name and retrieve the associated primary key, customerId. Now I need to feed the customer ID from the form to the report. DoCmd.OpenReport and adding a whereCmd doesn't seem to match up the where condition with the parameter.

So, I removed the parameter in the query, and tried just specifying the condition. Only the outer query doesn't know what a customerId is. Do I need to expose this in the inner query, and in the outer query, then just not use it in the final report just so I can filter it in the where clause?

They have other sample code that uses VBA in the form to rebuild a query (adding in whatever criteria for filtering) before launching the report. Surely this can't be the recommended practice, can it?

2
ms-access
" ... rebuild a query (adding in whatever criteria for filtering) before launching the report" Is that query used as the report's Record Source? – HansUp
The report's record source is the outer query, yes. But it's the inner query, which is called by the outer query, that's capable of identifying the customer. That information is not a field in the results of the inner,and thus not available in the outer, and thus I don't think I can filter on it. – knockNrod
From what I read, a filter is preferred overa parameter query. Ok, I added it, I'll take it back out. But I can't get the filter to work, and I believe that's because it's not a field in the report's record source. So, if I add the field to the field list of the inner query and the outer query, must it be used in the report? If it's not used in the report,will I still be able to filter on it? – knockNrod
You can include a field in the report's record source and not display it in any of the report controls. – HansUp
You should post that as an answer. It works a treat! – knockNrod

2 Answers

2
votes

Include the filter field in the report's record source. Then you can filter on that field when you open the report.

DoCmd.OpenReport "rptFoo", View:=acViewPreview, _
    WhereCondition:="[id]=1"

The record source field used in the WhereCondition does not have to be displayed by any of the report's controls ... as long as it's present in the record source it can be used in the WhereCondition.

0
votes

I would throw this at the report in OpenArgs. From your form, do something like:

DoCmd.OpenReport strReportName, , , , , Me.CustomerID

Then, in the Load event of the report, read the OpenArgs and alter the RecordSource of the report:

Me.RecordSource = "SELECT * FROM MyTable WHERE CustomerID = '" & Me.OpenArgs & "'"

This is "aircode" and will likely need a little tweaking, but it should get you really close. Obviously, if CustomerID is an INT field, remove the single quotes from the SQL in the report Load event.