Solved Filtering (using VBA) a subform based on Option Buttons on main form

[alan2013]

I wonder whether any guidance can be offered up re the following, please.

I have a tab control set within a main form, and on the one tab that I've created so far (to introduce others later), I have a subform.

Also on the main form (within the Form Header), I have a couple so far (to introduce others later) of Option Buttons, named optTC and optIP. Not an Option Group, mind you; Option Buttons that are independent of each other. So it could be that : neither are ticked; one or other is ticked; or both are ticked. I want to use those Option Buttons to filter the records in the subform (which has a 'continuous' layout).

The main form and subform are linked (unbound date selector on main form, and a 'PlannedStartDate' field on the subform).

The VBA on the After Update Event for the option buttons is currently as follows :

Private Sub optTC_AfterUpdate()
    FilterSubform
End Sub

Private Sub optIP_AfterUpdate()
    FilterSubform
End Sub

And the VBA for the called 'FilterSubform' is :

Private Sub FilterSubform()
    Dim strSQLSourceQuery   As String
    Dim strSQLWhere      As String
    Dim strSQL          As String
    Dim strJoin         As String
  
    Select Case Me.TabCtrl1.Value
        'BlockingAndSequencing tab'
        Case Me.pageBlockingAndSequencing.PageIndex
            strSubformName = "sfrmDay_BlockingAndSequencing"
            strSQLSourceQuery = "SELECT * FROM qry_sfrmDay_BlockingAndSequencing "
            
        'Other tabs to be introduced later
            
    End Select
            
    strJoin = "OR"
    strSQLWhere = ""

    'optTC
    If optTC = -1 Then 'TC is ticked
        strSQLWhere = "WHERE"
        strSQLWhere = strSQLWhere & " [AOStatus] = 'TC' "
    End If

    'optIP
    If optIP = -1 Then
        If Len(strSQLWhere) = 0 Then 'TC is not ticked
            strSQLWhere = "WHERE"
            strSQLWhere = strSQLWhere & " [AOStatus] = 'IP' "
        Else 'TC & IP are both ticked
            strSQLWhere = strSQLWhere & strJoin & " [AOStatus] = 'IP' "
        End If
    End If
    
    strSQL = strSQLSourceQuery & strSQLWhere

    
    Me.objSubform.Form.RecordSource = strSQL
    Me.objSubform.LinkMasterFields = "unboundDateSelector"
    Me.objSubform.LinkChildFields = "PlannedStartDate"
    Me.objSubform.SourceObject = strSubformName
    Me.objSubform.Top = Me.lblAnchor.Top
    Me.objSubform.Left = Me.lblAnchor.Left
    Me.objSubform.Visible = True

    [Forms]![frmDayToDay]![objSubform].Requery

End Sub

There are no criteria at all within the source Query for the subform. The Query just brings together various fields from various Tables. Included amongst the fields is a Status field. (TC and IP are the two possible options for Status). I'm trying to filter the subform according to what Option Buttons are and are not ticked, but can't get it to work.

Can anyone tell me where I'm going wrong, please ?

 

[arnelgp]

you can try this as replacement to your sub (not not tested):

Private Sub FilterSubform()
  
    Dim subfrm As Form
    Dim strFilter As String
    
    Set SubForm = Me("sfrmDay_BlockingAndSequencing").Form
    
    If optTC Then
        strFilter = strFilter & " Or [AOStatus] = 'TC'"
    End If
    If optIP = -1 Then
        strFilter = strFilter & " Or [AOStatus] = 'IP'"
    End If
    
    If Len(strFilter) Then
        strFilter = Mid$(strFilter, 5)
        With subfrm
            .Filter = strFilter
            .FilterOn = True
        End If
    
    Else
        subfrm.FilterOn = False
    End If

End Sub

 

[cheekybuddha]

The VBA on the After Update Event for the option buttons is currently as follows :

Don't use the AfterUpdate of the individual option buttons, use the value of the option frame, which takes the value of the selected option.

You will need to adjust the names of the controls to what you have:

Private Sub fraOptions_AfterUpdate()
' This assumes that the name of the frame control
' surrounding the option buttons is "fraOptions"

  FilterSubForm
 
End Sub

Private Sub FilterSubform()
    Dim strSQLSourceQuery   As String
    Dim strSQLWhere      As String
    Dim strSQL          As String
    Dim strJoin         As String
 
    Select Case Me.TabCtrl1.Value
        'BlockingAndSequencing tab'
        Case Me.pageBlockingAndSequencing.PageIndex
            strSubformName = "sfrmDay_BlockingAndSequencing"
            strSQLSourceQuery = "SELECT * FROM qry_sfrmDay_BlockingAndSequencing "
            
        'Other tabs to be introduced later
            
    End Select
            
    strJoin = "OR"
    strSQLWhere = ""
    
    Select Case Me.fraOptions
    Case Me.optTC.OptionValue
        strSQLWhere = strSQLWhere & " [AOStatus] = 'TC' "
    Case Me.optIP.OptionValue
        strSQLWhere = strSQLWhere & IIf(Len(strWhere), strJoin, vbNullString) & " [AOStatus] = 'IP' "
    End Select
    strSQL = strSQLSourceQuery & " WHERE " & strSQLWhere
    
    Me.objSubform.Form.RecordSource = strSQL
    
' ///////
' Do you need the following? Presumably these are already set
    Me.objSubform.LinkMasterFields = "unboundDateSelector"
    Me.objSubform.LinkChildFields = "PlannedStartDate"
    Me.objSubform.SourceObject = strSubformName
    Me.objSubform.Top = Me.lblAnchor.Top
    Me.objSubform.Left = Me.lblAnchor.Left
    Me.objSubform.Visible = True
' ///////

