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

A

alan2013

Registered User.
Local time
Today, 10:28
Joined
Mar 24, 2013
Messages
75
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 :

Code: 
Private Sub optTC_AfterUpdate()
    FilterSubform
End Sub

Private Sub optIP_AfterUpdate()
    FilterSubform
End Sub


And the VBA for the called 'FilterSubform' is :


Code: 
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 ?
 
you can try this as replacement to your sub (not not tested):
Code: 
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
 
alan2013 said:
The VBA on the After Update Event for the option buttons is currently as follows :
Click to expand...
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:

Code: 
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
 
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?
 
If TP and IP are the only possible options, why do you need a where clause if both are ticked?
 
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
 
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!
 
Last edited:
CJ_London said:
on filtering which wasn't what the OP asked
Click to expand...
alan2013 said:
I want to use those Option Buttons to filter the records in the subform (which has a 'continuous' layout).
Click to expand...

this is the correction:
Code: 
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
 
Last edited:
arnelgp said:
this is the correction:
Code: 
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
Click to expand...

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. "
 
alan2013 said:
atabase can't find the field 'sfrm_BlockingAndSequencing' referred to in your expression. "
Click to expand...
what is the name of your subform? then substitute your subform name on the code
 
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
 
Last edited:
OK OP did say

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

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.
 
what have you done so far?
did you change it to:

Set subfrm = Me("objSubform").Form
 
CJ_London said:
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.
Click to expand...
it has already a Link Master/Child fields, so less data is fetched.
 
arnelgp said:
what have you done so far?
did you change it to:

Set subfrm = Me("objSubform").Form
Click to expand...
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.
 
Last edited:
Oh, my goodness, it's now working.

The code is now :

Code: 
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
 
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
 
You must log in or register to reply here.

Similar threads

E
Solved Add and Edit VBA for form and relative subforms
Replies
12
Views
160
theDBguy
theDBguy
mloucel
Solved SubForm [Continuous Form] not updating data.
Replies
18
Views
916
mloucel
mloucel
R
  • Question Question
Using vba to select a particular record in a combo box is not working properly
Replies
4
Views
285
cheekybuddha
cheekybuddha
N
VBA for populating a subform from data in a listbox
Replies
12
Views
480
June7
June7
N
Solved How to force MS Access display a primary key on the forms after entering data in the first field
Replies
6
Views
238
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom