can i use VBA to plot line from equation on an existing graph?

[wiklendt]

heya,

what i have now:

a graph which plots the "standard condition scores" for horse health (a comparison of weight vs height and how it relates to the horse's health). i have been able (in report design) to plot the lines for each 'standard' condition score. what i'd like to do now, is plot a given horse(s) weight/height onto the graph to make assessing condition score visual. (i have conglomerated many sources on assessing condition score and none are simple to use, so i want to do this as a nice graphical way to display relative condition of horses to a known standard).

see attached (also shown below) for a screen shots of my graph and data for the standard condition scores.

i was thinking to either:

(i) make the standard graph the 'permanent' set of series (each condition has it's own points) then simply add a second series (or just plot the x,y values via VBA?) to have a single point on the graph for that horse. (perhaps this can be done by having a layered graph, maybe? could cause issues, though if a horse is 'outside' the range....?)

(ii) have the horse's data point as the only series, and via VBA draw on the extra condition score lines (as opposed to keeping the datapoints for each standard of condition score, as i currently have).

i have the equations of the lines for each condition score in the format y=mx+b.

i've been searching for a few days on google and the forum. found a few good references but can't relate these to what i want.

http://www.vb123.com/toolshed/00_docs/spatialaccess.htm
http://www.msaccesstips.com/2007/08/ms-access-and-graph-charts.shtml

 

[Banana]

Just wanted to ask-

Why not just export the data into Excel, automate the Excel to do the charting for you? Excel is so much better and could do the job even more prettier, I'm fairly sure.

 

[wiklendt]

hadn't really thought of that. i suppose it could do a better/prettier job. but i'd first like to find a way to do it in access because:

1) it makes it an 'all-in-one' kinda package

(as opposed to exporting to another application - i intend to distribute this DB in the future as a standalone, i can distribute with 2007 runtime, but can't guarantee they'll have excel)
​

2) i'm already half-way (?) there

3) it's an interesting challenge

(i.e., can it really be that hard to put one more little dot in the middle of the plot? (gasp! i'm a poet! LOL) but maybe we can come up with a clever/intuitive/ingenius solution...)
​

4) i know more about handling access data in access, than handling access data in excel

5) and i have never exported-to/automated excel before... not to mention it would turn the excericse into graphing AND exporting, rather than just a graphing exercise...

in any case i'm still trawling the net for possible solutions/ideas. i think the first i'll try tomorrow (still need to FEED said horses! LOL) is the layered graph idea - i THINK i read a solution like that some time ago, but didn't save it b/c at the time i didn't think i'd need that particular tip! i'm pretty sure it was somewhere here in Access World Forums...

 

[Banana]

wiklendt-

I certainly understand appeal of doing it in one compact package. I'm a kind of "the right tool for the right job" guy, I suppose.

I'm sorry I can't give you specific advices because I've jut never bothered doing anything graphical on Access- long ago concluding that if I was to make a pie chart, I'd have easier time getting it exported to Excel, tell it where to look for the info it needs to build a pie chart and voila! A hot apple pie for everyone!

Best of luck, though- Please do post the solution- I'd like to know myself!

 

[wiklendt]

ok, layering works - in that i can see both graphs, when they are on top of each other (upper graph with transparent background) however, in order to make sure they 'line up' properly, i need to 'standardise' the axes - y-axis is easy, that's just set in the chart options, but x-axis is not settable (via the given options).

i've (only quickly) tried to implement Rachael's manipulation of the graph via VBA with the code found here, but when i use it, the chart that is being manipulated 'disappears' (you can still see the control placeholder, but no axes, points, legends... nothing inside!) i don't use controls as references (like Rachael does in the example) b/c horses don't have infinite growth capability (in Hands high)... tallest horse recorded was in the 1840's at 22 Hh, so i've made this my 'maximum' scale... and hope to do so for the standard condition score graph. once i figure out how NOT to make it disappear!