MicrosoftВ® Office Access(TM) 2007 Inside Out (Microsoft Office Access Inside Out)

So far in this chapter you’ve learned about XML file types, how to import and export Access objects and data using XML, and how to modify table templates by changing the schema in the template files. Access 2007 and most applications in the 2007 Office release use XML in another important user interface element-Ribbons. Throughout this book you’ve seen how to use the Ribbon commands on the four main tabs-Home, Create, External Data, and Database Tools-as well as on the many contextual tabs. You even might have noticed the custom Ribbons we created in the Conrad Systems Contacts (Contacts.accdb) and Housing Reservations (Housing.accdb) sample databases when you opened any of the forms and reports used by the application. In the following sections, you’ll learn what steps are necessary to create a simple custom Ribbon for a form. You’ll see how to create XML for this Ribbon that displays existing groups from the four main Ribbon tabs. You’ll also create a new data entry form and assign your new Ribbon to this form to test the new commands.

Creating a USysRibbons Table

When you open an Access 2007 database, Access looks for a local table called USysRib-bons during the startup process to see whether it needs to load any custom Ribbons. If Access does not find this table, it proceeds with loading all built-in Ribbons. You can also load custom Ribbons into your application by writing Visual Basic code to load the XML stored in a different table or defined within your code. We’ll discuss how to do this in Chapter 24, “The Finishing Touches.”

Access does not create a local table called USysRibbons when you create a new blank database or use one of the database templates-you need to create this table yourself. For Access to use the USysRibbons table, the table must contain the two fields listed in Table 23–1. The RibbonName field is a unique name used to identify the name of the Ribbon. The RibbonXml field contains the XML used to define the custom Ribbon. The XML must be well-formed XML in order for Access to interpret the code and apply it to the Ribbon. Note that you can have additional fields in this table if you want (such as a field that documents what’s on your custom Ribbon), but Access looks for only the two fields listed in Table 23–1 when loading your Ribbons.

By default, Access does not display in the Navigation Pane any local tables that start with the prefix USys because it considers these to be system tables. Depending upon what settings you have configured in the Navigation Options dialog box, you might not be able to see any system tables. For example, if you create and save a new table with the name USysRibbons, you might not see this new table in the Navigation Pane.

In the Conrad Systems Contacts database, we have included this table to load the custom Ribbons we use in the application. Open the Contacts.accdb database, and click OK in the opening message box. 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 database. If you scroll through the list of tables, you’ll notice you do not see a table called USysRibbons. To see this table in the Navigation Pane, right-click the menu at the top of the Navigation Pane, and click Navigation Options. In the Display Options section in the Navigation Options dialog box, select the Show System Objects check box to display all system objects in the database, as shown in Figure 23–19.

Figure 23–19: Select the Show System Objects check box to display the USysRibbons table.

Click OK to close the Navigation Options dialog box, and then review the list of tables in the Navigation Pane. You’ll notice you can now see six tables that start with the prefix MSys and the USysRibbons table. Right-click the USysRibbons table in the Navigation Pane and click Design View on the shortcut menu to see this table in Design view. In Figure 23–20, you can see the table has one additional field-ID-with a data type of AutoNumber. Remember that Access needs to have only the RibbonName and RibbonXml fields and will ignore any other fields. We added the ID field and used it as the primary key for the table to make sure our entries are unique.

Figure 23–20: Access looks for a table called USysRibbons during startup to load custom Ribbons.

Switch to Datasheet view by clicking the arrow in the Views group on the Design tab and clicking Datasheet View from the list of available views. You’ll notice that there are three records in the USysRibbons table, as shown in Figure 23–21. Each of the records in this table denotes a specific custom Ribbon. You can see the names of each of the Ribbons in the RibbonName field-rbnForms, rbnPrintPreview, and rbnCSD. The rbnForms Ribbon is used by most of the data entry forms in the Conrad Systems Contacts database, the rbnPrintPreview Ribbon is used by the reports, and the rbnCSD Ribbon is displayed for the frmMain, frmCodeLists, and frmReports forms.

Figure 23–21: The Conrad Systems Contacts database includes three custom Ribbons.

