Can I use form filtered data for a report?

[Wight Dave]

Is there any way of automatically generating a report using filters that have
been applied to a form?

I have a main form (Menu – Main) which includes a tab control (TabCtl105).
The tab control contains a form (FRM_Tasks) which includes task description,
category, project and person delegated to.

I use menu bar controls to filter by selection on the form (for example,
all tasks delegated to Mike) and would like to be able to print a report
which uses the same filtered data set.

Is there any way of doing the equivalent of

open report where data equals filtered data on FRM_Tasks

Many thanks

Wight Dave

 

[Allen Browne]

You may be able to filter the report so it matches the filter on your form
just by adding a command button to your form and setting up its Click event
procedure like this:

Private Sub cmdPreview_Click()
Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

That works for many cases. If you have problems, post back.

 

[Wight Dave]

Hi Allen

Thanks for getting back to me. I’ve tried the procedure but have not yet got
it working.

THE CODE
I entered the code you suggested but Access defaults the first line to
Private Sub Command118_Click(). When I use that, the code opens the report
but pulls back all records even when the filter by selection is used. When I
replace the first line with the one you suggested (Private Sub
cmdPreview_Click()), pressing the button does nothing at all

The code as entered

Private Sub Command118_Click()
Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub


TAB CONTROL
I wondered if the problem was filtering data on a tab control so tried
adding the button directly to a form not on a tab control. This got me
slightly further.

The report opens and pulls back all data when no filters are applied.

When a filter is applied, when I run the code Access comes up with a box
saying ‘Enter Parameter Value’ and then the name of the underlying query and
the field I’m filtering on. If filtering on more than one field, it asks for
a parameter value for all the relevant fields.

If I leave the parameter field blank, I get no records. If I enter part of
the expected value and *, I get no records. If I enter the expected value for
the filter I'm using, I get all the records, rather than the ones expected
from the filter.

If I go into debugger, the second last line DoCMD.OpenReport …. is
highlighted in yellow.


Any thoughts?

Thanks

Wight Dave

 

[Duane Hookom]

Since the button to open the report is probably on the main form and the
filter is applied to a subform, you will need to change the code or add the
print button to the subform.

Private Sub Command118_Click()
Dim strWhere As String
If Me.sfrmCtrlName.Form.FilterOn Then
strWhere = Me.sfrmCtrlName.Form.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

 

[Wight Dave]

Hi Duane

Thanks for the reply. I tried using your code on the button on the main form
to refer to the subform and got a VB error message saying “Compile error.
Method or data member not found†around the statement .sfrmCtrlName (I’m
using Access 2000 in case that is relevant)

To keep things simple, lets forget about the subform..

I’ve set up a straightforward form which filters data using the Filter By
Selection button (with the funnel and yellow lightening icon). The filter
selects the data I want with no problems when I click on a field and press
the icon.

I’ve then added a button to the form with the following code from Allen
previously.

Private Sub Command38_Click()
Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

End Sub

With no filter applied, the code returns all the records I’m expecting.

When I apply a filter (by clicking on a field in the form and then clicking
on the funnel/lightening button), if I then run the code Access comes up with
a box saying ‘Enter Parameter Value’ and then the name of the underlying
query and the field I’m filtering on. If filtering on more than one field, it
asks for a parameter value for all the relevant fields.

The problem seems therefore to be that the code is not passing the correct
parameters to the query for some reason.

Wight Dave

 

[Duane Hookom]

When I stated "sfrmCtrlName" it meant you were supposed to substitute your
sfrmCtrlName. Do you have fields in the form's record source that have the
same name but from different tables? Is the record source of the form the
same as the report?

You should add a line of code to determine the value of the filter like:
Debug.Print "Form Filter: " & Me.sfrmCtrlName.Form.Filter

 

[Wight Dave]

Thanks for the ongoing support.

I've gone right back to basics with a new and very simple database and got
the code that Allen sent through working as I expected

ALLEN'S CODE

Private Sub Command38_Click()

I haven't yet managed to get Duane's code to determine the value of the
filter working though

DUANE'S CODE

I've added a button to the form with the following code attached. Since I've
done away with the subform, I've changed the statment
Me.sfrmCtrlName.Form.Filter to
Me.Form.Filter as below but nothing happens when I press the button.

Private Sub Command16_Click()
Debug.Print "Form Filter: " & Me.Form.Filter
End Sub

The form is named "Fruitandveg". Am I doing something stupid?

Wight Dave


--------------------

 

[Duane Hookom]

Try:
Private Sub Command16_Click()
If Me.Filter & "" <> "" AND Me.FilterOn = True Then
Debug.Print "Form Filter: " & Me.Filter
MsgBox "Form Filter: " & Me.Filter
Else
Debug.Print "No Filter Set"
MsgBox "No Filter Set"
End If
End Sub

 

[Wight Dave]

Thanks Duane. That worked for me.

I'll now take the two pieces of code and apply them to the database I need
them in.

Thanks for your help

Wight Dave