ProgressBar in Access...

[PaulDalyTT]

Hi folks,

First post on this forum. I finally hit a problem that an existing post here doesn’t seem to explain and help solve!

Can anyone help me ProgressBar in Access?

I have an Access 2003 application which does several steps
1. FTP files
2. Load files
3. Run ~100 queries on data
4. Produce and Email excel spreadsheet
5. Housekeeping

This has been working fine for ages, but I decided to add a progress / status bar to the app so people know it hasn’t just died as it works away. Unfortunately, I can’t get it to work properly!


Essentially for the status bar, at each step, I initialize a form which contains a text box and a progressbar. As each step progresses, I increment the progressbar.

When I am finished one step, I initialize the form again with a new set data. See code below…

There are 2 problems I am experiencing though.

The progress bar does not update when run as part of my VB app. – the first init brings up the form and populates the text but no other updates appear to have any affect. (note that when I test the functions in a stand alone module, the progress bar increments as expected)

When the process finishes (after not updating the progress bar!), Access appears to hang… the normal user interface is not responsive and I have to close access down.

Any ideas???

Cheers

Paul


This is the init proc

Public Function InitProgressBar(StepDesc As String, PBMax As Integer)

    Dim Frm As Form
    Dim status As Control
    Dim PB As Control
    'Dim Left As Control
    'Dim Right As Control
    
    DoCmd.Close acForm, "Frm_StatusBox"
    DoCmd.OpenForm "Frm_StatusBox"
    
    Set Frm = Forms!Frm_StatusBox
    Set status = Frm!StatusBox
    Set PB = Frm!ProgressBar
    'Set Left = Frm!LeftBox
    'Set Right = Frm!Rightbox
    
    Frm.Visible = True
    Frm.SetFocus
    
    PB.Max = PBMax
    PB = 0
    
    Frm.Repaint
    
    status = StepDesc
        
    Frm.Repaint


End Function

Progress bar incrementor

Public Function IncrementProgressBar()
    
    Dim Frm As Form
    Dim status As Control
    Dim PB As Control
    'Dim Left As Control
    'Dim Right As Control
    
    Set Frm = Forms!Frm_StatusBox
    Set status = Frm!StatusBox
    Set PB = Frm!ProgressBar
    'Set Left = Frm!LeftBox
    'Set Right = Frm!Rightbox
    
    PB = PB + 1
    
    Frm.Repaint
    
    Frm.Visible = True
    Frm.SetFocus
    
 
End Function

call to init / increment in code

Call InitProgressBar("FTPing Files...", 3)

'ftp file 1

Call IncrementProgressBar
'ftp file 2
.
.
.
etc

 

[PHV]

I'd use the DoEvents function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PaulDalyTT]

hi PHV,

thanks for your response. I have tried adding DoEvents at the end of each of the 2 functions (init + increment) but its doesnt seem to do anything differently...

The process still runs thru, but no update is visiable on the form...

any other ideas? is putting DoEvents at the end of each of the 2 functions the correct place to put it?

cheers

Paul.

 

[CautionMP]

PaulDalyTT,
Couple of thoughts:
[ol]
[li]Is [tt]PBMax[/tt] set low enough that incrementing by 1 will register?[/li]
[li]Have you changed the [tt]Application.Echo[/tt] property somewhere in your code?[/li]
[li]Is Frm_StatusBox set as Popup & Modal making the application appear unresponsive when finished?[/li]
[li]You can probably replace the [tt]Repaint[/tt] calls with [tt]DoEvents[/tt].[/li]
[/ol]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[oharab]

PaulDalyTT,
I have an example file on my website that may be of some assistance

http://www.robotparade.co.uk/downloads/ProgressBar.ZIP

It's a very rough demo, but should give you some idea of how I've done it in the past.

The update part of my routine is routine looks like

    DoEvents 'Allow the form to update
    Me.boxTop.Width = (Me.boxTop.Width + sngIncrement) 'update the width of the progress bar
    Me.Repaint 'redraw the form

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[PaulDalyTT]

Hi folks,

Thanks for the replys...

CautionMP:
1. PBMax is anywhere from 3 to about 80... so increment by one should be enough. I have allowed the app to run thru to completion so should have seen some update!
2. I dont believe I have changed application.echo anywhere, but will need to double check the code...
3.Not sure if the form is Popup & Modal - i'll have to check. I just created a standard form and added a progress bar (activeX) and text box... so whatever the standard type of form is, i guess that's what I have!
4.I have tried both DoEvents and repaint but neither work...

The form opens, displays the first text update ("FTPing files..." in this case) but then does not display anything else. the interesting thing is, that as the code progresses, i can see the form 'icon' opening and closing in the windows Task Bar, so it looks to be doing something, just not updating on screen for me!

Also, I am pretty sure my functions work, because ina standalone chunk of VBA, I can open, close, reopen and progress the ProgressBar... Just something funky going on with the display in my app!

Ben, thanks for the download. So you are resizing the image rather than using an actual progress bar... interesting. I might give that a shot and see what happens!

Anyway, I'm not back in work until Tuesday and will try then


Thanks again for the suggestions guys!

Paul.

 

[oharab]

It's not an image, I've just overlaid 2 rectangles and formatted them to give the impression of a progressbar.

IIRC the process exactly the same as using a progress bar, I just happen to prefer the look of the faux menu bars.

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[TheAceMan1]

How are ya PaulDalyTT . . .

Is your progressbar a textbox, label, or an ActiveX Control?

If its a textbox or label then there's a big problem with the incrementor!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PaulDalyTT]

HI folks,

Thanks very much for your help so far in trying to get this working. Much appreciated!

In follow up to some of earlier questions (back at work now so can look at the app rather than try to remember!)

application.echo is not set anywhere in my code. Should I be using it? I do have DoCmd.Echo False set in several places... could this be causing the problem??

Both Modal and Popup are set to NO in the Frm_StatusBox form. Is this correct

TheAceMan1:
The progress bar itself is an ActiveX status bar.... doing nothing with it other than what's in the code snippet in my first post.


Thanks again for the help guys! any further suggestions would be appreciated!


cheers,

Paul.

 

[PHV]

I do have DoCmd.Echo False set in several places
I'd comment out those lines.
Maybe you think the application hangs just because the Echo is disable ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PaulDalyTT]

Eureka!!

i commented out the DoCmd.echo lines in my functions and the form and bar now display perfectly.

Thanks for all the help with this one guys. Tek-tips strikes again!!

cheers,

Paul.