In the RibbonXml field, you can see the XML for each of these three custom Ribbons. The well-formed XML in these fields, however, is not particularly easy to read in table Datasheet view. You can place your insertion point in the field and use the arrow keys to read the XML, you can press Shift+F2 to open the Zoom box, or you can expand the height of the individual rows. However, a much easier way to view and modify the XML is to create a form bound to this table. In the Conrad Systems Contacts database (and the Housing Reservations database as well), we created a form to add and edit the records in this table. Click the menu at the top of the Navigation Pane, click Object Type under Navigate To Category, and then click Forms under Filter By Group to display a list of forms available in the this database. Find the form called zfrmChangeRibbonXML, and open it in Form view, as shown in Figure 23–22. If you use the record navigation buttons, you can see the XML for each of the three custom Ribbons. (You’ll study the three existing Ribbons in more detail in Chapter 24, so don’t be concerned about understanding the XML at this point.) We used the Form Wizard to create this form, but you could just as easily create one from scratch if you’d like. Creating a form to work with your USysRibbons table is not a requirement, but you’ll have an easier time viewing and modifying your XML by using a large text box on a form for the RibbonXml field.

Figure 23–22: You’ll have an easier time editing your XML for the USysRibbons table if you use a form.

Creating a Test Form

Most of the forms and reports in the Conrad Systems Contacts database each already have a custom Ribbon applied. In the next section, we’ll walk you through creating XML for a new form Ribbon. Before we build the XML, let’s first create a new data entry form based on the tblContacts table. We’ll use this new form to test the XML without disturbing any of the existing database objects. First, close the zfrmChangeRibbonXML form if you still have it open. 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. Finally, select the tblContacts table in the Navigation Pane, and click the Form button in the Forms group on the Create tab. Access creates a new columnar form based on the table and opens it in Form view, as shown in Figure 23–23. Click the Save button on the Quick Access Toolbar, name the form frmRibbonTest, and then close the form.

Figure 23–23: Create a test form on the tblContacts table to use for your Ribbon testing.

Building the Ribbon XML

To create a custom Ribbon for a form or report, you must first create the XML in a text editor such as Notepad or Notepad 2007 or in Visual Basic 2005 Express Edition. We used the Notepad text editor to create our XML. To create well-formed XML for Ribbons, you need to use the Microsoft Office system XML Schema Reference, which contains the schema information that Access 2007 needs to validate the Ribbon customizations. You might also want to download the Microsoft Office system Document Lists of Control IDs, which contains a complete list of the control IDs of the built-in tabs, groups, buttons, and other commands. Each button, group, and tab is assigned a unique control ID in the Ribbon schema file. You can use these controls IDs to place existing built-in Ribbon elements onto your custom Ribbons.

Hiding Existing Ribbon Elements

Open Notepad (or an XML editor) to begin building your XML. We’ll create a custom Ribbon for our test form that includes two groups from the Home tab and hides all the built-in main tabs. As we proceed, you’ll test each step to see the Ribbon take shape. The XML for Ribbons needs to start with the following line:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">

The first line tells Access which schema file to use when building this specific Ribbon. The next line should be as follows:

<ribbon startFromScratch="true">

If you specify true, Access hides the four main tabs on the Ribbon when your custom Ribbon is loaded. In addition, when you click the Microsoft Office Button, Access shows only the New, Open, Save As, Close Database, Access Options, and Exit Access options. Also, the Quick Access Toolbar shows no options except the arrow-and you can select only the options to place the Quick Access Toolbar above or below the Ribbon or minimize the Ribbon. If you want to have a more controlled interface and show only your custom Ribbons to users, you should set this XML attribute to True. If you set startFromScratch to False, Access does not hide any of the main Ribbon tabs, and it doesn’t hide any options when you click the Microsoft Office Button. Any new tabs that you create appear to the right of the Database Tools tab.

After these first two lines, you can begin to build any tabs, groups, buttons, and other Ribbon elements. For now, let’s complete this simple XML example with some ending tags for ribbon and customUI. Your XML up to this point should look like the following:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <ribbon startFromScratch="true"> </ribbon> </customUI>

Testing Your XML

As you build your XML, it’s a good idea to test it along the way to ensure that everything is working properly. You’ll have an easier time debugging any issues in your XML if you systematically test it after each major step. Highlight all the XML you’ve created so far, and copy it to the Clipboard. Next, open the zfrmChangeRibbonXML form in the Conrad Systems Contacts database in Form view, and navigate to a new record. In the Ribbon Name text box, enter rbnTest for the name of this Ribbon. Finally, press the Tab key to move to the Ribbon Xml text box, and paste in the XML content from the Clipboard. Your record in the form should look like Figure 23–24.

