This article is the third part of the series on building custom toolbar in MS Access.
In this article I will show to execute actions and keyboard shortcuts using the OnAction property and the Execute method. Furthermore, I will show how to create shortcuts in Access.
The difference between OnAction and Execute is that OnAction executes a command that we determined through a procedure whereas Execute executes an internal command such as print. However, Execute is activated when we create the button, thus, we will use the button ID to execute the command. In other words, we will define the button such that it acts as if we had clicked on the Relationships button under the Tools menu.
Firstly, let us create the code that will build our menu:
|
Public Const MENUACCESS As String = "Menu Bar"
|
|
Sub executeMenus()
Dim mnu As CommandBarPopup
Dim btn As CommandBarButton
On Error Resume Next
CommandBars(MENUACCESS).Controls("Execute Commands").Delete
Set mnu = CommandBars(MENUACCESS).Controls.Add _
(Type:=msoControlPopup, before:=1)
mnu.Caption = "Execute Commands"
Set btn = mnu.Controls.Add(Type:=msoControlButton)
With btn
.Caption = "Example OnAction"
.FaceId = 1018
'Refers to the procedure to be executed
.OnAction = "Message"
End With
Set btn = mnu.Controls.Add(Type:=msoControlButton, ID:=523)
With btn
.Caption = "Example Execute"
.FaceId = 523
'This option is commented as we will use its ID to execute
'the command we want
'.Execute
End With
End Sub
|
The new menu should look like this:
Figure 1 – OnAction property and Execute method
The FaceID choice is up to the reader.
Note that for the OnAction we put the name of the procedure to be executed when the button is clicked. However, on the Execute instance we refer to the command to be executed by using its ID. Given that the OnAction property needs a procedure, we shall use the example below as a simple way to get the button up-and-running:
|
Sub Message()
MsgBox "There is nothing under this button to be executed.", _
vbInformation
End Sub
|
When we click the button the message below is shown to the user:
Figure 2 – MsgBox called by the OnAction property
As for the second button, when we click it, the Relationships window is open:
Figure 3 – Relationships windows opened through the Execute method
Once we have defined the ID of the command, the Execute method becomes redundant, as when we click on the button the command is executed. However, there may be situations when you wish to have the command executed immediately.
The above commands are executed through cliks, however, we can also add keyboard shortcuts. These shortcuts will require that you only press a few key combinations to execute the command you want.
This will be discussed in my next article.