Progress Bar for Opening a Report 1

[perrymans]

I have some reports that are taking quite awhile to run and open (connecting to a MySQL server over the internet).

I have several good examples of progress bars, but they all have coding that is driven by a timer or a set number of iterations.

I know that I am supposed to substitute the number entered (like 1200) with the recordset count and move the bar as the recordset is processed. But I don't know how.

Here is the code of the bar I was thinking of using:

' Module     : Form_frmShowProgress
' Description:
' Procedures : UpdateCurStep(pintCurStep As Integer)
'              Let NumSteps(pintStrEventFunction As Integer)
'              cmdGo_Click()
'              Form_Open(pintCancel As Integer)

' Modified   : 10/21/02 - 16:33
' Author: Sandra Daigle
' 10/21/02 SMD Cleaned with Total Visual CodeTools 2002
'
' --------------------------------------------------

Private Const mcStrModule As String = "Form_frmShowProgress"
Option Compare Database
Option Explicit
Private mintNumSteps As Integer
Dim fInLoop As Boolean
Dim fExitLoop As Boolean


Public Sub UpdateCurStep(pintCurStep As Integer)
    ' Comments  :
    ' Parameters: pintCurStep -
    ' Modified  : 10/21/02 - 16:33
    '
    ' --------------------------------------------------
    'TVCodeTools ErrorEnablerStart
    On Error GoTo Proc_Err
    'TVCodeTools ErrorEnablerEnd
    
    Dim inti As Integer
    Dim dblPct As Double
    dblPct = inti / Me.txtNumIterations
    Me.txtPctComplete = dblPct
    Me.boxPct.Width = Me.boxWhole.Width * dblPct
    DoEvents
    
    'TVCodeTools ErrorHandlerStart
Proc_Exit:
    Exit Sub
    
Proc_Err:
    Select Case Err.Number
        Case Else
            Select Case ErrorDisplay(Err.Number, Error$, mcStrModule, "UpdateCurStep", Erl())
                Case errContinue
                    Resume Next
                Case errexit
                    Resume Proc_Exit
            End Select
    End Select
    'TVCodeTools ErrorHandlerEnd
    
End Sub

Public Property Let NumSteps(pintStrEventFunction As Integer)
    ' Comments  :
    ' Parameters:
    ' Created   : 10/21/02 16:25 SMD
    ' Modified  :
    '
    ' --------------------------------------------------
    'TVCodeTools ErrorEnablerStart
    On Error GoTo Proc_Err
    'TVCodeTools ErrorEnablerEnd
    mintNumSteps = pintStrEventFunction
    
    'TVCodeTools ErrorHandlerStart
Proc_Exit:
    Exit Property
    
Proc_Err:
    Select Case Err.Number
        Case Else
            Select Case ErrorDisplay(Err.Number, Error$, mcStrModule, "NumSteps", Erl())
                Case errContinue
                    Resume Next
                Case errexit
                    Resume Proc_Exit
            End Select
    End Select
    'TVCodeTools ErrorHandlerEnd
End Property

Private Sub cmdGo_Click()
    ' Comments  :
    ' Parameters:  -
    ' Modified  : 10/21/02 - 16:33
    '
    ' --------------------------------------------------
    'TVCodeTools ErrorEnablerStart
    On Error GoTo Proc_Err
    'TVCodeTools ErrorEnablerEnd
    
    Dim inti As Integer
    Dim dblPct As Double
    Me.lblEscape.Visible = True
    Me.lblAbort.Visible = False
    Me.txtI.Visible = True
    Me.txtPctComplete.Visible = True
    Me.boxWhole.Visible = True
    Me.boxPct.Visible = True
    fInLoop = True
    fExitLoop = False
    
    Do Until inti > Me.txtNumIterations Or fExitLoop
        dblPct = inti / Me.txtNumIterations
        Me.txtPctComplete = dblPct
        Me.boxPct.Width = Me.boxWhole.Width * dblPct
        Me.txtI = inti
        'If Me.txtI Mod 1 = 0 Then
            DoEvents
        'End If
        inti = inti + 1
    Loop
    Me.lblEscape.Visible = False
    fInLoop = False
    'TVCodeTools ErrorHandlerStart
