Results 1 to 2 of 2
2005-03-01, 20:57 #1
- Join Date
- Jan 2002
- Washington State
- Thanked 0 Times in 0 Posts
OpenReport using the FilterName Argument (A2K)
(Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>)
I have a bunch of sales reports that first open a form where a user can select a bunch different parameters. The recordsource is different for each report. However, each reports recordsource has at least has the same fields where the criteria is set in the form.
The QBF form has a bunch of list boxes (8) where a user can select different types of codes to query on ie, product code, manifest, salesman, etc.. When the user submits their request my code builds a wherecondition to use when opening the report using DoCmd.OpenReport. My where string is similar to: strWhere = [Storage] In ("RG","CA") and [Variety] In ("FU", "GR", "GA"). I then open the report using: DoCmd.OpenReport strReportName, acViewPreview, , strWhere.
No big deal, right? It's worked great for more than 2 years! Yesterday, however, a report was run that generated a strWhere string that was more than 2048 characters (we have A LOT of codes that can be selected - and we keep adding them!). An error message was generated that said: Run-time error 7769. The filter operation was canceled. The filter would be too long."
I then came across <!mskb=207615>Microsoft Knowledge Base Article 207615<!/mskb> that describes the problem I'm having. Basically it says that you can't have a where clause that's more than 2048 characters long when using DoCmd.OpenReport.
But the KB article gives two resolution options. One is to use the filtername argument of DoCmd.OpenReport and the other option is to set the reports Recordsource object in the On_Open Event. I don't really understand how the filtername argument is used and that's my question here. The KB article says it's used to pass the name of a saved query and that this method allows you to filter a report without limitation on the size of the WHERE clause. Is the filtername argument used just to pass through the Recordsource to be used for the report? I need flexability though. Each time the report is run the variables selected on my QBF will be different.
Will the filtername argument work for me (if I understand how to use it)? Or does anyone else have an idea to offer?
Thanks so much for the time! This site is a valuable resource.
2005-03-01, 21:03 #2
- Join Date
- Mar 2002
- Thanked 28 Times in 28 Posts
Re: OpenReport using the FilterName Argument (A2K)
If you want to use the first method mentioned in the MSKB article, you will have to create or modify a query each time you want to open the report with a different filter, not a very attractive option.
The second method may be better. You can assemble the SQL string to act as record source for the report in the code behind the command button on the form, and store it in a global variable. Then, in the On Open event of the report, set Me.RecordSource to the value of the global variable.