Setting FilterName at runtime not working

[wokkie]

Hi,

I am using Access 2003 ADP with a SQL Sever 2000 BE.

I am trying to set the FilterName of a report at runtime, so that I can use the same report with different data.

strQryName = "qryJournalMailingSheet"
strDocName = "rptIssuesToSend"

DoCmd.OpenReport strDocName, acViewPreview, strQryName

I have the code above. But it doesn't work.

What am I doing wrong and how do I solve it?

Best regards,

Ian

 

[jsteph]

Check that the filter doesn't confilct with the base query, ie, the filter doesn't replace the base query, it just adds it's criteria.

If you want to replace the base query, you can do a quick open of the report in design view, relace the RecordSource with the new query and do a .Save, then open, or open the querydef and replace it's .SQL with the filter query's .SQL and then open the report.
--Jim

 

[RickSpr]

You can't use a query name as the third parameter of OpenReport. It has to be something you can put in a WHERE clause.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[JustLJ]

What about if you just want to sort the report differently at run time? Is that a filter or a where?
Thanks.

 

[jsteph]

You can indeed use a query as the Filter argument. For the Where condition, you must use a Where-type clause, without the 'Where'.

If the query used in the Filter arg is not compatible, then it will give an error. Basically Access extracts the Where clause from the query used in the Filter arg. and uses that.
--Jim

 

[RickSpr]

Please excuse me--I was fatigued and misread the Help file. Jim is correct, you can use a query name in the 3rd (but not 4th) parameter.

However, I think either of these parameters is only used for filtering--selecting records--and has no effect on the sorting. That is, only the WHERE clause is extracted from the query, and not the ORDER BY clause.

One way to change the sort order would be to change its OrderBy (and OrderByOn) properties after you have opened it. That will probably cause an unpleasant flicker, though. To counter that, you could turn off screen updating temporarily before opening the report, and then turn it on again after setting the OrderBy property. (See the Application.Echo method.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[wokkie]

Thanks for all the replies,

I have now decided to use ADODB recordsets as the recordset.

Using:
<code>
Dim myRS As New ADODB.Recordset
Set myRS = myVolume.GenerateRenewalLabels(Forms!frmMain.cboRenewalPaymentType, Forms!frmMain.cboRenewalJournal, False)

Set Me.Recordset = myRS
</code>
In the Report_Open event.

This works fine and I think more flexable as I can now use procs to generate select the data I want, so I can use union queries if I want to.

Thanks again

Ian

 

[JustLJ]

thanks.
LJ