Force MS Graph to retrieve records

[Ian]

I have a microsoft chart embedded in a form, and supply
the SQL string for its rowsource property from VBA.

After setting this I want to go on and perform other
manipulations on the chart, which depend on the data
contained in the chart. The problem is that the chart
takes a finite time to retrieve all its records after
setting the rowsource, during which the VBA code is still
executing and so gets incorrect information on the number
of series and points when it interrogates the incompletely
updated chart.

Is there a way to force Graph to update completely before
continuing with VBA execution, or it there a way in VBA to
test whether the chart has completed updating?

At the moment the only thing I can do is set a timer to
trigger the further manipulation of the chart after a
decent period, which allows the chart to finish updating.
Not very satisfactory!

Thanks

Ian

 

[Van T. Dinh]

Any reason why you cannot set the RowSource correctly first then open the
Chart so that on open, the Chart has the correct RowSource?

I did something similar a while a go and I used a saved Query (but changes
required every time I open the Chart) as the RowSource for the Chart. When
I needed to open the Chart, I used the QueryDef Object in VBA code to modify
the saved Query (so that the Query returns the Records I wanted for the
Chart). After that, I open the Form that contains the Chart and the Chart
has the correct RowSource on opening!

 

[ian]

Any reason why you cannot set the RowSource correctly
first then open the

Chart so that on open, the Chart has the correct

RowSource?

I want users to be able to change the contents of the
graph, repeatedly, by clicking on a series of options and
by setting criteria that determine the records that are
retrieved. Unfortunately this means that I don't know what
the 'correct' rowsource is until the user makes a choice
and so i have to be able to handle it in VBA. I could set
the rowsource using the query def as you suggest, but that
would mean repaetedly closing and reopening the Chart form
every time the user made a change - and also I'd still
want to run code to label the points when the form opened,
which would still run the risk of the rowsource query not
having completed first.
Cheers
Ian

 

[Van T. Dinh]

That was basically my requirements also.

I open a Form "frmPreChart" so that the user can select different options.
Once, he/she selects the required options and click the "Show Chart"
CommandButton, the CommandButton_Click Event modify the SQL String of the
saved Query to select the required Records according to the user's selected
options and THEN open the Form with the Chart which uses the (modified)
saved Query as the RowSource.

Basically, the saved Query is just a name so that I can set it as the
RowSource for the Chart but every time the Chart is required, the
CommandButton_Click Event changes the SQL String of the Query substantially.

If the user wants to change the options, he/she has to come back to the
frmPreChart to select different options.

 

[Guest]

Ian,

I resorted to filling the datasheet using code. So, create the appropriate
recordset and pass it (and a pointer to the graph) to the function shown
below.

Steve

Public Sub FillDataSheet(GraphObject As Object, rs As DAO.Recordset)

'This will fill the datasheet of the Chart
'with data from the recordset
'RowSourceType and RowSource of Graph must be blank

' Use code like the following in your form or report:
' Dim rs As DAO.Recordset
' Dim strSql as String
'
' strSql = "Select * From tblSales2"
' Set rs = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
'
' FillDataSheet Me.Graph0.Object, rs
'
' rs.Close
' Set rs = Nothing

Dim oSheet As Object 'Graph.DataSheet
Dim r As Integer 'row counter
Dim c As Integer 'column counter

Set oSheet = GraphObject.Application.DataSheet

'clear old data
oSheet.Cells.ClearContents

'1st Row Field Names = Legend entries
For c = 1 To rs.Fields.Count
oSheet.Cells(1, c) = rs(c - 1).NAME
Next

'now data rows
r = 2
Do Until rs.EOF
For c = 1 To rs.Fields.Count
oSheet.Cells(r, c) = rs(c - 1).Value
Next
r = r + 1
rs.MoveNext
Loop

GraphObject.Application.Chart.Refresh
GraphObject.Application.Update

Set oSheet = Nothing

End Sub

 

[Guest]

I'll agree it can get bogged down with large datasets.
The following uses the windows clipboard, speedy.

Steve

Public Sub FillDataSheetClip(GraphObject As Object, rs As DAO.Recordset)

'This will fill the datasheet of the Chart
'with data in the recordset using the Clipboard and a tab delimited
string
'RowSourceType and RowSource must be blank

Dim oSheet As Graph.DataSheet

Dim r As Integer 'row counter
Dim c As Integer 'column counter
Dim strArray As String 'used for pasting data to datasheet via clipboard

Set oSheet = GraphObject.Application.DataSheet

'clear old data
oSheet.Cells.ClearContents

'1st Row Field Names = Legend entries
For c = 0 To rs.Fields.Count - 1
strArray = strArray & rs(c).NAME & Chr(9)
Next
strArray = Left(strArray, Len(strArray) - 1) & vbCrLf