Figure 23–24: Create a new record in the USysRibbons table for your test Ribbon by using the zfrmChangeRibbonXML form.

Close the zfrmChangeRibbonXML form to save your changes to the USysRibbons table. To display this Ribbon for your test form to see how it looks, you need to assign the rbnTest Ribbon to the Ribbon Name property of the form. Before you do this, however, you need to first close the database and then reopen it. You’ll remember we mentioned earlier that Access loads all the Ribbons found in the USysRibbons table during the application startup process. Because you just added this new record to the table, Access has not loaded this Ribbon into memory. If you opened the Property Sheet window for your test form at this point, you will not see rbnTest as an available option on the Ribbon Name property. (Note that you can type rbnTest in the property line, but you still won’t see this Ribbon displayed until you close and reopen the database.)

Close the database now and then reopen it to have Access load your new test Ribbon into memory. After you reopen the database, open your test form-frmRibbonTest-in Design view. Click the Property Sheet button in the Tools group on the Design tab to open the property sheet for the form. On the Other or All tab of the property sheet, click the arrow on the Ribbon Name property line, and then select rbnTest from the list of four Ribbons. Click the Save button on the Quick Access Toolbar to save your changes and then switch to Form view to see the result.

When you switch views, you’ll notice the entire Ribbon disappears, as shown in Figure 23–25. Access also hides all the options on the Quick Access Toolbar except for the arrow to open the Customize Quick Access Toolbar menu. Click the Microsoft Office Button, and you’ll see that Access reduces the number of options, as we discussed previously. Because you specified startFromScratch=True in your XML and did not specify any other custom tabs, Access presents a very limited user interface. Unless you want to provide any features such as filtering and sorting directly on your forms, you clearly need to improve this custom Ribbon beyond the bare essentials.

Figure 23–25: The simple XML you created earlier completely hides the Ribbon.

Creating Tabs

Creating an interface with no Ribbon showing, such as the one you just tested in the previous section, might work for some applications, but what if you want to provide your users with the same navigation and filter options for your forms that normally appear on the Home tab? To add buttons and controls to your custom Ribbon, you first need to create a tab to hold these controls. The Access Ribbon schema uses a tabs tag to denote a new tab to appear on the Ribbon.

Open Notepad (or an XML editor), and return to the XML file you were creating earlier. To create a new tab, you first need to use a tabs tag (<tabs>) followed by a line with the following syntax:

<tab id=UniqueTabName label=LabelCaption

The UniqueTabName must be a unique name for the current Ribbon XML. The LabelCaption attribute is optional, but if you don’t provide a caption for the tab, Access displays a small, empty tab header. At the end of any XML for the tab, you also need to provide an ending tab tag (</tab>) for each tab and an ending tabs tag (</tabs>) following all the individual tab tags. For this example, we’ll create a new tab called tabTest with a caption called Navigation. Add the following XML between the two ribbon tags (<ribbon> and </ribbon>) to create this new tab:

<tabs> <tab label="n"> </tab> </tabs>

Your completed XML should now look like the following:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <ribbon startFromScratch="true"> <tabs> <tab label="n"> </tab> </tabs> </ribbon> </customUI>

Now let’s test this markup with our test form and see how it looks. Highlight all the XML you’ve created so far, and copy it to the Clipboard. Next, open the zfrmChangeRibbonXML form in the Conrad Systems Contacts database in Form view, and navigate to the record that has rbnTest in the Ribbon Name text box. Press Tab to move to the Ribbon XML text box, and paste the XML content from the Clipboard, overwriting the previous XML. Next, close the zfrmChangeRibbonXML form to save your changes to the USysRibbons table. Finally, close the database, reopen it, and then open the frmRibbonTest form in Form view. Access now displays the Ribbon at full height with a new tab and caption of Navigation, as shown in Figure 23–26.

Figure 23–26: You can create custom tabs for your Ribbons.

Adding Built-In Groups to Tabs

Now that you have a new tab created in your markup, you can begin the process of adding different controls to this tab. For this test form, it would help your users to navigate through the contact records if they could use some of the built-in buttons and commands found on the Home tab-specifically the Records, Sort & Filter, and Find groups. You could write your own XML to create custom buttons that mimic the actions of each of the individual buttons in those three groups; but to make things easier, you can use the RibbonX architecture to copy buttons, commands, and options found in a built-in group onto one of your custom tabs. (We’ll show you how to create individual custom buttons on tabs in Chapter 24).

