Possible bug in Access 2000? 1

[JimmyL]

I have a simple routine running from the click event of a button on a Access 2000 form. The button does some file operations ultimately updating an Excel spreadsheet of all the PC Users in our company.

Next to the button are some progress labels with tick images next to them which are set to Visible=No at design time. The idea of these labels and ticks is that as each successive operation is peformed by the Visual Basic routine behind the button, each successive label is ticked giving a visual idea of how far through the entire process we are. This is my attempt of mimicking the sort of tick-display you will see when you uninstall some software using UninstallShield. As each operation is done by the Visual basic, a tick is made visible next to the label describing that operation.

My problem is that my lovely ticks do not appear in order when I run the Visual Basic normally (by clicking on the button). They only appear right at the end of the lengthy process, after all the Visual Basic operations have been done. However, when I step through the code in break mode the ticks appear one at a time down the form, perfectly in order and at the right time!

Is this a bug in Access 2000?

My code (greatly simplified) is presented below:-

================ CODE =============================

Private Sub cmdUpdateTable_Click()

Dim fso As Object, excelApp As Object

' After the update button is clicked the Update progress labels are made visible
' on the main form
Forms!frmMain!lbl1.Visible = True
Forms!frmMain!lbl2.Visible = True
Forms!frmMain!lbl3.Visible = True

' Copy UserList.xls from network drive into C:\Data\UserList2.xls
Set fso = CreateObject("Scripting.FileSystemObject&quot
fso.CopyFile "N:\Admin\UserList.xls", "C:\Data\UserList2.xls"

' Make tick visible next to lbl1 ("UserList.xls copied onto C-drive&quot
Forms!frmMain!imgTick1.Visible = True

' Open excel and C:\Data\UserList.xls and delete the data in range A1 to D20
Set excelApp = CreateObject("excel.application&quot
excelApp.Visible = False ' I don't want to see Excel opening
excelApp.Workbooks.Open FILENAME:="C:\Data\UserList.xls", updatelinks:=False
excelApp.Workbooks("UserList.xls&quot.sheets("list&quot.Range("a1:d20&quot.delete

' Make tick visible next to update label 2 ("UserList.xls opened and A1-D20 deleted&quot
Forms!frmMain!imgTick2.Visible = True

.
.
.
. Etc..........
.
.
.

End Sub

=================================================

 

[CptTom]

Have you tried putting the screen repaint command at the end of each routine? That should repaint the screen and display controls based on any changes in criteria.

Larry

 

[JimmyL]

Hi Larry!

You are a 24 carat genius!

I simply used Me.Repaint after each "Forms!frmMain!lblx.Visible = True" command and it worked!

Many Thanks!

JimmyL B-)

 

[Ed2020]

Why not use the ActiveX progress bar control instead?

Ed Metcalfe.

 

[JimmyL]

Hi Ed,

I've found the control: Microsoft ProgressBar Control 6.0 SP4, and can easily change its appearance, but cannot work out how to link it to my procedure.

Any ideas?

Cheers, Jimmy

 

[Ed2020]

There isn't really an easy answer to this one. You update it by passing the percentage complete value of the procedure. Unfortunately you have to calculate the percentage complete yourself.

If you are cycling through records it is fairly easy to calculate accurately how far through the process you are. For other tasks I simply break the code down into stages and say when this stage is complete we are 25% through the process............ etc. etc..

Does this make sense?

Ed Metcalfe.

 

[JimmyL]

HI Ed,

I understand what you are saying. How does the control accept a percentage that I pass to it? What property controls the "progress" itself?

Cheers, Jimmy

 

[Ed2020]

Jimmy,

You need to pass a value from 1 to 100. For example:

Me!prgMyProgressBar.Value = 50

HTH,

Ed Metcalfe.

 

[JimmyL]

Hi Ed,

I'll try this.

Cheers, Jimmy