<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Understanding and using CommandBars, Part II (Creating your own)

Published on
57,788 Points
41,288 Views
20 Endorsements
Last Modified:
Awarded
Community Pick
Applies to: 2000, 2003, 2007, 2010, 2013 (desktop)

As stated in the first article,  Understanding CommandBars (Part 1), users of Windows applications expect to be able to right click their mouse button over a form or control and see a popup menu designed to provide options for the user based on where they are and what they are doing in the application.  The CommandBars built into Access do a pretty good job of meeting these needs, but custom applications require custom solutions and Access provides a robust way for you to create your own shortcut bars (at least for your desktop apps).

In that article, I briefly described how to identify the names of each of the Access CommandBars (also referred to as shortcut or popup menus) and the controls within each of those CommandBars.  In this article you will learn how to create your own shortcut menus, modify existing CommandBars, and implement custom shortcut menus within your applications.  The sample database contains all of the code used in the article.

If you are fortunate to have a copy of Access 2003 laying around, you can use the instructions in this link from the Microsoft Office web site to create your custom shortcut/popup  menus, and then import them into your application.  In later versions (2007+) of Access, this feature has been discarded from the user interface, so you are left with macros and VBA code to create your shortcut/popup menus.  This article describes how to create the VBA code to institute several popup menus in your applications.  It describes how to instantiate the menu, add controls to it, and use it in an application.

To start with, you should add the Microsoft Office XX Object (Image 1) library to your application references in the VB Editor menu.  Image 1When you do this, you will be able to take advantage of IntelliSense as you build your shortcut menus.  Once you are done creating your menus, you can remove this reference and make some minor modifications to your code (discussed later in the article) to implement late binding and provide compatibility of the shortcut menus across multiple versions of Office.

Step 1: Creating the code module and subroutine
The first step in defining a shortcut menu is to create a code module and subroutine to put it in.  You can build your menus and the supporting functions in your form class modules, and this can be beneficial if you are creating form specific functionality.  However if you are creating shortcut menus for use throughout your application, you will want to put these in a code module (Image 2) where the functions can be accessed no Image 2 matter what form or report has the focus.   Additionally, by placing all of your shortcut menus in a single location, you will know exactly where to find them when (or if) you need to edit them.  The only thing you must consider if you place all of your shortcut menu definitions and Action functions in a standard code module is that if you want to reference a control on a form or report, you must explicitly define which form/report in your code.

Once you have created your code module, create the procedure which will actually build the menu.
Private Sub FormOptions
End Sub

Open in new window

Step 2:  Define your CommandBar
In order to create your shortcut menu, you must declare your CommandBar object and the control objects that you will be using in the menu.
Dim cbr as CommandBar
Dim cbrButton As CommandBarButton 
Dim cbrcombo As CommandBarComboBox
Dim cbrEdit As CommandBarControl
'or
Dim cbr as Object         'Commandbar
Dim cbrButton As Object   'CommandBarButton 
Dim cbrcombo As Object    'CommandBarComboBox
Dim cbrEdit As Object     'CommandBarControl

Open in new window

Note: The second method shown above is used when implementing late binding.

After declaring the CommandBar and the controls, you must instantiate the CommandBar.
set cbr = CommandBars.Add(Name, [Position], [Menubar], [Temporary])

Open in new window

1. The first argument, [Name], of the CommandBars.Add method is the name you will use to refer to this CommandBar throughout your application.

2.  The second argument, [Position], defines the position of the CommandBar; for popup/shortcut menus this argument must be 5 (msoBarPopup).

3.  The third argument, [Menubar], is a true/false argument which indicates whether to replace the active menubar with this bar.  The default value is False, and I've never tried setting it to anything else.

4.  The last argument, [Temporary], determines whether the CommandBar will only be active in the current session of the database (True), or whether it will be a permanent part of the database (False).  If you set this argument to False and call the subroutine that creates the CommandBar, you should never have to call it again (in the active  database).
The sample database uses Temporary= True and re-instantiates the CommandBars each time the database is opened.

Step 3: Defining Menu Controls
CommandBar controls are similar to their form counterparts and include buttons, text boxes, and combo boxes.  The syntax to add a control to the command bar is simple:
Set ctrl = cbr.Controls.Add([Type], [ID], [Parameter], [Before], [Temporary])

Open in new window

1.  The [Type] argument can contain any of the msoControl constants (msoControlButton, msoControlEdit, msoControlDropdown, msoControlComboBox, or msoControlPopup).  Use the numeric values or create your own constants if you intend to institute late binding.

2.  When you define your own shortcut menus, you are not limited to the controls you create, you can use the myriad of shortcut controls already built into Office (Cut, Paste, Print, Print Preview, Save, Delete, ...).  To take advantage of these Office shortcut controls in your menu, you simply have to set the [ID] argument in the controls.Add method to the [ID] value associated with the standard office control.  When you do this, your new control inherits all of the properties associated with the standard Office shortcut control (caption, image, and actions).

The challenge is determining the [ID] values of the various standard menu controls you want to use.  If you know the name of the CommandBar and caption of the control (or its position within the CommandBar) you can use the following line of code to get the ID value of the control.
Debug.print CommandBars(commandbarname).controls(controlcaption).ID
example: 
Debug.print CommandBars("Database Table/Query").controls("&Print...").ID

Open in new window

If you don’t know the CommandBar name, or the precise caption of the control, use the following subroutine to print a list of the controls  (and their associated CommandBars)  which have a caption similar to what you are looking for.
Public Sub FindID(CaptionContains As String)
    Dim cbr As CommandBar
    Dim ctrl As CommandBarControl

    For Each cbr In Application.CommandBars
        For Each ctrl In cbr.Controls

            'Because many of the controls contain keyboard shortcuts, they 
            'have ampersands embedded within the caption that must be 
            'removed before doing the comparison to the search string.
            If InStr(Replace(ctrl.Caption, "&", ""), CaptionContains) > 0 Then
                Debug.Print cbr.Name, ctrl.Caption, ctrl.Id
            End If
        Next
    Next
End Sub

Open in new window

Note: I found 50 CommandBar/control combinations that contain the caption “Print” (the ID for “&Print…” is 4, the ID for “Print Pre&view” is 109).

3.  The [Parameter] argument of the Add method allows you to assign a parameter value to a particular CommandBar control.  This is helpful if you want to run the same function for multiple CommandBar controls but pass the function a different value for each CommandBar button (this technique is used in the FormOptions and FormReports code in the sample database).

4.  The [Before] argument indicates the position of the new control within the CommandBar;  the control will be inserted in the CommandBar controls collection immediately before the control that is currently in that position (and will assume that position).  If you use a value larger than the number of controls in the collection plus one (> .count+1), Access will raise RunTime error #9, (Subscript out of range).  To avoid this error, use the default and simply add the controls to the CommandBar in the order in which you want them to appear.
Note:If you are trying to modify existing Office CommandBars, you will only be able to append the new controls to the end of the menu list.

5.  The final argument, [Temporary] has the same purpose for controls as for the CommandBar object; it determines whether the control is a temporary or permanent addition to the CommandBar.  If you create the CommandBar as Temporary, this setting is irrelevant as the CommandBar will be dropped from the database when you close it.  However, if you create the CommandBar as permanent (Temporary:=False), but set the controls Temporary property to true, then the next time the database is opened, the CommandBar will be present, but the control will not.  This is especially useful if you want to temporarily add controls to an already existing Office shortcut menu.  The code in the sample database uses this method to temporarily add a button to the Form Datasheet Row CommandBar, allowing the user to right-click on a row in the datasheet and generate a details report that is specific to the selected record.

Control Properties
Like the controls placed on forms and reports, CommandBar controls have numerous properties associated with them.  Buttons are like toggle buttons and include a “state” property which indicates whether the button is Up or Down.  Text boxes and combo boxes operate similar to their form and report counterparts.  

1.  All of these controls share a base set of control properties, the most critical of which is the “Caption” property.  The "Caption" performs dual roles as both the control label and the control name.
Ctrl.Caption = “&Active Only”

Open in new window

