Forms in MS Access are really just special glorified classes, so you can create new instances of them from code like this.
Dim someForm as Form_Form1 set someForm = New Form_Form1 someForm.visible = true
I decided to take advantage of this and create a progress bar form to display during long running processes. I created a form named
ProgressBar with a rectangle and a text box.
Option Compare Database Option Explicit ' Class: Form_ProgressBar ' Popup progress bar Private Const MaxBoxWidth As Long = 7200 Public Enum ePBarModeType PBarMode_Percent = 0 PBarMode_Executing = 1 End Enum Private mMode As ePBarModeType Private mCurrentProgress As Long Private mSteps As Long Public Property Get PercentComplete() As Double 'read only PercentComplete = mCurrentProgress / mSteps * 100 End Property Public Property Let Mode(PBarMode As ePBarModeType) mMode = PBarMode End Property Public Property Get Mode() As ePBarModeType Mode = mMode End Property Public Property Let CurrentProgress(lng As Long) ' Updating the CurrentProgress property updates the status of the Progress Bar mCurrentProgress = lng ' format #0 makes a 1 or 2 digit number without decimals If mMode = PBarMode_Percent Then Me.txtStatus = Format(Me.PercentComplete, "#0") & " % Complete" ElseIf mMode = PBarMode_Executing Then Me.txtStatus = "Executing..." End If ' boxProgress.Width = a percentage of maximum box width Me.boxProgress.Width = (mCurrentProgress / mSteps) * MaxBoxWidth Me.Repaint DoEvents End Property Public Property Get CurrentProgress() As Long CurrentProgress = mCurrentProgress End Property Property Let steps(lng As Long) mSteps = lng End Property Public Sub init(steps As Long, Mode As ePBarModeType, Optional strCaption As String = "Loading...") Me.Mode = Mode Me.Caption = strCaption mCurrentProgress = 0 mSteps = steps Me.txtStatus = "Ready" Me.boxProgress.Width = 0 Me.Visible = True End Sub
Private Sub exampleCall1() ' example call for using progress bar with a looping process Dim pbar As Form_ProgressBar Dim i As Long Dim steps As Long steps = 100000 Set pbar = New Form_ProgressBar With pbar .init steps, PBarMode_Percent, "Hey, I'm working here!" For i = 1 To steps ' do something in a loop ' update progress .CurrentProgress = i Next i End With Set pbar = Nothing End Sub Private Sub exampleCall2() ' example call for using progress bar with an adodb.command Dim bimic As New BiMic_Wrapper Dim cmd As New ADODB.Command Dim prm As ADODB.Parameter DoCmd.HourGlass True With cmd .ActiveConnection = bimic.Connection .CommandType = adCmdStoredProc .CommandTimeout = 0 .CommandText = "dbo.uspSomeStoredProcedure" End With 'must execute command async cmd.Execute , , adAsyncExecute Dim pbar As New Form_ProgressBar Dim i As Long pbar.init 10000, PBarMode_Executing, "" Do While cmd.STATE = adStateExecuting For i = 0 To 10000 pbar.CurrentProgress = i Next i Loop Set pbar = Nothing DoCmd.Hourglass False End Sub
- Am I really gaining anything from using a full fledged property for
Mode? It's a simple Get & Let. Would it be cleaner to simply use a Public variable instead?
- Am I doing enough to ensure that the status doesn't display anything stupid? (Like displaying 103% complete.)
I feel like this is a little obscure. I was thinking of splitting the logic into a private function with a decent name. Is it worth it? What would you name it?
' boxProgress.Width = a percentage of maximum box width Me.boxProgress.Width = (mCurrentProgress / mSteps) * MaxBoxWidth