Open Notepad (or an XML editor), and return to the XML file you were creating earlier. To use an existing built-in group on a custom tab, use the following syntax:

<group idMso=ControlID>

You can add this tag any where within a tab definition (delimited with <tab> </tab> tags). You can use the idMso attribute to denote a built-in control ID-a built-in control defined in the RibbonX architecture. Every button, group, and tab on the built-in Ribbon has an internal control ID that you can reference. In this case, you’re going to show how to use the idMso attribute to refer to a specific group that you want to use on your tab. To help identify the names of these groups, you can use the Microsoft Office system Document Lists of Control IDs Excel spreadsheet that contains a complete list of these internal control IDs. For this example, you want to add the Records group on the Home tab to your custom Navigation tab to help your users add, save, and delete records. The control ID for the Records group is GroupRecords. At the end of any XML for a new group, you also need to provide an ending group tag. Add the following XML between the tab tags to create this new group:

<group idMso="GroupRecords"> </group>

Your completed XML up to this point should now look like the following:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <ribbon startFromScratch="true"> <tabs> <tab label="n"> <group idMso="GroupRecords"> </group> </tab> </tabs> </ribbon> </customUI>

Now let’s test this XML with our test form to see how it looks. As you did previously, highlight all the XML you’ve created so far, and copy it to the Clipboard. Next, open the zfrmChangeRibbonXML form in the Conrad Systems Contacts database in Form view, and navigate to the record that has rbnTest in the Ribbon Name text box. Press Tab to move to the Ribbon XML text box, and paste the XML content from the Clipboard, overwriting the existing XML. Next, close the zfrmChangeRibbonXML form to save your changes to the USysRibbons table. Finally, close the database, reopen it, and then open the frmRibbonTest form in Form view. Access now displays all the buttons and commands from the Records group on your custom Navigation tab, as shown in Figure 23–27. If you test some of the buttons, you’ll see that they all work just as they do on the Home tab.

Figure 23–27: The built-in Records group now appears on your custom Ribbon.

Now that you’ve added the Records group to your tab, let’s finish the XML by adding the Sort & Filter and Find groups to your custom Ribbon. Open Notepad (or an XML editor), and return to the XML file you were creating earlier. The control ID for the Sort & Filter group is GroupSortAndFilter, and the control ID for the Find group is GroupFindAccess. Add the following XML after the group end tag and before the tab end tag:

<group idMso="GroupSortAndFilter"> </group> <group idMso="GroupFindAccess"> </group>

Your final XML should now look like the following:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <ribbon startFromScratch="true"> <tabs> <tab label="n"> <group idMso="GroupRecords"> </group> <group idMso="GroupSortAndFilter"> </group> <group idMso="GroupFindAccess"> </group> </tab> </tabs> </ribbon> </customUI>

Now test your completed XML on your test form. As you did previously, highlight all the XML you’ve created so far, and copy it to the Clipboard. Next, open the zfrmChangeRibbonXML form in the Conrad Systems Contacts database in Form view, and paste the XML content from the Clipboard into the Ribbon XML text box on the rbnTest record, overwriting the existing XML. Close the zfrmChangeRibbonXML form to save your changes to the USysRibbons table. Close the database, and reopen it to have Access load the new Ribbon changes. Finally, open the frmRibbonTest form in Form view to see the results. Access now displays all the buttons and commands from the Records, Sort & Filter, and Find groups on your custom Navigation tab, as shown in Figure 23–28. With only a few lines of XML, you’ve created a custom Ribbon that you can use in your application. You could assign this Ribbon to any of the forms in your application. Also, because the XML is stored in a local table, you could easily import this table into another database and reuse the Ribbon for those forms.

Figure 23–28: The custom Ribbon you created now includes buttons and commands from three built-in groups.

You should now have a good grasp of the technologies included in Access 2007 to integrate with the Web. In Part 6, of this book, we’ll expand our discussion of RibbonX architecture and show you the more advanced concepts of using Visual Basic with Ribbons. We’ll show you how to create your own custom buttons and load custom Ribbon schemes. You’ll also learn about additional features you can use after you’ve finished designing your database application.