How do I set up query to look for parameters in a form?

[Guest]

I have followed the tutorial posted by Sal Ricciardi (Using parameters with
queries and reports). The form (dialog box) with combo boxes and command
buttons works great until I try to set the query to look for the parameters
in the form. In design view, I add the following in the criteria field for
each combo box: [Forms]![Sales By Category Form]![Category]. Now this works
as expected until I save the query. After saving the query and returning to
open the form, it is blank. No combo boxes or command buttons. If I return to
the query in design view and remove [Forms]![Sales By Category
Form]![Category] for each combo box, the form returns to its original state.
What am I missing. I am unsing Access 2003.

 

[Allen Browne]

There's a bit of a catch 22 here.

When the form opens, it runs the query so it can choose the records to show
in the controls on the form. But the query is trying to read values from the
form so it can choose the records so it can show the values in the form. We
are going round in circles.

That's complicated by the fact that the Detail section of a form goes
completely blank when:
a) there are no records to show, and also
b) no new record can be added.

If the source query is not updatable, that would explain (b). If the query
can't match anything to the combo which has no value yet, that would explain
(a). When this happens, you can still see controls in the Form Header and
Form Footer sections, but they don't work properly.

There has to be a better way to retrieve records for the form. Could you
leave the parameters out of the query, and instead apply a Filter to the
form? For example you might do this in the AfterUpdate event procedure of
the combo:
If Not IsNull(Me.Category) Then
Me.Filter = "[CategoryID] = " & Me.Category
Me.FilterOn = True
End If
Note that if the CategoryID is a Text field, you will need extra quotes:
Me.Filter = "[CategoryID] = """ & Me.Category & """"

Applying a Filter in this way is much more flexible, especially where there
are many possible fields that you might want to filter on. You can download
a sample database that demonstrates this approach:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html