VBA in Access or Export to Excel for Graphs

[octsim]

Hey there,

I'm trying to create graphs using 2 selectable fields in a table as a source and automate that process. I want a user to be able to select the x and y axis (fields in a table), press on a button and always create for all data there is in those fields a graph of the same type (point graph without connecting lines). I've seen the graph control and I've tried to pass that control the source for it but I can't figure out how it works. Is it even possible to do it or would it be easier to just select the 2 fields I want to display as a graph, save those in a new Excel spreadsheet and automate the graph in Excel?

Thanks in advance!



octsim

[June7]

VBA code can set the RowSource property of the graph. This is where the x and y fields are defined.

[octsim]

I've seen that setting, the problem is that I can't just put an empty unbound graph control into my form. Do I need to load a bound graph control and then change the values for the x and y axis?
How do I assign x and y if theres only one RowSource?
I've created 2 string variables x_axis and y_axis which are assigned the name of the desired fields in a table. I want to select the field names through 2 list controls

Dim x_axis As String 
Dim y_axis As String
Dim x_full As String
Dim y_full As String

x_axis = Me.list_x.Value
y_axis = Me.list_y.Value
x_full = "tablename." + x_axis
y_full = "tablename." + y_axis

graph_ctrl.RowSource = x_full, y_full

That's obviously not working because I don't understand the format of RowSource.
Any help is appreciated!

[June7]

The RowSource would be an SQL statement.

On second thought, might not need VBA to set the RowSource. Create the graph with references using any xy pair you want then replace the field names with references to the listboxes, something like:

SELECT "[" & Me.list_x & "]" As X , "[" & Me.list_y & "]" As Y FROM tablename;

I am using the [] in case your field names have spaces or special characters or are reserved words. If none of those pertain, can remove the []'s and the &'s.

Might need VBA to refresh the form and the graph: Me.Refresh

If that RowSource doesn't work then can look at VBA constructing the SQL statement and setting RowSource.

graph_ctrl.RowSource = "SELECT [" & Me.list_x & "] As X , [" & Me.list_y & "] As Y FROM tablename;"
Me.Refresh

[octsim]

Hey, thanks, I'm getting somewhere with that but not quite there. All it does is copy and paste the name of my field a couple times but it does not assign values from my table. I have the same problem trying to select fields for a query (see other post: https://www.accessforums.net/queries...orm-39068.html) - I think it's the same problem. The graph is not working, all it does is use the field name as a value multiple times. Same happens in my query on the other post - I want to set the field name through a combo box and it does read it's content but it's not transferred as a field but as just a string. Any ideas?

[June7]

AFAIK, a saved Access query object cannot be set up to select different fields dynamically (I have tried). I had hoped the SQL statement in RowSource could be (have not tried).

So back to using VBA to construct the SQL statement and set RowSource property.

VBA can also modify a saved Access query object using QueryDefs collection.