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
About

Software developer living in the Philippines.

Posted in Microsoft Access
2 comments on “How to add a professional status bar in your Microsoft Access application?
  1. keem says:

    trying this on my code, but i have a lot of loops within loops, can you send me a simple sample on how your code is counting the records?

  2. […] found this code at https://hgminerva.wordpress.com/2011/…s-application/ Can you rewrite the code and send it to me and I will try it??? Reply With […]

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

Author

Harold Glenn P. Minerva
Software Developer / Tech Enthusiast
Living in the Philippines

View Harold Glenn Minerva's profile on LinkedIn

Instagram

Software Engineer - Seasonal and Range Trading Software. Magenta Trader is a powerful stock market visualization software that increases your probability of trading success.

Software Architect and Founder - Easyfis.com is a multi-tenant cloud-based Software-as-a-Service (SaaS) business app that caters to micro, small and medium trading businesses.

CTO and Co-Founder - We give your company the leverage by providing innovative software solutions products such as Point-of-Sales (POS), Financial Information System (FMIS), Payroll and DTR (HRIS), and many more.

%d bloggers like this: