Show a Progress Bar on Form Loading ...

[Marshall Brooks]

I'll try to respond to everyone ...

• First off. I was able to open the database from home today. A form that takes 1-2 seconds to load in the office takes about 45 seconds to load from home. However, anything with file transfer (saving or copying Word files over the network, for example), also takes much longer from home.
• @Josef P. 's suggestion is an improvement. At least I see a progress bar, although it doesn't move until the subforms start loading.
• It occurred to me that I already have a working progress bar in the status bar - it says Running Query and it progresses from left to right across the form. Ideally, what I would like to do is somehow harness this and use it to feed into my pop-up progress bar.
• I also realize that this is somewhat OTT/OCD on my part. I.e. for most people, the database is pretty fast. In the office, you would barely notice the progress bar, and I don't know that for anyone else it is taking 45 seconds to load the forms.
• I can't find it now, but there was a thread on here where someone did what I want, but I think they used HTA to display the progress bar in Internet Explorer while the form was loading and that is a bit OTT, even for me.

@Pat Hartman and @The_Doc_Man : Meaningful is a relative term. Lots of commercial software (Lots of Windows apps) have a progress bar that moves across the screen to 100% reaches the end and starts over as many times as needed until the process completes. That's what I am looking for. Agreed, if you can't feed events to the progress bar you can't update it.

@isladogs - TBH, I don't know enough to know if your code can be adapted to what I want to do or not. Let me give more details:

• As I mentioned, I'm looking for a Progress bar that displays (and starts over) during form loading and then closes. With Josef P.'s help, I now have a progress bar that does that, it just doesn't move until the subforms load. It seems to me like your progress bar is set to run for a fixed time (60 seconds in the example). If so, that won't work for me. I don't want to set the timer for 45 seconds and then in office people have to wait for 42 seconds for the PB to load when they could be working, and setting it for 2 seconds and having it disappear at home would be pointless.
• What I think could be modified to work (With a lot of changes) is to instead of have your database open with frmPB5, I would modify the FormLoad event of frmStart to open frmPB5 and then have the FormCurrent event of frmStart close frmPB5. I think that could work, but it would fly by since your frmstart loads pretty much instantly. (I may do some testing with that).
• I skimmed your page and your PB is a lot more complex than the one I am currently using (It's a lot fancier also), but you have three functions to set it up, and then one line to update it as needed. Mine basically just displays an Excel-style UserForm (not an Access Form - I got in trouble previously for calling them user forms, but I don't know how else to differentiate them), and you simply tell it how much % complete to display and change the caption as desired whenever desired.
• I'm not sure where the code would go for what I am desiring:
  • I was thinking it would go in the timer section of the form being loaded (call that frmFormA), but @Josef P. subform delayed loading is using that, and I'm not sure how those would play together, and I don't know that you could have two timers. (And I didn't have much luck getting my PB to run off a timer, although I'm sure that should be possible).
  • Similar to my second bullet to you above, I was thinking about somehow having a new form (frmProgressBar?) that only displayed the PB and load that form hidden from the frmFormA load event and close it from the frmFormA current event, but I don't know where to put any of the code on that form.

I'm going to try one idea and if this can't be done, I'll go with what I already have - it's better than I started with.

 

[Marshall Brooks]

My idea didn't work, but I noticed something very odd (at least to me) also ...

First what I tried:

• I had my database open and saved a copy of it, so if this didn't work I could go back to what I had so far.
• I copied frmPB5 and the two modules from @isladogs example file.
• I opened frmPB5 in my database, and it was working, but it was basically maximized.
• I changed frmPB5 to PopUp - Yes in design view.
• I commented out my progress bars and opened frmPB5 in FormLoad (of frmFormA) and closed it in FormCurrent (of frmFormA).
• It works about like the progress bars that I had - i.e. I saw frmPB5 pop-up, it said step 2 of 600 and then it stayed of Step 2 of 600, the status bar PB kept going and then the frmFormA opened normally.

If it had worked, I would have had to make a lot of changes to the form to make it look similar to my other PB's, but ...

Now the odd thing:

When I first open the database from home, frmFormA takes about 45 seconds to load, in the office it takes 1-2 seconds. If I close frmFormA (via the X on the tab using tabbed view) and then re-open it from home, it takes 1-2 seconds to load - like it does in the office. Does this seem strange to anyone?

 

[Marshall Brooks]

Marked the thread as solved as @Josef P.'s suggestions work well enough for my purposes. I changed my Progress bars so they initially come up at 50% and then go to 67%, 84% and 100% as the three subforms load. It gives the desired effect although they don't move smoothly.

 

[Gasman]

Progress bars are old hat these days.
You need to use that circle of dots, that shows no indication whatsoever as to when something is going to complete.

 

[Marshall Brooks]

Already had that: Screen.MousePointer = 11.

 

[isladogs]

I wasn't suggesting you imported my form but instead that you used that approach.

In fact, my progress bar is very easy to use
a) Define the number of 'steps' - default is 50 if not otherwise set
b) SetupProgressBar frm - displays the progress bar controls in the active form
c) UpdateProgressBar frm - updates (moves) the progress bar after each step
d) HideProgressBar frm - hides the progress bar controls

Normally it responds to a series of events but will work on a timer
You can interrupt the process at any time with suitable code at step c) which hides the progress bar and exits the procedure

In the case where you want to use it whilst 2 subforms load, build these in as two steps

