UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Passing A Filter And Sort Order From A Form To A Report, Access 2010    
 
   
bugs007
post Apr 9 2020, 06:09 AM
Post#1



Posts: 23
Joined: 1-October 19



Hi all.

Having a problem passing a form's applied filter and sort order to a report. This worked perfectly in Access 2003.

I have a form with a dozen or so fields and 4 different reports.

After some trial and error it seems I can pass either the filter or the sort order but not both together.

What has changed in Access 2010 to break what was working fine in 2003.

Form and reports are based on the same query.

This is the code used in the open report buttons

Dim stDocName As String

Set frm = Forms!TblMain2

stDocName = "RptMainList"
DoCmd.OpenReport stDocName, acViewPreview, , frm.Filter
Reports!RptMainList.OrderBy = Me.OrderBy

I have tried using Tempvars to save the form's filter and orderby and load them with the report's on load event but it still will not work as it does in 2003. In 2003 I could set any combination of sort order and filter on the form and these were passed to the reports without a problem.

Can anyone shed light on what is happening and suggest a possible work around please.

Version is 2010 SP2 and it must be able to run as a runtime application so the option of opening the report in design view on the fly is unavailable.

I look forward to your suggestions and replies.

Thanks in advance.

Go to the top of the page
 
pere_de_chipstic...
post Apr 9 2020, 06:21 AM
Post#2


UtterAccess Editor
Posts: 10,624
Joined: 8-November 07
From: South coast, England


welcome2UA.gif

I am surprised that the command "Reports!RptMainList.OrderBy = Me.OrderBy" ever actually worked as once this command was issued the report would have been rendered

Have you tried putting the code in the report's OnOpen event: Me.OrderBy = Forms!YourFormName!OrderBy
Also is the Report's OrderBy property 'On' (Me.OrderByOn = True)

hth
This post has been edited by pere_de_chipstick: Apr 9 2020, 06:23 AM

--------------------
Warm regards
Bernie
Go to the top of the page
 
bugs007
post Apr 9 2020, 10:01 AM
Post#3



Posts: 23
Joined: 1-October 19



Many thanks have just tried your suggestion and it seems to work fine.

The only remaining problem is if I do only a sort order on the main form without a filter, the sort order is applied to the report and the last applied filter which is no longer active on the form. Even if I click the "Remove Filter" button prior to doing the sort on the form the report still launches with the last used filter.

My Remove Filter button has the following code

Me.FilterOn = False
Me.Filter = ""
Me.OrderBy = "QryMain.Title"
DoCmd.ShowAllRecords

The filter is somehow stuck in there even if I close and repoen the database. When I open the edit filter grid I can see the last used filter there which my code does not seem to remove. Is there any way to remove the last filter used ?

I need to be able to sort without a filter being appled.

Thank you.

Go to the top of the page
 
pere_de_chipstic...
post Apr 9 2020, 10:21 AM
Post#4


UtterAccess Editor
Posts: 10,624
Joined: 8-November 07
From: South coast, England


Hi
If you open the report in design mode, clear the filter property, set the filter on load property to no and then save the report. Make sure the reports grouping and sorting criteria are cleared as well (or grouped as appropriate!)

You may find that if you open the report and then change to design mode then you may need to clear these filters before you save the report design.

Also you may need to change the on open code to e.g.
CODE
If IsNull(forms!yourformname.orderby) then
    Me.orderby =""
    Me.orderbyon = false
Else
    Me.orderby = forms!yourformname!orderby
    Me.orderbyon = true
End if


hth

--------------------
Warm regards
Bernie
Go to the top of the page
 
bugs007
post Apr 9 2020, 11:35 AM
Post#5



Posts: 23
Joined: 1-October 19



Many thanks. I am aware of saving the form or report with the filter property cleared as I have been caught out by this before.

Was thinking similar re your code. I am using the same method for the filter also.

Me.OrderBy = Forms!TblMain2.OrderBy
Me.OrderByOn = True
Me.Filter = Forms!TblMain2.Filter
Me.FilterOn = True

I shall try your suggestion.
Go to the top of the page
 
pere_de_chipstic...
post Apr 9 2020, 11:46 AM
Post#6


UtterAccess Editor
Posts: 10,624
Joined: 8-November 07
From: South coast, England


Hi Bugs007

You shouldn't need to set the filter in the reports OnOpen event as you can set the filter in the OpenReport Command, as you do in your code:
DoCmd.OpenReport stDocName, acViewPreview, , frm.Filter

If there is an option to open the report with no filter then you could code this as (e.g.)
CODE
if Me.OptionNoFilter then
    DoCmd.OpenReport stDocName, acViewPreview
Else
    DoCmd.OpenReport stDocName, acViewPreview, , frm.Filter
End if

or
CODE
Dim strFilter as String
...
...
If not Me.OptionNoFilter then strFilter = frm.Filter
DoCmd.OpenReport stDocName, acViewPreview, , strFilter

The OnOpen Event Sort instructions are independent of the report filter.

hth

--------------------
Warm regards
Bernie
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th May 2020 - 02:19 AM