Solved Option Group filter months

[Nightfire22255]

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.

 

[arnelgp]

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]

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]

add a function to your Form:

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

=fncFilter()

 

[Uncle Gizmo]

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...