Graphs In Report Not Working In .accde Version

[ddk]

Using Access 2010 32-bit. I have a report that contains a Graph Chart (class = MSGraph.Chart.8). The reports works fine using the uncompiled .accdb. When I compile to .accde, the report is not rendered...I get an empty report screen as though Access is going to produce the report and nothing happens. I have to terminate Access using the "X" at the top right.

[ddk]

Since posting, I have been able to successfully send the reports as a snap shot (.snp) file without any problems.

This problem is really taking its toll on my project. Any help will be greatly appreciated!

[ddk]

Today's Update. Since using VBA code to modify the min/max values of the axis in the graph works just fine in accdb and not in accde, I placed several "MsgBox's" at various places in the code which display as I execute the .accde version of the app. Since I cannot or do not know how to use the immediate window in an .accde, this was the only way I could think of to determine where in the code the error was occurring.

The error occurs on the following line:

DoCmd.OpenReport strReportName, acViewDesign

I get Err.Number = 7802 Err.Description=The command you specified is not available in an .mde, .accde, or ,ade database.

As much as I find it hard to believe, I must admit defeat in that I CANNOT use VBA code to dynamically change the min/max values of the axis of a chart in an .accde

I am at my wits end!!! D

oes anybody have any advice or example of code that will allow me to dynamically change the min/max values of a chart in an .accde?

[ddk]

Problem solved!!

After countless searches, I stumbled onto a very simple fix submitted by Gunth Ritte on the web site http://www.office-archive.com/6-ms-a...1a6463937e.htm


Here's my solution:

1. Open the report that contains the graph in Design View
2. If Properties is not visible, Right Mouse Click on the "Detail" section and select Properties
3. Mouse click on "Event" tab
4. Select "On Format" property, Select "Event Procedure" from the list box, and then click on Ellipsis (...)
5. Use whatever code is necessary to set up the report, the code to calculate the min and max values. The code I use appears below. Since I am not an expert VBA guy, there may be an easier way to do this but I have solved my problem and am satisfied with this solution.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo Err_Detail_Format

Dim dbs As Database
Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection

Set dbs = CurrentDb()
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset

' A query is used to get min and max values for the scale

rst.Open "qryXYZ_MaxMinPrice", cnn, adOpenStatic, adLockReadOnly

Dim max As Integer
Dim min As Integer
Dim strReportName As String
strReportName = "rptXYZ"

If Not rst.EOF Then
rst.MoveFirst
End If

' Let's assume that the max value returned from the query is 124.2 and the minimum number is 101.5

max = rst!MaxPrice

' Since the max number of the chart scale always has to be larger than the maximum dollar
' figure, always round up to the next "10". The code to do so is explained below
' IIF(x Mod r, (x \ r + 1) * r, (x \ r) * r)
' x = number to round
' r = the nearest number to round to
' ex: 161.25 becomes 170

max = IIf(max Mod 10, (max \ 10 + 1) * 10, (max \ 10) * 10)

'Now check to see if the max is the same as the calculated scale, if so bump up by 10%

If max = rst!MaxPrice Then
Debug.Print " rst!MaxPrice...increase by 10%"
max = max * 1.1
End If

' Now check to see if the increased number is divisible by 10 such as 143. you can now round 143
' DOWN to 140 and still achieve an increase but no more than 10 numbers on the scale

max = Round(max / 10) * 10 ' THE "ROUND" FUNCTION WILL ROUND 143 DOWN TO 140

Debug.Print "new calculated max value for scale = " & max

' now calculated min value

min = rst!MINPrice
min = Int(rst!MINPrice / 10) * 10

Debug.Print "MinimumScale will be set to min price rounded = " & min

'********* HERE IS WHERE THE SCALE IS DYNAMICALLY SET ********************

Me.OLEUnbound0.Axes(xlValue).MaximumScale = max
Me.OLEUnbound0.Axes(xlValue).MinimumScale = min

Exit_Sub: Exit Sub

Err_Detail_Format:
MsgBox ("Error in Detail_Format(). Err.Number=" & Err.Number & " Err.Description=" & Err.Description)
End Sub