Chapter 11: Creating Dynamic Ribbon Customizations (2 of 2)
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.
Previous Part: Chapter 11: Creating Dynamic Ribbon Customizations (Part 1 of 2)
.gif "Important note") Important |
|---|
The code marked as Visual Basic in this chapter is written in Visual Basic for Applications (VBA). |
Contents
New Types of Controls
Images
Moving Functionality into the Ribbon
Other Ribbon Modifications
Summary
Additional Resources
New Types of Controls
In addition to standard types of controls, such as buttons, edit boxes, and drop-downs, the Ribbon provides several new types of controls that enable you to create rich experiences for your users.
Dialog Box Launcher
You may have noticed at the bottom of certain groups, there is a very small button in the corner. This button is contained in a control called a dialog box launcher. This control is typically used to launch more options for the group than can be displayed in the Ribbon. For example, let's say that your application provides options to its users. If you have many options, it might not be feasible to put all of them into the Ribbon for fear of cluttering it. Instead, you might only include the most common options in the customization and expose the rest on an Access form. The dialog box launcher lets you create this button at the bottom of a group, as shown in the following XML. (This example shows the entire group as an example. Notice that this customization requires the OnOpenForm callback previously defined.)
<group id="grpOptions" label="Options">
<labelControl id="lblOptions" label="Options go here..."/>
<dialogBoxLauncher>
<button id="btnMoreOptions" onAction="OnOpenForm" tag="frmOptions"/>
</dialogBoxLauncher>
</group>
Gallery
Use the gallery node in the XML for the ribbon customization to create a gallery. Galleries are used to display items that can be arranged in a grid-type layout. Items in the gallery can display images, text, or both images and text. Galleries can also include one or more buttons that appear at the bottom of the gallery.
Let's say that you were building a custom filter for a report and would like to include the months of the year to filter by date. To prevent users from scrolling through a list of months or from selecting from a long list, you may split up the months by quarters, as shown in Figure 11-18.
Figure 11-18. Using a gallery to organize months by quarters
.jpg "Using a gallery to organize months by quarters")
The XML for the gallery, as shown in the following, includes 12 item nodes and a button node that the user can use to change their regional settings. The following layout for the gallery is three columns by four rows.
<gallery id="galMonthsEng" label="Months (English)"
columns="3" rows="4">
<item id="galMonth1" label="January"/>
<item id="galMonth2" label="February"/>
<item id="galMonth3" label="March"/>
<item id="galMonth4" label="April"/>
<item id="galMonth5" label="May"/>
<item id="galMonth6" label="June"/>
<item id="galMonth7" label="July"/>
<item id="galMonth8" label="August"/>
<item id="galMonth9" label="September"/>
<item id="galMonth10" label="October"/>
<item id="galMonth11" label="November"/>
<item id="galMonth12" label="December"/>
<button id="btnGal1" label="Regional Settings"
imageMso="ShowTimeZones"
onAction="ShowRegionalSettings"/>
</gallery>
Split Button
Split buttons are controls that contain a button and a menu and are created using the splitButton node. The button inside the split button is displayed in the Ribbon, and as such, is used to set the label attribute for a split button. The splitButton node itself does not define the label attribute. As with other buttons, the button node inside a split button has an onAction attribute that you can handle to receive an event from the Ribbon. The menu items appear as additional choices beneath the button. We take a closer look at split buttons in the section "Creating a Split Button That Sticks."
Dynamic Menu
As the name suggests, a dynamic menu is a menu that is filled at runtime. Use the dynamicMenu node to create a dynamic menu. To fill the content, you must provide a getContent callback for the dynamic menu. This callback is required. Dynamic menus are useful for scenarios where users can contribute to the content of the application. We look at an example for using the dynamicMenu control in a few moments.
Images
One cool thing about the Ribbon is that it is highly graphical in nature. We think this is cool not just because of the nice graphics, but because the graphics provide some really great opportunities for you and your users. Applications are easier to use because users have graphics as a guide. Naturally, you don't have to use graphics in your applications, and there are likely to be many applications where they are not appropriate. However, the addition of rich graphics enables new scenarios that may not have been possible in the past.
For the remainder of this chapter, we look at specific scenarios for using ribbon customizations in your applications. The examples for the scenarios are available for download and we've included the name of the sample database at the top of the section.
.gif "Note") Note |
|---|
The code for the samples that follow is contained in the sample file RibbonImages.accdb, which is available for download on the Web site for this book. |
Images Included with Office
As cool as we think graphics are, we have to admit — we're graphically challenged. We're far more comfortable writing code than drawing bitmaps or icons. Luckily for us (and for you), there are many images included with Office that can be used in Access applications. Attributes in customizations that end with Mso are items that are included with Office. Using the imageMso attribute, you can specify the name of an image included with Office. As mentioned earlier in the section "Development Tips," the easiest ways to find control or image names is to use the Options dialog box for a given application.
You are not limited to using images from Access. In fact, most of our applications that contain ribbon customizations tend to use images from Word or Outlook! The following XML is an example of using images built into Office. (Figure 11-19 shows the result of this customization.)
<group id="grpImageMso" label="Examples: imageMso attribute">
<button id="btnWeather" imageMso="PictureBrightnessGallery"
label="Weather" size="large"/>
<button id="btnNotes" imageMso="ExchangeFolder"
label="Notes" size="large"/>
<button id="btnSearch" imageMso="ZoomPrintPreviewExcel"
label="Search" size="large"/>
<button id="btnHelp" imageMso="TentativeAcceptInvitation"
label="Help" size="large"/> <separator id="s1"/>
<button id="btnUsers" imageMso="DistributionListSelectMembers"
label="Manage Users..." size="normal"/>
<button id="btnSysHealth" imageMso="OfficeDiagnostics"
label="System Health" size="normal"/>
<button id="btnTechSupport" imageMso="TechnicalSupport"
label="Technical Support" size="normal"/>
</group>
Figure 11-19. The customized interface that results
.jpg "The customized interface that results")
Loading Images from External Files
The imageMso attribute makes it really easy to get started with images in your applications. But, what if you want to use images that you have on the hard drive or want to display images in the Ribbon as a feature of your application? For example, if you have a product catalog that includes images of the product, wouldn't it be cool to put that into the Ribbon for your users as a selection item? Users would immediately be able to make selections based on a visual. This can go a long way toward making applications easy-to-use.
There are basically two ways to load images dynamically. The easiest way is to use the loadImage callback, which is defined in the customUI node of a customization. Alternatively, you can handle the getImage callback for a given control.
Let's take a look at these two methods.
Creating a Global Image Handler
In the root node of a customization, customUI, there is a callback attribute defined called loadImage. This callback is used in conjunction with the image attribute on controls, and is called when the Ribbon asks for an image. Using the loadImage callbacks enables you to define one image handler for the application and specify the name of the image in the image attribute. Let's take a closer look to see how this works.
The gallery control mentioned earlier can also be used to display images. This is very useful for creating features, such as:
Product catalog
Membership photo gallery
Options for a screen layout
Galleries can easily be created using the loadImage callback. Start with a new customization, as follows.
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
loadImage="OnLoadImage">
<ribbon startFromScratch="true">
<tabs>
<tab id="tab1" label="Images Examples">
<group id="grpLoadImage" label="Examples: loadImage">
Next, add the gallery control with images using the image attribute. Notice that we've filled the gallery with sample images from Windows Vista. We've also set the height and width of the items in the gallery using the itemHeight and itemWidth attributes respectively, as follows.
<gallery id="galVista" label="Vista Sample Images"
itemHeight="100" itemWidth="100" size="large"
imageMso="PictureEffectsShadowGallery">
<item id="galImg1" image="Autumn Leaves.jpg"/>
<item id="galImg2" image="Creek.jpg"/>
<item id="galImg3" image="Desert Landscape.jpg"/>
<item id="galImg4" image="Dock.jpg"/>
<item id="galImg5" image="Forest Flowers.jpg"/>
<item id="galImg6" image="Forest.jpg"/>
<item id="galImg7" image="Frangipani Flowers.jpg"/>
<item id="galImg8" image="Garden.jpg"/>
<item id="galImg9" image="Green Sea Turtle.jpg"/>
<item id="galImg10" image="Humpback Whale.jpg"/>
<item id="galImg11" image="Oryx Antelope.jpg"/>
<item id="galImg12" image="Toco Toucan.jpg"/>
<item id="galImg13" image="Tree.jpg"/>
<item id="galImg14" image="Waterfall.jpg"/>
<item id="galImg15" image="Winter Leaves.jpg"/>
</gallery>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
Write the OnLoadImage callback as follows. This routine uses the Environ function in VBA to help retrieve the path of the Sample Pictures folder on Vista. You may need to change this path, as follows, if you are not using Vista.
(Visual Basic for Applications)
Sub OnLoadImage(ImageName As String, ByRef Image)
' get the image path
Dim stPath As String
' this path works on Vista only
stPath = Environ("PUBLIC") & "\Pictures\Sample Pictures\" & ImageName
Set Image = LoadPicture(stPath)
End Sub
When you select the gallery, it should be filled with images, as shown in Figure 11-20.
Figure 11-20. The gallery should be filled with images
.jpg "The gallery should be filled with images")
getImage and getItemImage Callbacks
Instead of writing an image handler in the loadImage callback, many controls also provide a getImage callback that can be used to set the image for the control. This callback is useful when information about the image is stored in a table and you want to include more information about the image besides the file name that is stored in the image attribute.
Because we are working with a gallery control, let's actually set the image for items in the gallery, not for the gallery control itself. To do this, we use the getItemImage callback. This works very similarly to the getImage callback. To use the getItemImage callback, we create a table to store information about images. This table maps the ID of the control in the ribbon customization to the path of the image, along with some additional information.
Create a new table with the following fields. This table stores information about pictures, such as the file name, a friendly name of the image, and camera information that we display in a supertip. Save the table as tblImages.
| Note |
|---|
Remember that if you've opened a database with a customization that includes the startFromScratch attribute set, you need to hold down the Shift key to get back to the design tools. |
Field Name |
Data Type |
Properties |
ControlID |
Text (255) |
Primary Key |
ImageFileName |
Text (255) |
|
CameraMake |
Text (255) |
|
CameraModel |
Text (255) |
|
EXIFVersion |
Text (255) |
| Note |
|---|
This table with some sample data is available for download on the Web site for this book. The images used with the sample are also included. |
We use the sample pictures included with Windows Vista for this example and include some arbitrary information for the other fields for testing. Because the data comes from the table, the following customization is pretty easy:
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="true">
<tabs>
<tab id="tab1" label="Images Examples">
<group id="grpGetImage" label="Example: getImage callback">
<gallery id="galGetImage" label="Vista Images (detailed)"
size="large" imageMso="Camera"
itemHeight="100" itemWidth="100"
getItemCount="OnGetItemCount"
getItemID="OnGetItemID"
getItemImage="OnGetItemImage"
getItemScreentip="OnGetItemScreentip"
getItemSupertip="OnGetItemSupertip"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
You'll notice that there are a couple of additional callbacks we haven't discussed yet. So, let's discuss those for a moment. Screentips are large tooltips that can be displayed for controls in a customization. The screentip portion appears in bold and is used to provide context to the tip. The supertip portion of a screentip is not in bold and can display extra information. As you've probably guessed, we're going to use the screentip to display the name of the image and the supertip to display the detailed information about the image. By using these callbacks, we can create data-driven tooltips for our applications.
To fill a gallery dynamically such as this, we first need to tell the Ribbon how many items are in the gallery. For this, we implement the OnGetItemCount callback. But, before we can do this, we need a Recordset object because we want to read data from the tblImages table. So, define the following variable at the top of a new module:
(Visual Basic for Applications)
Dim m_rs As DAO.Recordset2
Next, add the OnGetItemCount callback. The Ribbon calls this one first, so we open the recordset and return the number of records as the count of items, as follows:
(Visual Basic for Applications)
Sub OnGetItemCount(ctl As IRibbonControl, ByRef Count)
' open the recordset
Set m_rs = CurrentDb().OpenRecordset("tblImages")
' return the count
Count = m_rs.RecordCount
End Sub
Next, add the OnGetScreentip callback to return the file name for the image in bold, as follows.
(Visual Basic for Applications)
Sub OnGetItemScreentip(ctl As IRibbonControl, Index As Integer, ByRef Screentip)
Screentip = m_rs("ImageFileName")
End Sub
Now, add the OnGetSupertip callback. This callback provides the detailed information to the tooltip.
(Visual Basic for Applications)
Sub OnGetItemSupertip(ctl As IRibbonControl, Index As Integer, ByRef Supertip)
Supertip = "Dimensions: " & m_rs("Dimensions") & vbCrLf
Supertip = Supertip & "Camera maker: " & m_rs("CameraMaker") & vbCrLf
Supertip = Supertip & "Camera model: " & m_rs("CameraModel") & vbCrLf
Supertip = Supertip & "EXIF version: " & m_rs("EXIFVersion") & vbCrLf
End Sub
We need to load the actual image. For this, we'll implement the getItemImage callback. In this code, we'll create the full path to the image using the file name in the table and a subdirectory of the folder where the database resides called images. Use the LoadPicture function in VBA to load the image, as follows:
(Visual Basic for Applications)
Sub OnGetItemImage(ctl As IRibbonControl, Index As Integer, ByRef Image)
Dim stPath As String
Set Image = LoadPicture(stPath)
End Sub
Last, we'll set the ID for the item. We aren't doing anything with the ID in this example, but if you were to later handle the onAction callback for items in the gallery you might need the ID. The ID is assigned using the getItemID callback, as follows.
(Visual Basic for Applications)
Sub OnGetItemId(ctl As IRibbonControl, Index As Integer, ByRef ID)
'Debug.Print ctl.ID, Index, "OnGetItemId"
ID = m_rs("ControlID")
m_rs.MoveNext
If (m_rs.EOF) Then
m_rs.Close
Set m_rs = Nothing
End If
End Sub
When you select the gallery and hover over an image, you should see the images with a tooltip, as shown in Figure 11-21.
Figure 11-21. Hover over an image to see the tooltip
.jpg "Hover over an image to see the tooltip")
Loading Images from an Attachment Field
Access 2007 includes a new data type called Attachment that allows you to embed files inside a database. This new data type, which is available only with the new .ACCDB file format, also compresses certain file formats such as bitmaps in a database. Bitmap images inside an attachment field can be used as the source for an image in a ribbon customization in Access. The trick to using these fields is to have a form that is bound to the attachment field in the table. Let's see how this works.
Create the Table
Start by creating a table that contains an attachment field. The table should have the following schema. Save the table as tblAttachments.
Field Name |
Data Type |
ID |
AutoNumber |
Attachments |
Attachment |
After you've created the table, add three bitmap (.bmp images into the first record of the attachment field). This example only handles cases where the attachments are in the first record. Make a note of the file name for each file.
Create the Form
You need a form to contain the attachments from the table because the images in the customization require the attachment control of a form to render the images. Create a new form using the tblAttachmentstable and save it as frmAttachments.
Create the Customization
Now, we need to add the customization. Use the following XML to define the customization. Notice that we've used the tag attribute to store the file name of the image as it appeared when you added it to the attachment field:
<group id="grpAttachmentImages" label="Access Attachment Field">
<button id="btnAttachment1" getImage="OnGetAttachmentImage"
size="large" label="Attachment1" tag="Image1.bmp"/>
<button id="btnAttachment2" getImage="OnGetAttachmentImage"
size="large" label="Attachment2" tag="Image2.bmp"/>
<button id="btnAttachment3" getImage="OnGetAttachmentImage"
size="large" label="Attachment3" tag="Image3.bmp"/>
</group>
Add the Callback
Last, add the OnGetAttachmentImage callback for the customization. This callback is used in the getImage callback for the button, as follows.
(Visual Basic for Applications)
Sub OnGetAttachmentImage(ctl As IRibbonControl, ByRef Image)
' open the attachment form (hidden)
If (Not CurrentProject.AllForms("frmAttachments").IsLoaded) Then
DoCmd.OpenForm "frmAttachments", , , , , acHidden
End If
' bind the image which has the image name in the tag attribute
DoCmd.Close acForm, "frmAttachments"
End Sub
You'll notice that this code opens the frmAttachments form in hidden mode and uses the hidden PictureDisp property of the attachment control to retrieve the image. The name of the image is specified in the Tag property of the IRibbonControl object that you defined earlier. The PictureDisp property returns an IPictureDisp object similarly to the LoadPicture function in VBA.
Moving Functionality into the Ribbon
Now that we've gone through the available controls and programming the Ribbon, it's time to put these pieces to use with some more scenarios.
| Note |
|---|
The code for the samples that follow is contained in the sample file DynamicRibbons.accdb, which is available for download on the Web site for this book. |
The NotInList Event — Ribbon Style
Let's start with a pretty common scenario in Access. The NotInList event is used in combo boxes to add values to the underlying row source for the combo box. We can simulate the same effect using a combo box in a ribbon customization with just a few callbacks.
Let's say that we have a list of categories that is used as a lookup field. To manage the lookup field, we add a combo box to the Ribbon. There are two parts to this example. First, we need to be able to dynamically fill the combo box. Second, we need to be able to update it. To do this, we implement the following callbacks:
Fill — getItemCount, getItemLabel
Update — onChange
Add the following XML for the customization to the USysRibbons table:
<customUI xmlns=http://schemas.microsoft.com/office/2006/01/customui
onLoad="OnRibbonLoad">
<ribbon startFromScratch="true">
<tabs>
<tab id="tabNotInList" label="NotInList Example">
<group id="grpNotInList" label="NotInList Example">
<comboBox id="cboTest" label="Categories"
getItemCount="OnGetItemCount"
getItemLabel="OnGetItemLabel"
onChange="OnNotInListRibbon"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
We'll need a recordset to fill the combo box. Add the following declaration to the top of a new module:
(Visual Basic for Applications)
Private m_rs As DAO.Recordset
The first callback that the Ribbon asks for is the getItemCount callback, so let's start there. The getItemCount callback is used to tell the combo box how many items are in the list. To fill the list, we'll use a recordset that points to the Categories table (from previous versions of Northwind). As with some of the other callbacks that we've seen, we return the number of items in an argument that is defined in the callback, as follows:
(Visual Basic for Applications)
Public Sub OnGetItemCount(ctl As IRibbonControl, ByRef Count)
' open the recordset
Dim stSQL As String
Set m_rs = CurrentDb.OpenRecordset(stSQL)
m_rs.MoveLast
m_rs.MoveFirst
' return the count
Count = m_rs.RecordCount
End Sub
Next, we need to fill the items in the combo box using the getItemLabel callback. Having set the number of items in the combo box, the getItemLabel callback is called for each item. As a result, we need to keep track of where we are in the recordset.
(Visual Basic for Applications)
Public Sub OnGetItemLabel(ctl As IRibbonControl, index As Integer, ByRef Label)
' set the label
Label = m_rs("CategoryName")
m_rs.MoveNext
If (m_rs.EOF) Then
m_rs.Close
Set m_rs = Nothing
End If
End Sub
The callback starts by returning the label and moving to the next record in the recordset. This advances the cursor in the recordset and provides the next category to the combo box. When we reach end-of-file (EOF) in the recordset, it's safe to do cleanup. That's all that is required to fill a combo box in a ribbon customization using items in a table!
| Note |
|---|
This is a relatively straightforward scenario. There may be times when you need to handle selection of items in the list and want to assign ID values to each item in the list. To dynamically assign IDs, you should handle the getItemID callback. |
All that's remaining now is the actual NotInList implementation. To do this, we'll handle the onChange callback for the combo box. This callback is fired when the text inside the combo box is changed. The signature for the callback includes an argument called Text that represents the data that was entered in the combo box. This is analogous to the NewData argument to the NotInListevent in Access.
Remember that we closed and destroyed the recordset earlier so we need to re-open it. This time, however, we'll open it with a filter for the item that was entered. If there are no matching records, we add it to the recordset. As you might imagine, we then invalidate the combo box to refresh the items in the list that reflect the new record that was added to the Categories table, as follows.
(Visual Basic for Applications)
Public Sub OnNotInListRibbon(ctl As IRibbonControl, Text As String)
' open the recordset
Dim stSQL As String
stSQL = "SELECT * FROM tblCategories WHERE CategoryName = '" & Text & "'"
Set m_rs = CurrentDb().OpenRecordset(stSQL)
If (m_rs.BOF And m_rs.EOF) Then
' add the item
m_rs.AddNew
m_rs("CategoryName") = Text
m_rs.Update
' invalidate the combo box to refresh
gobjRibbon.InvalidateControl "cboTest"
End If
' close
m_rs.Close
Set m_rs = Nothing
End Sub
Form Navigation
Have you ever written your own navigation form because you wanted a different look than the navigation buttons provided by Access? If so, this next example is for you. Here's how you can move form navigation into the Ribbon.
We've started with a form based on the Customers table from Northwind again, but any form should work. You need to add code to the form for some of the requirements, which are listed here.
Move between first, last, previous, and next records
Populate a label control with the current position in the form
Jump to a particular record in the form
Navigation controls should be kept up to date when you navigate through the form directly without using the controls
Disable the navigation buttons in the ribbon customization depending on where we are in the form
Let's get started with the XML for the customization. Because we need to refresh a label and buttons, we need to invalidate controls. This means we need to handle the onLoad callback, as follows:
<customUI xmlns=http://schemas.microsoft.com/office/2006/01/customui
onLoad="OnRibbonLoad">
Next, set up the ribbon, the tab, and the group, as follows:
<ribbon startFromScratch="true">
<tabs>
<tab id="tabNav" label="Navigation">
<group id="grpNav" label="Navigation">
Add a button to open the sample form. This requires the OnOpenFormcallback, as defined earlier. As you can see, our sample form is called frmCustomers. This is simply a shortcut to help get into the sample.
<button id="btnSample" label="Open Sample Form"
imageMso="AccessFormModalDialog"
tag="frmCustomers" onAction="OnOpenForm"/>
Time to start adding navigation controls. We want a layout that is linear rather than vertical, so we're using a box control to define a horizontal layout. Start with the navigation label inside the box. To set the text for the label, we handle the getLabel callback, as follows.
<box id="boxNavLbl" boxStyle="horizontal">
<labelControl id="lblNav" getLabel="OnGetLabel"/>
</box>
Next, we want to lay out the buttons and edit box in a horizontal layout, so we create another box node. The buttons should appear to be grouped together, so we're using a buttonGroup control to define a particular appearance.
<box id="boxNav" boxStyle="horizontal">
<buttonGroup id="bg1">
Time to add the individual buttons. Notice that we are using built-in images specified with the imageMso attribute. Each button calls the same callback named OnNavigateRecord. To determine whether a control should be enabled, we handle the getEnabled callback, as follows:
<button id="btnNavFirst" imageMso="MailMergeGoToFirstRecord"
onAction="OnNavigateRecord"
getEnabled="OnGetNavEnabled"/>
<button id="btnNavPrev" imageMso="MailMergeGoToPreviousRecord"
onAction="OnNavigateRecord"
getEnabled="OnGetNavEnabled"/>
</buttonGroup>
Add the edit box for the navigation. We're handling the onChange callback so that the user can enter a number and jump to a particular record. We're handling the getText callback to put an empty string into the edit box for invalid data. The editBox control also defines an attribute called sizeString that is used to define the width of the edit box. The width of the string in this attribute determines the width of the control.
<editBox id="txtJump" sizeString="000000"
onChange="OnChangeRecord"
getText="OnGetText"/>
Finish the customization by adding two more buttons inside a buttonGroup, and close the nodes for the ribbon, as follows.
<buttonGroup id="bg2">
<button id="btnNavNext" imageMso="MailMergeGoToNextRecord"
onAction="OnNavigateRecord"
getEnabled="OnGetNavEnabled"/>
<button id="btnNavLast" imageMso="MailMergeGotToLastRecord"
onAction="OnNavigateRecord"
getEnabled="OnGetNavEnabled"/>
</buttonGroup>
</box>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
When you put this into a USysRibbons table, the customization should look something like the Ribbon shown in Figure 11-22.
Figure 11-22. The customized navigation control in the Ribbon
.jpg "The customized navigation control in the Ribbon")
To enable the functionality, we need to start writing the callbacks. Create a new module called basFormNavigationCallbacks for this code. Let's start by writing the OnGetText callback, as follows. This is used to simply write an empty string into the edit box for invalid entry cases.
(Visual Basic for Applications)
Public Sub OnGetText(ctl As IRibbonControl, ByRef Text)
If (ctl.Id = "txtJump") Then
Text = ""
End If
End Sub
Next, add the OnGetLabel callback. This routine is called to set the text for the navigation label. The following code sets the text for the label. If there are no forms open, we set the label to "No active form." When there is an open form, we set the label to something such as "Record 1 of 10." If the active form is filtered, we append the string "(Filtered)."
(Visual Basic for Applications)
Public Sub OnGetLabel(ctl As IRibbonControl, ByRef Label) Dim f As Form
If (ctl.Id = "lblNav") Then
If (Forms.Count = 0) Then
Label = "No active form"
Else
Set f = Screen.ActiveForm
Label = "Record " & f.CurrentRecord & " of " & _
f.RecordsetClone.RecordCount
If (f.FilterOn) Then
Label = Label & " (Filtered)"
End If
End If
End If
End Sub
Next, add the OnNavigateRecord callback, as follows. This routine is called by each of the navigation buttons. To do navigation, we're simply calling DoCmd.GotoRecord and passing the appropriate value based on the button that was clicked.
(Visual Basic for Applications)
Public Sub OnNavigateRecord(ctl As IRibbonControl)
Dim lRecord As AcRecord
Select Case ctl.Id
Case "btnNavFirst": lRecord = acFirst
Case "btnNavPrev": lRecord = acPrevious
Case "btnNavNext": lRecord = acNext
Case "btnNavLast": lRecord = acLast
End Select
' do the navigation
DoCmd.GoToRecord , , lRecord
If (Not gobjRibbon Is Nothing) Then
' invalidate the nav label
gobjRibbon.InvalidateControl "lblNav"
' invalidate the button to enable/disable
gobjRibbon.InvalidateControl ctl.Id
End If
End Sub
Because we've moved records, we also need to invalidate the navigation label. We do this by calling InvalidateControlfor for lblNav.
Next, add the OnChangeRecord callback that is fired when the user enters a value in the edit box. To prevent the user from doing something invalid, there are some additional checks in this code. We first ensure that the user entered a number. If they didn't, we alert the user and invalidate the edit box. This, in turn, calls the OnGetText callback and writes an empty string.
To move the record, we're manipulating the underlying Recordset for the form. Again, to prevent the user from doing something invalid (much as Access itself does), we've added some checks. If you enter a value that is greater than the number of records or less than zero, we alert the user and invalidate the edit box.
(Visual Basic for Applications)
Public Sub OnChangeRecord(ctl As IRibbonControl, Text)
If (Not IsNumeric(Text)) Then
MsgBox "Please enter a number", vbExclamation, "Cannot Move Record"
gobjRibbon.InvalidateControl "txtJump"
Else
' move to the specified record
With Screen.ActiveForm.Recordset
.MoveFirst
If (CLng(Text) > .RecordCount Or CLng(Text) < 1) Then
MsgBox "Cannot move to specified record", vbInformation
gobjRibbon.InvalidateControl "txtJump"
Else
.Move CLng(Text) - 1
End If
End With
End If
End Sub
Nice job so far. One callback left — OnGetNavEnabled. Remember that we want to disable controls when they are not available. In other words, if you're on the first record, you shouldn't be able to move to the first or previous records. So why not disable the controls? The following code sets the enabled attribute, depending on the selected control and the CurrentRecord property of the active form.
(Visual Basic for Applications)
Public Sub OnGetNavEnabled(ctl As IRibbonControl, ByRef Enabled)
If (Forms.Count > 0) Then
Set f = Screen.ActiveForm
Select Case ctl.Id
Case "btnNavFirst"
Enabled = (f.CurrentRecord > 1)
Case "btnNavPrev"
Enabled = (f.CurrentRecord > 1)
Case "btnNavNext"
Enabled = (f.CurrentRecord < f.Recordset.RecordCount)
Case "btnNavLast"
Enabled = (f.CurrentRecord < f.Recordset.RecordCount)
End Select
End If
End Sub
Great! Okay, hang on a minute — we're not quite done. We need to add some code to our form to keep the controls in sync if you navigate using the form instead of the controls. Keeping the controls in sync will mimic the behavior that Access provides in the navigation buttons for a form. To do this, add the following helper routine to the code behind the form. This simply invalidates the navigation controls.
(Visual Basic for Applications)
Private Sub InvalidateNavControls()
With gobjRibbon
.InvalidateControl "lblNav"
.InvalidateControl "btnNavFirst"
.InvalidateControl "btnNavPrev"
.InvalidateControl "btnNavNext"
.InvalidateControl "btnNavLast"
End With
End Sub
Last, we need to call this code as you move from record to record, and also when the form is closed. When the form is closed, the label resets to "No active form."
(Visual Basic for Applications)
Private Sub Form_Close()
' invalidate
InvalidateNavControls
End Sub
Private Sub Form_Current()
' invalidate
InvalidateNavControls
End Sub
To test the navigation, open the sample form and move from record to record. Also, try to use the first and last buttons and jump to a specific record.
| Note |
|---|
In this sample, we handled the getEnabled callback to disable a control by returning a Boolean value that indicates whether a control should be enabled. The general style guideline from Microsoft is that controls in the Ribbon should be disabled instead of hidden. If you need to hide controls, instead of disabling them, use the getVisible callback. |
Managing Filters Using a Dynamic Menu
Let's say that you enable your users to save filters of a particular form so that they can reuse the filter later. You store information about the saved filters in a table. This information includes a friendly name of the filter and a description. Using a dynamic menu is an interesting way to display this information to the user because it permits changes from the user. To be truly dynamic, the Ribbon defines an attribute on the dynamicMenu node called invalidateContentOnDrop. Set this attribute to true to call the getContent callback every time the user drops down the menu.
To create the filter example, start by creating a table to save filters. The table should have the following fields defined. Save the table as tblSavedFilters when you're done. Create a form based on this table that the user can use to manage filters later on. Name the form frmSavedFilters.
Field Name |
Data Type |
Properties |
FilterName |
Text (255) |
Primary Key |
FilterDescription |
Text (255) |
|
FilterString |
Text (255) |
Next, we define the XML for the customization. This sample was created using the Northwind sample database from previous versions of Access but it should work for any form because the filter is being saved as it relates to the form that is currently open.
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="true">
<tabs>
<tab id="tabSavedFilters" label="Saved Filters">
<group id="grpFilter" label="Filters">
<dynamicMenu id="dmnu1" label="Saved Filters"
getContent="OnGetContent"
invalidateContentOnDrop="true"
size="normal" imageMso="Filter"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
Next, add the getContent callback, starting with the declarations.
(Visual Basic for Applications)
Public Sub OnGetContent(ctl As IRibbonControl, ByRef content)
Dim stMenu As String ' XML for the menu being filled
Dim stID As String ' ID for buttons in the menu
Dim rs As DAO.Recordset2 ' recordset containing saved filters
Dim stSQL As String
Open a recordset against the saved filter table. We're sorting the recordset based on the FilterString field in descending order so that when the menu is created the FilterString field is sorted ascending. As an alternative to opening the recordset sorted in descending order, you could iterate backwards through the recordset.
' open the recordset
stSQL = "SELECT * FROM tblSavedFilters ORDER BY FilterString DESC"
Set rs = CurrentDb.OpenRecordset(stSQL)
We need to start building the menu. To do this, we define XML that will be used in the customization for the dynamic menu. Start with the menu node. Notice that we need to include the customUInamespace definition. We've also added a menu separator for aesthetics.
' create the menu node
stMenu = "<menu xmlns='http://schemas.microsoft.com/office/2006/01/customui'
itemSize='large'>"
stMenu = stMenu & "<menuSeparator id='msMyFilters1 ' title='Filters '/>"
It's time to fill the menu with the list of saved filters. To do this, we walk through the recordset. We're going to fill the menu with buttons so we've written a helper function called GetButtonXml to generate the XML for a button node given some parameters. This function appears in a moment.
One of the arguments to the GetButtonXml helper function is called strAction. This argument is used as the onAction callback for the button in the menu. We're going to create a callback called DoApplyFilter that applies the selected filter based on the user selection.
(Visual Basic for Applications)
' build the XML for the menu
While (Not rs.EOF)
' get the ID for the button by replacing any spaces with empty spaces
stID = Replace(rs("FilterName"), " ", "")
' Append the button node
stMenu = stMenu & GetButtonXml(stID, _
rs("FilterName"), _
"DoApplyFilter", _
rs("FilterDescription"), _
rs("FilterName"))
rs.MoveNext
Wend
Before we close the menu node, we'd like to add a couple of static buttons to our menu to manage filters. The first is a Save button that has its own callback. The second is a button that opens the frmSavedFilters form that you created earlier. Add the buttons as follows.
(Visual Basic for Applications)
' add buttons to manage filters
stMenu = stMenu & "<menuSeparator id='msMyFilters2' title='Manage Filters'/>"
stMenu = stMenu & "<button id='btnSaveFilter' label='Save Filter' " & _
"onAction='OnSaveFilter' imageMso='FileSave'/>"
stMenu = stMenu & "<button id='btnClearFilter' label='Clear Filter' " & _
"onAction='OnClearFilter'/>"
stMenu = stMenu & "<button id='btnFilters' label='Manage...' " & _
"tag='frmSavedFilters' " & _ "onAction='OnOpenForm'/>"
Okay, now we close the menu node and the recordset.
' close the menu node
stMenu = stMenu & "</menu>"
' cleanup
rs.Close
Set rs = Nothing
And last, of course, we return the content to the customization and exit the routine.
' return the XML for the menu
content = stMenu
End Sub
So far so good. We have some helper functions and additional callbacks to write, so let's add those starting with GetButtonXml.
(Visual Basic for Applications)
Private Function GetButtonXml(strID As String, _
strLabel As String, _
strAction As String, _
Optional strDescription As String = "", _
Optional strTag As String = "")
' builds the XML for a button
GetButtonXml = "<button id='" & strID & "'" & _
" label='" & strLabel & "'" & _
" onAction='" & strAction & "'"
' add the description attribute
If (Len(strDescription) > 0) Then
GetButtonXml = GetButtonXml & _
" description='" & strDescription & "'"
' add the tag attribute If Len(strTag) > 0 Then
GetButtonXml = GetButtonXml & " tag='" & strTag & "'"
End If
' close the node
GetButtonXml = GetButtonXml & "/>"
End Function
As you can see, this function accepts arguments for the id, label, onAction, description, and tag attributes of a button node in a customization.
Next, add the OnSaveFilter callback, as follows. This procedure is called when the user clicks the Save Filter button in the dynamic menu.
(Visual Basic for Applications)
Public Sub OnSaveFilter(ctl As IRibbonControl)
Dim stFilter As String
' get the filter for the current filter
On Error GoTo SaveFilterErrors
stFilter = Screen.ActiveForm.Filter
' make sure there is a filter
If (Len(stFilter) = 0) Then
MsgBox "Filter has not been set for the ActiveForm, cannot save.", _
vbExclamation, "Cannot Save Filter"
Exit Sub
End If
' open the form to save the filter
DoCmd.OpenForm "frmSavedFilters", , , , acFormAdd, , stFilter
Exit Sub
SaveFilterErrors:
If (Err = 2475) Then
MsgBox "There is no open form", vbExclamation
Exit Sub
Else
Stop
End If
End Sub
For this callback, we're making sure that a form is open and if so, getting its filter using the Filter property of the Form object. If the Filter property is not empty, we open the frmSavedFilters form in data entry mode and pass the filter to the form in its OpenArgs.
Next, add the OnClearFilter callback. This procedure is called when the user clicks the Clear Filter button in the dynamic menu.
(Visual Basic for Applications)
Public Sub OnClearFilter(ctl As IRibbonControl)
Screen.ActiveForm.Filter = ""
Screen.ActiveForm.FilterOn = False
End Sub
Next, we write the OnOpenForm callback.
(Visual Basic for Applications)
Public Sub OnOpenForm(ctl As IRibbonControl)
DoCmd.OpenForm ctl.Tag
End Sub
And last, the DoApplyFilter callback. When we created the button for the saved filter in the menu, we passed in the name of the filter in the FilterName field to the tag attribute of the button. This is used to query the tblSavedFilters table to ask for the filter string.
(Visual Basic for Applications)
Public Sub DoApplyFilter(ctl As IRibbonControl)
Dim stSQL As String
Dim rs As DAO.Recordset2
' build the SQL statement
stSQL = "SELECT FilterString FROM tblSavedFilters "
stSQL = stSQL & "WHERE FilterName = '" & ctl.Tag & "'"
' open the recordset
Set rs = CurrentDb.OpenRecordset(stSQL)
' set the filter
Screen.ActiveForm.Filter = rs("FilterString")
Screen.ActiveForm.FilterOn = True
' cleanup
rs.Close
Set rs = Nothing
End Sub
We need to add one more piece of code — to pick up the new filter in the frmSavedFilters form. Remember that we pass it a new filter via its OpenArgs property. Add the following code to the Form_Load event of frmSavedFilters.
(Visual Basic for Applications)
Private Sub Form_Load()
' check openargs for a filter string
If (Me.DataEntry And Not IsNull(Me.OpenArgs)) Then
Me.FilterString = Me.OpenArgs
End If
End Sub
To test this example, open a form and apply a filter. Then, click on the Save Filter button in the menu. The frmSavedFiltersform should open where you can assign a filter name and description. Now, when you drop down the menu again, the saved filter should be listed, as shown in Figure 11-23.
Figure 11-23. The saved filter appears in the menu
.jpg "The saved filter appears in the menu")
Creating a Split Button That Sticks
Suppose you want to create an application launcher for your Access application. The application launcher gives the user a list of applications that they can launch easily from within your application. Here are the requirements:
The launcher includes buttons for Word, Excel, Outlook, Calculator and Notepad.
The launcher should stick — that is, the last application launched should stick in the button in the customization.
To meet this last requirement, we use a split button. The split button control contains a button and a menu of choices. When an application is launched, we update the button to reflect the last application that was launched. Start by setting up the customization, as follows:
<customUI xmlns=http://schemas.microsoft.com/office/2006/01/customui
onLoad="OnRibbonLoad">
<ribbon startFromScratch="true">
<tabs>
<tab id="tabLauncher" label="Application Launcher">
<group id="grpLauncher" label="Launcher">
Add the split button. The splitButton control contains a button control that is used as the actual button that is clicked in the Ribbon. This button control should reflect the last application that was launched so we handle the getLabel and getImage callbacks to update the label and the image respectively.
<splitButton id="sbLauncher">
<button id="btnLauncher"
getLabel="OnGetAppLabel" getImage="OnGetImage"
onAction="OnLaunchApplication"/>
Now, let's add the menu and close the customization. This is the list of applications that can be launched from the customization. You'll notice that we are using images included with Office in the imageMso attribute. We're also storing the name of this image in the tag attribute as extra data. More on that in a moment.
<menu id="mnuLauncher" label="Launcher" itemSize="normal">
<menuSeparator id="ms1" title="Office Applications"/>
<button id="btnWord" label="Word"
imageMso="FileSaveAsWordDotx"
onAction="OnLaunchApplication"
tag="FileSaveAsWordDotx"/>
<button id="btnExcel" label="Excel"
imageMso="MicrosoftExcel"
onAction="OnLaunchApplication"
tag="MicrosoftExcel"/>
<button id="btnOutlook" label="Outlook"
imageMso="MicrosoftOutlook"
onAction="OnLaunchApplication"
tag="MicrosoftOutlook"/>
<menuSeparator id="ms2" title="Utilities"/>
<button id="btnCalc" label="Calculator"
imageMso="Calculator"
onAction="OnLaunchApplication"
tag="Calculator"/>
</menu>
</splitButton>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
The customization is complete, so let's add the callbacks. First, the OnGetAppLabel callback. This callback is defined in the launcher button that is updated when a selection is made. Because we're going to update labels and images, we need some private variables in the module to store this information between callbacks. Add the following code to the top of a new module called basSplitButtonCallbacks.
(Visual Basic for Applications)
Private mstrLabel As String
Private mstrImage As String
Add the OnGetAppLabel callback. If the mstrLabel variable has not been set, we default to Calculator. As an alternative, you could also retrieve this from a table as a setting.
(Visual Basic for Applications)
Sub OnGetAppLabel(ctl As IRibbonControl, ByRef Label)
' default to Calculator
If (mstrLabel = "") Then
mstrLabel = "Calculator"
Label = mstrLabel
End Sub
Add the OnGetImage callback. This callback updates the image in the button to reflect the last application launched. When we defined our customization, we used images included with Office in the imageMso attribute. However, you may have noticed that there is a getImage callback, but not a getImageMso callback. So how can we load a built-in image dynamically? Well, it turns out that while there isn't a getImageMso callback, there is a GetImageMso method! And, this method was added on the CommandBars object of all places. This method returns the image for a named image that is included with Office as you would define in the imageMso attribute. So, we can simply use this method to retrieve the image from Office.
(Visual Basic for Applications)
Sub OnGetImage(ctl As IRibbonControl, ByRef Image)
' default to calculator
If (mstrImage = "") Then
mstrImage = "Calculator"
' set the image
Set Image = CommandBars.GetImageMso(mstrImage, 16, 16)
End Sub
Okay, the last callback we need is OnLaunchApplication, which is called when the user clicks any of the buttons. Remember from our customization that the name of the launcher button is called btnLauncher. The buttons in the menu have names specific to their applications. So, clicking the launcher button in the Ribbon opens the application that was most recently launched. However, you need to set that value in mstrLabel, which is retrieved from the other buttons. For demo purposes, we are simply showing a message box. You could, however, also store the path to an application to launch from a table based on the button that was clicked.
Earlier we also mentioned that we were storing the name of the imageMso attribute as extra data in the tag attribute of the button. We use the tag value now to set the name of the image in mstrImage:
(Visual Basic for Applications)
Sub OnLaunchApplication(ctl As IRibbonControl)
' set the label and the image and invalidate
If (ctl.ID <> "btnLauncher") Then
mstrLabel = Mid(ctl.ID, 4)
mstrImage = ctl.Tag
gobjRibbon.InvalidateControl "btnLauncher"
End If
' launch the application
MsgBox "You are launching " & mstrLabel
End Sub
When you click the button, you should get a message that says Calculator is being launched. If you select an item in the menu, say Outlook, you should get a message that says Outlook is being launched and the button should update to the Outlook icon.
Other Ribbon Modifications
So far, we've taken an in depth look at creating ribbon customizations using controls such as gallery and button. However, there are other places in the Ribbon that allow for customization. Let's take a look at two of them — the Office menu and built-in commands.
Modifying the Office Menu
You've probably noticed that when you use the startFromScratch attribute, some of the controls in the Office menu are hidden, but not all of them. Luckily, you can modify the Office menu if you'd like by modifying controls in the officeMenu node of a customization. You can even add your own controls. The following XML defines a customization that adds a button and a menu to the Office menu:
<customUI xmlns=http://schemas.microsoft.com/office/2006/01/customui
loadImage="OnLoadImage">
<ribbon startFromScratch="false">
<officeMenu>
<!-- About button -->
<button id="btnAbout" label="About My Application..."
insertBeforeMso="FileNewDatabase" imageMso="Info"
onAction="=MsgBox('My Application - copyright 2007')"/>
<!-- Menu -->
<menu id="mnu1" label="Application Launcher"
insertBeforeMso="FileNewDatabase">
<checkBox id="chk1" label="Disable Launcher"/>
<menuSeparator id="ms1" title="Office Applications"/>
<button id="btnWord" label="Project" imageMso="MicrosoftProject"/>
<button id="btnExcel" label="Excel" imageMso="MicrosoftExcel"/>
<button id="btnOutlook" label="Outlook" imageMso="MicrosoftOutlook"/>
<menuSeparator id="ms2" title="Utilities"/>
<button id="btnCalc" label="Calculator" imageMso="Calculator"/>
<button id="btnNotepad" label="Notepad" imageMso="ReviewEditComment"/>
<button id="btnPhotos" label="My Pictures" imageMso="Camera"/>
</menu>
</officeMenu>
</ribbon>
</customUI>
The modified Office menu is shown in Figure 11-24.
Figure 11-24. The modified Office menu
.jpg "The modified Office menu")
Overriding Commands and Repurposing Controls
The Access Ribbon provides a wealth of built-in functionality. For example, there are buttons in the Office menu to create new databases or open an existing database. Other commands are available in the Ribbon itself, such as the new Encrypt with Password button. With all of these commands, there may be times when you want to include behavior or controls that Access provides in your applications. There may be other times when you want to override the default behavior of a control that Access provides. This is called repurposing a control. For example, consider a basic Contacts form in a contact tracking application. If you are deploying this application using the Access runtime you will likely want to create a ribbon customization for the application so users can interact with it. To prevent from implementing your own functionality for filtering or finding records, you can include the groups that are defined by Access, which provides these capabilities.
To include or modify existing controls, you need the idMso attribute. For commands that are not in the Office menu, use the commands node under the customUI root node of the ribbon customization, as shown in the code that follows. For instance, say that we want to disable the New button in the Office menu so that users cannot create new databases when they are using your application. The following customization will do this.
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<commands>
<command idMso="FileNewDatabase" enabled="false"/>
</commands>
</customUI>
Disabling controls is one way to change functionality, but the Ribbon also enables you to write your own code when a built-in control is clicked. The signature for callback routines changes when you repurpose a built-in control. Let's say that you wanted to enable users to encrypt their databases by adding a database password. Access provides this functionality using the Encrypt with Password button for the ACCDB file format so you don't have to create this yourself. However, Access gives an error when the database is not opened exclusively. Thus, we want to replace the Access error message. So, let's repurpose the control. Again, start with the customization, as follows.
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<commands>
<command idMso="SetDatabasePassword" onAction="OnSetPassword"/>
</commands>
</customUI>
To repurpose an existing control, specify an onAction callback with an idMso control. Now, add the OnSetPassword callback. Notice that the onAction callback includes an extra parameter called CancelDefault.
(Visual Basic for Applications)
Public Sub OnSetPassword(control As IRibbonControl, ByRef CancelDefault)
If (CurrentProject.Connection.Properties!Mode = 12) Then
CancelDefault = False
Else MsgBox "You must open the database exclusively to set the password.", _
vbExclamation, "Cannot Set Password"
CancelDefault = True
End If
End Sub
We are using the Mode property of the ADO Connection object for the database that is currently open. When the value of this property is 12, the database is open exclusively. We set the CancelDefault argument to False to let the built-in command run. When the property is not 12, we display a message and cancel the built-in command by setting the CancelDefault argument to True.
Now, if you click the Encrypt with Password button on the Database Tools tab in the Access Ribbon, you should receive the message box when the database is not opened exclusively.
Summary
The Ribbon, introduced in Office 2007, will undoubtedly change the way we look at user interface development in the future. It creates opportunities for us as developers to put the things in front of our users that really matter and help them do their jobs effectively. Sure, the model has changed, but we feel this model creates enough new opportunities for development and once you have learned them you might even be able to add new skill sets under your belt such as XML!
Here are some of the key points in this chapter:
The programming model for the Ribbon has changed. This will likely have an effect on how we perform user interface development.
There are many different controls that can be used to create rich user experiences.
Many resources are available for Ribbon development in Office 2007.
Using callback functions, you can provide the same functionality for several Access events in a ribbon customization.
Programming the Ribbon is different, but fun!
In the next chapter, we discuss configuration and extensibility, and go into the details of creating Access applications that can be configured by users, and even localized!
Additional Resources
For more information, see the following resources: