Issue with Filtering PivotChart Subform by Parameter without VBA

smcclure

New member
Local time
Today, 05:18
Joined
Jun 28, 2013
Messages
5
With regards to Access I'm amateur, but not novice.

I have a pivotchart subform who's Filter Property I've set to:

Code:
[EEIC_ID] In ([Forms]![MainDataControl].[EEICBuffer],0) And 
IIf([Forms]![MainDataControl].[TypeIDBuffer]="",[AG_TYPE_ID] Like "*",[AG_TYPE_ID] In ([Forms]![MainDataControl].[TypeIDBuffer],"")) And 
IIf([Forms]![MainDataControl].[AgencyBuffer]="",[AGENCY_ID] Like "*",[AGENCY_ID] In ([Forms]![MainDataControl].[AgencyBuffer],"")) And 
IIf([Forms]![MainDataControl].[FacilityBuffer]="",[FACILITY_ID] Like "*",[FACILITY_ID] In ([Forms]![MainDataControl].[FacilityBuffer],""))

The issue is that none of the parameters are recognizing the textbox controls even though the references appear to be correct. Per access.mvps.org/access/forms/frm0031.htm, I've attempted several versions of the syntax.

I believe that I've done this successfully in several other forms, though none have been been via a pivotchart. What's more, this exact same filter string worked when the pivotchart was its own popup and the referenced form was a separate window. Once I embedded it (being the pivotchart) as a subform, however, I began to be presented with the "Enter Parameter Value" dialog, even though the control's address had not changed. Note: the form with the embedded pivotchart is separate from the control's parent form.

In essence: the filter property of a pivotchart subform has a parameter that references a control on a seperate popup form, and every one of the syntactual statements I've tried returns an "Enter Parameter Value" dialog (where they did not when the pivotchart was not a subform).

Additional information: I've also attempted to isolate this issue by putting the text box controls on the parent form (rather than the separate popup form) and referencing them via the pivotchart subform, but this provided no resolution (the same thing happened).

I sincerely appreciate any assistance that can be provided, regardless of resolution.

Please note that unless it's a relatively simple change, I'm doing everything i can to avoid modifying the underlying architecture.

Thank you for taking the time to read this post.
 
Last edited:
Update:

I've traced back how far the filtering doesn't work in an effort to localize the error.

If I apply the original string (stated above) to the pivotchart design-view Filter property, the proper chart displays in the pivotchart view.

But if I try to open the exact same pivotchart as a subform, I get the "Enter Parameter Value" on the code that worked previously.

Perhaps I should ask: is there a specific syntax I should be using if referring to a "foreign" form from a subform?
 
Am I so lucky as to have found an issue that no one has a solution for?
 
Post you database with some sample data, (zip it because you haven't post 10 post).
 
Beware: it's a mess.

To recreate the issue: enable macros, open the MainDataControl form and click "view historical". You'll get the enter parameter value dialog. Click okay through to the pivot chart and you'll see it's empty. The form that opened was UtilityChartWindow (it has the embedded pivot chart HistoricalUtilityChargesSubform).

Now, close the emtpy pivot chart, but leave the MainDataControl form loaded. Double click from the forms list HistoricalUtilityChargesSubform and you'll see the properly filtered chart.

I just noticed that the entere parameter dialog does not pop up when macros aren't enabled, so it seems like it must be associated with them. But it's odd that there isn't any code in the UtilityChartWindow form.

Thank you for any help, and my apologies for the mess.
 

Attachments

Sorry - but I'll first have time to look at it tomorrow.
 
The problem is related to the name "HistoricalUtilityChargesSubform" has in the form "UtilityChartWindow".
The correct name is "Utility Consumption Chart" and not "UtilityConsumptionChart", ("Utility Consumption Chart" is the Caption of the form "HistoricalUtilityChargesSubform").
I've attached a picture where you have to change it, (the picture shows the correct name).
 

Attachments

  • CorrectName.jpg
    CorrectName.jpg
    56.4 KB · Views: 134
You're welcome - luck with you project.
 

Users who are viewing this thread

Back
Top Bottom