Making option boxes relient on each other.

[JackieSmith]

I have 2 option boxes with 3 radio buttons on each of them. They are deigned to fitler my subform depending on the selections.

They both work great independently but neither work together. The first option box is to select view only pass, fail or both. The second is to select male, female or both.

If I chose males that passed it only chooses the second option to displaying ignoring the other filter (this data is already filtered by drop downs) so I only see males. I want to make them dependent on each other so it displays exactly what both are asking.

The code I have for these I got help with elsewhere on here (some one else asked) and is as follows:

Private Sub Frame29_AfterUp date()
Select Case Me!Frame29
Case 1
Me![front screen data subform].Form.Filter = "[Pass or Fail] = 'Pass'"
Me![front screen data subform].Form.FilterOn = True
MsgBox "You have requested to view students who have Passed only"
Case 2
Me![front screen data subform].Form.Filter = "[Pass or Fail] = 'Fail'"
Me![front screen data subform].Form.FilterOn = True
MsgBox "You have requested to view students who have Failed only"
Case 3
Me![front screen data subform].Form.Filter = ""
Me![front screen data subform].Form.FilterOn = False
MsgBox "You have requested to See All Records"
End Select
End Sub

Private Sub Frame38_AfterUp date()
Select Case Me!Frame38
Case 1
Me![front screen data subform].Form.Filter = "[Male Female] = 'M'"
Me![front screen data subform].Form.FilterOn = True
Case 2
Me![front screen data subform].Form.Filter = "[Male Female] = 'F'"
Me![front screen data subform].Form.FilterOn = True
Case 3
Me![front screen data subform].Form.Filter = ""
Me![front screen data subform].Form.FilterOn = False
End Select
End Sub

Any suggestions on how to make them dependent??

[ADezii]

The following Code will make 2 Independent Option Groups 'Dependent' in the sense that Selections made in both will control what Records are displayed in a Sub-Form. The Code makes the single assumption that both Option Groups have Default Values and that the proper Records are displayed in the Sub-Form based on these Defaults.

1. Copy-N-Paste the following Private Function to your Form's Code Module:
   

   Private Function fAnalyzeOptionGroupSelections()
   Dim fra1 As OptionGroup
   Dim fra2 As OptionGroup
   Dim strFilter As String
   
   Set fra1 = Me![Frame29]
   Set fra2 = Me![Frame38]
   
   Select Case fra1
     Case 1        'Pass
       Select Case fra2
         Case 1        'Males who Passed
           strFilter = "[Pass or Fail] = 'Pass' AND [Male Female] = 'M'"
         Case 2        'Females who Passed
         Case 3        'Males and Females who Passed
       End Select
     Case 2        'Fail
       Select Case fra2
         Case 1        'Males who Failed
         Case 2        'Females who Failed
           strFilter = "[Pass or Fail] = 'Fail' AND [Male Female] = 'F'"
         Case 3        'Males and Females who Failed
       End Select
     Case 3        'Both
       Select Case fra2
         Case 1        'Males who Passed or Failed
           strFilter = "[Pass or Fail] In('Fail','Pass') AND [Male Female] = 'M'"
         Case 2        'Females who Passed or Failed
         Case 3        'Males and Females who Passed or Failed
       End Select
   End Select
   
   'Substitute for Main Form and add additional Criteria for strFilter
   Forms![<Main Form>]![front screen data subform].Form.Filter = strFilter
   Forms![<Main Form>]![front screen data subform].Form.FilterOn = True
   End Function

2. In the AfterUpdate() Event of each Option Group make the Call to the Function:
   

   Private Sub Frame29_AfterUpdate()
     Call fAnalyzeOptionGroupSelections
   End Sub

   Private Sub Frame38_AfterUpdate()
     Call fAnalyzeOptionGroupSelections
   End Sub

3. Fill in the missing Code, substitute your Main Form Name for <Main Form>, and you should be ready to go. If you have any questions, feel free to ask.

[JackieSmith]

Tried code as you suggested.

Doesnt work completely. They are dependent however male and pass shows required answer.
If I select female and pass I get all pass and fail etc...this happens in other options as well such as "both" in both option boxes. Any suggestions??

[ADezii]

You have to complete the additional Code for it to work properly.

[malcolmk]

In original post Jackie it seems to me that you are simply overwriting the form filter command in the second function call, what you need to do is combine the two required filters into one. Result of sub frame29 plus result of sub frame38.
ADezii has given you most of the code so just follow it through logically or modify your original code.

[JackieSmith]

Hi again,

Ive done as said but I cant get it to work when asking it to show both pass and failed. This is the code Im using

Case 3 'Both
Select Case fra2
Case 1 'Males who Passed or Failed
strFilter = "[Pass or Fail] =('Fail','Pass' ) AND [Male Female] = 'M'"
Case 2 'Females who Passed or Failed
strFilter = "[Pass or Fail] =('Fail','Pass' ) AND [Male Female] = 'F'"
Case 3 'Males and Females who Passed or Failed
strFilter = "[Pass or Fail] =('Fail','Pass' ) AND [Male Female] = 'F'or'M'"

any ideas??

[ADezii]

Here is the Long and Short of it:

1. Long:
   

   strFilter = "[Pass or Fail] = 'Fail' Or [Pass or Fail] = 'Pass' Or " & _
               "[Male Female] = 'F' Or [Male Female] = 'M'"

2. Short:
   

   strFilter = "[Pass or Fail] In('Fail','Pass') Or [Male Female] In('F','M')"

[JackieSmith]

again this works for showing both male and females who pass and fail but doesn't work when I alter the section that selects the sex

I alter it to read [Male Female] In('F')" for just female and this doesn't work. So the n I tried [Male Female]='F'" and again this doesn't work. The same for the male selection occurs. Everything else is working great. I really am struggling to see whats wrong as access is accepting the code and I have inserted pop ups to check it is running which it is but the selection isn't filtered.

Thanks

[ADezii]

The only thing that I can suggest at this point is that you Upload the Database to either my Private E-Mail Address or here at Bytes. If this is possible, let me know.

[JackieSmith]

I can send it to your private email if you let me know what it is. I Keep playing with it to try and get that last bit working but no luck