' This is probably also superfluous - setting the RecordSource causes an implicit Requery
    [Forms]![frmDayToDay]![objSubform].Requery

End Sub

 

[cheekybuddha]

Re-reading your code it looks more like you need checkboxes rather than an Option Group.

Have you just added radio buttons (option buttons) without an option frame to act like checkboxes?

 

[CJ_London]

If TP and IP are the only possible options, why do you need a where clause if both are ticked?

 

[CJ_London]

it looks more like you need checkboxes rather than an Option Group.

OP was clear

couple so far (to introduce others later) of Option Buttons, named optTC and optIP. Not an Option Group, mind you;

Think using ‘buttons’ is confusing but did say ticked or unticked

 

[cheekybuddha]

OP was clear

Not an Option Group, mind you;

I'll just go and get some more coffee

Sorry for the noise

 

[CJ_London]

It's happened to me as well - Arnel's code contains an error as well and is based on filtering which wasn't what the OP asked

But hey ho - it's the weekend

 

[Uncle Gizmo]

If the subform has to display many records, then a better route would be to avoid filtering and provide an SQL Statement for the sub-form record source....

Oops! ON Second look, it appears you are already using this method!

 

[arnelgp]

on filtering which wasn't what the OP asked

I want to use those Option Buttons to filter the records in the subform (which has a 'continuous' layout).

this is the correction:

Private Sub FilterSubform()
  
    Dim subfrm As Form
    Dim strFilter As String
    
    Set subfrm = Me("sfrmDay_BlockingAndSequencing").Form
    
    If optTC Then
        strFilter = strFilter & " Or [AOStatus] = 'TC'"
    End If
    If optIP = -1 Then
        strFilter = strFilter & " Or [AOStatus] = 'IP'"
    End If
    
    If Len(strFilter) Then
        strFilter = Mid$(strFilter, 5)
        With subfrm
            .Filter = strFilter
            .FilterOn = True
        End With
    
    Else
        subfrm.FilterOn = False
    End If

End Sub

 

[alan2013]

this is the correction:

Private Sub FilterSubform()
 
    Dim subfrm As Form
    Dim strFilter As String
   
    Set subfrm = Me("sfrmDay_BlockingAndSequencing").Form
   
    If optTC Then
        strFilter = strFilter & " Or [AOStatus] = 'TC'"
    End If
    If optIP = -1 Then
        strFilter = strFilter & " Or [AOStatus] = 'IP'"
    End If
   
    If Len(strFilter) Then
        strFilter = Mid$(strFilter, 5)
        With subfrm
            .Filter = strFilter
            .FilterOn = True
        End With
   
    Else
        subfrm.FilterOn = False
    End If

End Sub

On the line Set subfrm = Me("sfrmDay_BlockingAndSequencing").Form, I'm currently getting run-time error 2465. "Database can't find the field 'sfrm_BlockingAndSequencing' referred to in your expression. "

 

[arnelgp]

atabase can't find the field 'sfrm_BlockingAndSequencing' referred to in your expression. "

what is the name of your subform? then substitute your subform name on the code

 

[alan2013]

what is the name of your subform? then substitute your subform name on the code

Yeh, the subform name is sfrmDay_BlockingAndSequencing

 

[alan2013]

Correction : My subform Name will be objSubform, and the Source Object will be sfrmDay_BlockingAndSequencing

I'll try objSubform


Using
Set subfrm = Me("sfrmDay_BlockingAndSequencing").Form
got round runtime error 2465.

Now I'm getting runtime error 5 (Invalid procedure call or argument) on the line .Filter = strFilter

 

[CJ_London]

OK OP did say

I want to use those Option Buttons to filter the records in the subform (which has a 'continuous' layout).

But the code was all about modifying the recordsource which is a more efficient method for 'filtering' as there is less data to bring across. Filtering requires all data to be brought across for subsequent filtering. Doesn't really matter on small datasets but can have quite an impact on performance for large datasets.

 

[arnelgp]

what have you done so far?
did you change it to:

Set subfrm = Me("objSubform").Form

 

[arnelgp]

But the code was all about modifying the recordsource which is a more efficient method for 'filtering' as there is less data to bring across.

it has already a Link Master/Child fields, so less data is fetched.

 

[alan2013]

what have you done so far?
did you change it to:

Set subfrm = Me("objSubform").Form

Yes, and that got round runtime error 2465.
After that, I got a runtime error 5 (Invalid procedure call or argument) on the line .Filter = strFilter I had to go off and do something, and only coming back to it now,...so I haven't figured that one out yet.

 

[alan2013]

Oh, my goodness, it's now working.

The code is now :

Private Sub FilterSubform()
    Dim strSubformSourceObject  As String
    Dim strSubformName As Form
    Dim strFilter As String
  
    Select Case Me.TabCtrl1.Value
        'Blocking & sequencing
        Case Me.pageBlockingAndSequencing.PageIndex
            strSubformSourceObject = "sfrmDay_BlockingAndSequencing"
            Set strSubformName = Me("objSubform").Form           
    End Select
    
    'optTC
    If optTC = -1 Then
        strFilter = strFilter & " Or [AOStatus] = 'TC'"
    End If

    'optIP
    If optIP = -1 Then
        strFilter = strFilter & " Or [AOStatus] = 'IP'"
    End If
    
    If Len(strFilter) Then
        strFilter = Mid$(strFilter, 5)
        With strSubformName
            .Filter = strFilter
            .FilterOn = True
        End With
    Else
        strSubformName.FilterOn = False
    End If
End Sub

Thanks-ever-so-much, to arnelgp, CJ_London, Uncle Gizmo, cheekybuddha

 

[CJ_London]

the link properties are still a filter - as is the where parameter of openform.

Either way - the OP s happy with your solution so time to stop this to and fro