Basing a report on a filtered form

[TrevorJ]

I have a form with multiple search boxes to filter on one or more different
fields on the form. When any of the search boxes are changed, I make up an
SQL statement and poke it at the record source of a subform on the main form
to produce the filtered results that I
want.(Me![frmSearchResults].Form.RecordSource = strSQL & strCriteria)
I then need to get a report printout of the recordset displayed. I cannot
realistically make a query based on the form fields as they do not
neccessarily all contain my search criteria. I have played around with
recordset.clone, and can count the records, but what I want to be able to do
is poke the recordset (How can I or otherwise) at a report as its
recordsource to produce the same data.
Can anyone please help?
Trevor

 

[Marshall Barton]

I have a form with multiple search boxes to filter on one or more different
fields on the form. When any of the search boxes are changed, I make up an
SQL statement and poke it at the record source of a subform on the main form
to produce the filtered results that I
want.(Me![frmSearchResults].Form.RecordSource = strSQL & strCriteria)
I then need to get a report printout of the recordset displayed. I cannot
realistically make a query based on the form fields as they do not
neccessarily all contain my search criteria. I have played around with
recordset.clone, and can count the records, but what I want to be able to do
is poke the recordset (How can I or otherwise) at a report as its
recordsource to produce the same data.

If the report is based on the same (unfiltered) table/query
as the subform, then, in the command button that opens the
report, use the OpenReport method's WhereCondition argument
to filter the report to the criteria in your strCriteria
string.

 

[TrevorJ]

Thanks Marsh. Worked a treat. Moved the declaration of strCriteria to the top
of the module, had to ditch the 'Order by ....' from the end of strCriteria
for the report, and tack it back on for the Sub form.
Thanks again
Trevor

I have a form with multiple search boxes to filter on one or more different
fields on the form. When any of the search boxes are changed, I make up an
SQL statement and poke it at the record source of a subform on the main form
to produce the filtered results that I
want.(Me![frmSearchResults].Form.RecordSource = strSQL & strCriteria)
I then need to get a report printout of the recordset displayed. I cannot
realistically make a query based on the form fields as they do not
neccessarily all contain my search criteria. I have played around with
recordset.clone, and can count the records, but what I want to be able to do
is poke the recordset (How can I or otherwise) at a report as its
recordsource to produce the same data.

If the report is based on the same (unfiltered) table/query
as the subform, then, in the command button that opens the
report, use the OpenReport method's WhereCondition argument
to filter the report to the criteria in your strCriteria
string.