Custom Toolbars in Access

Your Access application will look more impressive and be easier for the users if you add some custom toolbars. One way of doing this is to right-click on one of the existing toolbars and select Customize but it's safer to write some VBA code and let the application create the toolbars for you. Access gives you complete control of these custom toolbars. You can create and destroy them and determine whether or not they are visible to a particular user.

[Access form with custom toolbar]

This example shows a simple Customer form with its own toolbar. This toolbar has the correct Windows icons and a familiar tooltip and will give the user confidence in the quality of the database you've developed.

References for a custom toolbar

The code that creates this toolbar is in the Load event of the Customer form. Before writing any code you must ensure that you have a reference to the right library. The toolbar and its buttons are instances of a CommandBar and CommandBarButton objects and these are not part of the core of Microsoft Access. You must tell Access where it can find them.

Open a code editing window and select References... from the Tools menu.

[Access References dialog]

The contents of this list will vary with the software already installed on your PC. You need to scroll down to the block of Microsoft software and tick the entry for the Microsoft Office Object Library. This example shows an entry for Access 97 in Office 8.0.

Note that this reference will have to be updated if you try to run this code on a machine running a different version of Microsoft Office. If you copy the database to a machine running Office XP then you will be able to open the database with Access XP but you will get an error message saying that it cannot find the Office 8.0 library. The solution is to open the code window on the new PC, clear the Office 8.0 tickbox and tick the box for the version of Office running on that PC.

Create a custom toolbar

The first thing to do is to create the toolbar as a CommandBar object:

'-- Create a command bar with three buttons
Set myBar = CommandBars.Add(Name:="Customer", _
            Position:=msoBarTop, _
            Temporary:=True)
With myBar
  .Controls.Add Type:=msoControlButton, Id:=539
  .Controls.Add Type:=msoControlButton, Id:=4
  .Controls.Add Type:=msoControlButton, Id:=644
.Visible = True
End With

The Temporary property is set True to define this as a temporary toolbar. It will disappear when the database is closed. This property defaults to False and any toolbar that you create will persist and reappear the next time that you open the database. If you do use a permanent toolbar then you must add error-handling code to ensure that you do not try to create it a second time.

A command bar can hold several different types of control and the Type parameter here declares that we are adding three command buttons. The Id parameter defines the icon that will appear on these buttons and the action that the button will perform if we do not override it later. Set an Id of 1 if you want a button with no icon.

Define the buttons

Each button on the toolbar is a member of the Controls collection of the CommandBar. This fragment of code defines the third button on the toolbar:

'-- Define the Delete button
With myBar.Controls(3)
  .Style = msoButtonIconAndCaption
  .Caption = "Delete Customer"
  .OnAction = "=DeleteCustomer()"
  .TooltipText = "Delete all records of this customer"
End With

The Style property is set to show an icon and some text. You can also choose msoButtonIcon or msoButtonCaption to show an icon or some text alone.

The OnAction property defines the action that must be performed when this button is clicked. In this example it calls a DeleteCustomer method of the form.

Show and hide a toolbar

Use the Visible and Enabled properties of the CommandBar object to control whether or not the user can see and operate the buttons on the toolbar.

Removing a toolbar

This code in the form's Unload event removes the toolbar from the collection of CommandBars. There is always the possibility that the user might have closed the toolbar manually so we have to work around this possible error.

'-- Simple-minded error handling in case the user has
'-- already deleted this toolbar.

On Error Resume Next
CommandBars("Customer").Delete

Access Tips

FoxPro Tips

General Tips

 

Related Items

Frequent, automatic backups

How to create backups automatically in FoxPro or Access using the Windows Scripting Host

Read More

Trimming text in VBA and VFP

Both Visual FoxPro and Visual Basic for Applications let you trim leading and trailing spaces from string of text

Read More

Finding characters in VBA and VFP

Both Visual FoxPro and Visual Basic for Applications let you find the position of one character inside another string.

Read More

Changing between upper and lower case in VBA and VFP

Both Visual FoxPro and Visual Basic for Applications let you chnage text between upper and lower case.

Read More

American date formats used by Access SQL

Access uses an American date format in SQL commands

Read More