X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Summary Report, Multiple Charts/graphs With Parameters, Access 2016    
post Sep 12 2017, 07:47 PM

Posts: 31
Joined: 23-May 16


I'd like to ask for some help...not with how to code but more with how to approach a problem. I'd be thankful for any advice I could get.

Here's the problem at hand: I've been tasked with trying to automatically generate what could potentially be hundreds of reports along the lines of client summary reports exported in PDF format. The reports don't really contain any detail themselves...a "report" really is just a container for a series of subreports that are really charts, graphs and tables for a particular client. I've set up the interface to automatically create empty reports (for testing) for individual clients with the appropriate filters. Where I'm stuck is how to go about passing parameters (client and time period) into the charts, graphs and tables contained within.

I don't like MS Graph...I find it very clumsy to use and I couldn't find much documentation online. But I spent the afternoon trying to figure out how to use that tool to create and receive parameters. Didn't have a lot of luck so far but it could be an option.

Late this afternoon I started playing with using an unbound object frame containing an Excel chart. My thinking is that I could programmatically populate the data for each Excel object necessary on the report when it opens. But here's where I'm stuck with this option: the interface I built has a listbox to select one or more clients to generate reports for. How would the report know which client to generate because the code in the interface is tracking which of the listbox.ItemsSelected is the current client that should be generated? If I can solve that I think I could get this to work.

And are there other options that I'm not considering that might be effective?

Thanks for any advice you could provide!
Go to the top of the page
post Sep 13 2017, 03:50 AM

Posts: 73
Joined: 5-August 05
From: Eastbourne, UK

You could use a 'clients selected' work table which you would populate from the list box and include it, using as a filter (JOIN), in the data for the Excel object
Go to the top of the page
post Sep 13 2017, 07:51 AM

UtterAccess VIP
Posts: 8,823
Joined: 10-February 04
From: South Charleston, WV

You might not need a table if you just filter your recordset.

Robert Crouser

Go to the top of the page
post Sep 13 2017, 11:55 AM

Posts: 31
Joined: 23-May 16


I abandoned by MS Graph approach entirely and went with an Excel chart embedded in an unbound OLE container. Realized I could grab the report filter and stick it in the WHERE clause of some SQL and everything would work fine. And it did except...for the life of me I can't figure out how to reference the workbook contained in the OLE control.

Private Sub Report_Open(Cancel As Integer)
Dim GraphQuery As New ADODB.Recordset
Dim CurrentField As ADODB.Field
Dim MSExcel As Excel.Application
Dim PercentLensChart As Object
Dim SQLText As String

' Initialize Variables
SQLText = "SELECT [Lens Types].[Lens Type Description] AS [Lens Type], " _
& "Count([DN Claims Detail].Claim_Number) AS [Claim Count] " _
& "FROM [Lens Types] INNER JOIN [DN Claims Detail] ON " _
& "[Lens Types].[Lens Type Code] = [DN Claims Detail].Lens_Type "
SQLText = SQLText & "WHERE " & Me.Filter & " "
SQLText = SQLText & "GROUP BY [Lens Types].[Lens Type Description] " _
& "HAVING ([Lens Types].[Lens Type Description] Is Not Null) " _
& "ORDER BY Count([DN Claims Detail].Claim_Number) DESC;"

' Initialize Objects
Set PercentLensChart = GetObject("", olePercentLens.Class)
Set MSExcel = PercentLensChart.Application

With GraphQuery
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenDynamic
.Open SQLText

If (Not .BOF) And (Not .EOF) Then
With MSExcel
' Clear Existing Data

' Paste Field Names
For Each CurrentField In GraphQuery.Fields
.ActiveCell.Value = CurrentField.Name
.ActiveCell.Offset(0, 1).Select
Next CurrentField

' Paste Data
.ActiveWorkbook.Worksheets("Data").Range("A2").CopyFromRecordset GraphQuery
End With
End If
End With

' Close & Release Objects

Set GraphQuery = Nothing
Set PercentLensChart = Nothing
Set MSExcel = Nothing
End Sub

This code fails when it hits the line in red...subscript out of range (can't find the worksheet named "Data"). In trying to debug, I put some code in to loop through and msgbox all the names of the activeworkbook and there was one sheet named "Sheet1". The workbook in the OLE control has two sheets, each unoriginally named "Chart" and "Data" respectively. When attempting to reference the workbook in the OLE control (in blue in the text) I seem to be referencing a brand new workbook. Could anyone please offer some advice/pointers on where my code is wrong?

Thank you!
Go to the top of the page
post Sep 13 2017, 08:16 PM

UtterAccess VIP
Posts: 8,823
Joined: 10-February 04
From: South Charleston, WV

Are you trying to reference and existing workbook? Where is the code that points to this workbook? Why do you want to open this workbook and then delete data from it?

Robert Crouser

Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    24th January 2018 - 02:29 AM