Progress bar during update not working

[ITguy1981]

I currently have a form that is just a progress bar. It uses an timer on open to basically always run a bar from left to right to show processing data. I have another form that imports an excel file. Here is the code for that:
Private Sub Update_Click()

On Error GoTo Err_Update_Click
With DoCmd
.SetWarnings False
.OpenQuery "updated table delete query"
.SetWarnings True
.TransferSpreadsheet transfertype:=acImport, tablename:="Updated Table", FileName:=Me.BrowseTextBox, HasFieldNames:=True
End With
Exit_CmdImportExcel_Click:

Exit Sub
Err_Update_Click:
MsgBox Err.Description
Resume Exit_Update_Click

Exit_Update_Click:
End Sub

I would like to have the form "progress" open when I click the "update' button, run the above code to import the excel file, and after it's done, close the form "progress". I tried using the DoCmd to open the form at the beginning of the above code and putting a close on the "progress" form at the end of the code. When I do that, I click update, the form "progress" opens, but the progress bar stays idle. Once the excel file is imported the progress bar starts to run. I'm not sure what I need to do to fix the issue. If you need any other code I can provide it. Thank you in advance.

 

[ITguy1981]

Just in case you need to see how my progress form works, here is it's code:
Option Compare Database
Option Explicit
Dim ClockName As Integer
-----------------------------------------
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
ClockName = 1

Exit_Form_Open:
Exit Sub
Err_Form_Open:
MsgBox Error$
Resume Exit_Form_Open
End Sub
----------------------------------------
Private Sub Form_Timer()
On Error GoTo Err_Form_Timer
ClockName = ClockName + 1
If ClockName > 26 Then
ClockName = 1
End If
Select Case ClockName

Case 1
Me![Box0].Visible = True
Me![Box1].Visible = False
Me![Box2].Visible = False
Me![Box3].Visible = False
Me![Box4].Visible = False
Me![Box5].Visible = False
Me![Box6].Visible = False
Me![Box7].Visible = False
Me![Box8].Visible = False
Me![Box9].Visible = False
Me![Box10].Visible = False
Me![Box11].Visible = False
Me![Box12].Visible = False
Me![Box13].Visible = False
Me![Box14].Visible = False
Me![Box15].Visible = False
Me![Box16].Visible = False
Me![Box17].Visible = False
Me![Box18].Visible = False
Me![Box19].Visible = False
Me![Box20].Visible = False
Case 2
More of the same data all the way to Case 26
End Select
Exit_Form_Timer:
Exit Sub
Err_Form_Timer:
MsgBox Error$
Resume Exit_Form_Timer
End Sub

 

[spikepl]

1. Your progress bar is not a progress bar but a timer, since it does not depend on anything other than the clock.
2. The TransferSpreadsheet is indivisible - i.e. the program control remains there until finished. If you want a true progress, you'd have to do the reading yourself and count the records but of course you don't know how many lines there are at the outset, so that would be rather tricky,

My recommendation would be to use your programming skills on something else. Put up a message in a form saying "Reading in progress - takes about a minute (or whatever)" and close that form on finish.

 

[tehNellie]

There is an inbuilt progress bar in Access, why not just use that instead?
No need to create forms, faff around trying to pass data backwards and forwards. One line of code to initialise it, one line of code to update it

dim varReturn as Variant

'initialise the progress meter to 1-100 (%)
varReturn = SysCmd(acSysCmdInitMeter, "Doing Something", 100)

'update the progress
varReturn = SysCmd(acSysCmdUpdateMeter, intNewPercentageComplete)

If you were looping through a recordset to run a number of different queries, a basic update would be to use the rs.percentposition property. Granted it can't tell the difference between a query that takes 2 seconds over one that takes 10 minutes to complete, but it's a handy way of updating easily how far through a process you happen to be.

 

[spikepl]

#4 The Access progress bar is an option, yes. But not when you have an atomic process, like in this case.

 

[smig]

The access progress bar control is not an option exactly from the same reason using many text boxes, or any other progress bar you might have/create is not...

If you can't split your running code to update the progress bar nothing will work. only the small progress bar at the buttom (status bar)

 

[ITguy1981]

Thank you for all of the responses. Is there a way to code the progress bar from the bottom of the screen on the status bar to a form. The progress bar at the bottom of the screen is accurate. I just have a feeling people won't pay any attention to it.

 

[ITguy1981]

So i've decided to just use a form to alert the user that they need to wait; however, when the update button is clicked, the form opens as blank, texts fills after the excel file imports, and the alert closes. So far i've read that there is not wait function in Access. Is there any way I can get my "Please Wait" form to completely open before Access starts the Excel Import? Here is my current code:
Private Sub Update_Click()
On Error GoTo Err_Update_Click
With DoCmd
.OpenForm "Please Wait", acNormal
.SetWarnings False
.OpenQuery "updated table delete query"
.SetWarnings True
.TransferSpreadsheet transfertype:=acImport, tablename:="Updated Table", FileName:=Me.BrowseTextBox, HasFieldNames:=True
.Close acForm, "Please Wait"
End With
Exit_CmdImportExcel_Click:

Exit Sub
Err_Update_Click:
MsgBox Err.Description
Resume Exit_Update_Click

Exit_Update_Click:
End Sub

 

[NigelShaw]

Hi

I have many progress bars in some of my apps but I had a scenario very similar to yours. In this case, I made a form and placed a gif image on it that displayed a continuous rotating circle of dots. This worked really well for me


Cheers


Nidge