Solved Option Group filter months (1 Viewer)

Nightfire22255

New member
Local time
Today, 04:35
Joined
Dec 21, 2020
Messages
6
Hello all,

I need some guidance on filtering a subform. I am still a novice at Access, so please bare with me.

Basically, I want to use an Option Group of 12 choices where each choice is a month (January, February, etc.). When I choose what month, it will filter my subform to those month's results only.

To specify the year I want to filter, will be done by a combo box. When I click the drop down, it will show all the years that are within my current records.

The Option Group and the Combo box should work in conjunction together. So if I choose January from the option box and the year 2019 from the combo box, it will only show me those results from Jan 2019 in my subform.

I'm still learning VBA, so please go easy on me. Again I just need some guidance on the right direction.

From the screenshot, I'm filtering just from one date in the record.
Access.png
 

arnelgp

error reading drive A:
Local time
Today, 22:35
Joined
May 7, 2009
Messages
11,536
if you used the Wizard to build your option group, the default values (1-12) will be assigned.
use the Change Event of the Option Group:

Private Sub Frame0_AfterUpdate()
With Me
.Filter = "Month([DateField]) = " & Me!Frame0
.FilterOn = True
End With
End Sub
 

Nightfire22255

New member
Local time
Today, 04:35
Joined
Dec 21, 2020
Messages
6
if you used the Wizard to build your option group, the default values (1-12) will be assigned.
use the Change Event of the Option Group:

Private Sub Frame0_AfterUpdate()
With Me
.Filter = "Month([DateField]) = " & Me!Frame0
.FilterOn = True
End With
End Sub

Thanks for responding man.

I tried your code out on a regular form and it works!

I figured out how to use it in a subform as well.

Care to give me direction on how to use the combo box to filter the year?

Thanks again.
 

arnelgp

error reading drive A:
Local time
Today, 22:35
Joined
May 7, 2009
Messages
11,536
add a function to your Form:

Code:
Public Function fncFilter()

    Dim strFilter As String

    If Me![FrameName] > 0 Then
        strFilter = "Month([DateFieldName]) = " & Me![FrameName] & " And "
    End If

    If Len(Me![YearTextboxName] & vbNullString) > 0 Then
        strFilter = strFilter & "[YearField] = " & Me![YearTextboxName]
    End If

    If Len(strFilter) > 0 Then
        Me.Filter = strFilter
        Me.FilterOn = True
    Else
        Me.Filter = vbNullString
        Me.FilterOn = False
    End If

End Function
remove all code from your "Frame Control" and "Year Textbox", instead
type this on their After Update

Code:
=fncFilter()
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:35
Joined
Jul 9, 2003
Messages
12,458
Please find attached sample database which demonstrates one technique you can use to do this. Basically you construct an SQL Statement and pass it into the sub-form record source...
 

Attachments

  • Option Group filter months_1a.zip
    350.5 KB · Views: 24

Users who are viewing this thread

Top Bottom