If the control is a button, the caption is displayed on the button.  If the control is a combo box or textbox, the caption will display to the left of the actual control, as though it were the label associated with the control.  The other purpose for the caption property is to use it to refer to the control object, as in:
set ctrl = cbr.controls(“&Active Only”)

Open in new window

2.  The purpose of shortcut menus is to simplify tasks, but it is left up to you (the developer) to define what you want to accomplish when a shortcut menu button is clicked, a combo box item is selected, or text is entered in a textbox.  You do this by defining the "OnAction" property of each of the controls.  If you fail to define the OnAction property of a button, clicking it will will have no effect.  If you fail to define the OnAction property of a combo box or textbox, then selecting an item from the combo box or entering text into a text box will update the “value” of the control, but will not perform any other action.  The syntax for the OnAction property is:
Ctrl.OnAction = “=fnSomeFunctionName()”

Open in new window

The function defined by the text within the quotes in the example above must be the name of a PUBLIC function, must be preceded by the equal sign, and must contain the opening and closing parenthesis at the end of the string.  If the function is defined as having mandatory parameters, then you must pass a value to the function within the parenthesis, just like you would if you were calling that function from some other code module in your form.
Ctrl.OnAction = “=fnSomeFunctionName(1)”
'Or
Ctrl.OnAction = “=fnSomeFunctionName(‘SomeTextValue’)”

Open in new window

An alternative to this method is to define your functions without the arguments and use the [Parameter] property of the control to assign a value to the control.  If you use this technique, then within the function defined by your OnAction property, you can determine the value of the parameter of the control that was clicked with the following line of code.
CommandBars.actioncontrol.parameter

Open in new window

There are a number of other properties associated with each control which you can browse using IntelliSense.  Generally, the Tag (used like a controls Tag property) and BeginGroup (draws a line in the menu above the current control) are the most commonly used of the other control properties.

Working with buttons
As mentioned above, buttons function similar to toggle buttons, but unless you programmatically change the state of the button it will remain in that state when you click it and execute the code defined in the OnAction property of the button.  In the example database, the Options button pops up a menu that contains two buttons (Display All and Display Active).  You don't really need both of these buttons since Display Active, with no check-mark next to it would probably represent Display all, but I've included both to show how to assign a parameter to the control and then use that parameter in the associated OnAction function call.  The code to create shortcut menu, with just the two buttons would look like:
Public Sub FormOptions(Optional Reset As Boolean)

    'the constants used in this code are declared in the example database

    Dim cbr As CommandBar 'Object   '
    Dim cbrButton As CommandBarButton 'Object  '

    On Error GoTo FormOptionsError
    
    If CmdBarExists("FormOptions") Then   'see sample database
        If Reset = False Then Exit Sub
        DeleteCmdBar "FormOptions"        'see sample database
    End If
    
    'Instantiate the commandbar
    Set cbr = CommandBars.Add("FormOptions", BarPopup, , True)

    'Create the two buttons
    With cbr

        Set cbrButton = cbr.Controls.Add(ControlButton, , , , True)
        With cbrButton
            .Caption = "Display All"
            .Parameter = "All"
            .State = -1
            .OnAction = "=fnFormOptionsDisplayRecords()"
        End With

        Set cbrButton = cbr.Controls.Add(ControlButton, , , , True)
        With cbrButton
            .Caption = "Display Active"
            .Parameter = "Active"
            .OnAction = "=fnFormOptionsDisplayRecords()"
        End With

    End With

    Exit Sub

FormOptionsError:
    
    MsgBox Err.Number & vbCrLf & Err.Description, vbOKOnly, "FormOptions Menu error"
    Debug.Print "FormOptions error", Err.Number, Err.Description
    
End Sub

Open in new window