'now concatenate tab delim data rows
Do Until rs.EOF
For c = 0 To rs.Fields.Count - 1
strArray = strArray & CStr(rs(c).Value) & Chr(9)
Next
strArray = Left(strArray, Len(strArray) - 1) & vbCrLf
r = r + 1
rs.MoveNext
Loop

'copy the data string to the clipboard
SetClipboard strArray

'Paste clipboard to graph datasheet
oSheet.Cells.Paste


GraphObject.Application.Chart.Refresh
GraphObject.Application.Update


'Done!
Set oSheet = Nothing

End Sub

'paste the following clipboard code in a standard module

Declare Function pas_lstrcpy Lib "kernel32" Alias "lstrcpyA" (ByVal
lpString1 As Any, ByVal lpString2 As Any) As Long
Declare Function pas_GlobalLock Lib "kernel32" Alias "GlobalLock" (ByVal
hMem As Long) As Long
Declare Function pas_GlobalUnlock Lib "kernel32" Alias "GlobalUnlock" (ByVal
hMem As Long) As Long
Declare Function pas_GlobalAlloc Lib "kernel32" Alias "GlobalAlloc" (ByVal
wFlags As Long, ByVal dwBytes As Long) As Long
Declare Function pas_OpenClipboard Lib "user32" Alias "OpenClipboard" (ByVal
hwnd As Long) As Long
Declare Function SetClipboardData Lib "User32.dll" (ByVal wFormat As Long,
ByVal hMem As Long) As Long
Declare Function pas_CloseClipboard Lib "user32" Alias "CloseClipboard" ()
As Long
Declare Function pas_EmptyClipboard Lib "user32" Alias "EmptyClipboard" ()
As Long
Declare Function pas_GetClipboardData Lib "user32" Alias "GetClipboardData"
(ByVal wFormat As Long) As Long


Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As
Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long


Public Const pas_CF_TEXT = 1
Public Const pas_GMEM_MOVEABLE = &H2
Public Const pas_GMEM_ZEROINIT = &H40
Public Const pas_GHND = (pas_GMEM_MOVEABLE Or pas_GMEM_ZEROINIT)
Public Const pas_MAXSIZE = 4096


Public Sub ClearClipboard()
On Error GoTo Err_cmdClip_Click

' Open, Empty and Close Clipboard
' No Clipboard API error handling
Call OpenClipboard(0&)

EmptyClipboard
CloseClipboard


Exit_cmdClip_Click:
Exit Sub

Err_cmdClip_Click:
MsgBox Err.Description
Resume Exit_cmdClip_Click

End Sub

Public Function SetClipboard(MyString As String)

'==========================================================================
'Description : Sets the clipboard contents
'Called By : GatherData()
'Calls : Several API calls
'Parameters : MyString as string - this is what goes into clipboard
'Returns : nothing
'Author : Peter Strong
'Date Created : 29/04/98 11:54:42
'Comments : restructured from MS KB article Q138909

'==========================================================================
On Error GoTo ProcError

Dim strActiveObjectName As String
strActiveObjectName = Application.CurrentObjectName & "SetClipboard"

Dim lngGlobalMemory As Long
Dim lngGlobalMemoryFP As Long
Dim lngClipMemory As Long
Dim lngRetVal As Long

'Allocate moveable global memory
lngGlobalMemory = pas_GlobalAlloc(pas_GHND, Len(MyString) + 1)

' Lock the block to get a far pointer to this memory
lngGlobalMemoryFP = pas_GlobalLock(lngGlobalMemory)

' Copy the string to this global memory
lngGlobalMemoryFP = pas_lstrcpy(lngGlobalMemoryFP, MyString)

' Unlock the memory
If pas_GlobalUnlock(lngGlobalMemory) = 0 Then

' Open the Clipboard to copy data to
If pas_OpenClipboard(0&) <> 0 Then

' Clear the Clipboard
lngRetVal = pas_EmptyClipboard()

' Copy the data to the Clipboard
lngClipMemory = SetClipboardData(pas_CF_TEXT, lngGlobalMemory)
Else
MsgBox "Could not open the Clipboard. Copy aborted.", vbCritical,
"Clipboard Paste"
End If

Else
MsgBox "Could not unlock memory location. Copy aborted.", vbCritical,
"Clipboard Paste"
End If

Exit_SetClipboard:
If pas_CloseClipboard() = 0 Then
MsgBox "Could not close Clipboard.", vbCritical, "Clipboard Paste"
End If
Exit Function

ProcError:
'Error Handler Goes Here
MsgBox "Unexpected error in routine: " & strActiveObjectName & " . Error
code: " & Err & ", " & Error$, 16
Resume Exit_SetClipboard


End Function

 

[ian]

Excellent, thanks - will investigate
Cheers
ian

-----Original Message-----
I'll agree it can get bogged down with large datasets.
The following uses the windows clipboard, speedy.

Steve

....