I am getting into VBA and having problems sorting a progress meter I am trying to develope. I have a macro the updates tables in one database and trasnfer the updates to another database. Users want to see progress meter in percentage when the updating is going on so that they can do other things before it finishes. I created this as below but nothing comes up;

    Dim varStatus As Variant
    Dim strStatus As String

    strStatus = "Updating ProAchieve DrillDown"
    varStatus = SysCmd(acSysCmdInitMeter, strStatus, 100)
    varStatus = SysCmd(acSysCmdUpdateMeter, 100)
    varStatus = SysCmd(acSysCmdClearStatus)

    DoCmd.RunMacro "M_ProAch_Update"

How can I get it right. I am a beginer so I will appreciate a step by step procedure.

Anyone there to help.

azambee

I do not know how to resolve the syscmnd issue but you can try this and see if it will give you the same result.

Build a rectangle box and increment its "width" property as your process progresses. Just make sure that you set a background color and that it's initial width value is set to 0.

you can also set your width value in a text box on top of the rectangle to represent a percentage of the process.


Thanks,
Jesse

Azambee,

It sounds to me like you're trying to run the SysCmd() function from MS-Access/VBA to update the progress meter found in the status bar in the lower-left of your window.

Your mention of copying tables could be done like the following:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim lngCurrRow As Long ' Current row number
Dim lngTotalRows As Long ' Number of total rows in the table

' Open the database and recordset objects
Set db = CurrentDb
Set rs = db.OpenRecordset("Customers", dbOpenSnapshot)

' If no rows in input table, exit sub/function
If rs.BOF and rs.EOF Then
   rs.Close
   db.Close
   Exit Sub
End If

' Position at the last row in the recordset
rs.MoveLast

' Obtain the total number of rows
lngTotalRows = rs.RecordCount

' Initialize the system meter
SysCmd acSysCmdInitMeter, "Loading entries", lngTotalRows

' Position at the first row in the recordset - you definately
' want to be sure to do this after positioning at the last row
' a couple of steps back.
rs.MoveFirst

' Initialize the current row counter
lngCurrRow = 1

' For each row in the recordset,
While Not rs.EOF

   ' Update the system meter
   SysCmd acSysCmdUpdateMeter, lngCurrRow

   ' INSERT YOUR CODE HERE

   ' Position at the next row in the recordset
   rs.MoveNext

   ' Increment the current row counter
   lngCurrRow = lngCurrRow + 1

Wend

' Clear the status line
SysCmd acSysCmdClearStatus

' Close the recordset and database objects
rs.Close
db.Close



First of all, you can have any text you'd like in the InitMeter SysCmd command (if any). Also, depending on the total number of entries to be processed, the meter will automatically update with each evenly-divided group of entries. It may increase one block for each 10, 700, or 20,000 entries for example.

Hope this helps and good luck on your project.

Best Wishes,

Warren
:D

Warren's got the right idea.
Your code WAS actually working - and if you had a computer that ran one instruction per minute, you'd probably see that.
Basically you were setting up the meter, changing it, then clearing it, all before you do anything else, so it all happened, but only in a matter of nanoseconds.

Steven

I am a loud man with a very large hat. This means I am in charge

Hi Warren,

G'day mate, I would like to thank for the code, that you have given us, however the code which you had given works very well, in the sense it does loop through and debug.print at the time of debugging.

But the problem is, it does not show up in the progress bar, and in the startup I have checked display status bar.

Your speedy replies, would be highly appreciateable

Thanks,

Antonio