doevents doesnt!

[OPMCoordinator]

i have noticed others with similar problems to my own and wonder if someone out there can help.

i have a form is use to generate a graph. there are multiple options the user can choose to construct the graph. depending on the options chosen, the rowsource sql changes as well as the style of graph. i then use a button to print out the form with the graph and criteria selections as per the screen (i know that is bad practice but it has caused no real problems in itself thus far).

there is also a little button that saves the criteria (choices) to a table for automatic report runs.

i have a module that then opens the table and the form, applies the stored choices to the form one at a time and tells the graph to update and print.

problem: it takes a while to update the graph on some sets of criteria and the code runs away on itself giving totally bogus information on the graphs.

Solution 1: a timer. i have managed to get a timer working (needs a pause of 45 secs on the larger data sets, 5-10 on the smaller sets) but that leaves the 'puter sitting idle at times doing nothing but ticking off the counter seconds. with up to 100 graphs at a shot, we are talking lots of time doing nothing!

Solution 2: Doevents. i put this in after the .graph.requery bit but it does not seem to work. using the timer solution, the hourglass comes up (form is visible so i can see that all is ok) while the graph is updating, inferring that their is something going on to yeild the processor to as doevents is suppoed to do. when using doevents, the graph appears to be updating independantly of what else is going on in the form ie iterating through the table, updating the controls and printing.

it gives appearance that every second graph is printed twice, but is more likely a timing issue between when it finishes updating and which record of the table happens to be printing at that point in time.

any suggestions?

Mike C

 

[Fizzio]

Post the snippet of code for the graph updating and displaying.

 

[OPMCoordinator]

Heres the lot!
Sub Multiprint()
'Define Variables
Dim db As DAO.Database
Dim frm As Form
Dim List As DAO.Recordset
Dim FinishDate As Date
Dim Periodd As String



'Set Variables
Set db = CurrentDb()

'Weekly or monhtly graphs
Periodd = InputBox("(W)eekly or (M)onthly?")
If Periodd = "w" Then
Set List = db.OpenRecordset("multireport", dbOpenTable, dbReadOnly)
FinishDate = InputBox("Enter the last day of the last full week")
ElseIf Periodd = "m" Then
Set List = db.OpenRecordset("multimonth", dbOpenTable, dbReadOnly)
FinishDate = InputBox("Enter the last day of the last full month")
Else
Exit Sub
End If

'Open graph form
DoCmd.OpenForm "main page"

'Set initial variables just to be sure
[Forms]![main page]!mach = ""
[Forms]![main page]!sd = ""
[Forms]![main page]!fd = FinishDate
[Forms]![main page]!group = 2
[Forms]![main page]!Reas = ""
[Forms]![main page]!category = 2
[Forms]![main page]!Frame44 = 2
[Forms]![main page]!Frame54 = 1
[Forms]![main page]!Reas.Visible = false
[Forms]![main page]!Text63.Visible = True
'take the info in the table and for each record...
Do Until List.EOF

'Use the form
With [Forms]![main page]

'load graph criteria from table to form controls
[Forms]![main page]!mach = List("machine")
[Forms]![main page]!sd = List("startDate")
'[Forms]![main page]!fd = FinishDate
[Forms]![main page]!group = List("majorgroup")
[Forms]![main page]!Reas = List("reas")
[Forms]![main page]!category = List("interval")
[Forms]![main page]!Frame54 = List("hoursas")
[Forms]![main page]!Frame44 = List("shiftsplit")
[Forms]![main page]!Text63 = List("who")

'Turn the one reason control box on or off
If [Forms]![main page]!group = 1 Then
[Forms]![main page]!Reas.Visible = True
Else
[Forms]![main page]!Reas.Visible = False
End If

'Decide which query to use for the graph
Dim togg As Integer
Dim togg2 As Integer

togg = [Forms]![main page]!Frame44.Value
togg2 = [Forms]![main page]!Frame54.Value

If togg2 = 2 Then ' if hours are selected as a percentage
[Forms]![main page]!Graph35.RowSource = "SELECT [step 4 prop].Intdate, [step 4 prop].[% Planned], [step 4 prop].[% Unplanned], [step 4 prop].[% Operating] FROM [step 4 prop];"
[Forms]![main page]!Graph35.ChartType = xlColumnStacked
ElseIf togg = 2 Then ' if want to split shifts
[Forms]![main page]!Graph35.RowSource = "SELECT Step2.Intdate, Step2.Hours FROM Step2;"
[Forms]![main page]!Graph35.ChartType = xlLine
Else ' otherwise use the normal one
[Forms]![main page]!Graph35.RowSource = "SELECT step2a.Intdate, step2a.[A Shift], step2a.[B Shift], step2a.[C Shift], step2a.[D Shift] FROM step2a;"
[Forms]![main page]!Graph35.ChartType = xlLine
End If

'Update the last 6 months sub form
.last6.Requery
'Update the graph
.Graph35.Requery
'make sure the graph has updated

'DoEvents
Pause (45)

'Print the graph
DoCmd.PrintOut

'Finished with the form for this record
End With

'next record in the table
List.MoveNext

'do again with the next record
Loop

'shut up shop
List.Close
[Forms]![main page]!Text63.Visible = False
DoCmd.Close acForm, "main page"


End Sub


not pretty, but here it is. it is still under development at this time.

you can see where ive tried to put the doevents. when i try it i comment out the pause. (pause is a function i found on this website and have used here)

i know i should have variablified the form name, but i couldnt find the right syntax immediately, got impatient and discovered it worked this way anyway!

cheers

mike c

 

[Fizzio]

I cannot really offer a better solution, as I have not worked extensively with the graph model in access. The doevents is where I would put it as well.