This code includes calls to several other procedures (CmdBarExists, DeleteCmdBar) which are provided in the example database.  You should note that the State of the "Display All" button is initially set to -1, to place a check-mark next to it.  Also note that both buttons call the same function in their OnAction events, and have different parameter values.  Use of that parameter value can be seen in the following OnAction function:
Public Function fnFormOptionsDisplayRecords()

    Dim strAction As String
    
    strAction = CommandBars.ActionControl.Parameter
    
    'Change the state of the two buttons
    CommandBars("FormOptions").Controls("Display All").State = (strAction = "All")
    CommandBars("FormOptions").Controls("Display Active").State = (strAction = "Active")
    
    'Filter or clear the filter, depending on which button was selected
    If strAction = "All" Then
        Form_frm_CommandBars.sub_Presidents.Form.Filter = ""
        Form_frm_CommandBars.sub_Presidents.Form.FilterOn = False
    Else
        Form_frm_CommandBars.sub_Presidents.Form.Filter = "[Active] = -1"
        Form_frm_CommandBars.sub_Presidents.Form.FilterOn = True
    End If

End Function

Open in new window

This code determines which of the two buttons was clicked and assigns that string value to the variable strAction.  It then toggles the two buttons on/off (up/down) based on the value of strAction.  And finally, the code in the If / End If segment either sets the subforms filter or clears it.

Working with ComboBoxes
Shortcut menu combo boxes only contain a single column, do not have a RecordSource property, and do not have  a "Value" property.  Despite these drawbacks, they can be very useful tools in your shortcut menus.  The FormOptions CommandBar in the sample database contains a combo box which performs the same function as the two buttons (Display All and Display Active) discussed above.  The code that creates this combo in the CommandBar is:
Set cbrcombo = cbr.Controls.Add(ControlComboBox, , , , True)
With cbrcombo
    .Caption = "Display records:"
    .BeginGroup = True
    .Width = 150
    .DropDownWidth = 100
    .AddItem "All"
    .AddItem "Active"
    .OnAction = "=fnFormOptionsDropdown()"
    .ListIndex = 1
End With

Open in new window

The differences between this and the button code are the addition of the DropDownWidth property to define the width of the dropdown, the addition of the .AddItem methods to add items to the combo boxes list, and the addition of the ListIndex property to set the default "value" of the combo box.  When determining the "Value" of the combobox in function fnFormOptionsDropDown(), you must use the controls ListIndex property.  Although this is not as simplistic as the using the "Parameter" property of the button control, it provides the ability to add add multiple values in a small space.  If you have a large number of items to add to the combo box, you can create a recordset and loop through that recordset to add multiple values to the combo.  If you really wanted to, you could create a cascading combo box functionality by using the controls .Clear method and then rebuilding a comboboxes Item list in the function called by the OnAction event of another combo box.

Working with textboxes
Personally, I've found very little use for textboxes in my shortcut menus;  one purpose might be to enter values to be used in a search function or filter.  The sample database includes code to create a textbox in the FormOptions menu:
Set cbrEdit = cbr.Controls.Add(ControlEdit, , , , True)
With cbrEdit
    .Caption = "Year:"
    .OnAction = "=fnFormOptionsYear()"
End With

Open in new window

and to display the entered value in a message box after it is entered.  Note that the CommandBar textbox does not have a "Value" property.  You must refer to the "Text" property of the control to determine what was entered into that control:
    MsgBox CommandBars("FormOPtions").Controls("Year:").Text

Open in new window

Working with Popups
Yes, you can embed shortcut menus inside of other shortcut menus.  To do this, use the
msoControlPopup constant (or its associated value: 5) when you define your control.  Then treat it as though it were a separate CommandBar and assign controls to it within a With / End With code segment.  The sample database does not include an example of how to do this.

Implementing your custom CommandBars in your application
If you choose to instantiate your custom CommandBars as Permanent, you will only need to run the code that creates them once for each database.  If you have a standard set which you will want to use repeatedly you might run that code in your New Database template; in which case you would never need to call them, unless you decided to modify them.  However, if you instantiate your custom CommandBars as Temporary, you will need to call the code that creates them every time the database opens; I do it in the Timer event of my splash form or in the Open event of some forms if they have a CommandBar which is unique to a form which may never get opened during a particular session.

The easiest way to implement custom shortcut menus in your application is to set the "Shortcut Menu Bar" property of your forms, reports, and controls to the Name you assigned to your CommandBar.  When implemented in this fashion, your custom CommandBar will replace the standard Office shortcut menu in that context.  I've found that this does not work with command buttons (at least not in 2007 and 2010).Furthermore, unless you assign a custom CommandBar to the form as well, you will still see the standard form shortcut menu when you click on the forms title bar.

