Customized Toolbars, Drop-Down Menus, and Right-Click Menus

Friday, March 08, 2002 04:12:51. Updated Friday, January 03, 2003 23:54:22

Microsoft has a great article about this subject here.

There is also this knowledge base article 207854--ACC2000: Cannot Rename or Delete Custom Shortcut Menus

One way, I feel, to make your database a lot more professional and customized with the end-user in mind is to eliminate the normal toolbars and drop-down menu options from the database in the front-end. As a very good example of this, the 'print' icon showing up while a user has a form open (as opposed to a report) is deceptive; an end-user may think that, say, to print the current record being displayed they should just click the normal print-icon showing up on the toolbar, just like when they're in (let's say) Microsoft Word--or they should select "File...Print" from the pull-down menu. As you almost certainly know, the results aren't professional, to say the least. Also, end-users, don't need to see, for example, "Tools....Relationships" or "Tools...ActiveX Controls" as drop-down options while running the database as an end-user (as opposed to as a designer). Below is an example of exactly what I'm referring to:

If the user clicked the printer icon as shown here, the results would be disastrous.

Oh, the violations of this common-sense rule which exist above. How many? Geez, for starters:

The most obvious, as illustrated--if the user clicked the printer icon as shown, the results would, to say the least, not be what the user had in mind.

Notice the binoculars, which activate the "Find" function. The end-user would not have the powerful record-searching tools they would expect. More about designing a REAL search form in Access can be read here.

The floppy-disc icon for saving. As we know, Access saves new records and their changes automatically, on the fly. The existence of this button confuses the user into believing they have to click it now & then or else lose data. So unprofessional.

Basics of Designing Customized Pull Down Menus, Toolbars, and Right-Click Menus

Here are the basics you need to know about designing custom menus & toolbars

Depending on what you need to accomplish, you will likely need to know how to do some programming in Visual Basic. Any function you have a custom toolbar execute which is not predefined will execute functions typed in modules, which are basically groups of Visual Basic code.

Custom menus & toolbars occur on a form-by-form basis, although if you want a particular toolbar or menu to be used in several forms you can do this without having to create multiple copies of it

Unless you uncheck "allow built-in-toolbars" under "Tools...Startup" and do the same with "allow default shortcut menus," then any form not assigned to a specific toolbar or shortcut menu will get the default Access toolbars & menus

Toolbars, right-click menus and pull-down menus are actually all part of a larger group called "command bars." It's the same as saying, for example, that wheat, rye and white are 3 different types of bread.

No matter whether you want your "command bar" to be a toolbar, pull-down menu or right-click menu, you want to specify it as a toolbar initially, then change it to a right-click or pull-down menu afterwards (more on that later)

Names are important. I mention my suggested naming conventions later on, and you can also check out the Reddick Naming Conventions, a highly respected protocol of object naming.

The Steps

Layout the toolbars (or pull-down menus, right-click menus, etc)

Enter the code in the modules for executing the commands (if necessary)

Assign the code to the buttons on the toolbars(menus, right-clicks etc as well)

Layout the Toolbars

Here is a custom menu being created. The toolbar is off to the right, and the toolbox for creating the commands is called "customize." You would drag the buttons from the "customize" box to the toolbar, right-click, select PROPERTIES and set it up from there.

Click "View...Toolbar....Customize"

Click NEW

Give it a name

You will see a new toolbar appear. Naming Suggestions for Toolbars

Type of Toolbar	Example of Name
Toolbar	tbrName
Pull-down Menu	tbrMenuName
Right-click Menu	tbrRightClickName

If you intend to make this a regular toolbar or right-click menu--as opposed to a pull-down menu--then skip the next step. Otherwise:

If you, say, want a pull-down menu with the initial menus reading something like "File....Edit......View....Tools....Help" etc, then on the CUSTOMIZE box currently showing select NEW MENU on the left and then NEW MENU on the right. Drag the NEW MENU from "Commands" onto the toolbar, right-click on the resulting button and name it "File" or "View" or "Edit," whatever you want it to be called. Repeat this step for every one. Now, it's time to create the buttons under each menu

On the CUSTOMIZE box, go to COMMANDS. If the command you want is a preset one, you can browse under "Categories" and "Commands" to look for it. But if it's going to be a customized command, then select "File" from "Categories" and "Custom" from "Commands". Drag the button to the toolbar. Right-click on the button and select PROPERTIES. From there you can rename the button as well as assign it the code that will execute

Enter the code in the modules for executing the commands (if necessary)

If the command execting is not "preset" and needs to be customized to execute, this would be accomplished by way of code entered into the database's MODULE section which the buttons on the command bars refer to.

Create a new module