Proc_Exit:
    Exit Sub
    
Proc_Err:
    Select Case Err.Number
        Case Else
            Select Case ErrorDisplay(Err.Number, Error$, mcStrModule, "cmdGo_Click", Erl())
                Case errContinue
                    Resume Next
                Case errexit
                    Resume Proc_Exit
            End Select
    End Select
    'TVCodeTools ErrorHandlerEnd
    
End Sub

Private Sub Form_KeyPress(KeyAscii As Integer)
If fInLoop And KeyAscii = 27 Then
    fExitLoop = True
    Me.lblAbort.Visible = True
End If
End Sub

Thanks. Sean.

 

[TheAceMan1]

How are ya perrymans . . .

My impression is that your expanding the width of a textbox with colored background as the progress bar. If this is true (correct me if I'm wrong) then your problem is a unit of measure called [blue]twips[/blue]. VBA uses [blue]twips[/blue] instead of inches or centimeters in measuring an objects width & height. All you really need is a little conversion in resloving your increment value. I'll use the 1200 increments you stated as an example:

Reference
*********
twips = 1400 * inches
inches = twips/1400

Now lets say you want to expand the textbox from 0 (hidden) to 3inches max. Converting the max to twips we have
4200 = 1400 * 3

We divide twips by the max number of iterations (1200) to get our increment value:
3.5 = 4200/1200

To even things out we'll use 4twips causing the bar to extend 1200 * 4 or 4800twips. This translates to 4800/1440 or 3.33inches max length of the bar.

Finally for 1200 increments you'd have:

[blue]   Me.boxPct.Width = Me.boxWhole.Width + 4[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[perrymans]

The 1200 actually represents the time the bar takes to go across. The sample database can be seen here:

http://www.daiglenet.com/Samples/ProgressMeter.ZIP

I can figure out how to change the numerical value to a percentage complete, I just don't know how to make it based on a recordset.

Let's say a recordset = 1200 records. Then 1200 = 100%. So I would need to pass the record sequence to the progress bar as it goes along so that, for example, after 600 records had processed, the progress bar would show 50%.

Unless I am thinking these things work totally differently, but they all appear to be timer driven, and therefore I would have to know how long the query/report takes to process before I could program the progress bar.

This can't be right. I must be misunderstanding these things.

Maybe I should rethink this.

Thanks. Sean.

 

[TheAceMan1]

perrymans . . .

I believe your confusing time with incrementing the bar. 1200 records means 1200 increments. The question is: [blue]how much time does it take to process each record for what your code is doing.[/blue] If most of the 1200 records takes say 1sec, while a few others take 2sec, and still a couple take 4sec, it still takes 1200 increments to complete the bar! This is why with other applications you have installed, you've seen the bar pause in certain areas while others take a quick jump! The pauses could be the 4sec processing while the quick jumps are the 1sec processing. How much time it takes to traverse the 1200 increments is how long you'll see the bar (its normally closed when your done).

[purple]Remember . . . the whole idea is about incrementing the bar . . . no matter how you do it![/purple]

So what do you do when you can't derive an increment value? This is where you see timers used to increment the bar. This can occur when your say ... downloading a large file. There's no way to derive an increment value, so a timer is used.

A typical ActiveX ProgressBar has the following main properties
There are three main operational attributes for a typical ActiveX ProgressBar:

[purple]Min[/purple], which defaults to zero and usually needs no setup.

[purple]Max[/purple], the full count of the bar, usually set by the programmer . . . AKA . . . You!

[purple]Value[/purple] (and the most important), the current value of the bar, >= min & <= max that is [purple]setup/enumerated by you![/purple]

Here's a typical secnario for incrementing the microsoft activeX progressbar:

[blue]   Dim rst As DAO.Recordset, x As Integer
   
   Set rst = Me.RecordsetClone
   Me!ProgressBarName.Max = rst.RecordCount [green]'has only 1200 records[/green]
   
   For x = 1 To 1200
      [green]'Your record processing here[/green]
      Me!ProgressBarName.Value = x
      rst.MoveNext
   Next[/blue]

Hope this helps . . .

BTW: I've never seen such elaborate code to increment a progressbar as you've presented!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]