Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 14th, 2004, 10:31 AM
Authorized User
 
Join Date: Dec 2003
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default Progress Messagebox

I have a large table of several tens of thousands of rows and about 30 columns and I'm using VBA to calculate the values of 20 columns from the other 10 for all the rows in the table.

It takes quite some time and I want to have my coding open up a messagebox to give information on progress - either number of rows processed or the % of total rows processed. The table can then be closed on completion

The VBA works through the table "MyTable" Until.EOF

Can anyone tell me how to do this?

Thanks

Andrew
 
Old July 14th, 2004, 06:20 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Andrew,

If you don't have the Developer Edition of Office (which includes the Progress Bar ActiveX control) you could roll your own text-based "progress meter" form. What I have in mind is a form that would open as a modal dialog, and display a string like:

"Processing record 1 of 1000" etc...

To get this going, create a form to use as your progress monitor, place a textbox on it, and set the control properties as follows:

Form: frmProgress
-----------------

Pop Up: Yes
Modal: Yes
Scroll Bars: Neither
Record Selectors: No
Navigation Buttons: No
Dividing Lines: No
Auto Center: Yes
Border Style: Dialog
Caption: Processing...

Textbox: txtProgress
--------------------
Enabled: No
Locked: Yes
BackColor: 2147483633

(Set the backcolor of the textbox to the backcolor of your form to make the textbox "transparent")

Now create a second form to do your processing and open the progress monitor form from a command button. Here is the code behind the command button. I'm just updating each of a thousand records by incrementing the field value by 1.

Private Sub Command0_Click()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim intRecordCount As Integer
    Dim strCounter As String
    Dim intWait As Integer

    Set cnn = CurrentProject.Connection

    Set rst = New ADODB.Recordset
    rst.Open "tblValues", cnn, adOpenKeyset, adLockOptimistic, adCmdTable

    ' Get record count.
    intRecordCount = rst.RecordCount

    ' Open modal progress monitor form.
    DoCmd.OpenForm "frmProgress", acNormal

    ' Process records.
    Do While Not rst.EOF

        ' Slow the processing down a bit so the textbox on the
        ' progress monitoring form has time to update and repaint.
        For intWait = 1 To 15
            DoEvents
        Next

        ' String to display progress.
        strCounter = "Processing record " & _
                rst.AbsolutePosition & " of " & rst.RecordCount

        Forms!frmProgress!txtProgress = strCounter

        ' Update Value field for each record in the recordset.
        rst!Value = rst!Value + 1
        rst.MoveNext
    Loop

    ' Close progress monitor form.
    DoCmd.Close acForm, "frmProgress"

    MsgBox "Processing complete."

End Sub

Your other option might be to use a graphical progress meter on the Access status bar (or combine this approach with the custom form). You can do that using the SysCmd() function as follows:

Private Sub Command0_Click()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim intRecordCount As Integer
    Dim varTemp As Variant
    Dim intWait As Integer
    Dim lngCounter As Long

    Set cnn = CurrentProject.Connection

    Set rst = New ADODB.Recordset
    rst.Open "tblValues", cnn, adOpenKeyset, adLockOptimistic, adCmdTable

    ' Get record count.
    intRecordCount = rst.RecordCount

    ' Initialize progress meter in status bar.
    varTemp = SysCmd(acSysCmdInitMeter, "Progress", intRecordCount)

    ' Process records.
    Do While Not rst.EOF

        ' Slow the processing down a bit so the progress meter
        ' has time to update and repaint.
        For intWait = 1 To 15
            DoEvents
        Next

       ' Increment progress meter.
       varTemp = SysCmd(acSysCmdUpdateMeter, lngCounter)
       lngCounter = lngCounter + 1

        ' Update Value field for each record in the recordset.
        rst!Value = rst!Value + 1
        rst.MoveNext
    Loop

    MsgBox "Processing complete."

    ' Clear progress bar.
    varTemp = SysCmd(acSysCmdClearStatus)

End Sub

HTH,

Bob

 
Old July 14th, 2004, 06:28 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Don't forgot to close the connection and recordset:

cnn.Close : Set cnn = Nothing
rst.Close : Set rst = Nothing

Bob

 
Old July 15th, 2004, 04:44 PM
Authorized User
 
Join Date: Dec 2003
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Bob,

thanks very much for the coding, I'll take a good look through it and get back to you with any queries if I may.

I tried running my code in my table today but gave up after 4.5 hours - really not sure if it had stopped or what. Your coding will certainly help to know what's going on!

Best wishes and thanks again,

regards

Andrew





Similar Threads
Thread Thread Starter Forum Replies Last Post
Progress Bar jmss66 VB How-To 6 December 16th, 2009 10:25 PM
Progress bar Poncho Pro VB 6 1 March 23rd, 2006 03:18 PM
progress bar treasacrowe Classic ASP Basics 11 February 11th, 2005 10:47 AM
Progress Bar toekneel Crystal Reports 0 October 19th, 2004 11:04 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.