Filtering subforms in reports

[garfieldsevilla]

I have two databases, one with products and another with accessories and a query which relates products and accessories. I created a simple report which lists the product fields and has two subforms. The first subform simply lists all the accessories. The second subform needs to list a subset of accessories so I wrote a few lines in VBA which is called on the LOAD control. The VBA simply gets some criteria from another table, makes some decisions and sets a filter to limit accessories by a date:

Me.Filter = "([OldNew] > " & Format(Min_yr, strcJetDate) & ") AND ([Nacido] <= " & Format(Max_yr, strcJetDate) & ")"
Me.FilterOn = True
 

The problem is that the subform works fine when you load it from the interface. However, in the report the filter never gets applied and the form simply displays the same as the first subform, ie all the accessories are shown. The subform when displayed as part of a report does not call the VBA code. How can I get around this?

Many thanks!

[garfieldsevilla]

I guess the only way to do this is create two queries, one with all the accessories for a product and another which accessories for a product filtered by date.

Does anyone know of a more elegant solution?

[NeoPa]

Let's get the terminology right to start with, to save you getting any more confused (Because there should be no doubt that misuse of terminology can cause confusion). The Load event would fire your code in the event procedure if there were a load event for subforms.

Subforms are not loaded as such. The report is loaded. The subforms are included as part of the design of the report. Does that explain why it's not running the code?

You need to consider positioning your code somewhere within the Load event of the report I would suggest. Particularly I suspect, because it is relative to the report itself rather than the subform (even though it may deal directly with objects on the subform).

[garfieldsevilla]

thanks- yes, that is certainly what I have observed.

Looking at using a function in the query linked to the subform as a way of controlling output.

[NeoPa]

GarfieldSevilla:
thanks- yes, that is certainly what I have observed.

I take this as a response to my paragraph #2. Fine.

GarfieldSevilla:
Looking at using a function in the query linked to the subform as a way of controlling output.

Is this a question? Maybe a request for more help?

I'm not particularly inclined to spend more effort on a new question when you don't seem to have responded in any way to my previous suggestion in para #3. Not only is it a waste of time if you ignore what I post, but I also have no reason to suppose you understand the thrust of what I'm saying. The question(?) doesn't seem related in any way I can see to what I was suggesting. Maybe you just want to be in control of where we go with this. I don't know. I'm just guessing from what little you've said.

You may want to make yourself clearer. You don't want to go off at a tangent without explaining why if you want me to come with you.

[garfieldsevilla]

hi NeoPa, I understood what you were saying in #2 and decided not to proceed with VBA in the report load control.

Rather, it seemed easier to filter the contents of the subform using a query with a funtion in the criteria (WHERE). So, I removed the VBA from the subform, added some VBA in a public function and a reference to this function in the query. When the query runs, a value is passed from the report and I decide whether or not to filter the record in the public function.

It turns out this query will be useful elsewhere and so is worth having.

The result now appears in a subreport on the report and the report has no VBA in the load control.

I have no idea how to achieve this using VBA in the report load control but have arrived at the required functionality another way.

[NeoPa]

That sounds like you have what you need anyway. Excellent.