?
Solved

Custom command bar help required..

Posted on 1997-09-26
13
Medium Priority
?
321 Views
Last Modified: 2010-05-18
Access 97

I am trying to write a function which will read in columns from a table and
provide these as the options within my custom menu bar for my application,
as a sub-menu.

For example, the columns in my table may be:

Option Number 1
Option Number 2
Option Number 5

My custom menu bar looks like, for example:

File     Edit     View     Custom      Help

Within 'Custom', I want an option which automatically provides a popup list
of these columns:

File    Edit    View     Custom     Help
                                 OptionA
                                 OptionB
                                 OptionC
                                             Option Number 1
                                             Option Number 2

Please help - I have been reading all the Access 97 help files and I cannot
work it out.  I am a novice to Access programming!

Thanks

Ciaran
icon@iol.ie



0
Comment
Question by:icon
  • 7
  • 4
  • 2
13 Comments
 
LVL 3

Expert Comment

by:chapie
ID: 1956705
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
0
 
LVL 4

Expert Comment

by:ozphil
ID: 1956706
ill have a go when i when i feel like wading through the daunting menubar object. More points would help.
0
 
LVL 3

Expert Comment

by:chapie
ID: 1956707
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!!
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:icon
ID: 1956708
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!
0
 
LVL 4

Expert Comment

by:ozphil
ID: 1956709
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.



0
 

Author Comment

by:icon
ID: 1956710
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.


0
 
LVL 4

Expert Comment

by:ozphil
ID: 1956711
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

0
 
LVL 4

Accepted Solution

by:
ozphil earned 400 total points
ID: 1956712
This is an enhancement. You can change the menu names as you specified and store changes to your table.

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
          .Tag = dn!menuName
          .Style = msoButtonCaption
          .TooltipText = "Display Message Box"
          .OnAction = "=changeMenuNameRequest(" & "'" & .Tag & "')"
        End With
        dn.MoveNext
    Loop
End Sub

Function changeMenuNameRequest(sTag As String)
    Dim cmdBar As CommandBar
    Dim strBarName As String
    Dim cbarCtl As commandbarcontrol
    Dim sNewName As String
     
    strBarName = "myMenuBar"
    Set cmdBar = CommandBars(strBarName)
    Set cbarCtl = cmdBar.FindControl(Type:=msoControlButton, Tag:=sTag, recursive:=True)
    If cbarCtl Is Nothing Then
    Else
        sNewName = InputBox("change menu name: '" & sTag & "' to:")
        If sNewName <> "" Then
            With cbarCtl
                .Caption = sNewName
                .Tag = sNewName
                .OnAction = "=changeRequest(" & "'" & .Tag & "')"
            End With
            ' save change to table as well
            Dim sSQL As String
            sSQL = "UPDATE tblmenus SET menuName = " & "'" & sNewName & "'"
            sSQL = sSQL & " WHERE menuName = " & "'" & sTag & "'"
            DoCmd.SetWarnings False
            DoCmd.RunSQL sSQL
            DoCmd.SetWarnings True
           
        End If
    End If

End Function


0
 
LVL 4

Expert Comment

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

Author Comment

by:icon
ID: 1956714
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.
 
0
 
LVL 4

Expert Comment

by:ozphil
ID: 1956715
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)

 
0
 

Author Comment

by:icon
ID: 1956716
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.
0
 
LVL 4

Expert Comment

by:ozphil
ID: 1956717
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!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question