Progress Bar not accurate

[anujkmehrotra]

Hi,

I'm making a code to run a Progress Bar from different forms and as per their conditions.

Have two forms:
1- Form1="frmProgressMeter"
2- Form2="frmDatabaseStwitcher"
3- Module1=for Functions

On frmDatabaseStwitcher, the code for Progress Bar is:
-----------

Option Compare Database
Option Explicit
Private Declare Sub sapiSleep Lib "kernel32" _
Alias "Sleep" _
(ByVal dwMilliseconds As Long)

Sub OpenProgressBar(nTopLimit As Long, strCaption As String)
If nTopLimit > 0 Then
DoCmd.OpenForm "FrmProgressMeter", OpenArgs:=strCaption
Forms("FrmProgressMeter").TopLimit = nTopLimit
End If
End Sub

Function SetProgressBar(nCurrent_Pos As Long) As Long
If IsLoaded("FrmProgressMeter") Then
Forms("FrmProgressMeter").Current_pos = nCurrent_Pos
SetProgressBar = Forms("FrmProgressMeter").Current_pos()
End If
End Function

Sub CloseProgressBar()
If IsLoaded("FrmProgressMeter") Then
Forms("FrmProgressMeter").CloseMe
End If
End Sub

Sub sSleep(lngMilliSec As Long)
If lngM
illiSec > 0 Then
Call sapiSleep(lngMilliSec)
End If
End Sub

------------

Code to run Progress Bar with Condition is:

----------

 Private Sub cmdDisconnect_Click()
On Error GoTo Err_cmdDisconnect_Click

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strDatabaseName As String
Dim File As String
Dim retval
Dim NOS As Long 'No of steps
Dim n As Long 'Total Steps

Set dbs = CurrentDb()
File = "Data" & Format(StartDate, "yy") & Format(EndDate, "yy") & ".accdb" '(Output = Data1516.accdb)
strDatabaseName = Folderpath & "\" & File

Me.lblStatus.Caption = "Connecting to current database. Please wait..."
DoCmd.Hourglass True
Me.CmdClose.Enabled = False
Me.cmdConnect.Enabled = False
Me.Dbase.Enabled = False
Me.CmdDisconnect.Enabled = False
retval = SysCmd(acSysCmdSetStatus, "Connecting to current database")

OpenProgressBar 100, "Completed"

[B]NOS = dbs.TableDefs.count
For n = 1 To 100
sSleep 10
SetProgressBar n + NOS
Next n
[/B]
For Each tdf In dbs.TableDefs
If tdf.Connect <> "" Then
tdf.Connect = ";DATABA
SE=" & strDatabaseName & (";PWD=zujan")
tdf.RefreshLink
End If

[B]SetProgressBar n + NOS
[/B]Next tdf
sSleep 1000
CloseProgressBar

Me.lblStatus.Caption = "Done."
DoCmd.Hourglass False
retval = SysCmd(acSysCmdClearStatus)

MsgBox "The Auto Evolution database is now connected with " & _
"'" & File & "'.", vbInformation, "Connection Successful"
DoCmd.Close acForm, Me.NAME

Exit_cmdDisconnect_Click:
Exit Sub

Err_cmdDisconnect_Click:

DoCmd.Hourglass False
If Err.Number <> 2467 Then
MsgBox ("Unable to execute database switch now."), vbCritical, "Error"
Me.CmdClose.Enabled = True
Me.cmdConnect.Enabled = True
Me.Dbase.Enabled = True
Me.CmdDisconnect.Enabled = True
Resume Exit_cmdDisconnect_Click
End If
End Sub

 

[Frothingslosh]

And as I said in the thread you deleted, it will help all of us if you could be bothered to use the code tags and proper indentation, as it says in the sticky at the top of the forum.

Deleting the thread because you don't like being told that isn't going to magically make the forum regulars here jump to help you.

Especially when you've already been pointed to a fully operational progress bar.

 

[anujkmehrotra]

did I wrong again.

 

[Frothingslosh]

did I wrong again.

Use of the code tags is shown HERE. Basically, unlike quote tags, code tags retain formatting, so you can post your code propertly indented, which in turn makes it FAR easier to read.

It's much easier to see, for example, whether loops and conditional blocks are closed and commands placed inside the correct loop or block if your code is indented correctly.

Basically, unindented code takes more to work through, even short stuff. I'm still planning on looking through what you posted, but if you don't format it, it's going to have to wait until I have more free time this afternoon. If it gets formatted correctly, then I can work on it by sneaking peeks while waiting for processes to finish right now. It's mostly a matter of 'effort available to spend'.

And yeah, you can edit your posts without deleting them. Just don't select 'DELETE'.

 

[anujkmehrotra]

I didn't understand your point earlier. Please check

 

[Frothingslosh]

You close out a code tag by putting a slash in front of the word code. It's like how HTML works, but with square brackets rather than < and >.

Example:

Unformatted function:

Public Function Pause(NumberOfSeconds As Variant)
On Error GoTo Err_Pause
Dim PauseTime As Variant, Start As Variant
PauseTime = NumberOfSeconds
Start = Timer
Do While Timer < Start + PauseTime
DoEvents
Loop
Exit_Pause:
Exit Function
Err_Pause:
MsgBox Err.Number & " - " & Err.Description, vbCritical, "Pause()"
Resume Exit_Pause
End Function

Using code tags:

Public Function Pause(NumberOfSeconds As Variant)
On Error GoTo Err_Pause
    Dim PauseTime As Variant, Start As Variant
    PauseTime = NumberOfSeconds
    Start = Timer
    Do While Timer < Start + PauseTime
        DoEvents
    Loop
Exit_Pause:
    Exit Function
Err_Pause:
    MsgBox Err.Number & " - " & Err.Description, vbCritical, "Pause()"
    Resume Exit_Pause
