Robert Flake
Microsoft Corporation
December 18, 1996
The following articles contain useful information about using the new command bars feature in Microsoft® Access 97. In previous versions of Microsoft Access, the only way to create and modify menu bars, toolbars, and shortcut menus was to use menu bar macros or the Customize Toolbar dialog box. In some cases, you had to use Windows® API calls to in order to make certain modifications to these elements of the user interface.
In Microsoft Access 97, menu bars, drop-down menus, toolbars, and shortcut menus are all types of command bars. You can add and modify these command bars by using the Customize dialog box and the property sheets available by pointing to Toolbars on the View menu and then clicking Customize. You can also use the new command bar object model to create and modify command bars in Visual Basic®.
Note In order to program with command bars in Microsoft Access 97, you must first set a reference to the Microsoft Office object library. Click References on the Tools menu while in module Design view, and select the check box next to Microsoft Office 8.0 Object Library.
Once you've set a reference to the Microsoft Office object library, you can view the Microsoft Office objects, properties, and methods in the Object Browser. The objects that you can use to program command bars include the CommandBar, CommandBarControl, CommandBarButton, CommandBarComboBox, and CommandBarPopup objects, and the CommandBars and CommandBarControls collections.
These new features are described in the Microsoft Office 97 and Microsoft Access 97 Help topics. The following sections give useful tips and solutions to common command bar problems that may not be covered explicitly in the Help topics, or they point out especially useful techniques for working with command bars.
The new command bars object model in Microsoft Office 97 lets you programmatically create and modify the various kinds of command bars: menu bars, toolbars, and pop-up menus. Pop-up menus appear in three ways: as menus that drop down from menu bars, as submenus that cascade off menu commands, and as shortcut menus. Shortcut menus (also called "right-click menus") are menus that appear on the screen when you right-click a particular area on the screen; for example, a blank area on a form or report, or a form control.
In Microsoft Access 97, you can create a custom shortcut menu by using the Customize dialog box. For more information, see the section "Creating Custom Shortcut Menus Using the Customize Dialog Box." You can also create custom shortcut menus by using Visual Basic. To do this, you use the Position argument of the Add method of the CommandBars collection. To create a custom shortcut menu named OrdersPopup, you can use the following syntax:
Dim mybar as CommandBar
Set mybar = CommandBars.Add(Name:="OrdersPopup", _
Position:=msobarPopup)
Once you create a custom shortcut menu, you can modify its properties by using Visual Basic. For an example of creating and modifying a custom shortcut menu, see "Example: Creating a Custom Shortcut Menu."
Note You can use the Add method to create a custom shortcut menu, but you can't change an existing command bar of another type to a shortcut menu by using Visual Basic. The Position property for command bars has an msoBarPopup setting. However, this setting is read-only; it is used to determine whether an existing command bar is a pop-up menu. You can't set this property to change the type of a command bar to msoBarPopup.
You can change the type of a command bar by using the Toolbar Properties dialog box in Microsoft Access (see the section "Creating Custom Shortcut Menus Using the Customize Dialog Box"). However, if you want to change an existing command bar to a shortcut menu in Visual Basic, you must use the Delete method to delete the existing command bar and the Add method to create the new shortcut menu. You can also copy an existing pop-up command bar and then modify it to include the controls you want. For information about copying a command bar, see the section "Copying Command Bars."
This example creates a custom shortcut menu named OrdersPopup, and adds commands to this shortcut menu. The example also sets the ShortcutMenuBar property of the Orders form to the name of this custom shortcut menu. When you right-click in a blank area of the Orders form, Microsoft Access displays the shortcut menu. Note that the procedure uses the GetID function to determine the ID numbers for several built-in Microsoft Access commands. For information on this function, see "Finding the ID Number for a Built-in Command."
Sub CreateOrdersShortcut()
Dim cmdBar as CommandBar
Dim ctlNew as CommandBarButton
Const conShortcutAlreadyCreated = 5
On Error GoTo CreateOrdersShortcut_Err
Set cmdBar = CommandBars.Add(Name:="OrdersPopUp", _
Position:=msoBarPopup)
' Add the built-in controls Filter By Form, Apply Filter/Sort
' and Remove Filter/Sort. The GetID function finds the ID value
' for each of these built-in controls.
With cmdbar
.Controls.Add msoControlButton, _
GetID("Filter Context Menu", "Filter By Form")
.Controls.Add msoControlButton, _
GetID("Records", "Apply Filter/Sort")
.Controls.Add msoControlButton, _
GetID("Records", "Remove Filter/Sort")
End With
' Add a command button control that prints the current record.
Set ctlnew = cmdbar.Controls.Add(msoControlButton)
With ctlnew
.BeginGroup = True
.Caption = "Print Current Record"
' The = and () aren't required for the OnAction property
' setting, but they can be used to distinguish functions
' from macros.
.OnAction = "=PrintRecord()"
.Style = msoButtonCaption
End With
' Attach the new shortcut menu to the Orders form.
DoCmd.OpenForm "Orders"
Forms!Orders.ShortcutMenuBar = "OrdersPopup"
CreateOrdersShortcut_Bye:
Exit Sub
CreateOrdersShortcut_Err:
' You can't create a command bar if a command bar of the same
' name already exists. This If clause checks for the error
' message that occurs if a command bar named "OrdersPopup"
' already exists and deletes this command bar. The procedure
' then resumes, and adds the new shortcut menu.
If Err = conShortcutAlreadyCreated Then
CommandBars("OrdersPopup").Delete
Resume
Else
MsgBox Err.Description, vbOKOnly, "Error = " & Err.Number
Resume CreateOrdersShortcut_Bye
End If
End Sub
Function PrintRecord ()
' This function runs from the command button control "Print
' Current Record" on the shortcut menu. It selects the current
' record in the Orders form and prints this record.
RunCommand acCmdSelectRecord
DoCmd.PrintOut acSelection
End Function
When you add a built-in control to a custom command bar in Visual Basic, you use the Id argument of the Add method of the CommandBarControls collection. For example, you may want to create a custom menu that has several built-in controls and several custom controls. You can determine the ID value to use with the Id argument by using the Id property for the control. The following function returns the ID for a built-in control. You pass the name of the control in the strControlName argument and the name of the command bar the control appears on in the strBarName argument. For example, you could determine the ID value for the New Database… command that appears on the File menu on the main Microsoft Access menu bar by calling the GetID function using the following arguments:
Dim lngControlId as Long
lngcontrolID = GetID("File", "New Database...")
In this example, the name of the command bar is "File". This particular command bar is a pop-up menu that appears when you click File on the main menu bar. You must pass the exact command bar name in the strBarName argument. Note also that you must pass the control name exactly as it appears on the command bar. In this example, this means that the New Database… command name must be passed with the trailing three periods.
You can use the GetID function with the Add method of the CommandBarControls collection to add the specified control to a command bar. For an example, see the section "Example: Creating a Custom Shortcut Menu."
Function GetID(strBarName As String, strControlName As String) As Long
Dim cbr As CommandBar, cbrctl As CommandBarControl
On Error GoTo GetID_Err
Set cbr = CommandBars(strBarName)
Set cbrctl = cbr.Controls(strControlName)
' The ID value of the control is the setting of its
' Id property.
GetID = cbrctl.Id
GetID_Bye:
Exit Function
GetID_Err:
MsgBox Err.Description, vbOKOnly, "Error = " & Err.Number
Resume GetId_Bye
End Function
For example, if you enter the following in the Debug window:
? GetID("Insert", "Module")
The function returns 621, the ID number for the built-in Module command on the Insert menu.
To add this command to a custom menu, you could use the following syntax:
Set ctlnew = Controls.Add(msoControlButton, 621)
Or you could use the GetID function directly in the code that adds the command:
Set ctlnew = Controls.Add(msoControlButton, _
GetID("Insert", "Module"))
Note When you use this function, you must know the complete and correct name for the command bar that contains the control whose ID value you want to find. This is the name you use for the strBarName argument passed to the function. The command bar name is not always the name you see in the user interface. For example, the name of the pop-up menu that appears when you click a command that has subcommands is not necessarily the name of the command. For example, when you click the Filter command on the Records menu, you see a submenu containing commands such as Filter By Form and Filter By Selection. The name of this pop-up submenu is Filter Context Menu, not Filter.
To find the name of a command bar, use the Name property. For example, to find the name of the pop-up submenu mentioned in the previous paragraph, use the following syntax:
CommandBars("Records").Controls("Filter").CommandBar.Name
Microsoft Access supplies a number of built-in shortcut menus, each of which has a name. Although almost all built-in controls on these shortcut menus are also available on other menus, you may want to find the ID value for a built-in control on one of the shortcut menus. To see a function that when run lists all of the command bars in your application (including the shortcut menus) and their names, see "Example: Listing the Names of Command Bars in Your Application."
This example lists the names of all the command bars in the CommandBars collection. Command bars can be one of three types, as defined by the Type property: toolbars (represented by the intrinsic constant msoBarTypeNormal), menu bars (msoBarTypeMenuBar), or pop-up menus, which include submenus and shortcut menus (msoBarTypePopup).
Sub ListCommandBars()
Dim cbr As CommandBar
For Each cbr In CommandBars
listbar 1, cbr
Next cbr
End Sub
Sub listbar(level As Integer, thisbar As CommandBar)
Dim cbrctl As CommandBarControl
Dim indent As Integer
' Indent the command bar depending on its level in
' the menu structure.
For indent = 1 To level
Debug.Print " ";
Next indent
Select Case thisbar.Type
Case msoBarTypeMenuBar
Debug.Print "Menu Bar: " & thisbar.Name
Case msoBarTypeNormal
Debug.Print "Toolbar: " & thisbar.Name
Case msoBarTypePopup
Debug.Print "Popup: " & thisbar.Name
End Select
For Each cbrctl In thisbar.Controls
' If the control doesn't have a command bar associated
' with it, then don't print it.
If cbrctl.Type <> 1 And cbrctl.Type <> 2 _
And cbrctl.Type <> 4 And cbrctl.Type <> 16 _
And cbrctl.Type <> 18 Then
listbar level + 1, cbrctl.CommandBar
End If
Next cbrctl
End Sub
With the new command bars feature in Microsoft Office 97, you can create and modify the various kinds of command bars: menu bars, toolbars, and pop-up menus. Pop-up menus appear in three ways: as menus that drop down from menu bars, as submenus that cascade off menu commands, and as shortcut menus. Shortcut menus (also called "right-click menus") are menus that appear when you right-click a particular area on the screen; for example, a blank area on a form or report, or a form control.
In Microsoft Access 97, you can create a custom shortcut menu by using the Customize dialog box, available by pointing to Toolbars on the View menu and then clicking Customize. To create a custom shortcut menu, click the New button in the Customize dialog box and name your new shortcut menu. Set any toolbar properties you want, making sure to set the Type property to Popup. You'll see a message telling you that the menu will disappear from view. You can see the menu by selecting the Shortcut Menus check box in the Customize dialog box, and then clicking Custom on the menu bar that appears.
You can add controls to this custom menu just as you can for toolbars and menu bars, and set the control properties using the Control Properties dialog box. To attach the custom shortcut menu to a particular form, form control, or report, set the form, form control, or report's ShortcutMenuBar property to the name of the custom shortcut menu.
You can also add custom shortcut menus to your application by using Visual Basic. For more information, see the section "Creating Custom Shortcut Menus in Visual Basic." Note, however, that you can only change the type of an existing command bar to Popup by using the Toolbar Properties dialog box. You can't change a command bar of another type to a shortcut menu by using Visual Basic.
With the new command bars feature in Microsoft Office 97, you can create and modify the various kinds of command bars: menu bars, toolbars, and pop-up menus. In Microsoft Access 97, you use the Customize dialog box, available by pointing to Toolbars on the View menu and then clicking Customize, to create command bars and set their properties.
When you create a new command bar, you use the Add button on the Customize dialog box to create and name the new command bar. You can then add built-in controls to the command bar by using the Commands tab in the Customize dialog box. There's a selection in the Categories list, Built-in Menus, that you can use to add any of the various built-in menus and their commands to your command bar.
Caution If you modify a built-in menu that you've added to your custom command bar, the built-in menu will be modified everywhere it appears in the product. For example, if you rename a command in your command bar, this command will also be renamed in any of the built-in menu bars or toolbars on which it normally appears.
To avoid this problem, don't use the Built-in Menus selection. Instead, use the New Menu selection in the Categories list on the Commands tab to add a new blank menu to your command bar, and then drag the built-in commands you want to this menu. You can name the menu the same name as the corresponding built-in menu. For example, you could add a new menu, rename it "File", and then add all the built-in commands normally found on the File menu to your command bar.
The Position property for command bars is primarily used to set the position of a command bar after it's created or when you want to move an existing command bar. However, there's some additional functionality of this property that's not immediately apparent.
The Position property applies to all command bars. The syntax for setting this property is:
CommandBars("commandbarname").Position = msoBarPosition constant
You can set this property to one of seven intrinsic constants (see Table 1).
Table 1. Intrinsic Constants of the Position Property
| Constant | Description |
| msoBarLeft, msoBarTop, msoBarRight, msoBarBottom | Indicates where the command bar appears on the screen. |
| msoBarFloating | Indicates that the command bar isn't docked. |
| msoBarPopup | Read-only. Indicates that the command bar is a shortcut menu. |
| msoBarMenuBar (This constant is incorrectly listed as msoBarMenu in the Help topic for the Position property.) | Indicates that the command bar is a Macintosh® system menu bar (and replaces the built-in system menu bar). This constant does NOT indicate that this command bar is a Microsoft Access menu bar, and indeed this command bar can't be used with Microsoft Access. |
Note that these constants are also used with the Position argument of the Add method of the CommandBars collection, which you use to add command bars and set their type and position. An important difference between the two, however, is that the msoBarPopup setting is read-only for the Position property. You can't change an existing command bar to a shortcut menu by using the Position property after the command bar has been created. You could delete the command bar and use the Add method to add it as a shortcut menu (as long as you didn't need the existing command bar anymore). You could also copy an existing shortcut menu, and modify it to contain the controls you want. For more information, see the section "Creating Custom Shortcut Menus in Visual Basic."
This example repositions the specified command bar in the specified screen location, at the top, bottom, right, or left of the screen, or makes it undocked. You pass the name of the command bar in the strBarName argument and the desired location (one of the intrinsic constants for the Position property setting) in the intLocation argument.
Function MoveCommandBar(strBarName As String, intLocation As Integer)
Dim cbr As CommandBar
Set cbr = CommandBars(strBarName)
With cbr
If .Visible = True Then
Select Case intLocation
Case msoBarTop
.Position = msoBarTop
Case msoBarBottom
.Position = msoBarBottom
Case msoBarRight
.Position = msoBarRight
Case msoBarLeft
.Position = msoBarLeft
Case msoBarFloating
.Position = msoBarFloating
Case msoBarPopup
MsgBox "You can't change an existing" & _
" command bar to a shortcut menu."
Case Else
MsgBox "Invalid Position Argument"
End Select
End If
End With
End Function
Often, you'll want to create a command bar that contains almost all of the controls from an existing command bar. For example, you may have just created a custom toolbar that you use for one of your forms, and you want to create other very similar toolbars for other forms that contain the same controls with just a few modifications. The following function will copy a specified command bar and its controls to a new command bar. You can then make any modifications you want to this command bar. Note that there is no way to copy a command bar in its entirety by using the user interface; you must use Visual Basic.
Function CopyCommandBar(strOrigCBName As String, strNewCBName As String) As Boolean
' This procedure copies the command bar named in the
' strOrigCBName variable to the new command bar named
' in the strNewCBName variable.
Dim cbrOriginal As CommandBar, intOrigCount As Integer
Dim cbrCopy As CommandBar, cbrCtl As CommandBarControl
On Error GoTo CBCopy_Err
Set cbrOriginal = CommandBars(strOrigCBName)
intOrigCount = cbrOriginal.Controls.Count
Set cbrCopy = CommandBars.Add(strNewCBName)
' Make sure the new command bar is visible.
cbrCopy.Visible = True
For Each cbrCtl In cbrOriginal.Controls
cbrCtl.Copy cbrCopy
Next cbrCtl
Exit Function
CBCopy_Err:
MsgBox Err.Description
Exit Function
End Function
One of the useful new features of command bars is the ability to add different types of controls to your command bars. The following example shows you how to add a combo box control to the main menu bar and fill the combo box list with values from a table. This example also shows how to delete the combo box control when you no longer need it. For example, you may want to display this combo box control when a certain form (for example, the Orders form) is open, and remove it when the form is closed or no longer has the focus. To do this, you could run the AddCombo procedure when the form's Open event occurs, and run the RemoveCombo procedure when the form's Unload event occurs.
This example can be used as is with any of the sample applications supplied with Microsoft Access. (Note, however, that the main menu bar for the Northwind Traders database is called NorthwindCustomMenuBar, not Menu Bar.) To use it with your application, replace the table and field names with those from your database.
Sub AddCombo()
' This procedure adds a combo box control to the menu bar
' and adds CompanyName information from the Customers table.
Dim mybar As CommandBar, mycontrol As CommandBarComboBox
Dim dbs As Database, rst As Recordset, intI As Integer
Set mybar = CommandBars("Menu Bar")
On Error Resume Next
' See if the control already exists
Set mycontrol = mybar.Controls("Customers")
If Err = 0 Then Exit Sub ' Control exists so exit procedure.
' Add control to menu bar.
Set mycontrol = _
mybar.Controls.Add(Type:=msoControlComboBox, Id:=1)
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Customers", dbOpenDynaset)
rst.MoveFirst
intI = 1
' Fill combo box with names from the Customers table.
While Not rst.EOF
With mycontrol
.AddItem Text:=rst!CompanyName, Index:=intI
End With
intI = intI + 1
rst.MoveNext
Wend
' Set combo box properties
With mycontrol
.Caption = "Customers"
.Width = 200
.DropDownLines = 10
.DropDownWidth = 300
' This is the function called when an item is
' selected from the list.
.OnAction = "=ShowCustomerInfo()"
End With
End Sub
Function ShowCustomerInfo()
' This procedure runs when an item is selected from the combo
' box created in the AddCombo procedure. It displays a message
' box showing the name, title, and phone number of the contact
' for the company you select in the combo box list.
Dim mybar As CommandBar, mycontrol As CommandBarComboBox
Dim dbs As Database, rst As Recordset
Dim strCriteria As String
On Error Resume Next
Set mybar = CommandBars("Menu Bar")
Set mycontrol = mybar.Controls("Customers")
Set dbs = CurrentDb
' Create a recordset containing a single record that matches
' the company name selected from the combo box.
' This If clause handles the case where the company name
' contains an embedded apostrophe.
If InStr(mycontrol.Text, "'") > 0 Then
strCriteria = "Select ContactName, ContactTitle," & _
" Phone From Customers Where CompanyName" & _
" = " & """" & mycontrol.Text & """"
Else
strCriteria = "Select ContactName, ContactTitle," & _
" Phone From Customers Where CompanyName" & _
" = '" & mycontrol.Text & "'"
End If
Set rst = dbs.OpenRecordset(strCriteria)
MsgBox "Name = " & rst!ContactName & vbCrLf & _
"Title = " & rst!ContactTitle & vbCrLf & _
"Phone = " & rst!Phone
End Function
Sub RemoveCombo()
' This procedure removes the combo box control created
' in the AddCombo procedure from the menu bar.
On Error Resume Next
Dim mybar As CommandBar, mycontrol As CommandBarComboBox
Set mybar = CommandBars("Menu Bar")
Set mycontrol = mybar.Controls("Customers")
mycontrol.Delete
End Sub