filtering subform using radio buttons

[Jody Cliffe]

Hi.

Im trying to program my radio buttons to filter data for student pass/fail in a sub form called [front screen data] I have using help found here got this far but its not working and Im not sure why. my radio buttons are as follow
#1 pass
#2 fail
#3 both

my field is [PassFail]

This is what i have

Private Sub Frame29_BeforeUpdate(Cancel As Integer)
Select Case Me!Frame29
Case 1
Me.Filter = "Pass or Fail = Pass"
Me.FilterOn = True
MsgBox "Pass or Fail = Pass"
Case 2
Me.Filter = "Pass or Fail = Fail"
Me.FilterOn = True
MsgBox "Pass or Fail=Fail"
Case 3
Me.FilterOn = False
MsgBox "Show All Records"
End Select
End Sub

Any help would be appreciated

Thanks

[Stewart Ross]

Hi. Firstly, you'll need to use the AfterUpdate event of your frame, not its BeforeUpdate event. Secondly, if the field you are filtering on is called Pass or Fail you will need to enclose the field name in brackets, like this: [Pass or Fail]. Thirdly, the text values 'Pass' and 'Fail' must be enclosed in quotes:

Private Sub Frame29_AfterUpdate()
  Select Case Me!Frame29
  Case 1
    Me.Filter = "Pass or Fail = Pass"
    Me.FilterOn = True
  Case 2
    Me.Filter = "Pass or Fail = Fail"
    Me.FilterOn = True
  Case 3
    Me.FilterOn = False
  End Select
End Sub
 

If the frame (and consequently its AfterUpdate event code) is on the main form you will need to refer to the subform specifically, whereas at present the code above may be referring to the main form. If you need to filter the subform from the main form, the syntax is

Me![YourSubformControlName].Form.Filter = "[Pass or Fail] = 'Pass'"
Me![YourSubformControlName].Form.FilterOn = ***

(Of course you would need to substitute the actual name of your subform control for the placeholder in the example above.)

-Stewart

[Jody Cliffe]

Thanks sooooo much. This worked perfectly. I had tried to put the field name in brackets but when I did this i removed the speech marks causing an error. Didnt think about the quotation marks though.

Also thought about the subform bit but tried to add that to [frontend subform]![pass or fail] so didnt work. This worked perfectly. I am forever grateful

Jody

[Jody Cliffe]

Ok so just noticed something...added the syntax just before the select case me part and now when I use the form it sort of works.

The screen when I press pass works fine and shows all that have passed but when I select fail the subform refreshes but remains the same (showing all passed) so when I select Both the same happens (showing all passed)

any ideas???

[Stewart Ross]

Not sure; I'd suggest that you add the following statement just after the End Select statement to provide some state tracing of what is going on:

msgbox "Button value: " & Me!Frame29 & " - Filter: " & Me.Filter & " - Filter Status: " & IIF(Me.FilterOn, "On", "Off")

If, as I suspect is the case, the filter is being applied as expected then the problem is in the relationship between your main form and your subform in some way or another.

If the filter shown in the messagebox is not as expected you will need to check the context of the button value and the case statement involved.

-Stewart

[Jody Cliffe]

As suspected when the filter is selected the pop up says the filter status is off. Do you have any suggestions??

I am unsure where to go as I am not a novice but I am also not an expert. I have some knowledge but apparently not enough.

Any advice you have is gratefully received.

[Stewart Ross]

If you could post the whole of the current AfterUpdate code I'll check the syntax for you.

Please be aware that filtering the main form will in no way affect the subform's records.

-Stewart

[Jody Cliffe]

This is what I have now completely. I added in some requery's to updat it each time to check if that was the problem so here is everything I got

Private Sub Frame29_AfterUpdate()
Me![front screen data subform].Form.Filter = "[Pass or Fail] = 'Pass'"
Me![front screen data subform].Form.FilterOn = True
Select Case Me!Frame29
Case 1
Me.Filter = "[Pass or Fail] = 'Pass'"
Me.FilterOn = True
MsgBox "You have requested to view students who have Passed only"
Forms![Front Screen]![front screen data subform].Requery
Case 2
Me.Filter = "[Pass or Fail] = 'Fail'"
Me.FilterOn = True
MsgBox "You have requested to view students who have Failed only"
Forms![Front Screen]![front screen data subform].Requery
Case 3
Me.Filter = ""
Me.FilterOn = False
MsgBox "You have requested to See All Records"
Forms![Front Screen]![front screen data subform].Requery
End Select
MsgBox "Button value: " & Me!Frame29 & " - Filter: " & Me.Filter & " - Filter Status: " & IIf(Me.FilterOn, "On", "Off")

End Sub

Thanks

[Stewart Ross]

Problem is that in your Select Case statement you are applying filtering to the main form, not to the subform. The statements you added before the Select Case apply a filter to the subform - the case statement is having no effect as it is filtering the main form only.

I'd replace the AfterUpdate code as follows:

Private Sub Frame29_AfterUpdate()
  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
  MsgBox "Button value: " & Me!Frame29 & " - Filter: " & Me![front screen data subform].Form.Filter & " - Filter Status: " & IIf(Me![front screen data subform].Form.FilterOn, "On", "Off")
End Sub

There is no need for the requery statements, as turning the filter on or off requeries the subform automatically.

-Stewart

[Jody Cliffe]

Thank you soooo much. This has been bugging me for over 3 days before I sent you the message so I really welcome this.

Thanks

[Jody Cliffe]

Hi again,

This has been working fine but Im trying to use a DCount to count the records in my subform based on my selections using a my drop downs but for some reason it doesnt work based on my selections using the radio buttons.

This is the code I have added (after the End Select)
[Text54] = DCount("[Surname]", "front screen data")

Any ideas why this isnt working??(it isnt updating in the DCount box)

Thanks

[Stewart Ross]

Although you are posing a different question - which should be in its own thread - DCount is a very simple function which does what it is asked to do. A quick way to check what is going on is to replace your current line with this one:

Msgbox "Rows in front screen data: " & DCount("*", "front screen data")

This will show you the value of the number of rows in table or query front screen data. If this value is 0 then there are no rows in that data source.

I cannot tell you more about what to check given that I know nothing at all about what the datasource 'front screen data' is or how it is updated.

-Stewart