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"
Dim mnu As CommandBarPopup
Dim btn As CommandBarButton
On Error Resume Next
Set mnu = CommandBars(MENUACCESS).Controls.Add _
mnu.Caption = "Execute Commands"
Set btn = mnu.Controls.Add(Type:=msoControlButton)
.Caption = "Example OnAction"
.FaceId = 1018
'Refers to the procedure to be executed
.OnAction = "Message"
Set btn = mnu.Controls.Add(Type:=msoControlButton, ID:=523)
.Caption = "Example Execute"
.FaceId = 523
'This option is commented as we will use its ID to execute
'the command we want
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:
MsgBox "There is nothing under this button to be executed.", _
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.