Option Group to filter a form

[EonDel]

Hello,

I am trying to set up an option group (StatusOptions) in a form (frmHistory) that acts as a filter.

The form is based on a query (qryHistory) and the field that contains the data to be filtered is (Status), there are 3 possible options in this field; "Open - Normal", "Open - Urgent", and "Resolved"


For the Option Group in the form the options I want are:
Option 1 Is unfiltered; (default option)
Option 2 Is "Open - Normal" & "Open - Urgent";
Option 3 Is "Resolved

Any help with the vba expression would be appreciated

 

[John Big Booty]

In the On Click Event of your option group you could have some code along the lines of;

Dim strFilter As String

Select Case [YourOptionGroupName]

Case 1
     Forms!YourFormName.Form.FilterOn = False

Case 2
     strFilter = "[YourFieldName] = 'Open - Normal' Or [YourFieldName] = 'Open - Urgent'"
     Forms!YourFormName.Form.Filter = strFilter
     Forms!YourFormName.Form.FilterOn = True

Case 3
     strFilter = "[YourFieldName] = 'Resolved'"
     Forms!YourFormName.Form.Filter = strFilter
     Forms!YourFormName.Form.FilterOn = True

End Select

 

[John Big Booty]

Here's a small sample that use the technique mentioned above.

 

[Steve C]

Thank you very much JBB, I'll play with what you've given me and report back how it goes.

 

[EonDel]

Thanks so much

 

[Steve C]

Your method gave me some really good ideas - thank you very much indeed.

 

[John Big Booty]

Glad I could help you

 

[ginabenina]

Trying to achieve a similar outcome using the option group to filter between active/inactive entries with the form loading showing only active entries. The field is based of a true/false

I keep getting a data type mismatch error. Any help would be greatly appreciated.

Private Sub fraActive_AfterUpdate()
Dim strFilter As String
Select Case Me.fraActive
    Case 1
        strFilter = "[Active]='True'"
        Forms!NewUser.Form.Filter = strFilter
        Forms!NewUser.Form.FilterOn = True
    Case 2
        strFilter = "[Active]='False'"
        Forms!NewUser.Form.Filter = strFilter
        Forms!NewUser.Form.FilterOn = True
        
End Select
End Sub

:banghead:

 

[JHB]

Try the below:

Private Sub fraActive_AfterUpdate()
Dim strFilter As String
Select Case Me.fraActive
    Case 1
        strFilter = "[Active]=-1"
        Forms!NewUser.Form.Filter = strFilter
        Forms!NewUser.Form.FilterOn = True
    Case 2
        strFilter = "[Active]=0"
        Forms!NewUser.Form.Filter = strFilter
        Forms!NewUser.Form.FilterOn = True
        
End Select
End Sub

 

[ginabenina]

Still nothing. Should I have changed the option values under properties to -1 & 0?

 

[ginabenina]

OK, it's working now. I had incorrect syntax prior. The only issue now is the form won't load with the active filter.

the form properties show "[Active]= -1" but it's not working. Any ideas?

 

[ginabenina]

OK everything is almost working. The only problem is when the form loads the "Active" option group button isn't selected even though the filter is working.

the default value of the option group frame is set at -1.

 

[ginabenina]

Everything is working. I accidentally had the default value set to -1 for the option group instead of 1.

Thank you very much!

 

[deltekkie]

I tried using a similar method to your to filter records in a time log. There are 4 separate areas of the log which people use to log different activities. So I use the filter so they only see and enter data in the area they are concerned with. The problem I am having is that Access keeps writing the "case" number into the record of the column I am trying to filter. Any ideas why this is happening?


Here is the VB code:

Private Sub LogTypeSelector_Click()

Dim strFilterSQL As String

'Set default record source of form
Const strSQL = "SELECT tblLogs.ID,tblLogs.LogType,tblLogs.EmpFirstName,tblLogs.EmpLastName,tblLogs.PartType,tblLogs.PartNumber,tblLogs.Vendor,tblLogs.ControlNumber,tblLogs.StartTime,tblLogs.StopTime,tblLogs.TimeSpent,tblLogs.txtNotes FROM tblLogs"

Select Case Me!LogTypeSelector
'Filter record source dependant on option checked
Case 1
strFilterSQL = strSQL & ";"
Case 2
strFilterSQL = strSQL & " Where [LogType] = 'Setup';"
Case 3
strFilterSQL = strSQL & " Where [LogType] = 'Dimensions';"
Case 4
strFilterSQL = strSQL & " Where [LogType] = 'Function Test';"
Case 5
strFilterSQL = strSQL & " Where [LogType] = 'Data Collection';"
'If filter applied with no option selected use default record source
Case Else
Forms!YourFormName.Form.FilterOn = False
strFilterSQL = strSQL & ";"
End Select

' Set record source with filtered SQL
Me.RecordSource = strFilterSQL
Me.Requery

End Sub

This is attached to an options group with 5 options. "All Logs", "Setup", "Dimensions", "Function Test", and "Data Collection"

If I click back and forth between each of the buttons several times I end up with several row with numbers in them instead of the words that are listed in the option group to match against.

Hope you can help. I will continue to search for an answer.

 

[ginabenina]

So if you select "Setup" it's displaying "Case 2" instead?

Maybe try to update the row source and bound column.

 

[moke123]

Use an unbound option group.
It's saving the value because you have it bound to a field.

 

[deltekkie]

Thank you very much. That was the answer. It was indeed bound to the LogType. When I removed it, everything is working as intended now.

Thanks again!!!