By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,682 Members | 1,980 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,682 IT Pros & Developers. It's quick & easy.

Filtering (sorting) subreport from subform

100+
P: 167
Hi!

I have a MainForm and a SubForm on it. They're linked and can be filtered, sorted etc...
I designed MainReport and SubReport that should match filter and sort criteria from MainForm and SubForm.
I have a PrintButton on MainForm that opens the report!

At this moment my MainReport is being filtered and sorted excatly the way the MainForm is, and I was trying to get ma SubReport to be filtered and sorted the way the SubForm is.

I'm reading forums for the last three days trying to find some inspiration and I finaly decided to ask for help.

So please help out guys!
Thanks,

Hrvoje
Oct 17 '07 #1
Share this Question
Share on Google+
25 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Oct 17 '07 #2

100+
P: 167
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
Oct 18 '07 #3

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 18 '07 #4

100+
P: 167
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.
Oct 18 '07 #5

nico5038
Expert 2.5K+
P: 3,072
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:
Expand|Select|Wrap|Line Numbers
  1. IF len(nz(Me.Openargs)) > 0 then
  2.    me.subReportName.report.filter = Me.Openargs
  3. endif
  4.  
The other solution would imply code behind the Print button like:
Expand|Select|Wrap|Line Numbers
  1. Dim qd as DAO.Querydef
  2. set qd = currentdb.querydefs("<qry of your subreport>")
  3. qd.SQL = Translate(qd.SQL,";", " WHERE " & Me.subform.form.filter & ";")
  4. Docmd.Openreport ...
  5.  
Getting the idea ?

Nic;o)
Oct 18 '07 #6

100+
P: 167
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!
Oct 22 '07 #7

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 22 '07 #8

100+
P: 167
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.
Oct 23 '07 #9

nico5038
Expert 2.5K+
P: 3,072
For that you'll need to add the string " ORDER BY " & Me.Orderby & ";" after the WHERE that's already in place.

Nic;o)
Oct 23 '07 #10

100+
P: 167
Hi Nico!

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

Expand|Select|Wrap|Line Numbers
  1.     Dim FinalSQL As String
  2.     Dim sqlString As String
  3.     Dim Fltr As String
  4.     Dim Sort As String
  5.  
  6.  
  7.     sqlString = "SELECT qObracun.oID, qObracun.otID, qObracun.otcID, qObracun.oDatum, qObracun.oOpis, qObracun.oPotrBAM, qObracun.oPotrBAMrac, qObracun.oPotrEUR, qObracun.oPotrEURrac, qObracun.oPotrHRK, qObracun.oPotrHRKrac, qObracun.otcBAM, qObracun.otcEUR, qObracun.otcHRK, qObracun.UkupnoBAM, qObracun.UkupnoBAMrac FROM qObracun"
  8.     Fltr = "qObracun.oID Like '*'"
  9.     Sort = "qObracun.oID"
  10.  
  11.  
  12.  
  13.     If Me.FilterOn = True Then
  14.     If Me!sfObracun.Form.Filter <> "" Then Fltr = Me!sfObracun.Form.Filter
  15.     End If
  16.  
  17.  
  18.     If Me!sfObracun.Form.OrderBy <> "" Then Sort = Me!sfObracun.Form.OrderBy
  19.  
  20.  
  21.     FinalSQL = sqlString & " WHERE " & Fltr & " ORDER BY " & Sort & ";"
  22.  
  23.     CurrentDb.QueryDefs("qsrObracun").SQL = FinalSQL
  24.  
  25.  

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.
Oct 29 '07 #11

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 29 '07 #12

100+
P: 167
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.  
  3. If Right(Forms!fObracunTure!sfObracun.Form.OrderBy, 4) = "DESC" Then Me.GroupLevel(0).SortOrder = True
  4.  
  5. End Sub
This doesn't seem to work. But I'm getting closer thanks to your help :-)
Oct 30 '07 #13

nico5038
Expert 2.5K+
P: 3,072
Add:
Expand|Select|Wrap|Line Numbers
  1. Me.GroupLevel(0).SortOrder = True 'For Desc order, else False
  2.  
Nic;o)
Oct 30 '07 #14

100+
P: 167
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?
Nov 5 '07 #15

nico5038
Expert 2.5K+
P: 3,072
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)
Nov 5 '07 #16

100+
P: 167
hi N.
I creatde a test database but don't know how to attach it to my comment.
How can I do that????
Nov 10 '07 #17

nico5038
Expert 2.5K+
P: 3,072
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)
Nov 10 '07 #18

100+
P: 167
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.
Attached Files
File Type: zip Test.zip (24.6 KB, 193 views)
Nov 10 '07 #19

nico5038
Expert 2.5K+
P: 3,072
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)
Nov 11 '07 #20

100+
P: 167
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.
Attached Files
File Type: zip Test2.zip (59.2 KB, 168 views)
Nov 13 '07 #21

nico5038
Expert 2.5K+
P: 3,072
Check the Tools/References under VBA to see or there's no one missing...

Nic;o)
Nov 13 '07 #22

100+
P: 167
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.
Nov 19 '07 #23

nico5038
Expert 2.5K+
P: 3,072
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)
Nov 19 '07 #24

100+
P: 167
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.
Attached Files
File Type: zip Test3-final and it works.zip (27.4 KB, 356 views)
Nov 20 '07 #25

nico5038
Expert 2.5K+
P: 3,072
Well done :-)

Glad to be of assistance !

Nic;o)
Nov 20 '07 #26

Post your reply

Sign in to post your reply or Sign up for a free account.