Parameter value request on report filtered by filter by form

[John]

I am trying to open a report (rptEvaluation) with records filtered by a
filter by form proceedure on a form (frmEvaluation).

The process works fine other than when I use one of the form's combo boxes
to set the filter. These combo boxe's row sources are set to a table with
their control source set to the relevent ID field in the query underlying the
form. The filter by form works fine, however when I run the report I get a
dialogue box requesting: "Enter Parameter Value" and
"Lookup_Combo6897.SchemeName" or a reference to whichever combo box I have
used on the filter by form.

Command button onClick proceedure is:

Dim stDocName As String

stDocName = "rptEvaluation"

If Forms!frmEvaluation.Filter = "" Then
If MsgBox("No filter set. All records will show on reports. " &
Chr(10) & "Do you wish to continue?", vbYesNo) = vbYes Then
DoCmd.OpenReport stDocName, acViewPreview, ,
Forms![frmEvaluation].Filter
DoCmd.RunCommand acCmdZoom100
DoCmd.MoveSize 1500, 0, 13250, 8000
Else
Exit Sub
End If
Else
DoCmd.OpenReport stDocName, acViewPreview, ,
Forms![frmEvaluation].Filter
DoCmd.RunCommand acCmdZoom100
DoCmd.MoveSize 1500, 0, 13250, 8000
End If

Any suggestions to solve this gratefully received. Many thanks.

 

[Allen Browne]

This kind of filtering was new in Access 2002, so presumably you are using
2002, 2003, or 2007. The simplest solution is to alias the lookup table in
the query that serves as the RecordSource of the report.

1. If you don't already have a query as the source for your report, create
one. Use your main table, and also the table named in the combo's RowSource.

2. Right-click the combo's table (in the upper pane of the query design
window), and choose Properties. Set the Alias property to:
Lookup_Combo6897
Save the query.

3. Open the report in design view, and make sure its RecordSource property
is the query you just saved.

The report will now recognise Lookup_Combo6897.SchemeName as being the
SchemeName field from the table aliased as Lookup_Combo6897. The name in the
report now matches the name in the form, and so the filter will work.