Filtering Multiple Option Groups in Form

**DBS1** · 02-06-2023, 01:29 PM

I have a form named frmPayments that includes a subform called frmPaymentsSubForm. The subform is populated by a query called qryPayments. The form has 3 Option Groups with radio buttons as follows: 1)optTypePmt has fields (with radio buttons) called Cash and Cred, 2) optPmtStatus has fields called Pd and Unpd, and 3) optInvSent has fields Y and N. All fields are Y/N fields in the table and query. I've created an On Click event procedure for each of the option groups with the code below, and clicking the radio button in each option group filters that set of data fine, but when I click a radio button in another option group, I lose the filtering on the first group. I'd like to be able to set the form so I can filter based on the criteria from each of the option groups, or have the flexibility to leave one or more unfiltered. I've found a couple of places online that has referenced this, but I haven't been able to figure this out and apply it to my form. Below is the coding I have for the option groups.

First Option Group
Private Sub optTypePmt

Dim strFilter As String

Select Case Me.[optTypePmt}

Case 1
Forms!frmPayments!frmPaymentsSubForm.Form.FilterOn = False

Case 2
strFilter = "[Cash] = -1"
Forms!frmPayments!frmPaymentsSubForm.Form.Filter = strFilter
Forms!frmPayments!frmPaymentsSubForm.Form.FilterOn = True

Case 3
strFilter = "[Cred] = -1"
Forms!frmPayments!frmPaymentsSubForm.Form.Filter = strFilter
Forms!frmPayments!frmPaymentsSubForm.Form.FilterOn = True

End Select

End Sub

Second Option Group
Private Sub optPmtStatus

Dim strFilter As String

Select Case Me.[optPmtStatus}

Case 1
Forms!frmPayments!frmPaymentsSubForm.Form.FilterOn = False

Case 2
strFilter = "[Pd] = -1"
Forms!frmPayments!frmPaymentsSubForm.Form.Filter = strFilter
Forms!frmPayments!frmPaymentsSubForm.Form.FilterOn = True

Case 3
strFilter = "[Unpd] = -1"
Forms!frmPayments!frmPaymentsSubForm.Form.Filter = strFilter
Forms!frmPayments!frmPaymentsSubForm.Form.FilterOn = True

End Select

End Sub

Third Option Group
Private Sub optInvSent

Dim strFilter As String

Select Case Me.[optInvSent}

Case 1
Forms!frmPayments!frmPaymentsSubForm.Form.FilterOn = False

Case 2
strFilter = "[Y] = -1"
Forms!frmPayments!frmPaymentsSubForm.Form.Filter = strFilter
Forms!frmPayments!frmPaymentsSubForm.Form.FilterOn = True

Case 3
strFilter = "[N] = -1"
Forms!frmPayments!frmPaymentsSubForm.Form.Filter = strFilter
Forms!frmPayments!frmPaymentsSubForm.Form.FilterOn = True

End Select

End Sub

**June7** · 02-06-2023, 02:05 PM

Have one procedure with all 3 Select Case structures to build strFilter:
strFilter = strFilter & "[Y] = -1 AND "

Then set the Filter and FilterOn properties outside the Case structures.

Code:

Forms!frmPayments!frmPaymentsSubForm.Form.FilterOn = False

'each Select Case structure - Case 1 would not do anything


'now set the form properties
If strFilter <> "" Then
Forms!frmPayments!frmPaymentsSubForm.Form.Filter = Left(strFilter, Len(strFilter) - 5)
Forms!frmPayments!frmPaymentsSubForm.Form.FilterOn = True
End If

For more guidance on building filter criteria string, review http://allenbrowne.com/ser-62.html

Having pairs of fields like Cash and Credit, Pd and Unpd, Y and N is redundant. Assuming each pair is mutually exclusive (cannot be both Cash and Credit), a single field is better: IsCredit, IsPaid, IsY. These can be Yes/No fields or text or number. More on this topic from Allen Browne http://allenbrowne.com/NoYesNo.html

**DBS1** · 02-06-2023, 03:04 PM

June7, thanks for the quick reply. Your comments on the field structure are duly noted and I'll keep it in mind in the future. I'm not very fluent in VBA, but know just enough to get by through trial and error (most of the time). I'm not sure exactly where this code goes though. Once I figure that out, I'll try it.

**June7** · 02-06-2023, 03:47 PM

Have code in button click event - did you review Allen's tutorial?

Or have code in a procedure that can be called by each control's AfterUpdate event.

**DBS1** · 02-06-2023, 03:56 PM

Thanks. I'll try that. I did look through the Allen Brown tutorial. I understand most of what I read, however where I'm having difficulty is getting the three option groups to filter together.

**June7** · 02-06-2023, 04:01 PM

They work together by having code to build strFilter in one procedure with whatever controls are used to select criteria: combobox, listbox, textbox, option group - doesn't matter.

**CarlettoFed** · 02-06-2023, 10:00 PM

Code:

Private Sub optTypePmt_AfterUpdate()DataFilter End Sub

Private Sub optPmtStatus_AfterUpdate()DataFilter End Sub

Private Sub optInvSent_AfterUpdate()DataFilter End Sub

Private Sub DataFilter()
Dim strFilter As StringIf Me.optTypePmt > 1 Then strFilter = strFilter & IIf(Me.optTypePmt = 2, " AND [Cash]", "[Cred]") & " = True"
    If Me.optPmtStatus > 1 Then strFilter = strFilter & IIf(Me.optPmtStatus = 2, " AND [Pd]", "[Unpd]") & " = True"
    If Me.optInvSent > 1 Then strFilter = strFilter & IIf(Me.optInvSent = 2, " AND [Y]", "[N]") & " = True"
    If strFilter = "" Then