Enter the code for executing the custom command you need. (You need to know how to learn how to create functions; if you don't know how, you will need to exit and go do that 1st.)

Make reference to the function's name and copy that

Save and close the module. Give it a name like modCommandBarCode.

Assign the code to the buttons on the toolbars(menus, right-clicks etc as well)

Open the command bar in design view.

Select the properties of the button in the command bar. Under the "On action," enter the name of the function with an = sign proceeding. For instance, if the name of the function is searchrecords(), in the "on action" line you would enter =searchrecords().

Let's start off the simple way--by getting rid of the standard toolbars and drop-down menus:

How to Prevent the Standard Toolbars From Showing Up

Go to "Tools...Startup." UNcheck the "Allow Built-in Toolbars" option.

Now, the standard toolbars as shown above no longer show up at all. Obviously, if you're still designing the database, this cramps your style. But for an end-product oriented around the end-user, the results are FAR more professional.

Remove the Standard "Drop-Down" Menus

This doesn't happen globally; you have to apply it for each form you wish for it to apply to.

Open the form in DESIGN view. Select "Properties" (or double-click the square in the top-left corner of the form where the square dot is.) Go to the OTHER tab. Enter the value =1 (including the = sign).

Creating Customized Toolbars

Click "View....Toolbars....Customize....New"
Add the buttons etc you wish to be on the toolbar.
The Visual Basic you wish to execute would be typed in a MODULE, named something like command_buttons_module.

With each button, hover over it, select PROPERTIES and type the command in On Action.

One example: for a SHOW ALL button, the following code would appear in the MODULE:

Function showallmolds()
Forms!browse_form.Form.RecordSource = "mold_query"
Forms!browse_form.Form.StatusBox = "Showing ALL Records"
End Function

In the command button assigned to execute this code, the syntax would be =showallmolds().

Here are some other samples of code in the module for executing commands (derived from my 2001 TOOLING database

Function addcomment()
'DoCmd.GoToControl "issues_comments"
Forms!mold_form_main.comments.SetFocus
End Function

'-------------------------------------------------------
'-------------------------------------------------------

Function addnewmold()
DoCmd.OpenForm "mold_form_main", acNormal, "", "", acAdd, acNormal
End Function

'-------------------------------------------------------
'-------------------------------------------------------

Function printmolds()
DoCmd.OpenReport "mold_info_landscape", acViewPreview
End Function

'-------------------------------------------------------
'-------------------------------------------------------

Function showallmolds()
Forms!browse_form.Form.RecordSource = "mold_query"
Forms!browse_form.Form.StatusBox = "Showing ALL Records"Tuesday, April 02, 2002
End Function

'-------------------------------------------------------
'-------------------------------------------------------

Function todaysmolds()
Dim sSQL As String
sSQL = "SELECT mold_table.* FROM mold_table WHERE (((mold_table.date_modified)=Date())) " _
& "ORDER BY mold_table.time_modified DESC;"
Forms!browse_form.Form.RecordSource = sSQL
Forms!browse_form.Form.StatusBox = "Showing Molds Modified Today"
End Function

'-------------------------------------------------------
'-------------------------------------------------------

Function printsamples()
DoCmd.OpenReport "samples_landscape_1", acViewPreview
End Function

'-------------------------------------------------------
'-------------------------------------------------------

Function showallsamples()
Forms!browse_samples_form.Form.RecordSource = "samples_query"
Forms!browse_samples_form!StatusBox = "Showing All Entries"
Forms!browse_samples_form.Form.Caption = "Showing All Entries"
'Forms!browse_samples_form!Form.status_original = Me.StatusBox
' This code lets the form StatusBox show the sort order, too
End Function

'-------------------------------------------------------
'-------------------------------------------------------

Function anpnsort()
On Error GoTo Macro1_Err
Forms!browse_form.Form.OrderBy = "assembly_name,mold_description"
Forms!browse_form.Form.OrderByOn = True
Macro1_Exit:
    Exit Function
Macro1_Err:
    MsgBox Error$
    Resume Macro1_Exit
End Function

'-------------------------------------------------------
'-------------------------------------------------------

Function emailreport()
' Email Open Report
On Error GoTo Macro1_Err
Dim strRep As String ' LarryTucAZ@Yahoo.com
strRep = Reports(0).name
' The above 2 lines detect which report is currently open and assign that value to strRep
DoCmd.SendObject acReport, strRep, "SnapshotFormat(*.snp)", "", "", "", "samples report", _ 
& "here is a sample report. Make sure you have 'Snapshot Viewer' " _
& "installed in order to read this report", False, ""
Macro1_Exit:
    Exit Function
Macro1_Err:
    MsgBox Error$
    Resume Macro1_Exit
End Function

'-------------------------------------------------------
'-------------------------------------------------------

Function printreport()
' Print Open Report
On Error GoTo Macro1_Err
DoCmd.SelectObject acReport, Reports(0).name
DoCmd.RunCommand acCmdPrint
Macro1_Exit:
    Exit Function
Macro1_Err:
    MsgBox Error$
    Resume Macro1_Exit
End Function

'-------------------------------------------------------
'-------------------------------------------------------

Function closereport()
' Close Active Report
On Error GoTo Macro1_Err
Dim strRep As String ' LarryTucAZ@Yahoo.com
strRep = Reports(0).name
' The above 2 lines detect which report is currently open and assign that value to strRep
DoCmd.Close
DoCmd.Close acReport, strRep
Macro1_Exit:
    Exit Function
Macro1_Err:
    MsgBox Error$
    Resume Macro1_Exit
End Function

'-------------------------------------------------------
'-------------------------------------------------------

Function showallpurchasereqs()
Forms!purch_req_browse_form.Form.RecordSource = "purchase_req_query"
Forms![purch_req_browse_form]![purchase_req_all_datasheet_form].Form.RecordSource = "purchase_req_query"
End Function