Pass criteria to nested queries from multiple independent forms

[sterlecki]

Background: A report (Final Report) uses a query as it's data source. This query (qryFinal) consists of (qryPart1) and (qryPart2). qryPart2 and qryFinal both require the same criteria on the field API ([Forms]![frm_Completions and Perfs]![Well ID]). This orginates from a user selected combo box. I need this in both queries for the report to work correctly.

Problem: I would like the user to be able to run this report from multiple forms independently using a cmd button on each form and having the criteria coming from a combo box on the same form.

It is possible that multiple forms could be open within the application and with different Well ID being displayed in their respective combo boxes on the different forms.

Question: [highlight]How can I get the two queries to respond based on the combo box value from the specific form from which the on click event occurred?[/highlight] Using multiple lines in the Criteria could generate undesired results if multiple forms are open with different values.

[highlight]Is there a way to identify the form from which the on click event occurred and use an IIF statement for the criteria value?

Is there any difference on how I should write the criteria that refers to qryPart2 vs qryFinal?[/highlight]

 

[dhookom]

The simple method may be to open the original form in hidden view and set the value of the combo boxes or other controls.

Duane
Hook'D on Access
MS Access MVP

 

[sterlecki]

Yes that is a good suggestion, straight forward and easily accomplished. Thanks!

By having this ability to open or print this report from several independent forms the instance could arise where the user could be working in the primary form and be in the middle of something but may want to access the report of a different record and not interupt their work. At least this was my intention.

I guess I could create some copies of the queries and report for various forms and modify the criteria source for each.(Kind of a brute force approach)

I have seen mention of the idea of a DoCmd.openReport with the strWhere clause. I guess this might work on qryFinal but I'm not sure how to pass that to qryPart2.

Any other thoughts?

 

[dhookom]

I almost always use the WHERE CONDITION in the DoCmd.OpenReport. This assumes the fields/columns are available in the final report field list.

Another option is to change the SQL property of the saved query.

Duane
Hook'D on Access
MS Access MVP