Embed a generated Excel chart in Access

Question

The appearance of the ordinary charts widget of MS Access 2010 is not very attractive.

Is it possible (and how?) to embed the rather attractive Excel charts in Access and fill them with data from a query (dynamically)?

PS:

Because I want to update the chart depending on user input the use of pivot-charts is not possible.

Answer 1

Some notes on using a chart

Sub OpenMyChart()
''You could do this part without code, but let use say you want VBA
sSQL = "SELECT Table1.AText AS ACategory, Table1.ANumber AS AData, " _
     & "Table1.ADate AS AFilter, Table1.ATime AS ASeries " _
     & "FROM Table1 WHERE Table1.ADate=#1/20/2012#"

''This is the query that my Chart form uses
CurrentDb.QueryDefs("Chart").SQL = sSQL

''You can use a Where statement for opening the form, too
DoCmd.OpenForm "Chart", acFormPivotChart, , "ACategory='Bob'"
End Sub

Two other approaches using a similar set up with a subform.

/1. Use link child and master fields

The link master fields are set to the names of the listbox controls and the link child fields are set to the relevant fields for the chart:

Link Master Field: List1;List2
Link Child Field: AFilter;ACategory

Clicking the relevant control redraws the chart.

/2. Use a query and force a redraw:

Private Sub List1_Click()
sSQL = "SELECT Table1.AText AS ACategory, Table1.ANumber AS AData, " _
     & "Table1.ADate AS AFilter, Table1.ATime AS ASeries " _
     & "FROM Table1 WHERE Table1.ADate=#" _
     & Format(Me.List1, "yyyy/mm/dd") & "#"1/13/2013#"

''This is the query that my Chart form uses
CurrentDb.QueryDefs("Chart").SQL = sSQL

''Chart is the name of the subform control, and confusingly, 
''the name of the embedded form.
Me.Chart.SourceObject = "Chart"
End Sub