Chapter 24: The Finishing Touches
You’re in the home stretch. You have almost all the forms and reports required for the tasks you want to implement in your application, but you need some additional forms to make it easier to navigate to different tasks. To add a professional touch, you should design a custom Ribbon for your main navigation forms, another custom Ribbon for most data entry forms, and perhaps one for reports. You should take advantage of built-in tools to check the efficiency of your design, and you should make sure that none of your forms and reports allow Layout view. Finally, you need to set the startup properties of your database to let Microsoft Office Access 2007 know how to get your application rolling, and you need to perform a final compile of your Visual Basic code to achieve maximum performance.
| Note | |
Creating Custom Ribbons
When your application is running, the user probably won’t want or need some of the Office Access 2007 design features. However, you might want to provide some additional buttons on your form Ribbon so that the user has direct access to commands such as Save Record and Refresh All. For example, open the Conrad Systems Contacts sample database (Contacts.accdb), open the frmContactsPlain form (which uses the built-in Ribbon), and then open the frmContacts form. As you click each form window, Access changes the Ribbon. You can see some useful differences between the two Ribbons, as shown in Figure 24–1.
Figure 24–1: The standard Ribbon (top) displays many commands and tabs your end users won’t need, compared to the custom form Ribbon (bottom) from the Conrad Systems Contacts sample database.
Buttons, groups, and tabs the user won’t need (such as the buttons in the Views and Windows groups) aren’t available on the custom Ribbon. Also, Access disables all the buttons on the Quick Access Toolbar for the frmContacts form, such as Undo, Save, and Quick Print. (None of the forms in the Conrad Systems Contacts database are designed to be printed.) However, the custom Ribbon does have a Close Form button added at the left end of the Record Navigation tab, and we provided a custom Undo command because Undo is no longer available on the Quick Access Toolbar. In the Conrad Systems Contacts application, all forms (except frmContactsPlain and a few other example forms) have their Ribbon Name properties set to use the custom Ribbon.
The same is true of the built-in tabs. For example, you don’t want your users to be able to create new database objects using the buttons on the Create tab or to be able to use the tools available on the Database Tools tab. For most forms, you also don’t want the user to be able to switch to PivotTable or PivotChart view using the View button. The following sections show you how to build a custom main Ribbon and custom Ribbons for forms and reports.
Loading Ribbon XML
In Chapter 23, “Using XML,” you learned the basic structure of Ribbon XML and how to create a USysRibbons table to store the XML for each of your custom Ribbons. You learned that Access searches for this table during startup and that if it finds this table (and correct fields within the table), Access loads these custom Ribbons into memory. In the Conrad Systems Contacts (Contacts.accdb) sample database, we created three custom Ribbons for the application in the USysRibbons table-rbnCSD, rbnForms, and rbnPrintPreview.
After you define custom Ribbons in the USysRibbons table, you can specify that Access load a specific custom Ribbon each time you open the database. To accomplish this, click the Microsoft Office Button, click Access Options, and then click the Current Database category. In the Ribbon And Toolbar Options section, click the arrow on the Ribbon Name option, and then select your custom Ribbon from the list of loaded Ribbons, as shown in Figure 24–2. (Note that you might need to close and reopen the database to see any new Ribbon you just created appear in the list.) Click OK to save your changes, and close the Access Options dialog box. The next time you open your database, Access applies that custom Ribbon.
Figure 24–2: In the Current Database category in the Access Options dialog box, you can select a specific custom Ribbon to load each time you open the database.
| Note | To prevent Access from automatically loading any custom Ribbons during the startup procedure, press and hold the Shift key when you open the database. |
When you create a USysRibbons table, Access takes care of the work of loading your custom Ribbons. You can also load custom Ribbons into your application by using the LoadCustomUI method. When you dynamically load your Ribbon customization using the LoadCustomUI method, you can store your XML in a table with a different name, in a different database, or in a Visual Basic module.
Syntax
Application.LoadCustomUI(CustomUIName, CustomUIXML)
Notes
CustomUIName is a string variable or literal containing the unique name of the custom Ribbon to be associated with this XML, and CustomUIXML is a string variable or literal that contains the well-formed XML that defines your custom Ribbon.
If you want to dynamically load custom Ribbons, you need to call the LoadCustomUI method each time you open the database. In the Conrad Systems Contacts project file (Contacts.adp), we use this method to load the same Ribbons you see in the Conrad Systems Contacts database (Contacts.accdb). Close Contacts.accdb if you still have it open, and then open the Contacts.adp file. After you establish a connection to your SQL server, click OK in the opening message box. Next, click the menu at the top of the Navigation Pane, click Object Type under Navigate To Category, and then click Tables under Filter By Group to display a list of tables available in this project file. If you open the ztblRibbons table in Datasheet view, you’ll notice we have the same custom Ribbons in this table-rbnForms, rbnPrintPreview, and rbnCSD-as we have in the USysRibbons table in the Contacts.accdb sample database.
| Inside Out-Loading Ribbons into Access Data Projects | |
Close the ztblRibbons table, and let’s examine the Visual Basic code we use to load these custom Ribbons. Click the menu at the top of the Navigation Pane, click Object Type under Navigate To Category, and then click Modules under Filter By Group to display a list of Visual Basic modules available in this project file. Right-click modRibbonCallbacks in the Navigation Pane, and click Design View on the shortcut menu to open the Visual Basic Editor. The first function in this module-LoadRibbons-and the declarations above it use the LoadCustomUI method as follows:
' This serves as a cached copy of the RibbonUI. ' We can use this to then invalidate the Ribbon and refresh the controls Public gobjRibbon1 As IRibbonUI Public gobjRibbon2 As IRibbonUI Public Function LoadRibbons() As Integer ' Code called by frmCopyright and frmSplash to verify custom Ribbon load Dim rst As New ADODB.Recordset ' Set an error trap On Error GoTo LoadRibbon_Err ' Do nothing if gobjRibbon is set If Not (gobjRibbon1 Is Nothing) Then ' Set OK return LoadRibbons = True Else ' Try to load - open recordset on Ribbons rst.Open "SELECT * FROM ztblRibbons", CurrentProject.Connection, _ adOpenKeyset, adLockOptimistic Do Until rst.EOF Application.LoadCustomUI rst!RibbonName, rst!RibbonXML rst.MoveNext Loop ' Close out rst.Close Set rst = Nothing LoadRibbons = True End If LoadRibbon_Exit: Exit Function LoadRibbon_Err: ' Silently log error ErrorLog "LoadRibbons", Err, Error LoadRibbons = False Resume LoadRibbon_Exit End Function
The first line of code in the LoadRibbons function creates a new ADO recordset that the code uses to fetch the records from the custom Ribbon definition table. Next, the code instructs Access to go to the LoadRibbon_Err line if any errors occur. The If statement checks to see whether Access has already loaded customization by verifying whether a cached copy of the RibbonUI has been set. If the main custom Ribbon has been loaded, Is Nothing returns a value of False to indicate Access already has loaded the Ribbon. If the custom Ribbon isn’t loaded, the code following the Else line executes. To load the custom Ribbons, the code opens an ADO recordset on the ztblRibbons table, loops through each record in the recordset, and calls the LoadCustomUI method once for each record. The code uses the RibbonName field from the recordset to pass the name to the LoadCustomUI method, and the RibbonXML field contains the well-formed XML that defines each of our custom Ribbons. After Access loads each Ribbon, the code closes the recordset and sets it to Nothing. The last line before the End If statement returns a value of True for the LoadRibbons function, indicating success. The last part of the code has an exit procedure and our error handling code to handle the case if Access encounters an error.
As you can see, you can use the LoadCustomUI method to load a custom Ribbon from a different table, but you’re not limited to storing the XML in a table. You could also store the XML directly within a code module and set it to a string variable.
Using Ribbon Attributes
The RibbonX architecture contains many controls and attributes you can use in your applications. To help you understand some of the elements you can create with RibbonX, we’ll look at the custom Ribbons we created in the Conrad Systems Contacts sample database. Open the Contacts.accdb sample database, and click OK in the opening message box. Next, open the frmSplash form in Form view either by double-clicking the form in the Navigation Pane or by right-clicking it and clicking Open on the shortcut menu. The frmSplash form is displayed for a few seconds and then opens the frmSignOn form where you can sign in to the database. Select Jeff’s name from the User Name combo box, and click the Sign On button to sign in to the database. (Neither of the users has a password assigned.) Access opens the frmMain form and displays the custom main Ribbon for this database, as shown in Figure 24–3. The main Ribbon in this database, rbnCSD, has a tab called Conrad Systems Contacts and four groupsNews, Navigation, Exit, and About. The News group displays three labels, one of which displays the name of the current user signed in to the database. (You’ll learn how to dynamically change the Ribbon later in this chapter.) The remaining three groups display custom buttons that allow you to navigate to the various parts of the application.
Figure 24–3: The main Ribbon in the Conrad Systems Contacts database displays custom controls.
You can see in Figure 24–3 that all built-in Ribbon elements are hidden. As you recall from Chapter 23, if you set the startFromScratch attribute to True, Access hides all four built-in Ribbon tabs, displays limited options when you click the Microsoft Office Button, and displays a limited Quick Access Toolbar.
When you build a custom Ribbon definition, you can either define an entire custom Ribbon or define XML that modifies one of the built-in Ribbons. In your XML, you define custom controls. You assign attributes to your controls to define how they look and where they are positioned, and you define callbacks for your controls to define how they act. In Visual Basic terms, you can think of an attribute as a property of a control object and a callback as a method or event of a control object. When an attribute of a control is a callback, it is essentially the same as an event property of an Access control-you assign to a callback attribute the name of a procedure that will handle the event. You can also copy attributes of built-in controls by using an attribute name that is a Control ID, and you can reference a control in another custom Ribbon using a Qualified ID.
Let’s take a look at attributes you can assign to controls first. Table 24–1 lists the RibbonX attributes you can use in your XML customization.
| Attribute | Value or Type | Description |
|---|---|---|
| description | String | Defines the text shown in menu controls when itemSize="large". |
| enabled | True or False | Returns the control’s enabled state. Disabled controls (enabled=False) appear dimmed in the Ribbon. |
| getDescription | Callback | Names the macro or procedure that can set the description attribute of a control. |
| getEnabled | Callback | Defines the macro or procedure that can set the enabled state of a control. |
| getImage | Callback | Names the macro or procedure that can set the image attribute of a control. |
| getLabel | Callback | Defines the macro or procedure that can set the label attribute of a control. |
| getPressed | Callback | Names the procedure that can respond to the current state of a toggle button or check box. |
| getSupertip | Callback | Names the procedure that can set the Enhanced ScreenTip of a control. |
| getTooltip | Callback | Names the procedure that can set the ToolTip of a control. |
| getVisible | Callback | Names the procedure that can set the visibility state of a control |
| id | String | Provides the Unique ID for a user-defined control. |
| idMso | Control ID | Provides the Control ID for a built-in Ribbon element. |
| idQ | Qualified ID | Provides the qualified name of a control on another Ribbon. |
| image | String | Defines the image displayed on the control. |
| imageMso | Control ID | Provides the icon of a built-in control. |
| insertAfterMso | Control ID | Positions a custom control after a built-in control. |
| insertAfterQ | Qualified ID | Positions a custom control after a control defined by another Ribbon using a qualified name. |
| insertBeforeMso | Control ID | Positions a custom control before a built-in control. |
| insertBeforeQ | Qualified ID | Positions a custom control before a control defined by another Ribbon using a qualified name. |
| label | String | Returns a control’s label text. |
| onAction | Callback | Defines the macro or procedure called when a user clicks this control. |
| pressed | True or False | Returns the state of a toggle button or check box control. |
| showLabel | True or False | Determines whether a control label is visible. |
| size | Normal or Large | Sets the image size of a custom control. |
| supertip | String | Defines the text to display an enhanced ScreenTip when the user rests their mouse pointer on the control. |
| tooltip | String | Defines the text to display as a ToolTip. |
| visible | True or False | Returns the visible status of a control. |
Open table as spreadsheet In addition to attributes you can use in your XML customization, you can create many types of controls using RibbonX. Table 24–2 lists the types of controls you can use in a custom Ribbon definition and their associated attributes (properties) and callbacks (event properties).
| Control Name | Attributes | Callbacks |
|---|---|---|
| button | description, enabled, id, idMso, idQ, image, imageMso, insertAfterMso, insertAfterQ, insertBeforeMso, insertBeforeQ, keytip, label, screentip, showlmage, showLabel, size, supertip, tag, and visible | getDescription, getEnabled, getImage, getKeytip, getLabel, getScreentip, getShowImage, getShowLabel, getSize, getSupertip, getVisible, and onAction |
| buttonGroup | id, idQ, insertAfterMso, insertAfterQ, insertBeforeMso, insertBeforeQ, and visible | getVisible |
| checkBox | description, enabled, id, idMso, idQ, insertAfterMso, insertAfterQ, insertBeforeMso, insertBeforeQ, keytip, label, screentip, supertip, tag, and visible | getDescription, getEnabled, getKeytip, getLabel, getPressed, getScreentip, getSupertip, getVisible, and onAction |
| comboBox | enabled, id, idMso, idQ, image, imageMso, insertAfterMso, insertAfterQ, insertBeforeMso, insertBeforeQ, label, maxLength, screentip, showltemlmage, showlmage, showLabel, sizeString, supertip, tag, and visible | getEnabled, getImage, getItemCount, getItemID, getItemImage, getItemLabel, getItemScreentip, getItemSupertip, getKeytip, getLabel, getScreentip, getShowImage, getShowLabel, getSize, getSupertip, getText, getVisible, and onChange |
| dialogBoxLauncher | None Note that you must create a button inside a dialogBoxLauncher tag and then use the attributes of that button to specify attributes for the dialogBoxLauncher. | None Note that you must create a button inside a dailogBoxLauncher tag and then use the callbacks of that button to specify events for the dialogBoxLauncher. |
| dropDown | enabled, id, idMso, idQ, image, imageMso, insertAfterMso, insertAfterQ, insertBeforeMso, insertBeforeQ, keytip, label, screentip, showlmage, showItemLabel, showLabel, supertip, tag, and visible | getEnabled, getImage, getItemCount, getItemID, getItemImage, getItemLabel, getItemScreentip, getItemSupertip, getKeytip, getLabel, getScreentip, getSelectedItemID, getSelectedItemIndex, getShowImage, getShowLabel, getSize, getSupertip, getText, getVisible, and onChange |
| dynamicMenu | description, enabled, id, idMso, idQ, image, imageMso, insertAfterMso, insertAfterQ, insertBeforeMso, insertBeforeQ, keytip, label, screentip, showlmage, showLabel, size, supertip, tag, and visible | getDescription, getEnabled, getContent, getImage, getKeytip, getLabel, getScreentip, getShowImage, getShowLabel, getSize, getSupertip, and getVisible |
| editBox | enabled, id, idMso, idQ, image, imageMso, insertAfterMso, insertAfterQ, insertBeforeMso, insertBeforeQ, keytip, label, maxLength screentip, showlmage, showLabel, sizeString, supertip, tag, and visible | getEnabled, getImage, getKeytip, getLabel, getScreentip, getShowImage, getShowLabel, getSupertip, getText, getVisible, and onChange |
| gallery | columns, description, enabled, id, idMso, idQ, image, imageMso, insertAfterMso, insertAfterQ, insertBeforeMso, insertBeforeQ, itemHeight, itemWidth, keytip, label, rows, screentip, showlmage, showltemlmage, showItemLabel, showLabel, size, sizeString, supertip, tag, and visible | getDescription, getEnabled, getImage, getItemCount, getItemHeight, getItemID, getItemImage, getItemLabel, getItemScreentip, getItemSupertip, getItemWidth, getKeytip, getLabel, getScreentip, getSelectedItemID, getSelectedItemIndex, getShowImage, getShowLabel, getSize, getSupertip, getText, getVisible, and onAction |
| group | id, idMso, idQ, image, imageMso, insertAfterMso, insertAfterQ, insertBeforeMso, insertBeforeQ, keytip, label, screentip, supertip, and visible | getImage, getKeytip, getLabel, getScreentip, getSupertip, and getVisible |
| labelControl | enabled, id, idMso, idQ, insertAfterMso, insertAfterQ, insertBeforeMso, insertBeforeQ, label, screentip, showLabel, supertip, tag, and visible | getEnabled, getLabel, getScreentip, getShowLabel, getSupertip, and getVisible |
| menu | description, enabled, id, idMso, idQ, image, imageMso, insertAfterMso, insertAfterQ, insertBeforeMso, insertBeforeQ, itemSize, keytip, label, screentip, showlmage, showLabel, size, supertip, tag, and visible | getDescription, getEnabled, getImage, getKeytip, getLabel, getScreentip, getShowImage, getShowLabel, getSize, getSupertip, and getVisible |
| menuSeparator | id, idQ, insertAfterMso, insertAfterQ, and insertBeforeQ | getTitle |
| separator | id, idQ, insertAfterMso, insertAfterQ, insertBeforeMso, insertBeforeQ, and visible | getVisible |
| splitButton | enabled, id, idMso, idQ, insertAfterMso, insertAfterQ, insertBeforeMso, insertBeforeQ, keytip, label, screentip, showLabel, size, supertip, tag, and visible | getEnabled, getKeytip, getShowLabel, getSize, getSupertip, and getVisible |
| tab | id, idMso, idQ, insertAfterMso, insertAfterQ, insertBeforeMso, insertBeforeQ, keytip, label, tag, and visible | getKeytip, getLabel, and getVisible |
| toggleButton | description, enabled, id, idMso, idQ, image, imageMso, insertAfterMso, insertAfterQ, insertBeforeMso, insertBeforeQ, keytip, label, screentip, showlmage, showLabel, size, supertip, tag, and visible | getDescription, getEnabled, getImage, getKeytip, getLabel, getPressed, getScreentip, getShowImage, getShowLabel, getSize, getSupertip, getVisible, and onAction |
Now that you know the attributes and callbacks available in the RibbonX architecture, you can begin to study how we created the main Ribbon in the Conrad Systems Contacts database. If you still have the frmMain form open, click the Exit button to close it, and then click Yes to confirm that you want to exit. Next, find the zfrmChangeRibbonXML form in the Navigation Pane, and open it in Form view. Finally, move to the third record in the table where the Ribbon Name text box displays rbnCSD. Listed next is the XML customization for this Ribbon in the Ribbon XML text box. We’ve added line numbers to this code listing so that you can follow along with the line-by-line explanations in Table 24–3, which follows the listing.
1 <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" 2 onLoad="onRibbonLoad1"> 3 <ribbon startFromScratch="true"> 4 <tabs> 5 <tab label="Conrad Systems Contacts"> 6 <group label="s"> 7 <labelControl getLabel="onGetLabel"/> 8 <labelControl getLabel="onGetLabel"/> 9 <labelControl getLabel="onGetLabel"/> 10 </group> 11 <group label="n" visible="true"> 12 <button label="s" 13 imageMso="MeetingsWorkspace" size="large" 14 onAction="onOpenCompanies" 15 supertip="Edit company information."/> 16 <button label="s" imageMso="NewContact" 17 size="large" onAction="onOpenContacts" 18 supertip="Edit contact information."/> 19 <button label="s" 20 imageMso="FilePackageForCD" size="large" 21 onAction="onOpenProducts" 22 supertip="Edit product information."/> 23 <button label="Events" 24 imageMso="SendCopyFlag" size="large" 25 onAction="onOpenPendingEvents" 26 supertip="View any pending events."/> 27 <button label="s" 28 imageMso="CustomTableOfContentsGallery" size="large" 29 onAction="onOpenInvoices" 30 supertip="Edit invoice information."/> 31 <button label="s" 32 imageMso="FileDocumentEncrypt" 33 size="large" onAction="onOpenUsers" 34 supertip="Edit user information."/> 35 <splitButton size="large"> 36 <button imageMso="NewTask" 37 onAction="onOpenCodeLists" label="Lists"/> 38 <menu > 39 <button label="Types" 40 imageMso="NewTask" onAction="onOpenContactTypes"/> 41 <button label="Types" 42 imageMso="NewTask" onAction="onOpenEventTypes"/> 43 <button label="Categories" 44 imageMso="NewTask" onAction="onOpenProductCategories"/> 45 <button label="Titles" 46 imageMso="NewTask" onAction="onOpenPersonTitles"/> 47 <button label="Suffixes" 48 imageMso="NewTask" onAction="onOpenPersonSuffixes"/> 49 </menu> 50 </splitButton> 51 <splitButton size="large"> 52 <button imageMso="CreateReport" 53 onAction="onOpenReports" label="s"/> 54 <menu supertip="View reports."> 55 <button label="Reports" 56 imageMso="CreateReport" onAction="onOpenCompanyReports"/> 57 <button label="Reports" 58 imageMso="CreateReport" onAction="onOpenContactReports"/> 59 <button label="Reports" 60 imageMso="CreateReport" onAction="onOpenProductReports"/> 61 </menu> 62 </splitButton> 63 </group> 64 <group label="t"> 65 <button label="Database" 66 imageMso="PrintPreviewClose" size="large" 67 onAction="onCloseDatabase" supertip="Exit the database."/> 68 </group> 69 <group label="t"> 70 <button label="t" size="large" 71 imageMso="Help" onAction="onOpenFormEdit" tag="frmAbout" 72 supertip="View the About form."/> 73 </group> 74 </tab> 75 </tabs> 76 </ribbon> 77 </customUI>
| Line(s) | Explanation |
|---|---|
| 1 | Tells Access which schema file to use when building this specific Ribbon. |
| 2 | Specifies a procedure that processes the RibbonLoad event when Access first displays the Ribbon. In this event, you can save a pointer to the Ribbon to enable your code to dynamically update it. (We’ll explain how to update the Ribbon later in this chapter.) |
| 3 | Hides all built-in Ribbon elements. |
| 4 | Specifies the beginning tag to create a new set of tabs. |
| 5 | Creates a new tab with a Control ID called tabCSD and displays Conrad Systems Contacts in the tab caption. |
| 6 | Creates a new group with a Control ID called grpNews and displays News as the group label. |
| 7 | Creates a new label control, IblWelcome, and specifies the onGetLabel procedure to respond to the getLabel event to dynamically update the text displayed in the label. |
| 8 | Creates a new label control, IblToday, that also calls onGetLabel. |
| 9 | Creates a new label control, IblPending, that also calls onGetLabel. |
| 10 | Ends group tag for the grpNews group. |
| 11 | Creates a new group with a Control ID called grpNavigation and displays Navigation as the group label. |
| 12–15 | Creates a new button, cmdCompanies, with a label of Companies. Instead of specifying an image attribute, we used imageMSO to copy the image from the built-in control named FilePackageForCD. The button size is set to large, and the onAction attribute issues a callback to the onOpenCompanies procedure. Finally, we designate text to display as a supertip. |
| 16–18 | Creates a new button, cmdContacts, with a label of Contacts, an image copied from a built-in control, and a callback defined. |
| 19–22 | Creates a new button, cmdProducts, with a label of Products, an image copied from a built-in control, and a callback defined. |
| 23–26 | Creates a new button, cmdPendingEvents, with a label of Pending Events, an image copied from a built-in control, and a callback defined. |
| 27–30 | Creates a new button, cmdlnvoices, with a label of Invoices, an image copied from a built-in control, and a callback defined. |
| 31–34 | Creates a new button, cmdUsers, with a label of Users, an image copied from a built-in control, and a callback defined. |
| 35 | Creates a new split button, sbCodeList, with a large size. |
| 36–37 | Creates a new button, cmdCodeLists, with a label of Code Lists, an image copied from a built-in control, and a callback defined. This button becomes the top half of the split button. If you click the top half of the button, Access calls the onAction procedure for this button. |
| 38 | Creates a new menu control, sbMnuCodeLists, for the bottom half of the split button. |
| 39–40 | Creates a new button, cmdContactTypes, with a label of Contact Types, an image copied from a built-in control, and a callback defined. |
| 41–42 | Creates a new button, cmdEventTypes, with a label of Event Types, an image copied from a built-in control, and a callback defined. |
| 43–44 | Creates a new button, cmdProductCategories, with a label of Product Categories, an image copied from a built-in control, and a callback defined. |
| 45–46 | Creates a new button, cmdPersonTitles, with a label of Person Titles, an image copied from a built-in control, and a callback defined. |
| 47–48 | Creates a new button, cmdPersonSuffixes, with a label of Person Suffixes, an image copied from a built-in control, and a callback defined. |
| 49 | Ends the menu tag for menu sbMnuCodeLists. |
| 50 | Ends the split button tag for sbCodeList. |
| 51 | Creates a new split button, sbReports, with a large size. |
| 52–53 | Creates a new button, cmdReports, with a label of Reports, an image copied from a built-in control, and a callback defined. This button becomes the top half of the split button. If you click the top half of the button, Access calls the onAction procedure for this button. |
| 54 | Creates a new menu control, sbMnuReports, for the bottom half of the split button with a supertip. |
| 55–56 | Creates a new button, cmdCompanyReports, with a label of Company Reports, an image copied from a built-in control, and a callback defined. |
| 57–58 | Creates a new button, cmdContactReports, with a label of Contact Reports, an image copied from a built-in control, and a callback defined. |
| 59–60 | Creates a new button, cmdProductReports, with a label of Product Reports, an image copied from a built-in control, and a callback defined. |
| 61 | Defines the ending menu tag for menu sbMnuReports. |
| 62 | Defines the ending split button tag for sbReports. |
| 63 | Defines the ending group tag for the grpNavigation group. |
| 64 | Creates a new group with a Control ID called grpExit and displays Exit for the group label. |
| 65–67 | Creates a new button, cmdExitDatabase, with a label of Exit Database, an image copied from a built-in control, and a callback defined. |
| 68 | Ends the group tag for the grpExit group. |
| 69 | Creates a new group with a Control ID called grpAbout and displays About for the group label. |
| 70–72 | Creates a new button, cmdHelpAbout, with a label of About, an image copied from a built-in control, and a callback defined. A custom tag value is assigned to this control using the tag attribute. |
| 73 | Defines the ending group tag for the grpAbout group. |
| 74 | Defines the ending tag for the tabCSD tab. |
| 75 | Defines the ending tabs tag. |
| 76 | Defines the ending ribbon tag. |
| 77 | Defines the ending customUI tag. |
Creating VBA Callbacks
As you reviewed the XML customization for the rbnCSD Ribbon, you no doubt noticed that most of the buttons used the onAction callback. When you use onAction, you specify a saved macro object (you cannot use an embedded macro on a form or report in this case) or a Visual Basic for Applications (VBA) procedure to respond to the eventthe user clicking the button. (You can think of onAction for a button in a Ribbon as the same as On Click for a command button on an Access form.) In the Conrad Systems Contacts sample database (Contacts.accdb), we defined all the procedures to respond to callback events for our custom Ribbons in the module modRibbonCallbacks. Let’s take a look at some of these VBA procedures so that you can see how everything ties together. Close any objects you have open at the moment, right-click modRibbonCallbacks in the Navigation Pane (you might need to adjust your Navigation Pane display to see the modules), and click Design View on the shortcut menu to open this module in Design view.
Scroll down the procedures and functions in this module until you come to the onOpenCompanies procedure. This procedure responds to the On Click event of the very first button defined in our custom Ribbon-the cmdCompanies button. The procedure is as follows:
Public Sub onOpenCompanies(control As IRibbonControl) ' User wants to open Companies ' Make sure frmMain is there If IsFormLoaded("frmMain") Then ' Yes - execute the Companies procedure Form_frmMain.cmdCompanies_Click End If End Sub The idea is to duplicate what happens when the user clicks the Companies button in the main switchboard form. That form (frmMain) already has code to process the request, so why duplicate it? If you remember from Chapter 19, “Understanding Visual Basic Fundamentals,” you can make any procedure in a form’s class module a method of the form by declaring it Public. We did exactly that with the cmdCompanies_Click procedure in the frmMain form so that this onAction procedure can call it and not duplicate the code to open Companies. The procedure first verifies that the frmMain form is open because a call to the public method will fail if the form is closed. If the frmMain form is open, the procedure calls the cmdExit_Click procedure as a method of the form. If you scroll through the other procedures in the modRibbonCallbacks modules, you’ll find that most of them follow this same pattern-they run an existing command button Click event procedure on the frmMain form.
Dynamically Updating Ribbon Elements
When Access first opens a custom Ribbon, it issues callbacks for all the controls in order to set their attributes. Note that, other than for the custom Ribbon defined in the Ribbon Name property in the Current Database category of Access Options, Access opens a custom Ribbon when it is first referenced in the Ribbon Name property of a form or report that you open or that your application code opens. Executing the LoadCustomUI method of the Application object loads the Ribbon definition into memory, but it does not actually open the Ribbon.
After a Ribbon is loaded, the Ribbon is static, and Access does not update any elements. If you want to update the Ribbon, such as change the text in a label control to display the current user’s name, you must explicitly tell Access to reinitialize the entire Ribbon or a control on the Ribbon. Fortunately, the RibbonX architecture allows you to save a copy of a pointer to the Ribbon in a public variable so you that can update it at a later time. In RibbonX terms, you invalidate a control (or the entire Ribbon) to force Access to reload the object and issue any attribute setting callbacks. At the beginning of the XML customization for rbnCSD, you’ll recall seeing this line of code:
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="onRi bbonLoad1">
The first procedure in the modRibbonCallbacks module is onRibbonLoad. In the procedure called by onRibbonLoad, you can save a pointer to the Ribbon that is being opened. If you do not plan to ever update any of your Ribbon elements, you do not need to add the onRibbonLoad attribute to the <customUI> element. Access calls the onLoad callback only once during the process of opening the custom Ribbon for the first time. The following is the onRibbonLoad1 procedure that executes in the onLoad callback of the rbnCSD custom Ribbon:
' This is the customUI onLoad event handler for main ribbon Public Sub onRibbonLoad1(ribbon As IRibbonUI) ' Cache a copy of the Ribbon so we can refresh later at any time Set gobjRibbon1=ribbon End Sub
The IRibbonUI object is a parameter that you can use to save a pointer to the opened custom Ribbon. The IRibbonUI class provides methods that you can use to invalidate a single control in your customization or the entire Ribbon. Table 24–4 lists these methods.
| Method | Description |
|---|---|
| Invalidate() | Access reinitializes all custom controls. |
| InvalidateControl(string controlID) | Access reinitializes one specific control. |
After you signed in to the Conrad Systems Contacts database, you’ll recall seeing the user name displayed in the News group. To update the display of the three labels in the News group, we use the following onGetLabel procedure:
' This serves as a getLabel callback for the labels. ' We determine which Control ID was passed from the Ribbon ' and set the label appropriately. Public Sub onGetLabel(control As IRibbonControl, ByRef label) Select Case control.Id Case "lblWelcome" ' Update welcome information label = GetWelcomeMessage() Case "lblToday" ' Update Time label = "Today is: " & FormatDateTime(Date, vbLongDate) Case "lblPending" ' Update Pending Events message label = GetPendingEventsNumber End Select End Sub
We use a Select Case procedure to test the value of the label Control ID passed into the onGetLabel procedure. For the IblWelcome label, we update the display by calling the GetWelcomeMessage function to retrieve the name of the user currently signed in to the database. For the IblToday label, we retrieve the current date from the Windows system date and format it to display as Long Date. For the IblPending label, we retrieve the number of pending events for the current user by calling the GetPendingEventsNumber function.
You might be wondering, when does Access know to update these labels? On the frmSignOn form, you have to select a user name and provide a password. In the Click event of the cmdSignOn command button, we have this code just before the form close code:
' Refresh the data in the Ribbon gobjRibbon1.InvalidateControl "lblWelcome" gobjRibbon1.InvalidateControl "lblPending"
A few lines above this code, Access saves the user name to a public string variable, gstrThisUser, which is referenced in the GetWelcomeMessage function and the
GetPendingEventsNumber function. We invalidate the lblWelcome control in the cmdSignOn procedure, which causes Access to refresh this specific control and change the label’s text. We also invalidate the lblPending control so that the correct number of pending events is displayed for the current user. After you’ve saved a pointer to the Ribbon, you can invalidate the controls or the entire Ribbon as often as you need.
Loading Images into Custom Controls
The easiest way to display images on a custom button in your Ribbon customization is to use the imageMso attribute. As you’ll recall from Chapter 23, you can use the imageMso attribute to apply an existing icon from Access 2007 or from any 2007 Microsoft Office system applications that support Ribbons. All the custom command buttons on the Ribbons in the Conrad Systems Contacts and Housing Reservations sample databases reuse icons and images from other built-in controls.
You can also load images from an attachment field stored in a table or use the LoadPicture method to load image files stored in a folder. In the Housing Reservations sample database (Housing.accdb), we store all the pictures for the employees in a table called USysRibbonImages. (Note that you can name your table whatever you like, but we chose to name our table with the USys prefix so that it appears in the Navigation Pane only if you have enabled your Navigation Options to display system objects.) Open the Housing.accdb database now (close Contacts.accdb if you have it open), and click OK in the opening message box. Next, open the frmSplash form in Form view. After a few seconds, the frmSplash form closes, and then frmSignOn opens. Select Jeff’s name from the User Name combo box, and enter password in the Password text box. (All the passwords in this sample database are password.) Finally, click the Sign On button to sign in to the database under Jeff’s name. Access opens the frmMain form and displays a custom Ribbon with Jeff’s picture in the News group, as shown in Figure 24–4.
Figure 24–4: You can load images from attachment fields onto custom controls in your Ribbons.
Access is placing the image on a button in the News group, cmdPicture, that has no onAction attribute assigned to it. (We don’t want anything to happen if you click the button, but we’re using the button to display our custom image.) If you were to sign on as a different user, Access would update the picture because we invalidated the control in the Click event of the cmdSignOn button on the frmSignOn form. To set the picture, we use the getImage callback attribute of the custom cmdPicture control in our main Ribbon. The getImage callback runs the GetButtonImage function in the modRibbonCallbacks module listed here:
Public Function GetButtonImage(control As IRibbonControl, ByRef image) ' This function displays a picture of the logged on user ' The command button in the Welcome group will update from ' a picture stored in an Attachment field in the table USysRibbonImages. ' We have to load a hidden form in order to grab the picture Dim frmRibbonImages As Form Static rsForm As DAO.Recordset2 Dim rsAttachments As DAO.Recordset2 If frmRibbonImages Is Nothing Then ' Form is not opened, so open it DoCmd.OpenForm "zfrmUSysRibbonImages", WindowMode:=acHidden Set frmRibbonImages = Forms("zfrmUSysRibbonImages") Set rsForm = frmRibbonImages.Recordset End If ' Find the picture for the logged on user rsForm.FindFirst "UserID='" & gstrThisEmployee & "'" If rsForm.NoMatch Then ' User not found so set the image to nothing Set image = Nothing Else ' Found a match, so update the display on the Ribbon Set image = frmRibbonImages.RibbonImages.PictureDisp End If End Function To load an image from an attachment field onto a custom Ribbon, you have to assign an object that is in the correct format. The PictureDisp property of an Attachment control bound to a picture in an attachment field returns the correct object type. Using an open form bound to the table that contains the attachment field is a simple way to get what we need. (You could also write a COM object in C#, but that’s far beyond the scope of this book!) We created a special form, zfrmUSysRibbonImages, especially for this purpose. The code opens this form in hidden mode so it never becomes visible on screen. Next, the code searches the records in the table for a match to the public variable that contains the name of the user signed in to the database-gstrThisEmployee. If no match is found, Access sets the image to nothing. If a match is found, Access updates the image on the custom button with the picture stored in the attachment field.
Hiding Options on the Microsoft Office Button
You’ve previously learned that if you set the startFromScratch attribute of your customization to True, Access hides some of the options available when your Ribbon is open and you click the Microsoft Office Button. You can selectively hide buttons and commands by using the <officemenu> tags and setting the visible attribute for the built-in controls to false. For example, if you want to hide the New, Open Database, and Save As options, use the following XML example in a custom Ribbon that you load:
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <ribbon startFromScratch="true"> <officeMenu> <button idMso="FileNewDatabase" visible="false"/> <button idMso="FileOpenDatabase" visible="false"/> <splitButton idMso="FileSaveAsMenuAccess" visible="false" /> </officeMenu> </ribbon> </customUI>
If you click the Microsoft Office Button using this customization, you’ll see a very limited set of options available for your users, as shown in Figure 24–5.
Figure 24–5: You now have limited options available when you click the Microsoft Office Button.
Setting Focus to a Tab
The RibbonX architecture does not provide a method to place the focus on a specific tab, which is unfortunate. In the Conrad Systems Contacts and Housing Reservations sample databases, we wanted to display our main Ribbon at all times. For the data entry forms, we wanted to still see the main tab when the custom Ribbon for forms is open but put the focus on the Navigation tab as a visual cue for the users of the application. Instead, the Navigation tab opens to the right of the main tab but does not receive focus. Fortunately, RibbonX does provide a TabSetFormReportExtensibility element that you can use for these cases.
When you use the TabSetFormReportExtensibility element, Access places the content into the current tabSet, moves the focus to this tab, and places a caption above the tab. The tab caption matches the Caption property of the current form or report if this property is set. If no caption is set, Access uses the current name of the object. In the rbnForms custom Ribbon in both Conrad Systems Contacts and Housing Reservations, we duplicated all the controls in the main Ribbon-rbnCSD or rbnProseware-and then added the XML necessary to display the tab we wanted with groups and controls for form navigation. The specific customization for these follows this format:
<contextualTabs> <tabSet idMso="TabSetFormReportExtensibility"> <tab label="Navigation"> ....(remaining XML customization here).... </tab> </tabSet> </contextualTabs>
In essence we created our own contextual tab that appears next to the main Ribbon tab but receives the focus when the data entry forms open, as shown in Figure 24–6.
Figure 24–6: Use the TabSetFormReportExtensibility element to set focus to a specific tab.
Quite frankly, we could write an entire book about Ribbon customization, but you should have enough information at this point to get started building your own custom Ribbons. For more information, visit the Microsoft Developer Network Web site at http://msdn.microsoft.com/. In the remainder of this chapter, you’ll learn additional techniques that you can use to customize your applications for your users.
EAN: 2147483647
Pages: 234