asked on

I need to implement a progress dialogue in my app

Good day everyone,

I need to fetch a data from a database into my VBA project.
The code runs for a couple of minute because it connects to a remote server.
I am trying to implement an interactive dialogue to show the progress and need support on how to best implement this feature...

Below is my code snippet

Dim sTims, stTime, endTime As Double
Dim startTime As Boolean

Sub CommandButton1_click()

ProgressBar1.Visible = True

sTims = Timer

startTime = False

If CheckBox1.Value = False And CheckBox2.Value = False Then MsgBox "Pls Select a Location to View", vbOKOnly, "Oops!": Exit Sub

ShpData.closeRS
ShpData.OpenDB

StrSQL = "SELECT [Shipment Number], [WHExecution Date], [WH Officer],[Driver Name],[Quantity]FROM [ShipmentRecords] order by [WHExecution Date] DESC"

ShpData.DataRec.Open StrSQL, ShpData.DataConn, adOpenKeyset, adLockOptimistic

    If ShpData.DataRec.RecordCount > 0 Then
    
    Range("E12:I12").Select
    
    Range(Selection, Selection.End(xlDown)).ClearContents
    
    Range("E12").CopyFromRecordset ShpData.DataRec
    
    Else
        MsgBox "I was not able to find any matching records.", vbExclamation + vbOKOnly
        Exit Sub
    End If
    
    cleanup
    endTime = Timer
    startTime = True
    ProgressBar1.Visible = False
End Sub

Sub cleanup()

ShpData.DataRec.Close
ShpData.DataConn.Close
 
    Set ShpData.DataConn = Nothing
    Set ShpData.DataRec = Nothing

End Sub

Sub timerbar()

Static intCount As Integer

ProgressBar1.Value = 0
intCount = 0

DoEvents

Do Until startTime = True

intCount = Timer - sTims
ProgressBar1.Value = (intCount)

Loop

If startTime = True Then
MsgBox "Complete"
End If

End Sub

Open in new window

Dale Fye

you might want to look at my article on Progress Bars, but if you are using the CopyFromRecordset method, you will not be able to provide "progress" type reports. It works best when you are performing some sort of iterative process or looping through a recordset.

HTH
Dale

Iretioluwa Olawuyi

ASKER

Thanks for your comment, Dale.
I actually use CopyFromRecordset method. Is there any alternative I can use to achieve same result and also have my progress indicator easier?

PatHartman

To offer progress information, you need an estimate of how long the process should take (or how many records it will retrieve) and then you need a loop that performs the action whatever it is.

As Dale said, without a loop, you don't have any clue where you are at any point in time.

You can lock the screen when you start the process and show a timer so they can tell how long the process has been going on but that's about it. You could also change the color background while the form is working to make it obvious from across the room and you should post a complete and an elapsed time when it is done so the user knows when the process completes.

Dale Fye

The challenge with any progress indicator is that if you don't have a series of steps you are performing, each of which takes time to perform, you really have no way of indicating progress.

You might, for example open a recordset and then use the copyfromrecordset method.

So, before you open the recordset, you might set the status to 10%, then, after the recordset is instantiated, set the status to 60%, then, after the data is pasted to Excel, set the status to 100%. But because those are really the only major muscle movements in your code, there is no way for you to insert intermediate values in there that mean anything.

I have not tried this, but you might be able to do the following. Assume that the entire process is going to take 3 minutes, divide that into 20 intervals (5%) each, and then each interval would be about 9 seconds long. Using that assumption, you could then set the timerinterval of your progressbar form to 9 seconds (9000) and in the timer event, increment your progress bar accordingly. This, of course, could end early if you underestimate the time needed for the query and copyfromrecordset to run, but it would give you at least a semblance of a progress bar.

Just an idea.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.