Solved

Custom command bar help required..

Posted on 1997-09-26
13
305 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 100 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

749 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