OnOpen filter question

[arage]

OnOpen filter question
I specified the below in my reports onOpen event & yet I get the error I “entered an expression that has no value” the WHERE condition of the applyFilter property is sending something to the left side of the = sign though.

DoCmd.ApplyFilter , Reports![rptSearchByRefNum]![PromotionType] = Forms![frmTest]![cboType]

 

[Alexandre]

Happy new year arage,

I never used ApplyFilter myself. I generally set the filter property of the requested report from the command button used to launch it, then open the report.
However, since then second argument of the ApplyFilter action should take the form of a where clause, you should typically have the name of a field of the query underlying the report, as a first member of your expression.

Ex: Instead of

Reports![rptSearchByRefNum]![PromotionType] =

which seems to be the name of a control, you should have, assuming that your report is based on the query:
SELECT [PromotionType], [Field2] FROM Table1

ApplyFilter ,[PromotionType] = Forms![frmTest]![cboType]

Alex

BTW going through the help about ApplyFilter, I see that you should use a macro to implement this action from the OnOpen event

[This message has been edited by Alexandre (edited 01-19-2002).]

 

[arage]

hi Alexandre,
happy new year to you too hope all is well w/you.

I currently am using a macro on the reports onOpen event to set to applyFilter actions, whose WHERE clause are respectively:

[Reports]![rptSearchByRefNum]![PromotionType]=[Forms]![frmTest]![cboType]

AND…

[Reports]![rptSearchByRefNum]![RegionalDirectorCode]=[Forms]![frmTest]![cboRegion0] And [Reports]![rptSearchByRefNum]![EventNumber]=[Forms]![frmTest]![txtEvent0]

But my report shows nothing. When I go into its design view & check it’s record source, the datasheet view of the query the report is based on shows me ALL records.

 

[Alexandre]

Arage,

This is not how it works.
When you filter a recordsource (recordset, query or table), you basically "isolate" fieds according to a criteria. Then you do whatever you like with this selection (display in a report in your case).

You cannot test whether a control on your report match a given value, to apply a filter, since´:
1 the values on your report DEPEND on the report recordsource and filter you applied to it
2 OnOpen is the earliest triggered event for a form/reportand controls do not yet hold values at that stage.

So if your report is based on a query, this query probably holds the fields you want to filter on. Assuming that their names are also:
PromotionType
RegionalDirectorCode
EventNumber

then for example:

ApplyFilter ,([RegionalDirectorCode]=[Forms]![frmTest]![cboRegion0]) AND ([EventNumber]=[Forms]![frmTest]![txtEvent0])

And access will filter the records of the query underlying your report, selecting the ones where the above fields matche the values on your forms (if still opened).

Hope it helps

Alex

 

[arage]

thanks very much Alex, sometimes the forums advice sticks in my head & sometimes not, in this case it did your explanation was great, wonderful!

The form is apparently running fine now with all the right results.

Now only one further request if possible.

Imagine this:

A form with a single combo box & 1 tab control.
The tab control has 5 pairs of combo boxes & text boxes.
Basically I’m concerned with the tab control for this question.

The text box on the tab control can currently only hold one number but I’d like it to be able to hold several, comma delimited values like 1,324,43,55…etc The reason is that the accompanying combo box will be a grouping level for against which the text box values will be matched.

So if tab control combo box shows West and it’s text box shows 1,2,3,4 the report generated will include events 1, 2, 3, and 4 from the western region.

Hope I’m not confusing you further.

 

[Alexandre]

Enlight me, Arage. I do not understand how your system works. What prevents the user to enter comma delimited values if needed?

Perhaps describe how one would fill your form untill launching the report.

Alex

[This message has been edited by Alexandre (edited 01-22-2002).]

 

[arage]

I just posted the problem in FORMS forum just now title is "Numerous entries in one text box"