Progress Bar on Form

[Privateer]

I have this import procedure that is bringing in over sixty thousand rows of Excel data and then parsing the semi-colon separated strings into individual columns. All of this is working well. The question is about the progress bar to show the user that Access is working on this task and not locked up. So, I have this text box about three inches wide and I change the back color to blue and change the width as other procedures complete their tasks. However, once this parsing procedure gets started, (do until .EOF) the progress bar in the text box will not advance. I am using a calculation on the record count and the percent position and MOD to get a remainder of zero every twenty thousand rows, so I should get three bar movements during this procedure, but the form just doesn't do any updates. And the remaining procedures run pretty quickly, so the bar goes from about twenty percent complete to a long pause (1-2 minutes) to done, with no updates during the bulk of the work. In other words, the bar is useless. I would prefer not to use the system bar; so, I was just wondering if anyone knows how to pause a big loop and allow the form to get updated so the user doesn't think Access has crashed.
Thanks,
Mike

 

[CJ_London]

Perhaps if you used sql rather than vba you wouldn’t need a progress bar because it would be so much faster.

as regards your bar it may be it is not being refreshed, hard to say without seeing your code but you could try putting

doevents

just before the bar is updated

 

[MajP]

Here are a couple of meters you can use.
In most cases you need to make sure you add DoEvents as you iterate so you give control back to the display. If not the display will not update.

 

[The_Doc_Man]

I included a counter inside my big loops and had a rough guesstimate of how many records I was processing. So I made my counter "push" the next step of the progress bar inside the loop by dividing the record count (for the loop) by about 10. That is, every 1/10th of the total count would be represented by one progress bar step. Adding that test DID slightly slow down the loop - but only slightly.

Since you said you had an explicit loop (to .EOF=TRUE), that means you have code inside the loop.

Another technique I used sometimes was to look for markers in the numeric PK by determining whether the PK (as a number) MOD step-interval (which was a variable equal to about 1/8th of the record count) was equal to 0. There are tons of ways to do this.

 

[KitaYama]

@MajP You may want to check Your class. Follow these steps in MajPSingleProcessMeter.accdb.

1- On frmTestMeter click "Test Meter" Button.
2- While the progress bar is progressing, right click frmMeter form and select Close.
3- Click Exit button on frmTestMeter
4- Click frmMeter and select Design view.

Your class falls into an infinite loop of errors.

This is a gif of what I have.

 

[moke123]

@MajP You may want to check Your class. Follow these steps in MajPSingleProcessMeter.accdb.

1- On frmTestMeter click "Test Meter" Button.
2- While the progress bar is progressing, right click frmMeter form and select Close.
3- Click Exit button on frmTestMeter
4- Click frmMeter and select Design view.

Your class falls into an infinite loop of errors.

This is a gif of what I have.

I would expect that error as the underlying code is still running and looking to update the form you closed.

 

[KitaYama]

I would expect that error as the underlying code is still running and looking to update the form you closed.

Yes, I think so. But if I go through the same steps when the progress bar is over 50%, the error messages are not in a loop. It disappears after 3 times

 

[MajP]

Do not go into design view of a form being called within a loop. Wait till the loop is complete. That is not something a user would be doing.

 

[KitaYama]

That is not something a user would be doing.

But that's what a developer does. Testing if the task performs OK. and going into loop of errors is not cool.
Anyway, if it's what it's supposed to be, I suppose it's OK then. Just thought you may want to add a test if the form is loaded yet.

 

[moke123]

Just looked at my progress bar class and a simple fix is to set right click shortcut menu to false

m_objpfrm.ShortcutMenu = False

It does appear to pause the progress but doesn't seem to error.

 

[MajP]

But that's what a developer does. Testing if the task performs OK. and going into loop of errors is not cool.
Anyway, if it's what it's supposed to be, I suppose it's OK then. Just thought you may want to add a test if the form is loaded yet.

I kind of disagree. Normally a developer tests to ensure the User does not throw un trapped errors, not to ensure that another developer does not do something correctly. In this case the error is not in the class because the class checks if the form is loaded. That is why you can close out the progress bar and the loop would continue to do your tasks in the background. The short fall is in the fact a form can be loaded, but in design view. You can check if something is in design view by checking its "currentview" property. See if this fixes it. So now it has to be loaded and not in design view.

 

[gemma-the-husky]

You can update the progress meter within your loop as it reaches the count targets. Maybe that isn't working as it should.
Also add the DoEvents as @MajP mentioned earlier. It can't hurt, and it may be what's needed.

 

[KitaYama]

I kind of disagree. Normally a developer tests to ensure the User does not throw un trapped errors, not to ensure that another developer does not do something correctly.

I wasn't testing to ensure you've done it correctly or not. I know you're good in this and I was trying to learn something of your solution. Anytime you post something, I go through it to learn how to write a class correctly.
I'm sorry if I sounded like criticizing your class. I really didn't mean it that way.

And thanks for the corrected version.

 

[MajP]

I think this version is a little simpler. I do not think you need to make an extra class. Instead use a popup form but add additional properties to make this easier. I think you want to be able to tweak the duration between updates so you can actually see the form update. As the list gets bigger shorten the duration. If not it just flys by.