Custom command bar help required..

Hi Icon, (kewl nick, and yup it's the same Chapie from the NewsGrp)

I wanted to comment here to let you know this service does work, it just must be that your problem doesn't strike anyone's experiences as familiar.  Usually if someone can do it and feels your points are too low, they will post a comment that they have the answer and that you should raise (and I'm not saying you should).

Just wanted to let you know to hang in there and someone else should be posting pretty soon.  Also, do you have any part of this coded yet, for instance if you can add these options right now, just not reading them from a table, then I believe I can help you the rest of the way, but when I looked at the books I have I couldn't even figure out how to add customer commands into a toolbar using VBA, I usually just build a custom bar and use existing features since I distribute app's using run-time, which doesn't use the default bars so I have to create my own.

Anyway, let me know if you are already to doing

ill have a go when i when i feel like wading through the daunting menubar object. More points would help.

I thought you might jump in here Ozphil, I figured from your other posts that I've seen you would have the ability to track down the solution if not the first hand knowledge!!

In simple terms, I would like the values from my table to appear in a pop up sub-menu in the same way a list box is displayed - however, I do not want an actual list box.

The section in my application allows the user to change various menus which are stored in a header table.  The default name for each menu is Menu 1, Menu 2 etc. up to Menu 20.  However, the user can change the name of each menu name (eg. from Menu 1 to First Menu) and I want to ensure that the current menu name is displayed within my sub menu at all times.

Another way of phrasing my question which may help would be : how do I display a pop up menu of all the values from a particular column from a particular table ?

I wait in eager anticipation - and frustration!

I dont understand your menu table structure.

Would this structure more suitable:
Just three Fields:
menuID
menuCommand
menuCommandID

menuID values: menu 1,menu1,menu1, menu 2, menu2,menu 3, etc
menuCommand values: mn1 cmd 1, mn1 cmd2, mn1 cmd3, mn2 cmd1, mn2 cmd2, mn3 cmd1, etc
menuCommandID values: 1,2,3,1,2,1,etc

This is easier to manipulate programmatically with code and  SQL.

When i understand the table structure you are currently working with, or improve on it, the  rest should be straightforeward.

My table structure has 2 fields:

MenuName
MenuFormName

MenuName values are Menu1, Menu2 etc. up to Menu20, but the user can rename any or all of them within my application.

The MenuFormName fields remain static as they refer to the form that should be opened upon selection from the popup menu.  The popup menu displays the MenuName values as described above.

This may seem long-winded, but the application is for a touch screen based POS system whereby the user designs menu screens.  There are a maximum of 20 menus that can be defined, but it is imperative that the user can rename them themselves.

This will get you started.  You have here a good basis upon which to build a dynamic menu system to suit your needs.
Ill just supply the coding techniques you may require, icon. I dont think i want to get to the nitty gritty of your application. :)

I havent included code for modifying a menu name, but its easy with collections - to modify a menu name, use the delete and add methods, using the positioning parameter in the add method. Look up the help documentation.
Dont forget to add the library reference:
Microsoft Office 8.0 Object Library.
Youll see Microsoft Access 8.0 Library already included, but the
Microsoft OFFICE 8.0 Object Library isnt, so tick the check box and youre in business.

Ive named the menubar control in this example "myMenuBar" and the custom menu bar item "custom". Change these to suit your naming.

Option Compare Database
Option Explicit

Sub addMenuItems()
   
    Dim cmdBar As CommandBar
    Dim strBarName As String
    Dim cbarCtl As commandbarcontrol
    Dim cbarpopup As CommandBarPopup
    Dim i As Integer
   
    strBarName = "myMenuBar"
    Set cmdBar = CommandBars(strBarName)
    cmdBar.Visible = True
    Set cbarpopup = cmdBar.Controls("custom")
    'cbarpopup.Reset

    ' clear all the 'custom' menu controls
    For i = 1 To cbarpopup.Controls.Count
        cbarpopup.Controls(1).Delete
    Next
   
    ' For test purposes Create a button with text on the bar and set some properties.
    For i = 1 To 6
    Set cbarCtl = cbarpopup.Controls.Add(Type:=msoControlButton)
    With cbarCtl
       .Caption = "Button" & i
       .Style = msoButtonCaption
       .TooltipText = "Display Message Box"
       .OnAction = "=MsgBox(""You pressed a menu button!"")"
    End With
    Next
   
    'if you were reading from a table you would do this.
    ' (You have to apply your own filter to this)
    Dim db As Database, dn As Recordset
    Set db = CurrentDb()
    Set dn = db.OpenRecordset("tblMenus")
    Do While Not dn.EOF
    Set cbarCtl = cbarpopup.Controls.Add(Type:=msoControlButton)
        With cbarCtl
          .Caption = dn!menuName
          .Style = msoButtonCaption
          .TooltipText = "Display Message Box"
          .OnAction = "=MsgBox(""You pressed a menu button!"")"
        End With
        dn.MoveNext
    Loop
End Sub

There's a line in the above code which should be modified.
Please change this:
 .OnAction = "=changeRequest(" & "'" & .Tag & "')"
To:
.OnAction = "=changeMenuNameRequest(" & "'" & .Tag & "')"

Thanks, ozphil, I have tied your code into my application and it works, however, I have one last question.  The command line:

Set cbarpopup = cmdBar.Controls("custom")

refers to a command menu called "custom".  I want my "custom" menu to be a popup menu from, for example, my "Edit" menu.  Question:

How do I refer to this menu using the above Set clause.  I thought it might be something like:

Set cbarpopup = cmdBar.Controls("edit:custom")

but obviously, this is incorrect syntax.
 

This code will do what you want:
For test purposes i retained the menu control named 'custom', and created another named 'customedit"  via toolbar customize.
'customedit' is a popup control in the Edit group.
There is probably another way to drill down to the control using the collections hierarchy, but the documentation is not good and the menu object sure is a strange one.

old:
  ''Set cbarpopup = cmdBar.Controls("custom")

new:
  Set cbarpopup = cmdBar.FindControl _
    (Type:=msoControlPopup, Tag:="customsubedit", recursive:=True)

 

Thanks Ozphil, this works perfectly.  I think we have demonstrated here that Access is an extremely powerful development tool, but unfortunately it is difficult to get at the nitty-gritty bits of it.

I bought the Microsoft Access '97 Developers Handbook (Microsoft Press) and it doesn't cover customising menus or menu bars in much detail.

I wish I could find a 'How to' book for Access which is packed with specifics similar to my original problem.  If you know of such a book, please email me : icon@iol.ie

Once again, thank you for all of your help.

i havent got such a book at the moment, but id be looking at books authored by Ken Getz and the like.

Anyhow, we know what yardstick to measure an Access programmerts book by - advanced menu object programming!