Report from a filtered form?

[Jim Orson]

Can I generate a report from a form which has a filter applied? I have two
comboboxes on a form and I would like to filter by selection on either of
those two lists and then generate a report of only the resultant records.
Is this possible?

Thank you!

Jim...

 

[Allen Browne]

Place a command button on your form, and put something like this into its
Click event procedure to print a report with the same filter as your form:

Private Sub cmdPrint_Click()
Dim strFilter As String
If Me.Dirty Then
Me.Dirty = False
End If
If Me.FilterOn Then
strFilter = Me.Filter
End If
DoCmd.OpenReport "MyReport", acViewPreview, , strFilter
End Sub

Sometimes the Filter of the form refers to fields from the lookup table of
your combo. If so, you must also include that lookup table in the query that
is the RecordSource of the report. When adding the extra table to the query,
you may need an outer join. If you are not sure what that means, see:
The query lilst my records!
at:
http://members.iinet.net.au/~allenbrowne/casu-02.html

 

[Jim Orson]

Allen,
The command button code works perfectly for a simple text box on the form.
However, I am having trouble getting the query for the report RecordSource
to work. With a filter by selection on the combobox in the form, I get a
request for a parameter. Depending how I reply, I either get no records in
the report or all records. Maybe some info on the combobox would help you
get me back on track.

The combobox has a control source of CategoryID (from tblCategories) and the
SQL for the RowSource is SELECT DISTINCTROW tblCategories.* FROM
tblCategories ORDER BY tblCategories.CategoryName;

This allows me to view the actual Category Name in the combobox rather than
the CategoryID.

When I add the tblCategories to the report query as you recommended and
click on the line between it and tblHousehold, I get three choices of
"joins". I have tried selecting each of them and still the report does not
run with the filtered recordset.

Is this enough info for you to understand and correct the problem I am
having? I do appreciate your assistance, Allen.

Jim...

 

[Allen Browne]

To be honest, Jim, I don't use that kind of filter, and I have a dim
recollection of some kind of bug associated with it.

To help you debug what is going on, open your form and set it up with the
problem filter active. Then press Ctrl+G to open the Immediate window, and
enter something like this:
? Forms![MyForm].Filter

Look at the result. Make sure you have the field specified there in the
query of the report.

 

[Jim Orson]

Allen,
Thanks for your help and suggestions. The "field" in the immediate window
is actually a lookup table (not lookup field). I searched all over and
tried several things and cannot figure out how to put that field in the
report query. However, the whole matter led me into looking at datasheet
views of the form. I found many flexibilties with formatting, printing,
viewing, and filtering with the datasheet view. This is a personal
database and only my wife and I will be using it, so datasheet view
manipulation is just fine. I would not have found these features if you
would not have replied to my original post. Thanks again for your time and
comments!
Jim...

To be honest, Jim, I don't use that kind of filter, and I have a dim
recollection of some kind of bug associated with it.

To help you debug what is going on, open your form and set it up with the
problem filter active. Then press Ctrl+G to open the Immediate window, and
enter something like this:
? Forms![MyForm].Filter

Look at the result. Make sure you have the field specified there in the
query of the report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
The command button code works perfectly for a simple text box on the form.
However, I am having trouble getting the query for the report RecordSource
to work. With a filter by selection on the combobox in the form, I get a
request for a parameter. Depending how I reply, I either get no records
in
the report or all records. Maybe some info on the combobox would help you
get me back on track.

The combobox has a control source of CategoryID (from tblCategories) and
the
SQL for the RowSource is SELECT DISTINCTROW tblCategories.* FROM
tblCategories ORDER BY tblCategories.CategoryName;

This allows me to view the actual Category Name in the combobox rather
than
the CategoryID.

When I add the tblCategories to the report query as you recommended and
click on the line between it and tblHousehold, I get three choices of
"joins". I have tried selecting each of them and still the report does
not
run with the filtered recordset.

Is this enough info for you to understand and correct the problem I am
having? I do appreciate your assistance, Allen.

Jim...

either
of