Dynamically Altering Graph Y-Axis Range & Major Unit

[f15e]

I have several graphs used for charting structural weights. Each of the graphs are different in that as the user enters weight data, the y-axis max and min may change and the difference b/t the max and min can range anywhere from 100 to 1500 or so. I am able to dynamically adjust the y-axis max and min but it's the major unit that is giving me problems. For example, sometimes the graph goes outside of the y-axis max or min and I assume that 's b/c the major unit isn't divisible by the max and min difference. Say the graph ranges from 654 to 322 and the max is set for 660 and the min is set for 310, the min axis may be correct but the max would show as 650, for example, instead of 660 and the graph goes outside of the max bounds.



Is there any algorithm or other way to get my graphs to lie between the y-axis min and max without having the graph go outside of the max/min bounds ( or without it having too large of a y-axis range o where the graph is scrunched together in the middle section of the y-axis with a lot of space above or beneath the graph. I hope I've explained this well enough.

[June7]

Here is example from my db that sets range and major and minor units.

Sub FormatProcGraph(strObject As String, strLabNum As String, booMetric As Boolean, dblOMC, dblMDD)
'format Proctor graph on form and report
Dim obj As Object
Dim gc As Object
Dim intMaxD As Integer
Dim intMinD As Integer
Dim intM As Integer
If strObject Like "Lab*" Then
    Set obj = Reports(strObject)
Else
    Set obj = Forms(strObject).Controls("ctrProctor").Form
End If
Set gc = obj("gphDensity")
intMaxD = Nz(Int(dblMDD), 0)
intMinD = Nz(Int(DMin("D", "GraphProctor", "Source='Lab' AND LabNum='" & strLabNum & "'")), 0)
With gc
'format y axis scale
If booMetric = True Then
    intMaxD = intMaxD + IIf(intMaxD - intMinD < 125, 50, IIf(intMaxD - intMinD < 250, 25, 0))
    .Axes(xlValue).MaximumScale = intMaxD
    .Axes(xlValue).MinimumScale = intMaxD - 250
    .Axes(xlValue).MajorUnit = 50
    .Axes(xlValue).MinorUnit = 10
Else
    intMaxD = intMaxD + IIf(intMaxD - intMinD < 6, 2, IIf(intMaxD - intMinD < 10, 1, 0))
    .Axes(xlValue).MaximumScale = intMaxD
    .Axes(xlValue).MinimumScale = intMaxD - 10
    .Axes(xlValue).MajorUnit = 2
    .Axes(xlValue).MinorUnit = 0.4
End If
'format x axis scale
If Int(dblOMC) > 6 Then
    intM = Int(dblOMC) + IIf(dblOMC - Int(dblOMC) >= 0.5, 1, 0)
    .Axes(xlCategory).MaximumScale = intM + 7
    .Axes(xlCategory).MinimumScale = intM - 5
End If
'y axis label
.Axes(xlValue, xlPrimary).HasTitle = True
If booMetric = True Then
    .Axes(xlValue, xlPrimary).AxisTitle.Text = "Dry Density, kg/cu.m"
End If
End With
End Sub

[f15e]

Thanks for the info June7. That helped. You have been very helpful and I really appreciate it.

I have one other question since you seem to be an expert in Access and DB in general. This question is different from the original one I posted but instead of creating a brand new thread, I thought I would just append it to this one.

After a user enters structural weight data into the main form and then is saved to it's table (struct_tbl_1), the associated graph updates correctly but when I close that form and open the graph in design view, the graph is as it originally was before the user enter new structural weight data. The graph doesn't get updated to incorporate the new data entered. I have attached screen shots for a better understanding. If the info here is not sufficient, please let me know.

Graph not updating.pdf

Below is code in the 'Save' button within the main form:
-------------------------------------------------------------------------------------------
' Get name of selected graph (e.g. Bridge_Graph_ST)
graph_name = Me(selected_graph).Report.Get_Graph_Name()

Set rpt_obj = Me(selected_graph)
Set grph_obj = rpt_obj(graph_name)

DoCmd.OpenReport ReportName:=selected_graph, View:=acViewPreview, WindowMode:=acHidden

' Call to function to get the max and min value from within the struct_tbl_1 table
y_axis_max = Me(selected_graph).Report.Current_Max_Val(Wt_Hist_ Tbl_Name, Selected_UoM, "max")
y_axis_min = Me(selected_graph).Report.Current_Min_Val(Wt_Hist_ Tbl_Name, Selected_UoM, "min")

grph_obj.Axes(xlValue).MaximumScale = y_axis_max
grph_obj.Axes(xlValue).MinimumScale = y_axis_min

DoCmd.Save acReport, selected_graph
DoCmd.Close acReport, selected_graph
-------------------------------------------------------------------------------------------

[June7]

Doesn't really matter what the graph shows when in design view as long as it correctly displays data in normal view.

How do you filter the graph RowSource property to display desired records? Here is example from my db:

SELECT [DropNum], CumulativePenMM FROM [dcpfiledata] WHERE [HoleNum] = [Hole];

HoleNum is a field on the form or report so the graph synchronizes with the form/report record.

[f15e]

The table name is Bridge_Gross_Wt_Hist_Tbl. Below is how I filter the RowSource for one of my graphs. Same applies for the other graphs. Bridge_Gross_Wt_Hist_Tbl has 15 different fields. 5 are the Short Ton unit, 5 are Metric Ton, and 5 are kip unit. The user selects the area, i.e. table, and the unit and the graph is displayed. The graph shows correctly during run-time but the problem I have is that I have a 'Save to JPG' and "Print Graph" button where the user can save the selected graph to .jpg, or print it, but the saved jpg or printed graph isn't correct. It is showing as number 1 or 3 in the attachment from my previous entry instead of 2. This is why I need the actual graph object to update as data is added to the table.

RowSource:
SELECT Bridge_Gross_Wt_Hist_Tbl.REVISION, Bridge_Gross_Wt_Hist_Tbl.[LOAD-OUT (ST)], Bridge_Gross_Wt_Hist_Tbl.[TRANSPORT (ST)], Bridge_Gross_Wt_Hist_Tbl.[LIFT (INSTALLATION) - (ST)], Bridge_Gross_Wt_Hist_Tbl.[LIFT (TRANSFER) - (ST)], Bridge_Gross_Wt_Hist_Tbl.[IN-PLACE OPERATING (ST)] FROM Bridge_Gross_Wt_Hist_Tbl GROUP BY Bridge_Gross_Wt_Hist_Tbl.REVISION, Bridge_Gross_Wt_Hist_Tbl.[LOAD-OUT (ST)], Bridge_Gross_Wt_Hist_Tbl.[TRANSPORT (ST)], Bridge_Gross_Wt_Hist_Tbl.[LIFT (INSTALLATION) - (ST)], Bridge_Gross_Wt_Hist_Tbl.[LIFT (TRANSFER) - (ST)], Bridge_Gross_Wt_Hist_Tbl.[IN-PLACE OPERATING (ST)];

[f15e]

Update to my last entry. When I click the print or save to jpg button, everything shows correctly except for the y-axis max and min. The y-axis max and min are not updating with the rest of the graph. As mentioned earlier, I am dynamically changing the axis max and min values based on the latest user weight entries. Does anyone know why the y-axis is not updating?

[June7]

Forms are not intended to be printed, reports are. The code to modify graph must be run again for the print (to printer or PDF) and this does not happen with form print because there is no event for that. The code to modify graph on report must be in Format event of the section the graph is in. The procedure I posted is called by my report Detail section Format event. Format events only trigger for printing or Print Preview, not Report View.

Print a report instead and see if the output is correct.

[f15e]

I finally got my graphs to print correctly. You were correct that I needed to use Detail_Format. Detail_Print also works. I'm not sure which is better but they both seem to work when printing a graph. Thanks for you help.

As for exporting a graph to a .jpg file, do I need to use some other event to get the jpg to show correctly if a user enters new data the graph, saves it and then exports as .jpg? Detail_Print and Format do not seem to work b/c I assume I'm not really printing the graph and instead saving it as a picture. Currently when I click my Export to JPG button (after user enters new graph data and saved to corresponding table), the exported file is NOT displaying the newly entered data portion of the graph and instead shows as the older version.

[June7]

So you are printing a report instead of form?

I've never output to jpg, only pdf.

Exactly how are you doing the 'export' - right click shortcut menu? I expect this does not trigger the OnFormat or OnPrint events.

[f15e]

Yes. The graphs are on reports. I call the code below which is located in a module. I use a button which when clicked calls 'Output_Chart_as_JPG' and opens the Save dialog and saves the jpg to my desktop or where ever. No the OnFormat or OnPrint does not trigger. The code below works but doesn't show the correct graph. Going back to what I mentioned previously about the version of the graph in the design view as opposed to the view when the code is actually running, this is what is happening. See attached.
Graph export to jpg.pdf

'************************************************* **********************************
Public Sub Output_Chart_as_JPG(Report_Name As String, graph_name As String)

Dim getUserDesktop As String

Set fDialog = Application.FileDialog(msoFileDialogSaveAs)

With fDialog
Call Forms("Graphs_Tbls_History_Frm").Set_Graph
.AllowMultiSelect = False
.Title = "Select the location you would like to save your .jpg file."
user_profile = LCase(Environ("UserName"))

getUserDesktop = GetDesktop

.InitialFileName = getUserDesktop & "" & Report_Name & ".jpg"
.Show

If (.SelectedItems.Count = 0) Then
MsgBox "The export operation has been cancelled.", vbInformation
Else
If intChoice <> -1 Then

DoCmd.OpenReport Report_Name, acViewReport
Reports(Report_Name).Controls(graph_name).Enabled = True
Reports(Report_Name).Controls(graph_name).SetFocus


Set grph = Reports(Report_Name).Controls(graph_name)

'get the file path selected by the user
ExportFile = Application.FileDialog(msoFileDialogSaveAs).Select edItems(1)

If ExportFile <> "" Then
grph.Export Filename:=ExportFile, FilterName:="JPG"

DoCmd.Close acReport, Report_Name

'displays the result in a message box
Call MsgBox("The .jpg file has been saved to the following location:" & vbCrLf & vbCrLf & ExportFile, vbInformation)
End If

End If

End If

End With

'Set the object variable to Nothing.
Set fDialog = Nothing

ExportFile = ""
user_profile = ""
Report_Name = ""
graph_name = ""

End Sub

'************************************************* **********************************

[June7]

Well, if export does not trigger OnFormat or OnPrint then export is not a viable approach.