Option group to filer subform

[Asghaar]

Hello everyone,

I'm trying to use an option group to filter a subform from my main form.
I did try some scrips behind the option group Afterupdate event but i'm not able to make it work and actually filter the information shown in the subform.
I managed in the end to kind obtain what i was looking for but is not very elegant and needs one extra step from the user.
More exactly i put the following code behind the afterupdate event :

If Me.Frame100 = 1 Then Me.txtFilter = "1"
If Me.Frame100 = 2 Then Me.txtFilter = "2"

This inserts a value in a hidden textbox (called "txtFilter").
After i have a button near the option group with the following code behind the click event :

 Dim strFilter As String
    
    strFilter = "Comment = '" & Me.txtFilter & "'"

    Forms!T_all!T_all_sub.Form.Filter = strFilter
    Forms!T_all!T_all_sub.Form.FilterOn = True

* [Comment] is the field in the subform that i want to filter

Although I tried to have the above code directly to the textbox events, none of the evnts seem to run the code ( afterupdate , on chnage, on dirty,on enter ). and the only way it works is by having the seperate button described above.

Could you please help me with a solution to filter the subform (T_all_sub, found in the main form T_all) directly from the option group or at least form the textbox without the need of a separate button.

Thank you everyone for your answers and help.

Regards,

 

[pbaldy]

You would use the after update event of the frame, not the options themselves.

 

[Asghaar]

You would use the after update event of the frame, not the options themselves.

Hello,

On the frame events i've used the code and wasnt able to make it work.
It's like nothing happens.

regards,

 

[pbaldy]

Can you post the db here?

 

[Asghaar]

Can you post the db here?

Hello,

Please find attached the database i'm working.
It's not the complete db,i removed what is not connected to the situation presented.


Thank you very much for the help.

 

[Asghaar]

Hello,
Any solution how I could insert everything in the option group frame event?

Thank you.

 

[John Big Booty]

Given that you wish to apply the Filter to a Subform, from the Main Form your filter needs to be in the form;

Me.subformname.Form.Filter = "[FieldToFilterOnHere]= " & Chr(34) &  Forms!mainformname.controlname & Chr(34)
Me.subformname.Form.FilterOn = True

 

[Asghaar]

Hi,
Thank you for your fast reply.
But since I'm far from having a real grasp on vba and access - I'll repeat to see if I understood well.

So I need to insert your code into the option group frame after update event? (the option group is found in the main for as is seen in the dba attached previously)

Once again thank you.

 

[John Big Booty]

Personally I'd put it in the On Click event of the Option Group but After Update event should have the same effect.

You will of course need to change subformname, FieldToFilterOnHere, mainformname and controlname to reflect the reality of your DB.

Also, your Option group will be returning a number. Does the field you wish to filter hold a number or a text value?

 

[Asghaar]

Hi,
Do you mean the click event of the frame or the click event of each option from the option group (I have to ask just to be sure).
Regarding the value, honestly is not important since that column will not be visible in the subform.

Regards,

 

[John Big Booty]

Same as what pbaldy said, up there ^^, but the On Click event. The difference is probably six of one or half a dozen of the other.

 

[pbaldy]

The click event fires at times other than when the value is changed, which is why I would use the after update event. The only time you really want it to fire is when the value changes. Doubt you'd see a noticeable performance difference, but why have it running unnecessarily?

 

[Asghaar]

Hello,

Thank you guys,especially John Big Booty.
Worked beautifully.

But: How to do if i want in the option group to have the option to see all again ?
Meaning when i press third option - "shows all" - the filter to be set to False and show me again all the records?


Regards,

=== so found a solution,not very elegant, but it works.

If you have any other solutions please don't hesitate to share them .

Me.T_all_sub.Form.Filter = "[Comment]= " & Chr(34) & Forms!T_all.Frame100 & Chr(34)
Me.T_all_sub.Form.FilterOn = True
 
 If Me.T_all_sub.Form.Filter = "[Comment]= " & Chr(34) & "3" & Chr(34) Then
  Me.T_all_sub.Form.FilterOn = False
 End If

 

[pbaldy]

Set FilterOn to False.

 

[Asghaar]

Set FilterOn to False.

How to do that since in the after event of the entire option group i have it set to True and to filter based on the selection.
And since my no 3 selection(the one that should show all) - must have an option value ( default to 3) and the script only works one way by filtering based on the Optionvalue selected.. how to set the filter to off in the same script without using the solution found in my anterior reply.

 

[pbaldy]

You appear to already have the solution. Test the group for the value of 3 and set FilterOn to False if it is. I'd probably test first so you don't filter then unfilter.

 If Forms!T_all.Frame100 = 3 Then
  Me.T_all_sub.Form.FilterOn = False
  Exit Sub
 End If