To disable the forms standard shortcut menu, you must turn set the forms Shortcut Menu property to No, but this disables the shortcut menus for all of the controls on that form as well.  There is, however, a way that to disable a forms Shortcut Menus (set the property to No) and still enable all of your custom shortcut menus.  Instead of assigning your menus to the Shortcut Menu Bar property of your forms, reports, and controls, you can use the MouseUp event associated with those objects.  This is implemented in the FormHeader_MouseUp event with the following line of code.
If Button = acRightButton Then CommandBars("FormMenu").ShowPopup

Open in new window

This process a little more effort, since you must add a line of code to the MouseUp event of each of the controls where you want to implement your shortcuts, but it is worth it to get rid of the form shortcut menu and still have access to your custom Commandbars.

Late Binding
The advantage of late binding is that you let Access determine which library references are necessary for your application, and it will select the appropriate version based on the version of Office that is being run.  If you choose to use Early Binding, you should ensure that you compile your code on a machine running the earliest version of Office your users are likely to encounter.  Office does a good job of updating references to later versions, but fails every time if you compile the code on a machine with a later version of Office than one of your users has on their desk.  To implement late binding, you simply change the object declarations from "as CommandBar" or "as CommandBarButton" to "as Object".  You will also need to replace all references to the constants associated with Microsoft Office XX Object library, and then remove that reference from your project.  Your code will not compile properly if you have forgotten to change one of the object declarations or have used an invalid constant.

Manipulating standard Office Shortcut menus
In addition to creating your own Commandbars, you can manipulate the standard Office shortcut menus as well.  In the sample database, you will find that this is accomplished in the Form_Open and Form_Close event of datasheet subform.  The datasheet normally has three CommandBars associate with it, one for Rows, one for Columns, and the other for Cells.  

1.  In the Form_Open event, the FormDatasheetMenu subroutine adds a button to the "Form Datasheet Row" CommandBar;  the last item in that list.

2.  The FormDatasheetRowMenu subroutine hides/reveals all of the other items in the "Form Datasheet Row" CommandBar.  You can see the effect of this by changing the value of the Datasheet Row Menu option group and then right clicking on the datasheets record select button.  

3.  The final line of code in that subroutine enables/disables the "Form Datasheet Cell" CommandBar, the one that would normally popup if you right click in a datasheet cell.  CommandBars do not have a Visible property, to hide them, you simply set the Enabled property to False.  You can see this in action by changing the option in the "Datasheet Cell Menu" option group, and then right clicking in one of the datasheet cells.

Conclusion:
Shortcut menus are a critical aspect of Windows applications and VBA is a powerful tool for creating your own or manipulating the standard Office shortcut menus.

If you found this article helpful, please don't forget to vote "Yes"!
CommandBars-II.mdb
20
Comment
Author:Dale Fye
38 Comments
LVL 48

Expert Comment

by:aikimark
I expected to see some use of the XML files (or, at least, some mention of that technique).  That approach seems easier than this dynamic method.
0
 

Administrative Comment

by:Eric AKA Netminder
Congratulations; your article has been selected as Experts-Exchange Approved.

And it got a yes vote besides.

ericpete
Page Editor
0
LVL 61

Expert Comment

by:mbizup
aikimark,

I'm not sure where a discussion of XML (Ribbon) techniques would fit.  The article by title and content is about CommandBars, a very popular feature that to many developers' disappointment has been deprecated.  

To my understanding the target audience here is seasoned developers who are aware of the XML based ribbon, but who do not want to use it -- developers who want to tap into the deprecated CommandBar collection in all versions of Access, NOT the Ribbon that has replaced commandBars in Access's GUI since Office 2007 (or who specifically want to use the commandBar collection to supplement a ribbon interface):


If you are fortunate to have a copy of Access 2003 laying around, you can use the instructions in this link from the Microsoft Office web site to create your custom shortcut/popup  menus, and then import them into your application.  In later versions (2007+) of Access, this feature has been discarded from the user interface, so you are left with macros and VBA code to create your shortcut/popup menus.  

