Filtering in subreport

Question

I have encountered issues with using "filtering menus" in report during development of database aplication for my employer.

For my filtering options I created buttons with OnClick Event:

DoCmd.GoToControl "MyReportField"
DoCmd.RunCommand acCmdFilterMenu

These buttons are embedded in subreport. Initial thought was to create buttons, similar to the one in ribbon, with filtering options (in final stages of development I am planning to disable/hide Access ribbon for users). I created 5 buttons, 3 for fields with data type Text, 1 with data type Date and 1 with Boolean data type.

To quote a classic, the buttons "are misbehaving." When I test their function in form in form view, the "BooleanFilterBtn" on click already doesn't display Yes/No options (or something similar), but displays 2 numeric values (like -24441 or -29696). And filter is not working whatsoever.

Another problem arises when I try to have active more than one filter at a time. Date field filter and one of the Text field filters are working when combined with another filter option. But after using one of the two remaining Text field filters, when I click any other of the filtering buttons, the FilterMenu becomes blank.

Worth noting is that right-clicking on given field in report works without issues, but not the way I want.

Given these rather odd behaviours I think I am missing something. Is there a way how to make FilterMenus work the way as familiar from the ribbon? If not, is there any other option with similar design, options that I could try out?

Additional info:

Subreport is connected with form through "MyIDField." Both subreport and form have query data source, already saved.

All field names and data types are matching.

EDIT: Also I should have written that the FilterBtns doesn't work if I open the subreport separately, they work only when I open form (with subreport in it), eventhough the FilterBtns are using only controls from the report.

EDIT2: Code behind filter button filtering Boolean data type (boolean data are in Report in form of Yes/No check-fields, as mentioned in June7 comment bellow):

Private Sub btn_FiltrGarant_Click()
DoCmd.GoToControl "Garant"
DoCmd.RunCommand acCmdFilterMenu
End Sub

When I change this code to:

Private Sub btn_FiltrGarant_Click() 
Me.Garant.SetFocus 
DoCmd.RunCommand acCmdFilterMenu 
End Sub

Brings no effect, only that the loading time of actual FilterMenu takes a bit longer.

Example for Text data type field:

Private Sub btn_FiltrRzh_Click()
DoCmd.GoToControl "ZkracenyNazev"
DoCmd.RunCommand acCmdFilterMenu
End Sub

Changing code in the same manner:

Private Sub btn_FiltrRzh_Click() 
Me.ZkracenyNazev.SetFocus 
DoCmd.RunCommand acCmdFilterMenu 
End Sub

Brings no effect too.

May it be that the FilterMenu is not meant to/available to be used in reports? Or may it be disabled in default settings of Access?

Because also the situation, that if I open the Report separately (not in form as a subreport, but just as report by itself in report view), using any of the FilterBtns gives me RunTime error 2046, that the action GoToControl is not available. Changing to SetFocus eliminates the error, but the button doesn't bring up any FilterMenu at all. But these issues are valid only if I open report separately (which is not my interest), but might be helpfull as information.

Right now, I don't know where the problem could be. Anything I could think of was data type/naming mismatch, but that's not the case.

FINAL EDIT: After almost a month (there were holydays) of trying and searching for a way how to make this setup work I decided to switch from subreport to subform, in which I will try to obtain the same functionality. Maybe just reports are not supposed to be handled the way I wanted to.

Just out of courtesy I am putting June7 answer as verifiedfor the time spent on helping me.

Thank you.

Answer 1

Don't know why your yes/no field displays 2 numeric values like -24441 or -29696 in the filter menu. Regardless, not able to get the filter menu to act on a yes/no field at all. However, get around that with an expression in report RecordSource query that changes the Yes/No field from Boolean to text values and use the calculated field in report design, like:

SELECT *, IIf([fieldname], "Yes", "No") AS IsSomethingTrue FROM tablename;

Use Me.controlname.SetFocus instead of GoToControl so the report will open independently or as a sub object.

Answer 2

You have to be careful how you refer to a Report object that is within a subForm container. They do not behave the same as subforms in many ways.

Even a simple refresh is actually difficult to achieve, as reports are not meant to be dynamic like a form.

To refer to it in VBA you have to use the full form syntax;

[Forms]![MainFormName].[Form]![subReportHolderName].Requery

Using the normal Me.blah.blah reference will not work with a form embedded report. Note that you refer to the container - NOT the report object.