Option Groups to filter report

[gcarpenter]

I have a report with 2 option groups, I cannot get both of them to filter the data simataneously, they will each inititally filter the data, but when the opposite group it clicked, the previous filter data does not remain filtered. I have posted the code for the two option groups, I know I'm doing something wrong.

First option group code:
Private Sub Frame43_Click()
Select Case Me.Frame43.VALUE
Case 1
Me.Filter = "allowable_weight Like '263000'"
Me.FilterOn = True
Case 2
Me.Filter = "allowable_weight Like '268000'"
Me.FilterOn = True
Case 3
Me.Filter = "allowable_weight Like '286000'"
Me.FilterOn = True

End Select
If Me.Filter = "" Then
MsgBox "Plesae ensure railcar weight is choosen"
Else
DoCmd.OpenReport "eBill_Status", acViewReport, , Me.Filter

End If
End Sub

Second Option Group Code:
Private Sub Frame83_Click()
Select Case Me.Frame83.VALUE
Case 1
Me.Filter = "Origin = 'FORSTER'"
Me.FilterOn = True
Case 2
Me.Filter = "Origin = 'SPARTANBURG' Or Origin = 'GREER '"
Me.FilterOn = True

End Select
If Me.Filter = "" Then
MsgBox "Plesae ensure location is choosen"
Else
DoCmd.OpenReport "eBill_Status", acViewReport, , Me.Filter

End If
End Sub

 

[Pat Hartman]

Whenever you create controls on a form it is good practice to name them with meaningful names. No one wants to read code that references Frame83 or Text45. If you change the names after you have added event code, the code will be orphaned and you will have to modify the event procedure names manually to link them back up.

When I have multiple criteria, I prefer to add an additional button to "activate" the criteria. I also have a button to clear the filter. That simplifies the code and keeps the user from getting confused.

In each click event populate a public variable with the criteria for the filter. Then in the click event for the "apply filter" button, you would concatenate the variables and turn no the filter.

If sFilter1 & "" = "" Then
    If sFilter2 & "" = "" Then
        Msgbox "No filter has been set.",vbokOnly
        Exit Sub
    Else
        Me.Filter = sFilter2
    End If
Else
    Me.Filter = sFilter1 & " AND " & sFilter2
    Me.FilterOn = True
End If

I would also use a separate button to open the report.

And finally, LIKE is only used when you have a partial string and you are using wild characters to specify the missing part. Your string is complete and so you should use the = operator.

 

[gcarpenter]

I appreciate your help, I agree my naming convention needs work. However, I am unable to pass the variable to the report with this setup. If filters nothing.

 

[Mihail]

For sure, Pat's code is "in air".

Try this:

If sFilter1 & "" = "" Then
     If sFilter2 & "" = "" Then
         Msgbox "No filter has been set.",vbokOnly
         Me.FilterOn = False
Exit Sub
     Else
         Me.Filter = sFilter2
         Me.FilterOn = True
     End If
 Else
     Me.Filter = sFilter1 & " AND " & sFilter2
     Me.FilterOn = True
 End If

 

[gcarpenter]

I must be missing something, I collect the filter in with the first 2 option groups in the first message, that filter is not applying with the about code that is behind a command button event.

 

[Mihail]

Yes. You get it.
But, in my opinion, all the code should be under the button event.

Private Sub ButtonName_Click()
Dim strFilter1 As String
    Select Case Me.Frame43.VALUE
         Case 1
            strFilter1  = "allowable_weight Like '263000'"
        Case 2
            strFilter1  = "allowable_weight Like '268000'"
        Case 3
            strFilter1  = "allowable_weight Like '286000'"
        Case Else
            MsgBox "Plesae ensure railcar weight is choosen"
Exit Sub
    End Select

Dim strFilter2 As String
    Select Case Me.Frame83.VALUE
          Case 1
            Me.Filter = "Origin = 'FORSTER'"
          Case 2
            Me.Filter = "Origin = 'SPARTANBURG' Or Origin = 'GREER '"
       Case Else
            MsgBox "Plesae ensure location is choosen"
Exit Sub
     End Select
 
'Now, Pat's code
Dim Msg As vbMsgboxResults
  If strFilter1= "" Then
    If strFilter2 = "" Then
      Msg = Msgbox "No filter has been set.",vbokOnly
      Me.FilterOn = False
 Exit Sub
    Else
      Me.Filter = strFilter2
     End If
  Else
    Me.Filter = sFilter1 & " AND " & sFilter2
  End If

'Filter the form
  Me.FilterOn = True

'Open the report with the same filter
  DoCmd.OpenReport "eBill_Status", acViewReport, , Me.Filter
End Sub

Note please that this code is also "in air" (not tested) so it is possible to have some errors that should be fixed.