Prevent charts from "loading" on a form

[geomorph]

I have a form with 11 tabbed pages, each with it's own chart where data is derived from individual SQLs. Problem is that all 11 charts are loaded/activated/drawn or whatever, every time the form opens or update events I've created to change the charts' data occur. Needless to say, this takes a couple of seconds and is distracting to users. Is there a way to prevent all but one of these charts from loading/activating/drawing? I list these verbs together because I cannot determine what event is occurring that causes the time delay. It's not any one of the above mentioned (nor is it the SQLs) because I have tried trapping them in code to no avail. I assume it has something to do with OLE activation, but not sure exactly what.

 

[Trevil]

DANG! I can't find the thread where we covered a similar problem (within the past several weeks!) Although that problem was solved by changing from an 'Image' control to a 'OLD Bound' object, I think you want to 'unbind' all of your controls until you are ready to view them. IF you have 11 tabs, then unbind 2 - 11. Then when you click on the tab, go ahead and link the corresponding image for that tab.

"Hmmm, it worked when I tested it....

 

[geomorph]

Thanks for the reply. I looked for the thread you refer to and couldn't find it either.
By "unbind" do you mean clear the RowSource? I tried that, and it did not work.

 

[Trevil]

What type of control? I assume it is an OLE Bound? And is you chart Linked or embedded? The following is the code that I had placed into the Form_Current event.
Me.OLEBound2.OLETypeAllowed = acOLELinked
Me.OLEBound2.SourceDoc = Me!ImagePath
Me.OLEBound2.Action = acOLECreateLink
Me.OLEBound2.SizeMode = acOLESizeZoom


"Hmmm, it worked when I tested it....

 

[geomorph]

It's embedded and bound. Should it be linked?

 

[Trevil]

I was using a "Bound Object Frame" control, or are you using an "Unbound Object Frame"? PLEASE let me know as the solution will differ.

What is the origin of these charts (Excel?)? And how are you changing them (code?)



"Hmmm, it worked when I tested it....

 

[geomorph]

I inserted the charts using Insert on the menu and followed the wizard, which automatically creates a bound object (I believe) to fields in a query I selected. The charts are Microsoft Graph Chart (MSGraph.Chart.8), embedded, linked with fields on the main form. The data changes based on SQLs tied to update events for an option group which is indentical on each tab page. Here's an example of code for update event on an option group for a single page:

'Set variables for options selection, species, and
'continuous parameter type

ContVar = "PatchSize"
Opt = Me![grpRespType1]
Spp = Forms![frmSpeciesList]![strSppSeas]

'Set Response Type filter based on which
'option button is selected

If Opt = 1 Then
RespType = "Nonlinear"
strTitle = "Nonlinear Response Type"
ElseIf Opt = 2 Then
RespType = "Linear Positive"
strTitle = "Linear Positive Response Type"
ElseIf Opt = 3 Then
RespType = "Linear Negative"
strTitle = "Linear Negative Response Type"
End If

Me.Filter = "[strSppSeas]=" & "'" & Spp & "'" _
& " AND [strContVar]=" & "'" & ContVar & "'" _
& " AND [strRespType]=" & "'" & RespType & "'"
Me.FilterOn = True

'SQL that extracts the data from qryContinuous to place in the
'graph based on the Response Type selection made by the user

strPSSQL = "SELECT qryContinuous.strSppSeas, " _
& "qryContinuous.intMarLow AS [Marginal Low], " _
& "qryContinuous.intSuitLow AS [Suitable Low], " _
& "qryContinuous.intOptLow AS [Optimal Low], " _
& "qryContinuous.intOptHigh AS [Optimal High], " _
& "qryContinuous.intSuitHigh AS [Suitable High], " _
& "qryContinuous.intMarHigh AS [Marginal High] " _
& "FROM qryContinuous " _
& "WHERE (((qryContinuous.strSppSeas)=" & "'" & Spp & "') " _
& "AND ((qryContinuous.strRespType)=" & "'" & RespType & "') " _
& "AND ((qryContinuous.strContVar)='PatchSize'));"

'Set the RowSource to the above SQL and
'change the chart's title depending on which Response Type is selected

With Me![chtPS]
.RowSource = strPSSQL
.HasTitle = True
.ChartTitle.Text = strTitle
.Refresh
End With

I also run SQLs for each chart when the form opens after clicking a button on another form. This is so when the form with all the charts on it first opens, the user will have a default view based on a default option selected.
Thanks for the input

 

[Trevil]

Have you tried clearing the properties for "Link Child Fields" and "Link Master Fields"?

Also, the property "Update Options" can be set to "Manual" so the chart is not updated automatically.

Finally, you say you run SQL's for each chart when the form opens .... why not move individual SQL code so it is executed when user goes to a tab?

Hope this helps.

"Hmmm, it worked when I tested it....

 

[geomorph]

Tried clearing the link fields and that did not help. I tried to change the update options but was not able to simply from the form properties. I will try with code later. The problem with assigning SQLs only to tabs is that I get errors when the form opens and a chart has no data associated with it:
"An error occurred while sending data to the OLE server (the application used to create the object)". I get this error for each chart that has no SQL set for it.
I think I may go a different route and have the charts pop up in a separate form when the user puts values in the appropriate fields and clicks a button. This will allow me to isolate chart creation and let the user decide if they want to see a chart or not.
Thanks for all the help

 

[Trevil]

I would need to play around with multiple tabs, but I did receive the same error you did and was able to get around it.

Here's something else to try:
I changed the design view "Row Source Type" to "List" and then set "Row Source" to "1". (That's got to be faster than any query running).

Then in code, for the "Form_Current" event, I set "Row Source" to my SQL string, then set "Row Source Type" to "Table/Query" NOTE** Must be done in that order to avoid an error.

After that code ran, the graph was updated.

Hope this helps.

"Hmmm, it worked when I tested it....