A discussion of the XML-based ribbons would be a separate (and large) topic.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

LVL 52

Author Comment

by:Dale Fye
@aikimark, as Miriam mentioned, this article was focused on creating and using shortcut popup menus, via VBA.  I really have not had a chance to play much with the XML Ribbons, although I probably need to start.  As Miriam also mentioned , the XML Ribbon piece is a large topic, all by itself.

Thanks, Miriam.
0
LVL 48

Expert Comment

by:aikimark
1. My comment was meant to convey the lack of mention of the XML topic.  This can be used as a tickler to your future article.
0
LVL 52

Author Comment

by:Dale Fye
Thanks.
0
LVL 48

Expert Comment

by:aikimark
@Dale

One of the members of the local office user group (TriMOUG) gave a presentation on the subject.  If you want, I'll ask him for his links/examples.
0
LVL 52

Author Comment

by:Dale Fye
ask him to write an article!
0
LVL 48

Expert Comment

by:aikimark
He's not an EE member and he's not 'ready' to write an article.
0
LVL 75
"a very popular feature that to many developers' disappointment has been deprecated.   "
Commandbars have not been deprecated ... only the UI Customize Dialog has gone away :-(

mx
0
LVL 52

Author Comment

by:Dale Fye
Joe, would you buy it if I built it?
0
LVL 75
For SURE.
Would need to have same drop and drag capability ... good luck with that :-)

But ... all we really need is a scaled down 'Customize Dialog' to support only Shortcut - aka Right Click - aka Context menus.  No point in worrying about regular Menu and Toolbars at this point. I'm replacing all legacy menus in my dbs here at work with Ribbons, which in the end offer a lot more flexibility.

The fact that Microsoft did not supply a tool to build right click menus is unacceptable and unforgivable !  ALL professional applications of any kind in any platform make extensive use of right click menus.
0
LVL 38

Expert Comment

by:Jim P.
I'm replacing all legacy menus in my dbs here at work with Ribbons, which in the end offer a lot more flexibility.

Maybe that is worth an article. One of the ones I always used to do in Word 2k3 and below was take one off text from various sources, clean it up with advanced search and replace and then turn it into a table from a semi-customized table command bar.

I'm now using Word 07. I'm not allowed to use the words I desire to properly describe the experience. I'm very seriously thinking of going back to 03 on my laptop, I just haven't been to the office to grab a copy of 2k3 to downgrade.
0
LVL 48

Expert Comment

by:aikimark
You might want to check out the Microsoft Access Store.  One of the local evangelists suggested that I consider creating something and using that venue as my distribution channel.
0
LVL 75
Dale ... I think that the Customize Dialog is still buried in Access somewhere, just not exposed. Since the Customize Dialog IS exposed in the VBA editor - this tells me the UI version is somewhere.  I asked Jeff C to see if he could track it down ... but that has not happened.  I say it's somewhere in the Access.EXE or some DLL/OBJ file.

In days gone by, there were 'entry points' into the Access EXE. Back around A2.0 or 95/97 ... Ken Getz used one to get to the common dialog functionality !  I think that was described in his A97 Access Developers Handbook.

Just saying ...
0

Expert Comment

by:Erika E
Thank you so much for this helpful article. I'm struggling with this right now. Joe - Any luck finding the Customize Dialog UI?
0
LVL 52

Author Comment

by:Dale Fye
Erika,

I hope to release CommandbarTool add-in for Access within the next month.  Haven't figured out a price for it yet, but it will be somewhere under $50.  It will provide a simple to use interface to create your own popup menus, including incorporating the standard Access shortcut controls, resorting the controls in your popups, importing popups from other access databases, and even copying the code associated with those popups in other applications.

BTW , Welcome to Experts Exchange.
0

Expert Comment

by:Erika E
Thank you!
0
LVL 75
No.  Jeff Conrad was supposed to be looking for that, but it has not materialized. Sorry.
0

Expert Comment

by:Erika E
Thank for this article. I had to add a button to my toolbar (created in a version pre-Access 2007), and I was able to recreate the toolbar and add the new button.

