Filter a subform by a button/toggle on the main form

[James Frater]

Hello Gang,

I was wondering if someone would be able to help me out.

In the main form I have an option group of toggle buttons that make the case
criteria for a report, the criteria is loaded to the report through the
on_click event of a seperate button.

Would there be anyway that when I click on one of the toggle buttons in the
option group that it could also filter a subform (based on the same query as
the report) in my main form?

I've tried a few things however no luck, so if anyone has any ideas please
give me a shout.

Regards

JAMES

 

[strive4peace]

Hi James,

of course <smile>

once you build the filter for the subform, to apply it:

'~~~~~~~~~~~~~~~~
with me.subform_controlname.form
.filter = strFilter
.FilterOn = true
end with
'~~~~~~~~~~~~~~~~

WHERE
strFilter is the string variable containing the subform filter


.... I am assuming you are talking about a subFORM and not a subREPORT.
If you really meant to say subreport, then you can save the filter
before you run the report.

put this function in a general module:

'~~~~~~~~~~~~~~~~
Sub Report_SetReportFilter(pReportName as string, pFilter as string)
'PARAMETERS
' pReportName is the name of the report
' pFilter is the filter to apply

Dim rpt As Report
DoCmd.OpenReport pReportName, acViewDesign
Set rpt = Reports(pReportName)
rpt.Filter = pFilter
rpt.FilterOn = true
DoCmd.Save acReport, pReportName
DoCmd.Close acReport, pReportName
Set rpt = Nothing
End Sub
'~~~~~~~~~~~~~~~~

where
pReportName = name of report
pFilter = filter string
******************************

before you render the report, send the report name and filter string to
the SetReportFilter sub


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

 

[James Frater]

Crystal,

Hope you're well, and thanks for your quick response.

I'm afraid I'm still getting nothing.

I'm concerned this is something to do with that I'm using toggle buttons as
oppose to a regular cmd button? I've added in a private sub _click()
proceedure so do you think that's what's messing this up.

many thanks

JAMES

 

[strive4peace]

Hi James,

using a toggle button should be ok -- you can trigger an event in a
variety of ways

Why are you using toggle buttons instead of option buttons? I am
assuming since you said they are in an option frame that you can only
select one of them. I would be inclined to put the code on the
AfterUpdate event of the option frame.

If you are still having problems, please post the code that you are
using, thank you

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

 

[James Frater]

Crystal,

The answer was so obvious, of course it should go on the afterupdate! How
stupid of me.

The next time you see me post on here, reply with "James have you thought
this through?"

Many thanks

JAMES

 

[strive4peace]

you're welcome, James happy to help

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*