Access 2010 - Report asks to enter parameter value

Question

I have a report with a subreport. Report is unbound (it is used as a template for letter and contains plain text only). Values in the subreport are based on values of two combo boxes located in a form.

So the logic is as follows - when the form (frmForReport) is loaded the user selects values in combo boxes intended to filter the database (cboSelectCompany and cboSelectPU), then clicks OK button which opens my report with its subreport. Subreport is populated from a query where two of fields have those combo boxes as criteria. Everything works fine (I select what I need in combo boxes, report opens in Report View with the subreport showing all filtered records without any warnings or requests) until I want to switch to Print Preview or Create PDF. Every time when I do so I see pop-up window Enter Parameter Value with a reference to one of criteria used in the subreport. When I close this window I can proceed further, save it as pdf or switch to Report View but the subreport appears blank, literary disappears.

This is for Access 2010. Below is the query used as a record source for the subreport:

SELECT qryForLetter.CrossingID, qryForLetter.DispID, qryForLetter.AgreType, qryForLetter.Legal
FROM qryForLetter
WHERE (((qryForLetter.Company)=[Forms]![frmForReport]![cboSelectCompany]) AND ((qryForLetter.PU)=[Forms]![frmForReport]![cboSelectPU]));

What do I miss? How do I get rid of this pop-up window? Any help is greatly appreciated.

What happens if you open that query in Design View, then Add two columns using your comboboxes? i.e. "Expr1:[Forms]![frmForReport]![cboSelectCompany]" and "Expr2: [Forms]![frmForReport]![cboSelectPU]"? When you run that query do the values you selected in the comboboxes show in those two columns of the query? If not, you need to change the "Bound Column" property for your comboboxes. — Wayne G. Dunn
When I add two columns in Design View of that query as you suggested and run the query, I get popup window asking to enter parameter value firstly for Forms!frmForReport!cboSelectCompany and then for Forms!frmForReport!cboSelectPU. If I enter parameters in both windows, the query is opened in Datasheet View with all columns plus Expr1 and Expr2 with data filtered according to parameters I entered. So what does it mean? — Olga K.
My bad, I should have said to delete the selection criteria row. But, what values do you have in the two new columns? If not the values from your comboboxes, you need to change the "Bound Column" setting. — Wayne G. Dunn
Well, what I described happens when I delete the selection criteria. If I leave the selection criteria on its place and add expressions, I get only one record. But nevertheless in both cases after running the query the 2 new columns are populated with values that I entered in popup windows. Sorry if I misunderstand. The form that contains combo boxes works fine. The report opens also fine. The subreport contains records selected in combo boxes. My problems start when I want to switch the report view or save it as pdf. — Olga K.
What happens if you run the following SQL? I removed your selection criteria. If you are still prompted for any parameter, then that means 'qryForLetter' is asking for the parameters. Still want to know if Expr1 and Expr2 show what is in your comboboxes (your form needs to be open, and selections made): SELECT qryForLetter.CrossingID, qryForLetter.DispID, qryForLetter.AgreType, qryForLetter.Legal, [Forms]![frmForReport]![cboSelectCompany] as Expr1, [Forms]![frmForReport]![cboSelectPU as Expr2 FROM qryForLetter — Wayne G. Dunn

Monty Wild Monty Wild · Accepted Answer · 2014-02-25T00:27:56

You could try replacing parameters [Forms]![frmForReport]![cboSelectCompany]) and [Forms]![frmForReport]![cboSelectPU] with functions.

Create global variables in the header of a module:

Public Company As ...
Public PU As ...

... replacing the ellipses ... with the correct datatype.

In your Combo Boxes' AfterUpdate events, assign the value associated with the Combo to the appropriate global variable, e.g.:

Company = cboSelectCompany

and

PU = cboSelectPU

and create functions (again replacing the ellipses ... with the correct datatype):

Public Function fnCompany() As ...
    fnCompany = Company
End Function

Public Function fnPU() As ...
    fnPU= PU
End Function

Then change the data source to:

SELECT qryForLetter.CrossingID, qryForLetter.DispID, qryForLetter.AgreType, qryForLetter.Legal
FROM qryForLetter
WHERE (((qryForLetter.Company)=fnCompany()) AND ((qryForLetter.PU)=fnPU()));

This has the effect of removing the dependence of the report from the form fields, which appears to be the issue.

Access 2010 - Report asks to enter parameter value

1 Answers