Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms / May 2008

Tip: Looking for answers? Try searching our database.

Applying a Filter Using Multiple Option Groups

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jeff Garrison - 09 May 2008 18:29 GMT
Hell all -

I have a quick quesiton...(first off, I'n using Access 2007 FE and SQL
BE)...on a form, I would like to use 2 Option Groups to apply filters to the
form.  Details below...

Option Group 1

All Records        (No Filter)
Active Only        (Filter Only Active Records)
Inactive Only       (Filter Only Inactive Records)

Option Group 2

Company Employees
Temp Employees
All Employees

I'd like to do a filter with a combination of the 2 Option Groups...being
able to select options from both groups and filter the records based on the
Option selected.

Any help would be musch appreciated.

Thanks.

JeffG
George Nicholson - 09 May 2008 19:31 GMT
The following would all go into your form module. Add error handling, adjust
control/field names, etc., as appropriate for your reality.

'***** start aircode **********
Option Explicit
'(Top-of-form module) Module-level variables:
Private mstrRecordFilter as String
Private mstrEmpFilter as String

Private Sub frmOption1_AfterUpdate()
   Select Case Me.frmOption1
       Case 1
           'All records
           mstrRecordFilter = ""
       Case 2
           mstrRecordFilter = "[Active] = True"
       Case 3
           mstrRecordFilter = "[Active] = False"
   End Select
   Call ApplyFormFilter
End Sub

Private Sub frmOption2_AfterUpdate()
   Select Case Me.frmOption2
       Case 1
           mstrEmpFilter = "[EmpType] = 'Company'"
       Case 2
           mstrEmpFilter = "[EmpType] = 'Temp'"
       Case 3
           ' All employees
           mstrEmpFilter = ""
   End Select
   Call ApplyFormFilter
End Sub

Private Sub ApplyFormFilter()
       Dim strCombinedFilter as String

       ' Combine the 2 filters
       Select Case True
           ' Only one of these 3 things will execute: the 1st True
condition encountered
           Case mstrRecordFilter = ""
               strCombinedFilter = mstrEmpFilter
           Case mstrEmpFilter = ""
               strCombinedFilter = mstrRecordFilter
           Case Else
               ' User wants to filter on multiple fields
               strCombinedFilter = mstrRecordFilter & " AND " &
mstrEmpFilter
       End Select

       'Apply the combined filter
       Me.Filter = strCombinedFilter
       If strCombinedFilter = "" Then
           Me.FilterOn = False
       Else
           Me.FilterOn = True
       End If
End Sub
'******* end aircode **************************

Signature

HTH,
George

> Hell all -
>
[quoted text clipped - 23 lines]
>
> JeffG
Jeff Garrison - 09 May 2008 19:57 GMT
George -

Works like a charm.....THANKS!

Jeff

> The following would all go into your form module. Add error handling,
> adjust control/field names, etc., as appropriate for your reality.
[quoted text clipped - 85 lines]
>>
>> JeffG
Jeff Garrison - 09 May 2008 20:09 GMT
In a related question (sort of)...

How do you set the default option when the form is opened?

I used to remember how to do that, but it's been many years...

> The following would all go into your form module. Add error handling,
> adjust control/field names, etc., as appropriate for your reality.
[quoted text clipped - 85 lines]
>>
>> JeffG
George Nicholson - 09 May 2008 20:47 GMT
>How do you set the default option when the form is opened?

the default value of the OptionFrame can be preset in design view.
(Or Me.frmOption.DefaultValue = 1 I suppose)

If you want to select something other than the default when the form opens:

in Form_Open:

Me.frmOption1 = 1
Me.frmOption2 = 3
' If you also want the AfterUpdate code to run (and apply the filter) based
on those settings:
frmOption1_AfterUpdate
frmOption2_AfterUpdate

Signature

HTH,
George

> In a related question (sort of)...
>
[quoted text clipped - 91 lines]
>>>
>>> JeffG
Jeff Garrison - 09 May 2008 21:08 GMT
Thank.....I knew it had to be something easy...

> >How do you set the default option when the form is opened?
>
[quoted text clipped - 108 lines]
>>>>
>>>> JeffG
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.