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
|
