Strange graph behavior (xy scatter): Does not plot on x axis correctly

[emorris1000]

Having some strange graph behavior in a form of mine and it's driving me a bit nutty. Graphs in Access have always been sort of the bane of my existence, and often I've just written off the graph.exe crashes as something I can't fix, but this one has to be fixable.

Here's the issue. I have a graph based around a query tied to a listbox. When a value is selected it effects what is graphed. It does this through simple sql that says :

....Like """ & Nz(Me.LBox) & """"

I load the sql in VBA as an after update event. This works fine. The graph it loads is a transposed/crostabbed x/y graph. where the x values are in column 1 and the y values are in the rest of the columns (with column headers being the different series.



The problem is that occasionally when I select a new entry and it draws a graph the x axis is wrong. Sometimes the x values will be the same as the y values (so I just get a line) and other times there isn't really a rhyme or reason I can make sense of with it. Here's the goofy thing. If I double click the graph to jump into the graph.exe tool to actually look at the data all of the data is completely normal. And I can fix the problem by selecting "by row" and then "by column" in the standard toolbar. It starts as by column so I'm just kind of....well I'm turning it off and on again? And that fixes it, no problem.

But this is not acceptable for users. I always disable the ability to get into the graph.exe tool for users and I don't want them to experience this. So I either need to figure out what's causing this and fix it, or I need to figure out a programatic way to cycle byrow->bycolumn to fix it.

Anyone ever experienced this or had this issue? Any thoughts on how to fix it? There isn't a ton of documentation on working with graphs in VBA and it's been years since I have screwed with that.

[Micron]

No I haven't experienced this; mostly because I always avoided Access graphs like the plague. You don't say much about where the graph is or how it's presented, so here's a guess. You either have it on the same form as the listbox, or you are opening another form to show it (which probably means it shows OK the first time). However, when a new listbox item is chosen, you're either expecting the graph on that form, or the other one, to "refresh" the image/graph. Either way, it's probably holding on to old values and isn't handling the over-write very well. You could try .Refresh or .Requery of the form that the graph is on (.Repaint might do it) OR, if the graph is on a different form, close it and immediately reopen it with the new data source. If it's not and the other suggestion doesn't work, copy the graph control to a new form and try closing/opening that form after a listbox change. If you end up doing that, you'll have to bypass closing the form if it's not open (because it's the first time) otherwise you'll get an error.

If CurrentProject.AllForms ("frmMyGraphForm").IsLoaded Then DoCmd.Close acForm, "frmMyGraphForm"

I might not have that syntax quite right.

[emorris1000]

The graph is loaded on the same form. I do have a requery built into the listbox afterupdate event, and it's not carrying over old values. When I open up the graph through the double-click option you can actually see the data set being graphed, and the dataset is correct, and properly transposed, just the graph isn't correct. This can be resolved by switching to by row and back to by column, but that isn't an option for users.

If there is a programatic way of screwing with the by row and by column nature of the graph I could build that into the after update event to kind of jimmy the graph to the right state.

[Micron]

Is this graph in an Active x control like Microsoft Graph?
It's not clear what it is you're requerying - the form, listbox or graph control. AFAIK, the MG control does not behave the same as any regular form control. It's a type of OLE provider and can get out of sync. Suggest you requery (or whatever the appropriate method is) the control if you have not already tried that.

Here's something on how to play with the control properties although it's for Word, it deals with the graph control.
https://support.microsoft.com/en-us/kb/244589

Also, there can be a timing issue between form or report events and the control, though I thought this had more to do with older Office versions. A Do Events Loop was suggested, but the graph was not on the same form.

[emorris1000]

Is this graph in an Active x control like Microsoft Graph?
It's not clear what it is you're requerying - the form, listbox or graph control. AFAIK, the MG control does not behave the same as any regular form control. It's a type of OLE provider and can get out of sync. Suggest you requery (or whatever the appropriate method is) the control if you have not already tried that.

Sorry, to clarify I am requerying the graph object itself each time the listbox gets updated.

[Micron]

Then I got nothing more to offer than the link or the notion about putting the graph on it's own form as noted. Sorry.

[emorris1000]

Then I got nothing more to offer than the link or the notion about putting the graph on it's own form as noted. Sorry.

No problem, I appreciate the help.