Unsolved: Manipulate chart object via code (MS Graph) on a FORM (1 Viewer)

[my_thompson]

Unknown if this will be any help, but I too found this issue quite frustrating. I have many records on my main form and wanted a stacked column chart (Modern Chart as opposed to Standard Chart) on the main form to have the same x-axis range for each record. I tried many of the above mentioned property adjustments for the chart (to no avail). Therefore, I found a workaround for my situation.

Lets say my chart runs on the following data, where [RecordNo] is the field linking to the parent form.
[RecordNo] [Year] [yVal] [Colors]

• My chart was therefore setup to be formatted where X:[Year], Y:[yVal], Legend:[Colors]
• As a note, I finally figured out my years were formatted as text instead of numbers. Converting the data to a numeric field solved the issue such that gaps in the data are populated as blank columns (whereas if it is a text field, they are simply plotted as categories with no gaps). While this does not help extend the x-axis beyond the min/max limits of data for any individual [RecordNo], it gave me the idea in the following bullets.
• I created two queries to help populate 'fake' data as an extreme/maximum for each [RecordNo] on the table driving my parent form (lets call the table "tblParent"):
  • qry_xaxis_min <-
    SELECT tblParent.RecordNo, 1980 AS [Year], 0 AS [yVal], "" AS [Colors]
    FROM tblParent
  • qry_xaxis_max <-
    SELECT tblParent.RecordNo, 2020 AS [Year], 0 AS [yVal], "" AS [Colors]
    FROM tblParent
• Then, I ran a union query between my data table, qry_xaxis_min, qry_xaxis_max. This union query is then the new driver of my chart. Every time my form changes to a new record, there will always be some blank data at the year extremes I specified. This comes up in the legend as "<>". Successfully (for my specific scenario), the x-axis is the same for every form record.

While it is clearly not ideal, I just thought I would share in case somehow this helps someone else as it is also fairly simple/effective.