However, an even simpler method may be to use a label caption e.g. "Loading the subforms. Please wait . . ."
Then keep adding another "." at the end of the caption on a timer event e.g. every second whilst the subforms load

 

[Gasman]

Already had that: Screen.MousePointer = 11.

That appears to be an hourglass?

Screen.MousePointer property (Access)

Office VBA reference topic

learn.microsoft.com

 

[Marshall Brooks]

I wasn't suggesting you imported my form but instead that you used that approach.

Understood, but when I imported your form it did not update.

And before I updated it, it didn't look like your code would really do what I wanted (no offense).

i.e. when I modified your database to open frmPB5 at startup, the PB ran for a full minute, it did not stop when the form was current (granted it would have zipped by in less than a second.)

If I can have a Progress bar that opens at form load and advances and ends at form current - that is what I am looking for, but your database didn't seem to do it and @The_Doc_Man and others are saying it can't be done.

 

[Marshall Brooks]

That appears to be an hourglass?

Busy cursor - they just still call it an hourglass. In Win10 we typically call it "The spinning circle of death" (especially if you forget to set it back to 1 in your code).

Very similar to your circle of dots, if they were spinning fast enough.

 

[Gasman]

View attachment 109473
Busy cursor - they just still call it an hourglass. In Win10 we typically call it "The spinning circle of death" (especially if you forget to set it back to 1 in your code).

Very similar to your circle of dots, if they were spinning fast enough.

I will have to give that a try.

 

[Marshall Brooks]

It comes in handy for a lot of things in VBA that may take 20-30 seconds to load, but DON'T trigger the busy cursor.

You don't really want to code a PB or pop up a "Please Wait" message for that. But you don't want users clicking buttons b/c they think something should be happening, but it isn't.

As I said, just remember to turn it off especially before a MsgBox or InptBox or you'll have users saying "I need to input something here, but the systems been busy for 30 minutes ..." <OOPS!!!>

 

[Gasman]

Oh, it will not be for anything serious, just as an experiment.
I only use access for personal use now.

 

[Marshall Brooks]

It's nice to finally contribute as opposed to leeching information from here!

 

[Marshall Brooks]

Forgot to mention, it only works in Access and you can also use DoCmd.Hourglass = true/false - but that seems to reset when the current procedure ends: https://learn.microsoft.com/en-us/office/vba/api/access.docmd.hourglass

In Excel - it is Application.Cursor: https://learn.microsoft.com/en-us/office/vba/api/excel.application.cursor
In Word - it is System.Cursor: https://learn.microsoft.com/en-us/office/vba/api/word.system.cursor
In Outlook - it looks like it can be applied to a control on a form, without the control being clicked: http://www.vbaexpress.com/forum/showthread.php?16761-Solved-Is-there-an-Hourglass-in-Outlook
I couldn't find an equivalent for PowerPoint, although people asked.

Nice of Microsoft to be consistent across applications ...

 

[The_Doc_Man]

Nice of Microsoft to be consistent across applications ...

Oh, but they ARE. They confuse you uniquely every time.

 

[NeilT123]

Hi MB, is it possible to change how you are connecting to the office? I have recently moved my Access DB to a remote server which I now connect to via Remote Desktop and the performance is only slightly slower than when the database was in my office. The Front End and Back End are both on the remote server and even my most complicated form which has 6 subforms and also tabbed sub-subforms opens in less than 2 seconds.
Just a thought.

 

[Marshall Brooks]

Hi MB, is it possible to change how you are connecting to the office?

Yes, but 6 to one and half a dozen to the other ...

• When COVID19 first hit, I had a desktop in the office and they gave me a loaner laptop and let me remote into the desktop using it. That worked okay speed wise, but there were issues - mainly if IT re-booted or someone happened to turn off the desktop. I have a laptop now and they won't issue me a laptop and a desktop.
• In theory I could leave the laptop in the office and remote in from another PC (mine), but I don't have a spare PC to use, and that creates the same issues as before.
• I do have access to Citrix and can run the database off Citrix and it opens very quickly there. But 1-2 minutes for the database to load locally vs. 1-2 minutes for the Citrix OS to load and then 15 seconds for the database to load, plus I have apps locally that aren't on Citrix, so swapping back and forth.

 

[The_Doc_Man]

@NeilT123 - your idea is valid. By keeping everything between the FE and BE as a local operation, you surely reduce the network load. However, be aware that RDP solutions DO require careful configuration of the user's login and connection context to keep the FE as a distinct entity that is not directly shared. Some RDP admins don't do that step properly.

 

[Marshall Brooks]

Load subform later: start with empty subform and after the main form is loaded, reload the subforms (controlled by Form.Timer).
This takes longer (measured), but looks faster.

I ran into a slight glitch with this - actually a co-worker discovered it.

One of my subforms is a table imported from an Excel spreadsheet.

The width of the subform was 3.7201 - which is just wide enough to not require scrollbars and just narrow enough to show all the fields with no extra white space.

After the import, the scroll bars were present and I tried re-sizing the subform and 3.885 has the scroll bars and 3.89 has about a 1/4-inch gap on the right where it looks like the scroll bars should be.

I think the form had special effect sunken - but I think all of that should have carried over.

All of the main forms that include this type of subform are affected.

The other two subforms transferred over with no issues.

Any idea what I did wrong or how to correct it?

 

[Marshall Brooks]

I wasn't able to figure out why the Subform was not sizing properly - oddly, it did size properly on 3 of the 5 forms that used it.

I ended up loading it before the main form loads, but still loading the two other subforms after the main form loads.

Working fine again!