MajP
You've got your good things, and you've got mine.
- Local time
- Today, 18:57
- Joined
- May 21, 2018
- Messages
- 8,525
A few questions have come up about Command Bars recently.
I use this idea pretty frequently because I think it is cleaner and at times easier than creating cascading comboboxes. I always thought the idea would catch on, but this is the first time I have ever seen people asking about it.
In this example if I click on "Select Location" I get a command bar where I can pick the country, then the city, and a location in the city.
The code is really not that much to do this. This example may seem a little complicated because it loops three recordsets, but the code for actually building the commandbar is very short and simple once you see it.
Code to build the the command bars
Following the Code
1. You create a new command bar if it does not exist. In my case I delete comBarLocations each time, because I want to make sure it is current as I add new data
Set comBarLocations = CommandBars.Add(comBarName, msoBarPopup, False, False)
2. Each item on a command bar is a command bar control. At the first level (Country) you add a command bar control to the command bar. At the other levels you add the command bar control to its parent command bar control
3. Command bar control can have an OnAction which is the name of a procedure to run if it is selected.
comBarControl_Lvl_3.OnAction = "SelectLocation"
4. The procedure SelectLocation is a little tricky how it works
The on Action calls the SelectLocation method. The method then figures out which command bar control called it by this line
Set comBarCtl = CommandBars.ActionControl
To associate a control with the commandbar there is a property on the "Other" Tab called "Short Cut Menu Bar". Need to put the name of the command bar in there. In step 1 above, I called my command bar "comBarLocations"
To get the command bar to popup on a event like a click event you need to add code to show popup
Solved - Can i make a shortcut menu with levels ?
Hello guys , I can do a standard shortcut menu with commandbars method but can i make a shortcut with levels like windows using VBA ? Thanks in Advance .
www.access-programmers.co.uk
Solved - cascading context menu in form
Hello I have seen a couple of posts on this forum about shortcut menus and context menus and I wanna make it deeper I have a form with 3 cascading combo boxes, getting data from 3 tables, with a one to many relationships between them so, I wanna just create a button when I click it opens a...
www.access-programmers.co.uk
I use this idea pretty frequently because I think it is cleaner and at times easier than creating cascading comboboxes. I always thought the idea would catch on, but this is the first time I have ever seen people asking about it.
In this example if I click on "Select Location" I get a command bar where I can pick the country, then the city, and a location in the city.
Code to build the the command bars
Code:
Public Sub LoadCommandBar()
Const comBarName = "comBarLocations"
Dim i As Integer
Dim rsCountries As DAO.Recordset
Dim rsCities As DAO.Recordset
Dim rsLocations As DAO.Recordset
Dim comBarLocations As Office.CommandBar
Dim comBarControl_Lvl_1 As Office.CommandBarControl
Dim comBarControl_Lvl_2 As Office.CommandBarControl
Dim comBarControl_Lvl_3 As Office.CommandBarControl
Set rsCountries = CurrentDb.OpenRecordset("Select Distinct CountryID, Country from qryLocations Order By Country")
If isCommandBar(comBarName) Then
Application.CommandBars(comBarName).Delete
End If
'Build tha bar
Set comBarLocations = CommandBars.Add(comBarName, msoBarPopup, False, False)
'build the levels
Do While Not rsCountries.EOF
'level 1
Set comBarControl_Lvl_1 = comBarLocations.Controls.Add(msoControlPopup)
comBarControl_Lvl_1.Caption = rsCountries!Country
Set rsCities = CurrentDb.OpenRecordset("Select Distinct CityID, City from qryLocations where CountryID = " & rsCountries!CountryID & " Order by City")
Do While Not rsCities.EOF
'Level_2
Set comBarControl_Lvl_2 = comBarControl_Lvl_1.Controls.Add(msoControlPopup)
comBarControl_Lvl_2.Caption = rsCities!City
'Level_3 Not popup
Set rsLocations = CurrentDb.OpenRecordset("Select Distinct LocationID, Location from qryLocations where CityID = " & rsCities!CityID & " Order by Location")
Do While Not rsLocations.EOF
Set comBarControl_Lvl_3 = comBarControl_Lvl_2.Controls.Add()
comBarControl_Lvl_3.Caption = rsLocations!Location
comBarControl_Lvl_3.Tag = rsLocations!LocationID
comBarControl_Lvl_3.OnAction = "SelectLocation"
rsLocations.MoveNext
Loop
rsCities.MoveNext
Loop
rsCountries.MoveNext
Loop
End Sub
Public Function isCommandBar(strBarName As String) As Boolean
Dim cb As CommandBar
For Each cb In Application.CommandBars
If cb.Name = strBarName Then
isCommandBar = True
End If
Next cb
End Function
Public Sub SelectLocation()
Dim comBarCtl As CommandBarControl
Set comBarCtl = CommandBars.ActionControl
With Forms("frmSelectLocation")
.LocationID_FK = CLng(comBarCtl.Tag)
.Refresh
.Requery
End With
End SubFollowing the Code
1. You create a new command bar if it does not exist. In my case I delete comBarLocations each time, because I want to make sure it is current as I add new data
Set comBarLocations = CommandBars.Add(comBarName, msoBarPopup, False, False)
2. Each item on a command bar is a command bar control. At the first level (Country) you add a command bar control to the command bar. At the other levels you add the command bar control to its parent command bar control
3. Command bar control can have an OnAction which is the name of a procedure to run if it is selected.
comBarControl_Lvl_3.OnAction = "SelectLocation"
4. The procedure SelectLocation is a little tricky how it works
The on Action calls the SelectLocation method. The method then figures out which command bar control called it by this line
Set comBarCtl = CommandBars.ActionControl
To associate a control with the commandbar there is a property on the "Other" Tab called "Short Cut Menu Bar". Need to put the name of the command bar in there. In step 1 above, I called my command bar "comBarLocations"
To get the command bar to popup on a event like a click event you need to add code to show popup
Code:
Private Sub cmdSelect_Click()
If isCommandBar("comBarLocations") Then
CommandBars("comBarLocations").ShowPopup
Else
MsgBox "Command Bar 'comBarLocations' does not exist. See administrator"
End If
End Sub