End Function

 

[Frothingslosh]

You nailed it.

Going through the code now.

Well, almost nailed it. Make sure you're using indentation in your actual code - it really does help with legibility.

 

[anujkmehrotra]

Please guide me because MDSN says :

I agree with you that when there are relatively few steps, that the progress bar advances in discrete steps and not smooth. That is because you have to synchronize the progress bar with the progress of your process, that is the loop with the connection of the TableDefs.
You can "fake" some smoothening, by just move the progress bar, but in fact then you only introduce waiting time without synchronization. You have to synchronize with the looping process. Else you can have situations where the progress bar is e.g. at 60%, and at that point the process is ended, or the progress is already at 100% and you still have to wait till the looping process ends. And in my opinion both cases are not a good process bar.
Unless you can zoom in in the underlying steps of the loop (but with .Connect and .RefreshLink hardly to do), coarseness of the progress bar is determined by the number of steps in the loop. Trying to go finer (smoother) is beyond the possible resolution, and only increases the process time.

 

[Frothingslosh]

Okay, can you post a screenshot of your progress bar form, indicating what TopLimit and Current_Pos are on the form?

Normally I'd say to post a stripped copy of the database (a copy with fake data and only the stuff we need to handle your problem), but I can't download anything at work. If you DO post a stripped one, I can look at it after work.



The gist I'm getting from your code is that the form frmDatabaseSwitcher is where you run your update program. When the button "cmdDisconnect_Click" is pressed, your code does the following:

1. Opens your progress meter form, sets your top limit to 100, and sets your caption to "Completed".
2. You assign the number of TableDefs in the database as the number of steps (NOS).
3. You then have a FOR...NEXT loop that does nothing but increment the progressbar every ten seconds. The value displayed is always the number of the iteration plus the number of tabledefs. Two problems: You're not actually DOING anything here, and your progress should be based on the percentage of the tabledefs connected (and by the way, YOU POSTED YOUR PASSWORD!!!).
4. Once you've finished cycling (which, by the way, leaves your progress showing at 100 + the number of tabledefs), you then loop through the tabledefs themselves.
5. You link each tabledef, and then update the progress bar to show n + NOS. Since you're not actually changing the value, the progress bar will remain at, say you have 15 tables, 115 through this entire process.
6. Then you pause the computer for one second, then you close your progress form.

You have a few problems, noted above.

First off, your n loop does absolutely nothing. Instead, handle your progress bar updates entirely within the tabledef loop.

Also, instead of displaying n + NOS, what you want is to use n as a counter inside the loop, starting at 1. The value you pass to SetProgressBar should be n/NOS, not n + NOS, and then the last thing you do before the loop statement should be include the line "n = n + 1".

That should at least get your progress bar working.

As I said on the other thread, I do have a class already built and saved in the Repository here that does just what you want. All you really need to do is follow the instructions on my first post and it handles all the calculations.

 

[anujkmehrotra]

Also, instead of displaying n + NOS, what you want is to use n as a counter inside the loop, starting at 1. The value you pass to SetProgressBar should be n/NOS, not n + NOS, and then the last thing you do before the loop statement should be include the line "n = n + 1"

As per your advised I changed my code like this. This time it works better but after 100% complete, some process is still on going for few seconds (5-10). Why so??

 OpenProgressBar 100, "Completed"
 NOS = dbs.TableDefs.count
 For n = 1 To 100
For Each tdf In dbs.TableDefs
    If tdf.Connect <> "" Then
        tdf.Connect = ";DATABASE=" & strDatabaseName & (";PWD=zujan")
        tdf.RefreshLink
    End If
sSleep 10
SetProgressBar n / NOS * 100
 n = n + 1
    Next tdf
Next n
sSleep 1000
CloseProgressBar

View attachment Test.zip

 

[Frothingslosh]

Walk through that loop for a moment.

Right now, for no reason whatsoever, you have your database re-linking the tables 100 times.

One of the very first things you should do when debugging code, especially when it's a logic failure rather than a syntax one, is to step through your code.

 

[jdraw]

Anuj,

As Frothingslosh said -- do some step debugging to see the logic issue you have.

From Chip Pearson:

Stepping Through Code

One of the first methods to debug code is to step through the code one line at a time. To step through code, put the cursor on the first line of code to be analyzed and press F8 or choose Step Into on the Debug menu. The next line of code to be executed will be displayed in yellow background with a black font. Note that the highlighted line of code has not yet been executed -- it is the next line to execute.

If your code calls another procedure, stepping through the code with F8 will cause execution to enter the called procedure in a line-by-line sequence. If you want to execute the called procedure without stepping through it, press SHIFT F8. This will execute the called procedure and then pause on the line of code after calling the procedure. If you are already stepping through a procedure, you can press CTRL F8 to resume code execution line-by-line. At any time you are paused either in step-by-step mode or at a breakpoint (see below), you can press F5 or Continue from the Run menu to cause VBA to run to completion or until a pause statement is encountered.

Whenever you are paused in step-by-step mode, you can query or change a variable's value from the Immediate window.

You could also include

Debug.Print  " In tdf Loop  " & n

to show you the issue in the immediate window.

Good luck.

 

[anujkmehrotra]

I finally got what I expected. Many thanks to you to advice me.

 OpenProgressBar 100, "Completed"
NOS = dbs.TableDefs.count
TOS = 1
 For Each tdf In dbs.TableDefs
    sSleep 100
SetProgressBar TOS / NOS * 100
    TOS = TOS + 1
If tdf.Connect <> "" Then
        tdf.Connect = ";DATABASE=" & strDatabaseName & (";PWD=")
        tdf.RefreshLink
    End If
        Next tdf
    sSleep 100
CloseProgressBar