Display an ExCel Chart on MS Access form using data from Query to create the Excel chart

[JrMontgom]

I would appreciate some help on creating an Excel chart in a MS Access form. The data would change depending on user input. User input would create a set of x,y data where x = dates and y would indicate currency values. I have tried some code but I get an error. See code below:

    Dim XL As Excel.Application           'Object        ' Excel application
    Dim XLBook As Excel.Workbook         ' Excel workbook
    Dim XLSheet As Excel.Sheets      ' Excel Worksheet
    Dim XLChart As Excel.Chart     ' Excel Chart


    Dim iRow As Integer      ' Index variable for the current Row
    Dim iCol As Integer      ' Index variable for the current Row
    Dim rst As ADODB.Recordset
    Dim strQry As String
    Dim strType As String
    Dim strColName As String
    Dim intYr As Integer
    Dim intNumCols As Integer
    Const cNumRows = 1    ' Number of Series
    'variables defined need to make user chosen
    strType = "DIVCY"
    strColName = "CEF"    'Other choice is Bank codes CEF is the UUA acct
    intYr = 2014


    strQry = "SELECT tblIncome.DateStart, tblIncome.CEFAmt" & _
                    " FROM tblIncomeType INNER JOIN tblIncome ON tblIncomeType.IncomeSourceID = tblIncome.IncomeType" & _
                    " WHERE (((tblIncome.IncomeType) = '" & strType & "') And " & _
                    " ((tblIncomeType.CollectionName) = '" & strColName & "') And " & _
                    " ((Year([DateStart])) = " & intYr & "))" & _
                    " ORDER BY tblIncome.DateStart;"
Debug.Print strQry
'Get the data from the tblIncome using strQry


    Set rst = New ADODB.Recordset
       With rst
            .ActiveConnection = CurrentProject.Connection
            .CursorLocation = adUseClient
            .CursorType = adOpenDynamic
            .LockType = adLockOptimistic
            .Open strQry, Options:=adCmdText
       End With


    'Get the number of columns for graph


    intNumCols = rst.RecordCount     ' Number of points in each Series




'set data into aTemp array
    ReDim aTemp(1 To cNumRows, 1 To intNumCols)


    'Start Excel and create a new workbook
    Set XL = New Excel.Application        '("Excel.application")
    Set XLBook = XL.Workbooks.Add
    Set XLSheet = XLBook.Worksheets.Add


    'Fill the array with data for row and column
      rst.MoveFirst
    For iRow = 1 To cNumRows
       For iCol = 1 To intNumCols
          aTemp(iRow, iCol) = rst!CEFAmt
          rst.MoveNext
       Next iCol
    Next iRow


    Debug.Print XLBook.Sheets.Count

'THIS LINE IS WHERE ERROR OCCURS 
    XLSheet.Range("A1").Resize(cNumRows, intNumCols).Value = aTemp
                'METHOD OR DATA MEMBER NOT FOUND on .Range


    'Add a chart object to the first worksheet
   
    Set Chart = XLSheet.ChartObjects.Add(50, 40, 300, 200).Chart
    Chart.SetSourceData Source:=XLSheet.Range("A1").Resize(cNumRows, intNumCols)


    ' Make Excel Visible:
    XL.Visible = True


    XL.UserControl = True


    'set series 1 to "Dividends CY"


    oChart.HasTitle = True
    'Debug.Print oChart.
    'oChart.ChartTitle = "Dividends for " & CStr(intYr)

Any help would be appreciated. Thanks in advance

[ranman256]

You load charts in reports, not forms.
Just open the report.

[JrMontgom]

Thanks for the quick reply but I still can't figure out how to create an Excel chart/graph in VBA and then put in a report much less a form. I think you can do this as I have stumbled through putting up a PowerPoint presentation in an unbound frame but I can't figure out the error mentioned in the initial code I included in my original question. Are you sure you can't put a chart/graph in a form? Any help wuld be appreciated regarding my initial question as to what caused the error??? Once again thanks for the quick reply.