Enterprise Software

Build Your Skills: Add a pseudo status bar to a Microsoft Access form

Customize Access applications

By Susan Harkins and Charlotte Foust

The Windows status bar provides an easy way to interact with Access users because it displays information about the selected data. However, the status bar isn't always available to share that information. When you decide not to include the status bar, you can simulate the status bar's purpose in the current form by displaying helpful information in a pseudo status bar that's part of the form itself.

Displaying data in the form's status bar
The process is simple: Just add the appropriate controls to a form and write a little code to fill those controls. The only thing that may get complicated is the code—depending on the information you want to share.

In this first example, you'll add label controls to a continuous form's footer to display the current record's primary key value, the current date, and the time. All three are easily acquired values that require little code. The example uses the Customers table in the Northwind sample database that comes with Access.

To get started, base a tabular form on the Customers table; refer to Table A to set a few properties.
Table A
Object Property Setting
Form Caption Status Bar
Default View Continuous Forms
Scroll Bars Vertical Only
Navigational Buttons No
Timer Interval 6000
txtCustomerID Visible No

Bind only the CompanyName, ContactName, and CustomerID fields to text box controls and name the controls txtCompanyName, txtContactName, and txtCustomerID, respectively. Name the form frmStatusBarLabels. To create the pseudo status bar, open the form's Footer and add three label controls, naming them lblPane1, lblPane2, and lblPane3. The resulting form should resemble the one shown in Figure A.

Figure A
This sample form is based on the Customers table.


You can see txtCustomerID tucked away behind txtCompanyName. (The invisible control is yellow, just for the sake of convenience—it isn't necessary to color the control.) You don't need to display the primary key values on the form itself.

Next, click the Code button to open the form's module and add the event procedures shown in Listings A, B, and C.

The Load procedure displays the time in the middle label control. The Timer procedure updates the time based on the Timer Interval of 6000. If there are records in the form's underlying recordset, the Current event displays the primary key value, CustomerID, for the current record in the label control to the left and the date in the label control to the right. Open the form in Form View to see the results shown in Figure B. Select any record to update the contents of the label control to the left (lblPane1).

Figure B
Open the continuous form in Form View.


A more complex example
This next example is more complicated because it more truly simulates the Windows status bar. By that, I mean that the Windows status bar displays the Description property text for the current control. That way, you can supply very specific instructions about what type of data the user must enter—the user need only glance at the status bar to get a little help when entering data. The first example didn't do that. That example just displayed data easily gleaned from the data or by using built-in functions.

The second form, shown in Figure C, is similar to the first in structure, except it now contains three data controls in the Detail section and two label controls in the footer. If you use the first form, be sure to move txtCompanyName and txtContactName to the right and expose txtCustomerID. Also, set txtCustomerID's Visible property to Yes. Name the form frmStatusBarLabelsMetadata. The label control to the left displays the bound control's Description property text. The control to the right displays the current record number.

Figure C
The label controls in the footer display the selected control's Description property from the underlying table and the record number.


Open the second form's module and enter the event procedures shown in Listings D, E, F, and G.

The three GotFocus event procedures pass the bound field's name to a function procedure named ReturnDescription(). The procedure in Listing G returns the current record number. To create ReturnDescription(), open a new module by choosing Module from the Insert menu and enter ReturnDescription() as shown in Listing H. Save the module as basReturnDescription, return to Access, and open the form in Form View.

Getting the focus triggers each control's GotFocus event, which passes the bound field's name to the ReturnDescription() function as the variable fldname. The ReturnDescription() function creates a schema recordset and populates it with metadata on the Customers table. The For-Each loop cycles through each field looking for the COLUMN_NAME field. When it is found, the If() function determines if the current record's field name matches that passed in fldname. If they match, the code grabs the Description property from the same record and assigns it to the function. If the field names don't match, the loop moves to the next record and checks again. In Form View, select any control in any record and watch the label control update accordingly.

Notice that when you select a company name or contact name entry, the label is blank. That's because neither field contains a Description property in the underlying table, Customers. The function will accommodate this without any further work on your part. The Nz() function prevents an error when the Description property is Null. You can change that by opening Customers in Design View and adding a Description property. At this point, close the form, open Customers, and add the following Description property text:
CompanyName: Name of customer's company.
ContactName: Customer's contact personnel.


Save the table, close it, and reopen the form. Now, select any CompanyName or ContactName entry, and you'll see that the label control automatically displays the Description text you just added.

Conclusion
Don't let the absence of a status bar stop you from sharing helpful information with your users. Just add a simple label control to a form and display the information there instead. In this article, a label control displays each underlying field's Caption property. Use this simple technique to display exactly the information your users need, in lieu of the Windows status bar.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

0 comments

Editor's Picks