Show a Progress Bar on Form Loading ... (1 Viewer)

[Marshall Brooks]

Some of my forms take a while to load - especially when I am working at home, so I wanted to add a progress bar to show their progress.

I have no idea how long the file will take to load, so this will be one of those progress bars that just moves across the screen and then starts over again.

I am using a modified version of the progress bar shown here:

Beautiful VBA Progress Bar with Step by Step Instructions - wellsr.com

Make your macros stand out by creating this beautiful VBA Progress Bar to illustrate the progress. This tutorial walks you through how to make the UserForm.

wellsr.com

Typically, the code would look like this:

With ufProgress
    .LabelCaption.caption = "  Running Event 1"
    .LabelProgress.Width = 0
    .Show
    .Repaint
End With
' Do Event 1'
With ufProgress
    .LabelCaption.caption = "  Running Event 2"
    .LabelCaption.caption = "  Updating Templates"
    .LabelProgress.Width = 0.5 * (.FrameProgress.Width)
    .Show
    .Repaint
End With
' Do Event 2
Unload ufProgress

For a first attempt, I'm wanting the progress bar to update twice a second and increment 1 per-cent per second.

I thought I only needed this code in the Form_Timer() procedure:

Private Sub Form_Timer()
Dim I As Integer
Me.TimerInterval = 500 ' 1/2-second
I = (I + 1) / 2
If I > 100 Then
    I = 0
End If
With ufProgress
    .LabelCaption.caption = "  Loading frmForm A"
    .LabelProgress.Width = I / 100 * (.FrameProgress.Width)
    .Show
    .Repaint
    DoEvents
End With
End Sub

And the "unload ufProgress" and "Me.TimerInterval = 0" lines in the Form Activate event, but nothing seemed to happen.

I added to Form_Load():

Me.TimerInterval = 500 ' 1/2 second
With ufProgress
    .LabelCaption.caption = "  Loading C-130J TCTOs"
    .LabelProgress.Width = 0 / 100 * (.FrameProgress.Width)
    .Show
    .Repaint
End With

And I see the Progress Bar open and close, but no indication of movement. (It's possible it loads in less than a second - I can't really tell that now, but if I change the timer interval to 1, I don't see any progress either.

Where did I go wrong?

Thanks in advance!!!

 

[Josef P.]

I suspect that DoEvents are missing at some points in the form loading process so that the timer event can execute at all.

 

[The_Doc_Man]

Generally, a Form_Load event doesn't take very long. Therefore I wonder if you are describing something other than the Form_Load event. Are you explicitly opening multiple forms underneath a switchboard or something similar? Or did you add a chunk of code under your opening form or opening macro that takes a lot longer than normal?

 

[Marshall Brooks]

I added DoEvents to Form_Load() and didn't see a difference - i.e. I still see the progress bar pop up and close, but no movement - and that is with TimerInterval = 1.

 

[Josef P.]

Have you already gone through the code in single step?
If so, are there several places with "long-runners"?

 

[Marshall Brooks]

@The_Doc_Man - Good points as always!!!

The form that takes the longest to load has about 3000 records and there is a line in the load event to sort it on a binary field called "Sortkey" and then on a field named "Title".

When I load it from home, I typically see "Running Query" and the built-in progress bar in the status bar and it takes about 60 to 90 seconds to load. (Note that there is no query actually being loaded.) In the office (today), it loads in under a second, so I can't really tell if the progress bar is failing or not.

In Form_Activate(), I have code to close other open forms, but the unload ufProgress is after that, so I would expect the progress bar to still be running at that point.

Now that you mention it ... This particular form used to be my startup form and I had code in it to check the opening path of the database and to check for front end updates prior to sorting the records. I added a switchboard and I moved that code to a subroutine of my autoexec.vba and I have progress bars that are working for that (but I can identify discrete events for that.)

For testing, I am opening this form explicitly along with the switchboard, but I'm not sure that matters. I saw a delay at home when I closed and re-opened the form, and I see the progress bar (but no movement) now if I close and re-open it.

I just thought of and tried something else. I have a Pause function that I use elsewhere in the database, so I added a 10-second pause in the load event after the initial call of the progress bar and the progress bar is not moving - unless Pause shuts down the timer event also. This is in the main module:

Public Function Pause(NumberOfSeconds As Variant)
' https://www.access-programmers.co.uk/forums/showthread.php?t=167470
' Supports resolution at least to the the tenth of a second, maybe greater.
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:
    Box Err.Number & " - " & Err.DESCRIPTION, vbCritical, "Pause()"
    Resume Exit_Pause

End Function

 

[Marshall Brooks]

Have you already gone through the code in single step?
If so, are there several places with "long-runners"?

No, I'm having an issue with that also. Single step works fine in a module. In my form code, nothing seems to make the code stop - other than a MsgBox, but then it stops until I click OK, so I can't tell timing.

I tried setting breakpoints and I tried adding Stop statements and it just blows past them. I saw another thread on here that mentioned disabling Access special keys on startup and I had that unchecked to prevent F11 to show the Nav Pane, but I enabled the special keys and still don't get breakpoints to work.

 

[Josef P.]

How many records are loaded in the form? - Does it have to be these 3000 or can you maybe reduce that to 1?
Do you use subforms? ... You could load them a little later, when the main form is already open. In between you could then keep the progress bar running.

 

[Marshall Brooks]

There are 3700 records loaded. I'd prefer not to change that. If it takes 90 seconds to load, that's okay, I just want to see motion in the progress bar.

The form does have 3 subforms that load also. I'm not sure how to delay loading those ...

 

[Josef P.]

Load subform later: start with empty subform and after the main form is loaded, reload the subforms (controlled by Form.Timer).

Example:
Start with subforms with empty SourceObject property

In main form:

private sub Form_Current()
   Me.TimerInverval = 1 ' this will perform the form loading process and then immediately respond to timer event
end sub

private sub Form_Timer()
     me.TimerInterval = 0 ' Timer off
     me.FirstSubForm.SourceObject = ...
     ProgressbarNextStep
     ....
end sub

This takes longer (measured), but looks faster.

 

[Marshall Brooks]

Load subform later: start with empty subform and after the main form is loaded, reload the subforms (controlled by Form.Timer).

That seems to work - at least I see movement in the progress bar and it might actually be loading the form faster.

I can't tell for sure since the form loads pretty quickly in the office. I'm thinking what I might see at home is the form starts to load and the progress bar stays at zero for 45 seconds and then moves when the subforms load.

Question: Why do I want to use the timer with your solution?

I.e. as I understand it, I am waiting for the form to load, waiting 1 millisecond, and then loading the subforms. Wouldn't this work as well:

Don't use form Timer at all ...

private sub Form_Current()
     me.FirstSubForm.SourceObject = ...
     ProgressbarNextStep
     ....
     Unload Progress Bar
end sub

I haven't tested the above.

(Or would that make the subforms try to load everytime I clicked on the form ???)

 

[isladogs]

I have several example progress bars based on a timer (or event code)...

Progress Bar

This is a simple but effective progress bar for use in Access forms. Several different versions are provided based on a timer or a series of events. The progress bar can use a solid bar, image or gradient fill.

www.isladogs.co.uk

BUT no form should ever take more than a couple of seconds to load if designed well
Load as few records in the main form as possible initially - ideally one
Set the subform record source initially to nothing then populate once the main form has loaded

 

[The_Doc_Man]

When I load it from home, I typically see "Running Query" and the built-in progress bar in the status bar and it takes about 60 to 90 seconds to load. (Note that there is no query actually being loaded.) In the office (today), it loads in under a second, so I can't really tell if the progress bar is failing or not.

This is a description of a network issue, the "home" showing a slow connection and the "office" showing a fast connection. You cannot get any code to execute in the time gap for a "pure Access" operation because that form-data-load query is indivisible. Among other things, YOU didn't trigger it; Access did, as an automatic feature. In order to do something with a progress bar, you need some CPU time to execute code to diddle with the settings of the progress bar. But the automatic data-load query gives you no "hook" to execute code while it is running.

If that query were to an SQL engine and you ran it using a pass-thru query, it would be possible to test the underlying recordset for progress or completion. But for any native Access query, you run synchronous code for any event. The Access GUI synchronizes with Jet/Ace if the BE is a native Access BE file. And Jet or Ace itself is internally synchronous. (We delved into this a couple of years ago and got confirmation from various online sources.)

The reason your timer can't run is because a timer queues a timer EVENT, not an interrupt. (Some error traps ARE interrupts, which is why error event processing is slightly different from regular event processing.) If you are running code, it is already running in an event context. Here is the key: Without something like a divisible query and VBA code with a DoEvent, a timer cannot interrupt your code because one event cannot interrupt another. Events get queued up for serial execution. Actions like .SetFocus (as one simple example) can trigger insertion of other events into a queue ahead of already pending events. But that timer code to diddle with your progress bar can't and won't run until your current event exits.

Now if you were doing something ELSE in your _Load code, like launching a sequence of queries, you could trigger progress bar changes between the various steps, but unless some of those steps are VERY slow, again you probably wouldn't see much.

 

[Josef P.]

Question: Why do I want to use the timer with your solution?

This will display the main form directly after Current.
If you load the subforms in the Form_Current procedure, the main form will not be visible until everything is ready.

Example see attachment. (The data source of the forms is intentionally without index, etc., so that it takes longer to load.)

 

[Marshall Brooks]

@isladog - Nice work with the example Project Bars.

I thought I had the original solution, but I didn't:

Private Sub Form_Timer()
Dim I As Integer
I = (I + 1) / 2

What I THOUGHT was happening was:
I = 0
I = 0.5 - which I think bankers rounding resets to 0 as an Integer.
Repeat.

But I changed it to I = I+1 and it still did not work - No movement.

@The_Doc_Man - What you said makes sense - so no matter what I do, I can't show a progress bar on Form Loading.

I had another complicated idea: Could I put my progress bar on a different unbound form and have that hidden, then call that from from my FormA load event have the progress bar show that way?

Otherwise, I'll go with @Josef P. 's suggestion - I'll have a progress bar, but it won't move until I the main form is loaded, and then the subforms will load. What I might do is just Call the progress bar on form load and cancel it on Form Current. It won't move, but you will see SOMETHING to know that the form is loading.

Thanks all!

 

[Josef P.]

I had another complicated idea: Could I put my progress bar on a different unbound form and have that hidden, then call that from from my FormA load event have the progress bar show that way?

Access/VBA works synchronously. You would have to use multithreading to run the progressbar in parallel.

On the other hand, I would just make sure that the form loads fast enough so that you don't need a progressbar at all.

 

[Marshall Brooks]

On the other hand, I would just make sure that the form loads fast enough so that you don't need a progressbar at all.

That would be ideal, but as @The_Doc_Man said - it seems to be a networking issue and only affects me.

For example, in other locations, I have added Progress Bars for things that seemed to take a long time to execute from home, and when I come in the office, the event basically happens before the progress bar gets a chance to load.

Thank you all for the help and suggestions.

 

[Pat Hartman]

so I wanted to add a progress bar to show their progress.

For a progress bar to have any meaning, you need to know how many or how long and then have a way of calculating your percentage of the way through.

 

[The_Doc_Man]

AND have an opportunity to actually do something about it - which in a totally synchronous environment is sometimes harder to find.

 

[isladogs]

I have only skimmed this thread so apologies if I've missed the point here.

Am I right in saying that you just want a progress bar that runs automatically on a timer event when a form loads?
If so, its trivial to do despite the lengthy discussion which implies its not easy.

See Form 5 (frmPB5) in the attached example where the progress bar is added to Form_Load.
Now change the app startup form to frmPB5 and reopen the app - the progress bar starts automatically

Can that be adapted to suit your requirements? Or have I misunderstood what is wanted?