How to add a professional status bar in your Microsoft Access application?

To professionalized the UI experience of your application you need to tell the users that it is still processing in the background through the use of a status bar.  Below are the steps in creating a native form-type status bar where you could call anywhere in your application via VBA code.

First you should create a form and name it sysStatusBar or whatever you deem appropriate.  Set the form to POPUP and MODAL true as well as Dialog Border to None.

Create the following controls

1. Box1: Reactangle, Background=Transparent

2. Box2: Rectangle, Background=Blue

3. Percentage: Text Box, Background=Transparent, Foreground=Black

And then aligned them as shown below:

Write a code in the Form_Load event:

Private Sub Form_Load()
   box1.Width = 5200
   box2.Width = 0
   percentage = "0% Complete"
End Sub

Thats it.

Now, to use this form, see the code snippet below:

Public Sub updateInventory()
   Dim db As Database
   Dim rs As Recordset

   Dim lngTotalRecord As Long
   Dim lngRecord As Long
   Dim x

   Set db = CurrentDb
   Set rs = db.OpenRecordset("SELECT * FROM trnrritem")

   If rs.RecordCount > 0 Then
      DoCmd.OpenForm "sysStatusBar"
      rs.MoveFirst
      lngTotalRecord = 1
      Do Until rs.EOF
        rs.MoveNext
        lngTotalRecord = lngTotalRecord + 1
      Loop

      rs.MoveFirst
      lngRecord = 1
      Do Until rs.EOF
         updateWarehouseInventory rs!itemID, intWarehouseId, strBatchNumber, rs!BaseUnitCost

         Forms!sysStatusBar!percentage = Trim(Str(Int((lngRecord / lngTotalRecord) * 100))) & "% Complete"
         Forms!sysStatusBar!box2.Width = Int(Forms!sysStatusBar!box1.Width * (lngRecord / lngTotalRecord))
         Forms!sysStatusBar.Repaint

         rs.MoveNext
         lngRecord = lngRecord + 1
         x = DoEvents
      Loop

     DoCmd.Close acForm, "sysStatusBar"
  End If
  db.Close
  Set db = Nothing
End Sub

Now watch it in action.

Advertisements

2 thoughts on “How to add a professional status bar in your Microsoft Access application?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s