One note, I had trouble when the on-action subroutines were "Subs". I got an error: "The expression you entered has a function name that Microsoft Access can't find." But when I changed them to Functions, it worked as expected!
0
LVL 52

Author Comment

by:Dale Fye
I'll have to go back and look at the article.  I could have sworn that I was clear that the OnAction property required a public function.

Glad you were able to make it work.
0

Expert Comment

by:Erika E
You totally did, you must have typed "function" up there at least three times in the OnAction section. But that detail missed my brain, and didn't need a return value, so I made them Subs, and then took about 30 minutes before I realized my mistake.

Thank you so much for being so responsive!
0
LVL 1

Expert Comment

by:Joseph Krausz
Hello Dale

First i would like to thank for your efforts.without this article i wouldn't even consider using CommanBars.

I was wondering whether it's possible to determine which Form called the Commandbar. is there any properties keeping this info, perhaps, i can pass some parameter when calling the CommandBar (i am using a popUp CommanBar calling it with Application.CommandBars("MyBar").Show
Also, i wonder if i can get back which value the user chose

Regards

Joseph
0
LVL 52

Author Comment

by:Dale Fye
Joseph,

I hope to start selling an Access Shortcut Tool NLT the first of the year, which will make the process of creating your own, and modifying built-in commandbars much easier.  Don't know where I'll announce that, but keep your ears open.

If you have a commandbar that is being used on more than one form, or control, then I generally use code  similar to the following to determine the form that called my commandbar.
Public function MyBarSomeControlAction()

    dim frm as form

    set frm = screen.activeform
    while frm.ActiveControl.ControlType = 112    'this would be a subform control
        set frm = frm.ActiveControl.Form
    Wend

    debug.print frm.name, frm.ActiveControl.name

End Function

Open in new window


Ref your comment "can get back which value the user chose"
Are you talking about which value in a dropdown list?
Or, are you using the same OnAction function for multiple controls on your menu and trying to determine which one actually called the function?
0
LVL 1

Expert Comment

by:Joseph Krausz
Hi Dale

Thanks for reply.
you know my answer on that, no ? i hate to use the ActiveControl method... hardens testing and error prone.

Anyhow, in my case i am using the CommandBar just in one form but opening the frm in multiple instances, and the CommandBar supposes to choose the Date Filtering criteria. so the problem i came across was that the CommanBar didn't know which instance called him so he filtered all the opened instances so what i did. I have created a public local variable and before applying the filter i checked whether the frm expects yet a filter to be applied or not so it only applied the filter to the precise instance.
(Actually, i implement it with event listeners, but the concept is the same)

Private Sub txtFormatedDate_Click()
    Dim CBR As Office.CommandBar

    If Not CmdBarExists("ChatConvDateRightClick") Then
        CBChatDate
    End If
    
    e.RaisePerformNextOperation Name, Parent.Name, Parent.hwnd
    Set CBR = Application.CommandBars("ChatConvDateRightClick")
    With CBR
    .ShowPopup
    e.RaisePerformNextOperation "TasksChatRoomRpt", "TasksChatRoom", Null
    End With
'    bolFilterClick = True
       
End Sub

Open in new window



the event listener code
Private Sub evt_PerformNextOperation(CallingFrm As String, TargetFrm As String, var As Variant)
    If CallingFrm = "Function.FilterByDate" Then
        If Not bolFilterClicked Then Exit Sub
        Task.dtDefaultFilter = CDate(var)
        LoadConversations
        Me.TasksChatRoomRpt.Requery
        bolFilterClicked = False
    ElseIf CallingFrm = "TasksChatRoomRpt" And TargetFrm = Name Then
        If hwnd = var Then
            bolFilterClicked = True
        Else
            bolFilterClicked = False
        End If
    End If
End Sub

Open in new window



Regards

Joseph
0
LVL 61

Expert Comment

by:mbizup
IF you are using Access 2007 or higher, couldn't you use the TempVars collection to reference the active form?  That's assuming this code works in accdb format of course... I've only tried it in upgraded mdbs.  Similar in a way to public variables, but more reliable.
0
LVL 1

Expert Comment