Me.frmPaymentsSubForm.Form.Filter = ""
        Me.frmPaymentsSubForm.Form.FilterOn = Filter
     Else
Me.frmPaymentsSubForm.Form.Filter = Mid(strFilter, 6)
        Me.frmPaymentsSubForm.Form.FilterOn = True
     End If
 End Sub

**DBS1** · 02-08-2023, 11:09 AM

Where would I place this code? Obviously outside of the Option Groups since I have 3 of them and this needs to be inside of one procedure?

Originally Posted by June7

Have one procedure with all 3 Select Case structures to build strFilter:
strFilter = strFilter & "[Y] = -1 AND "

This goes in the Click event procedure for the button used to filter, correct?

Then set the Filter and FilterOn properties outside the Case structures.

Code:

Forms!frmPayments!frmPaymentsSubForm.Form.FilterOn = False

'each Select Case structure - Case 1 would not do anything


'now set the form properties
If strFilter <> "" Then
Forms!frmPayments!frmPaymentsSubForm.Form.Filter = Left(strFilter, Len(strFilter) - 5)
Forms!frmPayments!frmPaymentsSubForm.Form.FilterOn = True
End If

**DBS1** · 02-08-2023, 11:18 AM

Hi CarlettoFed. Thanks for your reply. This is a little different from what June7 suggested, but I suppose there is more than one way to skin a cat. I'll try both to see how they work, but have a few questions about the code. Is the DataFilter referenced in each of the Option Group events the name of the command button used to filter? I have a button called cmdFilter, so would I use that instead of DataFilter?

Originally Posted by CarlettoFed

Code:

Private Sub optTypePmt_AfterUpdate()DataFilter End Sub

Private Sub optPmtStatus_AfterUpdate()DataFilter End Sub

Private Sub optInvSent_AfterUpdate()DataFilter
 End Sub

If DataFilter is the name of the filter button as I assume above, then I'm assuming this is the code that goes into the event procedure, correct?

Code:

Private Sub DataFilter()
Dim strFilter As StringIf Me.optTypePmt > 1 Then strFilter = strFilter & IIf(Me.optTypePmt = 2, " AND [Cash]", "[Cred]") & " = True"
    If Me.optPmtStatus > 1 Then strFilter = strFilter & IIf(Me.optPmtStatus = 2, " AND [Pd]", "[Unpd]") & " = True"
    If Me.optInvSent > 1 Then strFilter = strFilter & IIf(Me.optInvSent = 2, " AND [Y]", "[N]") & " = True"
    If strFilter = "" Then
Me.frmPaymentsSubForm.Form.Filter = ""
        Me.frmPaymentsSubForm.Form.FilterOn = Filter
     Else
Me.frmPaymentsSubForm.Form.Filter = Mid(strFilter, 6)
        Me.frmPaymentsSubForm.Form.FilterOn = True
     End If
 End Sub

**Micron** · 02-08-2023, 11:48 AM

DataFilter is a simplified call to the procedure with that name:
Private Sub DataFilter()
You don't need to use the Call statement unless you want to return a value to something in that line of code. Then it's a different syntax.

**DBS1** · 02-08-2023, 01:46 PM

Thanks Micron. That helps. I've got it partially working now, but it's not recognizing values 3 in each group and I forgot to include value 4 in optTypePmt, which is [ATM]. It looks like I need to extend the IIf statement to include values 3 for all and value 4 for optTypePmt. Is that correct? Once I get this figured out I think it will be working like it should. This is where I need help. What I have now is

If Me.optTypePmt > 1 then strFilter = strFilter & IIf (Me.optTypePmt = 2, " AND [Cash]", "[Cred]") & " = True"

How would I add the additional part of the code to inlcude [ATM]? Would that be an additional IIf statement such as IIf(Me.optTypePmt = 3?

**June7** · 02-08-2023, 02:08 PM

Could have nested IIf() expression or use Switch(). Another trick to learn is to wrap code block with With Me and don't have to repeat all those Me references. Carletto's code is basically what I suggested but think has an error in the concatenation of " AND ". Whether " AND " is concatenated at beginning or end doesn't matter as long as extraneous " AND " is dealt with. Also, set FilterOn to True or False.

Code:

With Me
If .optTypePmt > 1 Then strFilter = strFilter & " AND " & Switch(.optTypePmt = 2, "[Cash]", .optTypePmt = 3, "[Cred]", .optTypePmt = 4, "[ATM]") & "=True"
If .optPmtStatus > 1 Then strFilter = strFilter & " AND " & IIf(.optPmtStatus = 2, "[Pd]", "[Unpd]") & "=True"
If .optInvSent > 1 Then strFilter = strFilter & " AND " & IIf(.optInvSent = 2, "[Y]", "[N]") & "=True"
If strFilter = "" Then
    .frmPaymentsSubForm.Form.Filter = ""
    .frmPaymentsSubForm.Form.FilterOn = False
Else
    .frmPaymentsSubForm.Form.Filter = Mid(strFilter, 6)
    .frmPaymentsSubForm.Form.FilterOn = True
End If
End With

**DBS1** · 02-08-2023, 03:05 PM

June7 thank you very much. That code did the trick. No way I could have figured this out without the help of you, Catletto, and Micron. I appreciate your patience and quick accurate responses.

Filtering Multiple Option Groups in Form

Thread Tools

Filtering Multiple Option Groups in Form

Similar Threads

Filter with multiple option groups

How do you format option buttons in option groups?

Using Option Groups Where First Option is No Filter

Form option groups and display styles

Question about Option Groups

Posting Permissions