Filtering (sorting) subreport from subform

Like the main/sub form, the report/subreport can be linked.
Just single-click the subreport and look in the Properties window under the Data tab to see the Linkage "master" and "child" properties where the field(s) need to be specified of the link.

Nic;o)

Like the main/sub form, the report/subreport can be linked.
Just single-click the subreport and look in the Properties window under the Data tab to see the Linkage "master" and "child" properties where the field(s) need to be specified of the link.

Nic;o)

Hi Nic;o!

Thanks for the reply!
My MainReport and SubReport are linked the same way the Form and Subform are.
But when filtering the MainForm using Filtr By Form from the toolbar, I'm also able to filter my Subform in the same blow.
What I want is to pass that filter from the subform to my subreport.

This is how im filtering MainForm:
-------------------------------------------------------------------------------

Private Sub Report_Open(Cancel As Integer)

On Error GoTo Err_Report_Open

Me.Filter = Forms!fObracunPlace.Filter
Me.FilterOn = Forms!fObracunPlace.FilterOn

Me.OrderBy = Forms!fObracunPlace.OrderBy
Me.OrderByOn = Forms!fObracunPlace.OrderByOn

Exit_Report_Open:
Exit Sub

Err_Report_Open:
Resume Exit_Report_Open

End Sub
-------------------------------------------------------------------------

I tried to use the same principle when filtering subreport but I get an Error ...

Did anyone figured out how to filter subreports too????

Thanks in advance for the support!

Hrvoje

Hmm, depending on the Access version you could use the report's OpenArg to pass the filter and apply it to the subreport.
Another 100% solution would be to dynamically change the subreport's query.
This requires a "template" copy of the original filter. In the [Print] button you can manage the querydef's SQL property with a replace of the trailing ";" with the subformfilter.

Getting the idea ?

Nic;o)

Hi again Nico!

Thanks for your advice! I must admit that I don't get it, I'm new to coding so the simple solution would be the best...

Can you please give me an example of what should I do?
I know it's like spoonfeeding, but I'm realy confused with this.

FYI.....I'm using Access 2003.

Thanks!
h.

Hi h.

I always start with directions so I don't spoil the coding fun for the experienced members :-)

The A2003 should have for forms the OpenArgs property in the parameterlist when issuing a docmd.openreport "rptName", ...
Shold be the last one.
By moving the Me.subformname.form.filter as the Openargs parameter, you can use the OnOpenevent of the mainreport to intercept this value with:
The other solution would imply code behind the Print button like:
Getting the idea ?

Nic;o)

Hi N.

I must admit I'm feeling confused now. I tried both of your solutions and I allways get the same error: "2101 - The settings you entered is not valid for this property"

I tried to turn the filter on in my SubReport after copy/pasting the filter from the SubForm. If i turn it on manually by setting FilterOn to "yes", then it filters the way I want it to be filtered.
However, If I try to do it in VB it gives me the above stated error.

I want to set the Filter, FilterOn, OrderBy and OrderByOn properties in VB!!!
Do you have any other suggestions left?

Thx!

The second option should always work.
Did you add the code behind the Print button on a form and not in the report ?

Nic;o)

Hi there!

I tried the second solution (with more attention this time:-)....) and it works! yay!
But it works only for filtering, and I can't make it work for sorting too.

For that you'll need to add the string " ORDER BY " & Me.Orderby & ";" after the WHERE that's already in place.

Nic;o)

Hi Nico!

thanks alot! I tried that allready. This is my current code:


It filters my subreport perfectly, but sorting is not working.
I tried:
If OrderByOn is True.... similar to filtering..but that doesn't work either...

Do yuo have any suggestions left?

h.

Oops, I forgot to realize it's a report you're working with and no form :-(

Reports won't work with the query's OrderBy, but will need the GroupLevel to be set correctly.
Check:
http://allenbrowne.com/ser-33.html

Nic;o)

Hi!

I tried to set the GroupLevel property of my SubReport... In the Field/Expression column I entered the path to SubForm's OrderBy property and it works great. Now my SubReport is being sorted by the same field as the SubForm.
Only now I have SortOrder problem. I tried this code in SubReport's Open event:

This doesn't seem to work. But I'm getting closer thanks to your help :-)

Add:
Nic;o)

Hi N.!

I tried the line of code you suggested and it did nothing.
I used a MsgBox to check if "DESC" is what I get when sorting Descending and it was correct.
Sorting does work, but only for Ascending sort order which is default in my SubReport. I just can't get it to work in Descending sort order too.

Ideas?

Strange, did you check the output to be in DESC order and did you check the datatype (text sorts differently as numbers and dates).

Perhaps you can create a small test database with the table(s) and the report, then add an attachement to a comment here so I can check why it doesn't work.

Another option, when you have only Ascending working, is to add an "inverted" field to get the opposite sort, e.g. change 1 into -1, 2 into -2, etc and the ascending becomes descending.

Nic;o)

hi N.
I creatde a test database but don't know how to attach it to my comment.
How can I do that????

1) First create the comment.
2) Save/store it
3) Use the "Edit/Reply" link bottom right under the comment.
4) Add the attachment on the edit form.

Nic;o)

Here is a Test database that reflects my problem, and my current situation.
You will notice that filtering works, and sorting only works for Ascending sort order which is default in my subreport.

Hope you'll be able to come up with something...


p.s. I deleted all the records so it's smaller, so you will need to put in some info to test it.

Hmm, took a quick look at your .mdb, but found for a testcase:
Forms!fMain!sfSub.Form.OrderBy the value:
[qSub].[aDescription] DESC, qSub.aValue DESC

This would require two GroupingLevels and for each the DESC test and the filling of the field in the controlsource.

Personally I would switch from using a report to use the form with a white background and with a "Print form" button with the property "Show when" set to "Only form".

Will save a lot of code....

Nic;o)

I have tried the basic step with qdefs for my test base and....it works!!!!!!!!!
Filtering and sorting works the way I want it. BUT...that is a test base, and I haven't found any difference between my database and my Test base.

Only difference I can remember is that they were designed on different PC's, but both with ACC2003.

I'm attaching the new test base here for some future users that might find it helpful.

Do you have any suggestions on how to make it work now?
I hope I won't be forced to make it from the scratch :-(

h.

Check the Tools/References under VBA to see or there's no one missing...

Nic;o)

Hi N.

I finaly found the source of my problem. The problem is my Sum field in SubReport's footer. If I don't have the Sum field than SubReport displays the sorted data just fine, but when I add the Sum field it overrides the sorting even though underlying query is sorted.
I tried to use DSum function, but with no success...
I hope I'll get it to work soon.

Hmm, you're using both subform and subreport.
Keep in mind that a subreport is sorted according the GroupLevel specified and NOT according the Order By in the query!

Nic;o)

Hi N. and everyone else out there reading this!

I finally FOUND THE SOLUTION to my problems!
What a great feeling :-)
What I did is this:

1. I Used Querydefs as described above to change the SQL definition of the underlying query for my SubReport (This is where I'm handleing SubReport's Filtering and Sorting).
2. I added a RUNNING SUM text box for every value field that needs to be summarised in my SubReport.
This Running sum text box [RSumBox] is placed in the detail section of a SubReport and I have a Sum control in Report footer section of my SubReport with record source set to (=RSumBox).

For anyone interested in understanding this better I attached the Test base to this reply. You can see all the code inside and everything else as explained above...

Once again I must say THANK YOU Nic;o! for being there for me every single day and for giving great ideas and advices. Respect!

h.

Well done :-)

Glad to be of assistance !

Nic;o)