Problem while using Progress bar/status bar in Access

Question

Hello experts,

I want to use statusbar in progress bar in my access project, hence I 've writed a sample code on onclick event of a button to do that. I have this expectation, that if I click this button, the status bar appears, but unfortunattly It's not so, but if I clos my access-project then the statusbar and progressbar appears. would you please help me to solve this prolem?

hier is my code:

Private Sub Befehl80_Click()
 Dim Progress_Amount As Integer, RetVal As Variant
  RetVal = SysCmd(acSysCmdInitMeter, "Reading Data...", 2000)
  For Progress_Amount = 1 To 2000
  RetVal = SysCmd(acSysCmdUpdateMeter, Progress_Amount)
  Next Progress_Amount
 
End Sub

  

Answer 1

Your code works, but works so fast you can't see it working. Then it finishes working. Only you don't do:

 ' Remove the progress meter.
   RetVal = SysCmd(acSysCmdRemoveMeter)

http://support.microsoft.com/kb/210474

So I guess this is why your progress bar is there next time you open the application.

---

Matthew Slyman M.A. (Camb.)

Answer 2

Put some other code inside the FOR loop, and you should effectively slow down the progress bar enough to see what's happening. If nothing else, try simply inserting a "DoEvents" statement.

---

Matthew Slyman M.A. (Camb.)

Answer 3

Thank you Mathew for your answer :)

During the performing of this Job, I want to show the progress bar:

Set db = CurrentDb
           
ssql = "DELETE FROM Test_Table"
db.Execute ssql, dbFailOnError
           
ssql = "INSERT INTO Test_Table SELECT DISTINCT tb_KonzeptDaten.DFCC,  " _
  & "tb_KonzeptDaten.OBD_Code AS Konzept_Obd,tb_KonzeptDaten.DFC " _
  & "FROM tb_KonzeptDaten"
db.Execute ssql, dbFailOnError

Where should I put this block of code? between for loop?

Answer 4

Unfortunately, Jet does not expose any event/method to follow the progression of a query.

Answer 5

The SQL is executed as a single batch regardless how many (2000?) records need to be read.  Unfortunate, your VBA code does not see the processing internally in database engine.  Thus, as posted, you code can only see 2 discrete steps: The first Execute for deletion and the second Execute for addition.  Thus, the progress meter, if used with the above posted code can only show 0% (start), 50% (after deletion) and 100% (after addition).  In addition, the progress indicator is, most likely, misleading also since deletion can be performed much faster than addition.

I am not sure if the progress meter is even useful in this case...

---

Van Dinh

Answer 6

Hi babak.per,

You may try the Hourglass method.

DoCmd.Hourglass Method (Access)
http://msdn.microsoft.com/en-us/library/office/ff835648.aspx 

Have a nice day.

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us