Requery Subform w/ Chart

[jas580]

Hi,

I have two subforms and a bound text box outside the subforms, where the
following process happens:

1. User selects a row in Subform A
2. The selected row has an ID#. If the user selects a different row, the
text box ID# changes.
3. Subform B has a graph based on a query. That query uses the ID# in the
text box as a criteria filter.

Now my problem:

I can't get Subform B and the chart to requery when the user selects a
different row. The text box and the underlying query adjusts when I change
row selection, but not the subform and chart. I thought I could just put a
"Me.SubformB.Requery" in the After Update event for the text box, but it
doesn't work. The strange thing is I tried adding a command button with that
same code and it refreshes the chart perfectly. However, I don't want the
button. I don't understand what the difference is between the two that would
allow the code to work for the button, but not the text box.

Thanks for the help!

-Jeff

 

[Rob Parker]

I think there's two things that will stop this from working.

First, the AfterUpdate event for the textbox ID# on subform A may not be
occuring - it won't, for instance, occur if you are using the Current event
of that subform to put the value from the selected row into it. And, if
that's what you are doing, you can instead use the Current event for subform
A to force the requery.

Second, if you are using an event from subform A to force a requery of
subform B, you need to use syntax of:
Me.Parent.SubFormB.Form.Requery
where SubFormB is the name of the subform control containing subform B in
the main form. This will force the graph to update if it is linked to
subform B's recordsource; if it is unlinked then you will need to requery
the chart object on subform B itself:
Me.Parent.SubFormB.Form.ChartControlName.Requery

HTH,

Rob

 

[jas580]

Hi Rob,

I've tried both of the syntax suggestions and I keep getting "run-time error
2455: You entered an expression that has an invalid reference to the property
Form/Report." Also, I noticed that when I'm in VB, subform B is not listed
in the "List Properties/Methods" dropdown.

Any other thoughts?

Thanks for the help,

Jeff

 

[jas580]

Hi Rob,

I just noticed something new. Using the syntax:
"Me.Parent.SubformB.Form.Requery", I the following happens...

-Attempt to open form
-Run-time error pops up
-Click "Debug"
-VB Editor opens
-Close VB Editor immediately and click "Close & Stop Debugger" when prompted
-The master form opens and your solution works perfectly!

I don't understand what is going on. Hopefully this info is useful...

Thanks,

Jeff

 

[Rob Parker]

Hi Jeff,

In response to both your follow-up posts: I'm not sure exactly what's going
on in your situation, but it's possible that you were using the subform
object's name, rather than the subform control's name; if these are
different, then you'll get a RTE, such as you described in your first
follow-up.

Also in response to your first follow-up: I don't understand what you mean
by the "List Properties/Methods" dropdown in the VBA editor. If you mean
the left-most drop-down above the code window, which shows all the object in
the module which you are currently editing, then subform B will not appear
when you're editing code in subform A, since it's not in subform A - it's in
subform A's parent form. And that's why you use the Me.Parent.... syntax.

As for the problem you describe below - I've no idea what's happening. I've
never come across something which will give a RTE which can be cured by
taking no action except to open and close the VBA editor. Perhaps there's
some corruption - have you tried a compact and repair? Does the code
compile correctly (from the Debug menu)?

Rob