by:Joseph Krausz
TempVars is good to use for someone who likes that approach of storing pieces of data. I used to use it heavily several years ago - mainly Primary/foreign keys to pass between related forms - but stopped as I couldn't have control on it. now days I'm passing class objects between related forms so all related forms are aware on what they need to be - locally. Anyhow, in this scenario I wouldn't say it's bad to use as its really 'temp' and has no further connections and yes it's much more reliable than ActiveForm.

Regards

Joseph
0
LVL 52

Author Comment

by:Dale Fye
Keeping track of multiple instances of the same form, and then referring to the correct instance in a public function would be problematic.

Allen Browne has a page on his web-site which discusses management of multiple instances of a form, along with some code examples, here.
0
LVL 75
I have used Allen's technique and expanded on it.  Mainly instead of using Collections, I replaced Collections with the Dictionary Class, wherein Patrick Matthews here on EE has a outstanding article on the Dictionary Class.
0
LVL 1

Expert Comment

by:Joseph Krausz
I don't see why it should be problematic. Actually, I came across this technic from Allens article.... And as you can see in my quoted code I rely on hWnd which identifies the correct instance. Although, collections are limited you can instead use dictionary- which I'm using only in special needs - when I need to get the extra methods it provides (array of items etc...) but problematic ? Please point me out where you came across any issues with a public collection. perhaps, when system goes in debug ... End.  so it loses the values, this is in must cases not an issue as my systems are mostly error handled. Plus, I believe this happens with any public variable including dictionary.

Joe - thanks for referencing the Dictionart article, although, Is already in my Arsenal I believe some more people will be able to utilize it.
if u can describe in short what you gain by using a dictionary over a collection for form instances I would appreciate it. (Is it appropriate to comment about dictionary in this article ?? )

Dale - I almost forgot the initial comment question .... Whether it's possible to get back from the commandBar object itself which value had been selected, never mind the OnAction just get back the index or value of DropDown selected item,  is it possible ?

Regards

Joseph
0
LVL 52

Author Comment

by:Dale Fye
@joseph

Commandbars.actioncontrol.index

Should be used for dropdowns.

Commandbars.actioncontrol....

This syntax will give you intellisense and you can select a variety of the properties associated with the control that was selected from your menu.

Dale
0
LVL 75
"if u can describe in short what you gain by using a dictionary"
Well it's been a while, so you will just kind of have to trust me on this. There are some limitations with Collections, something to do with Keys(?), etc. I don't recall exactly.  The Dictionary Class is a significant improvement over Collections.
0

Expert Comment

by:Steve_Pupel
Dale, this is an excellent resource!  I've been developing in MS Access (and many other) tools since the 1980s and I've googled about a million topics.  I'm really looking forward to using more pop up menus, I love the way they appear right near the mouse pointer as it makes apps that much easier to use.

Thanks, this is great work!  Well done.

Sincerely

Steve Pupel
Grand Rapids  MI
>>  December 14th, 2016
1

Expert Comment

by:Micheal Vasek
I found I am able to do some of this in the findControl() function, but with the advent of Office 365, the dB doesn't connect across applications, i.e. trying to see controls in Outlook from Access
0

Expert Comment

by:Bryan Lundeen
Not an Acceess problem but none the less maybe you can help. I'm trying to find a way to click the button in Word 2013 in the backoffice area under print and select print on both sides or print on single sided. I can't find the MsoID for those buttons. Any ideas?
0
LVL 52

Author Comment

by:Dale Fye
Bryan,

Are you trying to create a shortcut menu for Word?

Since you are talking about Word 2010, I'm inclined to think you are going to have to dive into the Ribbon, but I have no idea how to do that in Word (or Access) for that matter.

Dale
0

Expert Comment

by:Gilmer Pajarez
Excellent Article. it really help me to clarify many questions.
0

Expert Comment

by:Patel Vijay
amezing..........
0

Featured Post

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Join & Write a Comment

With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Wrapper-1-Query. Use an Excel function to calculate a column for an Access query. Part 1. Shows a query in Access that has a calculated column with the results of an Excel worksheet function. See how to call a wrapper function from a query, and …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month