Populating a Chart with data from a VBA Array

[Markb384]

Hi guys



I recently created a pretty big spreadsheet for analysing test results but it was full of small bugs etc. as effectively I was trying to recreate database functions within it. So I have started again using Access instead (of which I am a noob, but I am learning quickly) and it is going at full steam ahead at the moment. There is one small point I am struggling with.

My results table is structured based on the number of spots seen for a selection of groups from A - M (e.g. group A, 4 spots where seen, group B, 6 spots and so on) along with some information about some other parameters. Each group is subtly different from its neighbour. The number of spots is then compared to a "baseline" value to test for drift. There are three levels of data, a reference, a baseline (for comparing to the reference) and general (which is compared to the baseline).

The results require analysing the number of spots seen, correcting for group etc. and this is all incredibly easy within VBA (largely as I have similar code already written).

In my excel version, I managed to create a chart that was populated with an array of data using VBA. This worked perfectly, and I could even change the legend to reference, baseline etc. depending on the data being used at that instance. I am trying to recreate something similar within Access but I am stumbling at the first hurdle. The charts within the Report are based upon data either from a Table or a Query, neither of which are applicable in this case. Is there a way to set the values within the chart manually according to an array I will be creating within VBA? If this isn't possible, what is the best way around this?

[June7]

Only way I know is to set the RowSource property.

Could construct an SQL statement to set the RowSource of graph. The SQL has to reference an actual table as the source even if the constructed fields and values don't actually exist in table. This would result in a single record or through use of UNION construct, up to 50 records.

An alternative is to write data to a 'temp' table and use that as the graph RowSource.

[Markb384]

Hi June7

I think a temporary table is probably the easiest way forward I think, largely as I still don't truly understand what I am doing within SQL

[June7]

Code options:

1. open a recordset object of the temp table and add records to the recordset and update the table - this requires a SELECT sql statement to open recordset

2. insert records directly to temp table with INSERT sql action

3. open a form bound to the temp table and use code to move to new record and input data from the array

[Markb384]

Thank you for your reply June, but due to time constraints on this project, my manager has suggested that the graphs take a backseat for now as they don't particularly add anything to the result (apart from looking pretty) and can always be generated by a spreadsheet on a case-by-case basis if ever required.

Maybe once I have finished with the rest of the db, I will return to them.

Once again, thank you.