Adding a combobox control to a shortcut menu

[stinehelferw]

I need a right-click menu on a form list control with combobox
controls.

I used View->Toolbars->Customize to create a toolbar.
I added ComboBox through commands tab.
I set properties to Popup making it a shortcut menu.

I have no problem linking it to the list control.

The menu comes up when I right-click the list control.

I have no problem running an OnAction event when an item is selected on
the menu.

However, when I select a menu item and the OnAction fires, I get an
error saying I have a type mismatch which tags the line:

Set mycontrol = mybar.Controls("Position").
Here's the whole VBA function:
Public Function SeatmeatPosition()

Dim mybar As CommandBar, mycontrol As CommandBarComboBox
Dim dbs As Database, rst As Recordset, intI As Integer

Set mybar = CommandBars("TestSeatmeatPopup")
Set mycontrol = mybar.Controls("Position")

Set rst = CurrentDb.OpenRecordset("SELECT Position.RefId,
Position.Title FROM [Position] order by Position.RefId DESC")
rst.MoveFirst
intI = 1

' Fill combo box with crew position titles
While Not rst.EOF
With mycontrol
.AddItem Text:=rst!Title, Index:=intI
End With
intI = intI + 1
rst.MoveNext
Wend

' Set combo box properties
With mycontrol
.Caption = "Position"
.Width = 200
.DropDownLines = 10
.DropDownWidth = 300
' This is the function called when an item is
' selected from the list.
.OnAction = "=ShowCustomerInfo()"

End With
End Function

If I change the mycontrol definiton to: mycontrol As CommandBarButton
it gets past that line, but hangs on AddItem. That's of course
expected since AddItem isn't part of CommandBarButton.

So, how do I get a working combo box on a right-click shortcut menu
which use AddItem to add items from a selected table?

[stinehelferw]

Well, closer...

I changed the VBA from this:

Set mycontrol = mybar.Controls("Position")

to this:

Set mycontrol = mybar.Controls.Add(Type:=msoControlComboBox)

It adds a combo box, gets input from the table and adds the items.

Problem, is now that it does this every time I click on the Postion
menu item of my menu. I have multiple drop downs and a new one every
time I use the menu.
Of course, this is what I told the VBA to do.

So how do I get what I really want? A combo box labeled "Position"
with drop down items from the table. And just one combo box!
st**********@excite.com wrote:

I need a right-click menu on a form list control with combobox
controls.

I used View->Toolbars->Customize to create a toolbar.
I added ComboBox through commands tab.
I set properties to Popup making it a shortcut menu.

I have no problem linking it to the list control.

The menu comes up when I right-click the list control.

I have no problem running an OnAction event when an item is selected on
the menu.

However, when I select a menu item and the OnAction fires, I get an
error saying I have a type mismatch which tags the line:

Set mycontrol = mybar.Controls("Position").
Here's the whole VBA function:
Public Function SeatmeatPosition()

Dim mybar As CommandBar, mycontrol As CommandBarComboBox
Dim dbs As Database, rst As Recordset, intI As Integer

Set mybar = CommandBars("TestSeatmeatPopup")
Set mycontrol = mybar.Controls("Position")

Set rst = CurrentDb.OpenRecordset("SELECT Position.RefId,
Position.Title FROM [Position] order by Position.RefId DESC")
rst.MoveFirst
intI = 1

' Fill combo box with crew position titles
While Not rst.EOF
With mycontrol
.AddItem Text:=rst!Title, Index:=intI
End With
intI = intI + 1
rst.MoveNext
Wend

' Set combo box properties
With mycontrol
.Caption = "Position"
.Width = 200
.DropDownLines = 10
.DropDownWidth = 300
' This is the function called when an item is
' selected from the list.
.OnAction = "=ShowCustomerInfo()"

End With
End Function

If I change the mycontrol definiton to: mycontrol As CommandBarButton
it gets past that line, but hangs on AddItem. That's of course
expected since AddItem isn't part of CommandBarButton.

So, how do I get a working combo box on a right-click shortcut menu
which use AddItem to add items from a selected table?