Return multiple values

[Ivor Williams]

I have a form with a subform. In the subform is a field ProjNo. On the form
is a command button which, when clicked opens a query. The intent of the
query is to return a list of all the different values in the ProjNo field on
the subform. I've used as a record source for the query the table which is
the record source for the subform. In the Criteria field of the query, I've
used the following: Forms.frmPO.sfrOrder.Form.ProjNo. This works fine,
except the query only returns the ProjNo for current record in the subform.
How do I get the query to return all the records, not just the current one?

Ivor

 

[Carl Rapson]

I have a form with a subform. In the subform is a field ProjNo. On the form
is a command button which, when clicked opens a query. The intent of the
query is to return a list of all the different values in the ProjNo field
on the subform. I've used as a record source for the query the table which
is the record source for the subform. In the Criteria field of the query,
I've used the following: Forms.frmPO.sfrOrder.Form.ProjNo. This works
fine, except the query only returns the ProjNo for current record in the
subform. How do I get the query to return all the records, not just the
current one?

Ivor

Remove that value from the Criteria field of the query. You've told the
query to return all records where the ProjNo matches what you entered.

Carl Rapson

 

[Ivor Williams]

Sorry, I wasn't very clear. I want the query to return a list of all the
different (unique) values chosen in the ProjNo field of the subform, not all
the different values in the ProjNo field of the underlying table. The
ultimate objective is to run a report for each unique value in the ProjNo
field of the subform.

Ivor

 

[Carl Rapson]

You can't use a query to select values from a subform; a query will only
fetch values from a table (or another query). A query can't iterate through
values in a control on a form; you'd have to do that with VBA code.

You say your subform is bound to a table, but the values in the subform
aren't the same as the values in the table? What do you mean by "chosen in
the ProjNo field of the subform"?

Carl Rapson