Have you ever need to build custom toolbars in Access? If you have, how did you carry out the task?
There many ways to build your custom menus in MS Office and Access gives tables that we can use to automate the process by storing the information we will need and use in the project.
In this series of articles, I will show you how to build custom toolbars efficiently using a database connection to retrieve information from tables in the database. This tutorial takes that you have the basic knowledge of MS Access and that you know how to create and manipulate tables.
The picture below shows a few items that you will need to enter in your table before we move on to the code:
Picture 1 - An Access table with properties and methods of a toolbar and its controls
This table will keep the details of the items that will make up our toolbar. Name and save the table. After that, we insert a few items that are going to be used in building our toolbar:
Picture 2 - Entering the data to build the menu
With the data in the table, add a module to our project. Before you move on, you will need to install references to the MS Office Object Library. To do that, with VBE opened, go to Tools » References and install the Microsoft Office X.x Object Library.
With that out of the way, we are ready to write the code:
Writing the code:
|
Sub buildToolbar()
Dim cmdBar As CommandBar
Dim popup As CommandBarPopup
Dim btn As CommandBarButton
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Sql As String
Set cn = Application.CurrentProject.Connection
Sql = "SELECT * FROM [MenuItems]"
Set rs = New ADODB.Recordset
rs.Open Sql, cn, 1
On Error Resume Next
CommandBars("MAIN MENU").Delete
Set cmdBar = Application.CommandBars.Add _
(Name:="MAIN MENU", Position:=msoBarFloating)
If (rs.EOF) Then
MsgBox "There is no item to build the toolbar", vbInformation
CommandBars("MAIN MENU").Delete
Else
While (Not (rs.EOF))
mnuType = rs![Type]
Select Case mnuType
Case 1 'Popup
Set popup = cmdBar.Controls.Add(Type:=msoControlPopup)
With popup
.Caption = rs![Caption]
.Width = rs![Width]
End With
Case 2 'Button
Set btn = popup.Controls.Add(Type:=msoControlButton)
With btn
.BeginGroup = rs![BeginGroup]
.Caption = rs![Caption]
.FaceId = rs![FaceId]
.OnAction = rs![OnAction]
.State = rs![State]
.Width = rs![Width]
End With
End Select
rs.MoveNext
Wend
End If
cmdBar.Visible = True
cmdBar.Protection = msoBarNoCustomize + msoBarNoChangeDock + _
msoBarNoHorizontalDock
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub
|
The toolbar will be built based on each case that is found under the filed Type. Here I use a numerical value to determine the type of control which is being compiled on our toolbar. However, you may decide to use a string as the type instead of a number, if you think it is easier to visualize what you are doing. For example, we could define the button explicitly as BUTTON instead of the numerical value 2.
If you, however, decide to follow this route, be aware that when you select the case the case must be also a string and therefore must come between quotes, i.e. you would select a case as Case "BUTTON".
After we compile our toolbar the end result is:
Picture 3 - Compiled toolbar
Conclusion:
This is the first part of a series to be written on custom toolbar in MS Access. If you wish to know more about custom toolbars in MS Access you can purchase the full course on this website.
This article was originally published on MSDN Brasil Sharepedia and has been translated for publishing on this website.
If you have any comments or suggestions to make, please leave your comments here or write to the author on rm@msofficegurus.com