Solved

Custom command bar help required..

Posted on 1997-09-26
13
292 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:icon
Comment Utility
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
Comment Utility
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
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 4

Expert Comment

by:ozphil
Comment Utility
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 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now