How to create a functional drop-down list control on an Access toolbar.
Q: Charles Duwel wants to make a custom toolbar for an Access 2002 form, with a drop-down list control. He tried dragging a combo box to a toolbar, but it just worked like a button. Is it possible to place a functional drop-down control on an Access toolbar?
A: This can be done, though only with VBA code, not in the interface. There is a whole chapter on creating CommandBars (menu bars and toolbars) for Access in my new book, Access Inside-Out, including creating controls with drop-down lists of various types. Here is a procedure that creates a floating toolbar with a drop-down control, and a little function that saves the selected item to a table:
Public Sub CreateTestBar()
Dim cbrTest As Office.CommandBar
Dim btnDropDown As Office.CommandBarComboBox
Set cbrTest = Application.CommandBars.Add(Name:= _
“Test Toolbar”, Position:=msoBarFloating, _
temporary:=False)
cbrTest.Enabled = True
cbrTest.Visible = True
‘Creates a drop-down list where an animal can be selected
‘and saved to a table
Set btnDropDown = cbrTest.Controls.Add( _
Type:=msoControlDropdown)
With btnDropDown
.AddItem “Dog”, 1
.AddItem “Cat”, 2
.AddItem “Parrot”, 3
.AddItem “Kangaroo”, 4
.AddItem “Wildebeest”, 5
.DropDownLines = 8
.DropDownWidth = 75
.Style = msoComboLabel
.Caption = “Select Animal”
.Tag = “Test Drop-down”
.OnAction = “SaveAnimal”
End With
cbrTest.Visible = True
End Sub
Public Function SaveAnimal()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim cbr As Office.CommandBar
Dim cbo As Office.CommandBarComboBox
Set cbr = Application.CommandBars(“Test Toolbar”)
Set cbo = cbr.Controls(“Select Animal”)
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tblInfo”)
With rst
.MoveFirst
.Edit ![Animal] = cbo.Text
.Update
.Close
End With
End Function
You can’t create a toolbar that is a part of an Access form, but you can assign a toolbar by name to the Toolbar property of a form, so it only appears when that form is open.
