Chapter 9. Enhancing Product Administration
In this chapter, you'll add the final piece of significant form development. So far you have completed the following:
Provided forms for setting up categories and media types, plus a simple form for defining items (Chapter 6)
Implemented a check-out feature for creating new loans (Chapter 7)
Created a customer administration form (Chapter 8)
Now you'll significantly enhance the Item form to provide the following:
Data-bound images
Item search capability
Embedded web browser
Using Data-Bound Images
The saying goes, "a picture is worth a thousand words." I think in the technologically advanced society that we live in, pictures are nearly essential. Even if the images do not provide any "required" information, the "wow" factor alone will necessitate them. Undoubtedly, you will be asked to include images in your applications.
To be clear, in this chapter I'm addressing data-bound images; support for static images uses an entirely different approach, which I will cover in Chapter 11. By data-bound images, I mean images that are part of your data. For example, in the Library application that you're building, you will provide the ability to store an image for each item in the database.
Image Support in Access
Access provides the following three methods for including images in your database:
Storing bitmap files in the database
Storing OLE objects in the database
Storing images in the file system and storing a reference in the database
I will now briefly explain each of these techniques.
STORING BITMAPS IN ACCESS
The simplest approach is to store the bitmap images directly in the Access table. Add a field with a data type of OLE Object and include this field on a form, just like you would a Text field. To store the image, right-click the data-bound control on the form and click the Insert Object link. In the dialog box, select the Create From File option and browse to the file containing bitmap image, as shown in Figure 9-1.
Figure 9.1. Loading a bitmap image
The images must be bitmaps (.bmp) or device independent bitmaps (.dib). Once loaded in the database, your forms will display the images automatically. The advantage of this approach is that it's easy and requires no coding.
The disadvantages, however, are significant. The primary one is size. Bitmaps do not use any compression and can be three or four times the size of the same image in other file types such as JPEG, TIFF or PNG. While theoretically the file size limit of Access databases is quite high, generally the performance deteriorates as the file grows. Another disadvantage is that tables with image (OLE Object) fields cannot be published as a web database.
STORING OLE OBJECTS
As an alternative, you could store images that use compressed formats such as JPEG, which may take less space than bitmaps. There are numerous issues with this approach.
Object Linking and Embedding (OLE) is a technology that allows you to store data in a document that is rendered by another application. For example, you can embed an Excel spreadsheet inside a Word document. When you view or modify that spreadsheet in Word, you are actually running an Excel application. Excel is running as an OLE Server, rendering the embedded data on behalf of the Word application.
Similarly, when you store an OLE object in Access, an OLE Server will be launched to render the object. For this to work, there must be an OLE Server available to handle the type of object that was loaded. Earlier versions of Microsoft Office (earlier than Office 2003) included a Photo Editor application, which also served as an OLE Server. Image files saved as JPEG and GIF, for example, were rendered by the Photo Editor application.
With Office 2003 and later, the Photo Editor application was replaced with the Microsoft Picture Manager. This does not provide the OLE Server support that the Photo Editor did, so Access can no longer display these types of files. You can still store them, but Access does not know how to display them.
NOTE
You can install the old Photo Editor application from the Office 2000 or Office XP installation disk. Of course there is no 64-bit version of this application, so this is not an option if you are using the 64-bit version of Office 2010.
STORING IMAGES IN THE FILE SYSTEM
In the Access table, you'll use a Text field that will store the file path and name of the image. This takes virtually no space in the database. This is the most practical solution for many scenarios and the only one that is compatible with web databases.
There are some disadvantages with this approach, however, including the following:
You'll need to do a little bit of work to display the image on a form. You'll use an un-bound image control and set its path/filename using VBA code.
Because the images are not stored in the database, you'll need to make sure they are copied along with the database when deploying the solution.
These limitations are manageable and, considering the alternatives, this will be the optimum choice in almost any environment.
Adding a Picture to the Item Table
You'll now add images to the items in your database using the recommended approach I just described. You'll add a Text field to the Item table that will store the filename of the associated item.
TIP
This design will allow a single image for each item. If you needed to support two images – say, front and back cover – you would add two fields to store both filenames. If an undetermined number of images were required, you would create a child table and store the filenames there.
1. Open the Item table using the Design View.
2. Add a new field named Picture with a Data Type of Text. The default Field Size of 255 is fine. You can leave all the other default properties.
3. While you're here, add another field named URL and select the Hyperlink Data Type. You will use this field later in the chapter.
4. Save the table changes. The table design should look like Figure 9-2.
Figure 9.2. The updated Item table design
Modifying the Item Form
Now you'll add these two fields to the existing Item form so you can enter data for them. You will also add an Image control that will display the image on the form.
1. Open the Item form in the Layout View. Click the Add Existing Fields button in the Design tab of the ribbon. Drag the Picture field from the Field List pane to below the ReplacementCost control. An orange bar should appear indicating where the control will be added, as shown in Figure 9-3.
Figure 9.3. Dragging the Picture field onto the Item form
2. In the Other tab of the Property Sheet change the name of this control to txtPicture.
3. Merge the Picture control with the empty cell next to it.
4. In the same way, drag the URL field to just below the Picture control. After the row has been added, you may need to move the URL control and its label to the two leftmost cells on that row. Merge the URL control with the two empty cells next to it.
5. At the top-right corner there are six empty cells; merge these into one cell. This is where the image will be displayed.
6. In the Design tab of the ribbon, click the Image button, as shown in Figure 9-4. Then click on the large empty cell. After adding the Image control, a dialog will appear for you to select the image. Just cancel this dialog as this will be defined programmatically.
Figure 9.4. Clicking the Image button
WARNING
Just to the right of the Image button is another button labeled Insert Image. This is not part of the list of form controls. It is used to add a static image to your form, which I'll explain in Chapter 11.
7. In the Property Sheet, enter the name for this control as imgPicture.
8. Save the form changes. The updated form design should look like Figure 9-5.
Figure 9.5. The modified form layout
Displaying an Image
You have added a Text control to the form for entering the filename of the associated picture. You also have an Image control that will display this file. Now you'll need to connect the two controls with a little bit of VBA code.
ADDING THE CODE TO DISPLAY AN IMAGE
Switch to the Design View. In the Design tab of the ribbon, click the View Code button, which will create a code file for this form and display the VBA editor. Open the Main module and add code shown in Listing 9-1.
Example 9.1. Implementation of the GetFullImagePath Function
Function GetFullImagePath(ByVal sImage As String) As String
Dim sPath As String
Dim n As Integer
sPath = CurrentProject.FullName
n = InStrRev(sPath, "\", Len(sPath))
sPath = Left(sPath, n)
GetFullImagePath = sPath + "Images\" & sImage
End Function
This function gets the CurrentProject.FullName property, which specifies the full path and filename of the Access file that is being executed. It then strips off the filename to get just the path. It then adds the hardcoded Images subfolder and the image file name that was passed in as a parameter. The final string is provided to the caller as the return value.
With this implementation, you can deploy the application anywhere, as long as you put the images in an Images subfolder. Any place that needs to retrieve the images should use this function. If you decide later to change the location of the images, you only need to modify this function.
Now add the code shown in Listing 9-2 to the code file for the Item form.
Example 9.2. Implementation of the Item Form File
Private Sub Form_Current()
DisplayImage
End Sub
Private Sub txtPicture_LostFocus()
DisplayImage
End Sub
Private Sub DisplayImage()
If (Len(txtPicture) > 0) Then
On Error GoTo ErrorExit
imgPicture.Picture = GetFullImagePath(txtPicture)
Else
imgPicture.Picture = ""
End If
Exit Sub
ErrorExit:
imgPicture.Picture = GetFullImagePath("Static\NotFound.tif")
End Sub
This code implements an event handler for both the form's OnCurrent event and the txtPicture control's LostFocus event. The OnCurrent event is raised whenever a record is displayed on the form. If the filename is changed, the LostFocus event will also fire. Both of these event handlers call the DisplayImage method, which sets the Picture property of the Image control. If no picture is defined for the item, the Picture property is set to an empty string, which clears the control.
Because the images are stored outside of the database, it's a good idea to handle the situation where the specified filename is not valid. If an error occurs accessing the file, the On Error Goto ErrorExit statement moves the execution to the ErrorExit label. This displays a static image.
NOTE
Make sure you have an Images\Static subfolder that contains a NotFound.tif file, or you will generate an error trying to display the static image. You can download my Static folder from www.apress.com. I will cover that in more detail in Chapter 11.
LOADING THE IMAGE FILES
Now you'll need to set up a folder and store some image files. Then you will enter the filenames into your database.
1. Create an Images subfolder where your Access file is. If you have loaded your Item table with my rather eclectic list of items (or are using the downloaded database at the start of each chapter), you can download pictures of these items from www.apress.com. Otherwise, you should be able to get some images from the Internet.
2. Open the Item form using the Form View, which will display the first item.
3. Enter the filename for the associated picture. When you tab off this field, the picture should appear, as shown in Figure 9-6.
Figure 9.6. The Item form displaying a picture
4. Enter a filename for each of the other items so you'll have some data to test with later.
Implementing Item Search
Now you'll implement a form for searching the Item table. It will use a modal dialog like the CustomerSearch form that you implemented in Chapter 7. In the Form Header, you'll use a TabControl so you can implement multiple ways to search.
The Detail section will use the Continuous Form view so you can arrange the search results, including images. You'll simulate a record selector using conditional formatting.
Importing Item Data
It's difficult to test the search feature with just a handful of records. To load a larger sample of items, I have created an Access database with the entire list of titles published by Apress. Download this Item.accdb file from www.apress.com. I'll show you how to import these records into your Item table.
1. You'll need a new category for the items you're importing. Open the Category form in the Form View and add a new category, as shown in Figure 9-7.
Figure 9.7. Adding a category for technical books
2. From the External Data tab of the ribbon, click the Access button, which will display the dialog box shown in Figure 9-8.
Figure 9.8. Selecting the external Access database options
3. Browse to the Item.accdb file that you downloaded and select the first option, which is to import the data rather than link to it. The next dialog box, shown in Figure 9-9, allows you to select the objects that you want to import. Select the Item table.
Figure 9.9. Selecting to import the Item table
4. Click the OK button, which will start the import. When the confirmation dialog appears, just close it. This will have created a new table named Item1, because an Item table already existed. Now you'll create a query to copy the records from Item1 to Item.
5. From the Create tab of the ribbon, click the Query Design button. Select the Item1 table and close the Show Table dialog box. Click the Append button in the ribbon to change this to an append query. This will display the Append dialog box to select the table to be appended to. Select the Item table, as shown in Figure 9-10.
Figure 9.10. Appending to the Item table
6. Double-click each of the columns in the Item1 table, except for ItemID and LostFee. You won't specify a value for ItemID because this is an AutoNumber field and the database will assign unique IDs for you. Also, the LostFee column is calculated based on the ReplacementCost, so you'll let the data macro handle that for you. The completed query design should look like Figure 9-11.
Figure 9.11. The completed append query
TIP
The records in the Item1 table have the CategoryID set to 6. It's possible that your Technical Books category has a different ID. If so, instead of using the value from the Item1 table, in the Field row, replace CategoryID with an expression that has the appropriate value for your database.
7. Run the query and you should see a confirmation dialog, shown in Figure 9-12, telling you that 1,344 records were added to the Item table.
Figure 9.12. Confirmation of the records added
8. Close the query without saving it. Delete the Item1 table that was imported. Open the Item form in the Form View and page down through some of the new records. You should see the Image Not Found graphic, because the referenced file is not in your Images folder. You can also download the image files from www.apress.com.
Designing the Search Form
You'll start by designing the layout of the ItemSearch form. You'll use the Modal Dialog template and then place controls on the Form Header and Detail section.
1. From the Create tab of the ribbon, click the More Forms button and then click the Modal Dialog link. Right-click the form and click the Form Header/Footer link.
2. Cut and paste the OK and Cancel button from the Detail section to the Form Footer. Shrink the Detail section to about 1-inch high and expand the Form Header to be about 1½-inch high.
3. Click the Tab Control button in the Design tab of the ribbon then draw a rectangle that takes up the entire Form Header.
4. Change the Name and Caption properties on the first page to Basic. The Name and Caption properties of the second page should be Advanced.
5. Add a TextBox control at the top of the Form Header. Delete the associated label that is generated. Change the Name property to txtSelectedItemID and set the Visible property to No. This control will be used to keep track of which item in the Detail section has been selected.
The Form Header should look like Figure 9-13.
Figure 9.13. The initial Form Header layout
DESIGNING THE BASIC SEARCH PAGE
The basic search feature will accept a single keyword or phrase. You'll provide a ComboBox control to select which fields should be searched in such as Author, Title, or Description. You'll provide another ComboBox control to select which media types should be included.
1. Add a TextBox control to the Basic page (make sure the page is selected before you add the TextBox). Set the Name property to txtKeywords and set the Caption of the associated label to Keywords.
2. Select the txtKeywords control and its associated label. From the Arrange tab of the ribbon, click the Stacked button. This will create a layout control with two columns. Click the Insert Right button three times to create a total of five columns. Click the Insert Below button to add a second row.
3. Select the txtKeywords control and the two columns to the right and merge them into one cell.
4. Make sure the control wizards are turned on by clicking the dropdown icon to see all the form controls. The Use Control Wizards icon should be highlighted.
5. Click the Combo Box button and then click the Basic page. In the first Combo Box Wizard dialog box, select the second option as shown in Figure 9-14.
Figure 9.14. Selecting the option to specify the combo options
6. In the next dialog box, enter the following values, as shown in Figure 9-15.
<Any field>
Author
Title
Description
Figure 9.15. Specifying the allowed values
7. In the final dialog box enter Look In as the label text. Set the Name property of this control to cbField. In the Data tab, set the Default Value property to "<Any field>." Set the Limit To List property to Yes. Set the Allow Value Lists Edits to No. Drag the control to the second cell of the bottom row.
8. Add another ComboBox control to the Basic page. This time, in the Combo Box Wizard, select the first option, which is to get the values from a table or query.
9. In the second dialog box, select the Media table, as shown in Figure 9-16.
Figure 9.16. Selecting the Media table for the combo box source
10. In the third dialog box, shown in Figure 9-17, select the MediaID and MediaDescription columns to be included.
Figure 9.17. Selecting the columns to be included
11. In the fourth dialog box, shown in Figure 9-18, select the MediaDescription field for the sort option.
Figure 9.18. Sorting by the MediaDescription column
12. The fifth dialog box, shown in Figure 9-19, shows a preview of what the Combo Box will look like. Leave all the default settings.
Figure 9.19. The Combo Box preview
13. In the final dialog box enter Include as the label text. Set the control's Name property to cbMedia. In the Data tab, set the Default Value property to 0. Set the Allow Value Lists Edits to No. Drag the control to the fourth cell of the bottom row.
This cbMedia Combo Box will allow you user to select one of the media types. You also want to provide an option to include all of the media types in the search, which will require another row in the dropdown list. To do that you'll modify the query that populates this control.
1. Select the cbMedia control and in the Data tab of the Property Sheet, select the Row Source property. Click the ellipses, which will launch the Query Builder. Select the SQL View. Replace the existing SQL with the following code:
SELECT [Media].[MediaID], [Media].[MediaDescription] FROM [Media]
UNION SELECT 0, "<All media>" FROM [Media]
ORDER BY [MediaDescription];
TIP
This SQL uses a UNION clause to add an additional hard-coded value to the values supplied by the Media table. The value 0, with a description of <All media>, will indicate that the search should look in all media types.
2. Close the Query Builder and click the Yes button when prompted to update the property.
3. Select all the labels and set the Text Align property to Right.
4. Add a command button to each of the cells in the far-right column. Just cancel the control wizards when they launch. For the top button, enter BasicSearch for the Name property and Search for the Caption. For the lower button, enter the BasicClear for the Name property and Clear for the Caption.
Save the form and enter the name ItemSearch when prompted. The layout of the Form Header should look like Figure 9-20.
Figure 9.20. The layout of the Form Header
NOTE
To add other search methods, such as the Advanced tab, you would add unbound controls for specifying the search criteria. You then implement a Search button that formats a filter based on those criteria. I will leave the implementation details for you to work out on your own.
DESIGNING THE DETAIL SECTION
Now you'll design the results section of the ItemSearch form. It will use the Continuous Form View, so all of the controls that you place here will be repeated for each record returned by the search. Just like with the CustomerSearch form you created in Chapter 7, the detail section will contain data-bound controls. You will limit the records that are displayed with a filter that is generated based on the specified search criteria.
1. In the Property Sheet, select the Form object and the Data tab. For the Record Source property select the Item table.
2. Set the following form properties:
Filter On Load: Yes
Allow Additions: No
Allow Deletions: No
Allow Filters: No
Allow Edits: Yes (this is needed to be able to enter the search criteria)
Default View: Continuous Forms
Record Selectors: No
Navigation Buttons: Yes
Close Button: No
Filter: [ItemID] = CLng('0') (This will prevent items from displaying until the search criteria is entered.)
3. In the Design tab of the ribbon, click the Add Existing Fields button. Double-click the ItemID and Picture fields to add them to the Detail section. Delete their associated labels. Prefix the Name property of both of these controls with "txt." For example, the Name property of the Picture control should be txtPicture. Set the Visible property to both of these controls to No. Drag both of these controls to the top-left corner of the Detail section.
4. Click the Add Existing Fields button in the ribbon. Double-click the following fields to add them to the form and then delete the associated labels:
Author
Title
Description
5. In the Other tab of the Property Sheet, change the Name property for all three controls to prefix them with "txt." For example, txtAuthor.
6. Select all three of these controls and set their Locked property to Yes to prevent the user from modifying these fields.
7. With these controls still selected, from the Arrange tab of the ribbon, click the Stacked button to create a layout control. Click the Insert Left button to add a column to the left of the existing column. Merge all three cells of the left column into one cell.
8. Select the entire layout, click the Control Padding button, and then click the None link. This will remove the spaces between the controls.
9. Drag the layout to the top-left corner of the Detail section.
10. Add an Image control to the left cell of the layout. Set the Name property of this control to imgPicture. In the Data tab, for the Control Source property enter =GetFullImagePath([txtPicture]). This binds the Picture property of the Image control to the txtPicture control that specifies the filename. The layout should look like Figure 9-21.
Figure 9.21. The layout of the Detail section
11. Right-click the Cancel button and click the Build Event link. This should display the Macro Designer. The existing macro calls the Close Window action. Change the Save parameter from Prompt to No. Save the macro changes and close the Macro Designer.
12. Select the OK button. In the Event tab of the Property Sheet, change the On Click property from Embedded Macro to Event Procedure. Change the Name property to Close.
ADDING THE VBA CODE
In the Design tab of the ribbon, click the View Code button, which will generate a code file for this form and display the VBA Editor. Enter the code shown in Listing 9-3.
Example 9.3. Initial Implementation of the ItemSearch Form
Private Sub Close_Click()
If (Me.CurrentRecord = 0) Then
MsgBox "Please select an item first", vbExclamation, "No Item Selected"
Else
Me.Visible = False
End If
End Sub
Private Sub Form_Current()
txtSelectedItemID = Me.ItemID
End Sub
Private Sub BasicSearch_Click()
Dim s As String
Dim sFilter As String
If (IsNull(Me.txtKeywords) Or Len(Me.txtKeywords) <= 1) Then
MsgBox "Please enter a keyword", vbExclamation, "No Keyword Specified"
Else
s = "*" + Me.txtKeywords + "*"
sFilter = "(([Author] Like '" + s + "' And ([Forms]![ItemSearch]![cbField] " & _
"= '<Any field>' Or [Forms]![ItemSearch]![cbField] = 'Author'))" & _
"Or ([Title] Like '" + s + "' And ([Forms]![ItemSearch]![cbField] " & _
"= '<Any field>' Or [Forms]![ItemSearch]![cbField] = 'Title'))" & _
"Or ([Description] Like '" + s + "' And ([Forms]![ItemSearch]![cbField] " & _
"= '<Any field>' Or [Forms]![ItemSearch]![cbField] = 'Description')))" & _
"And ([MediaID] = [Forms]![ItemSearch]![cbMedia] Or " & _
"[Forms]![ItemSearch]![cbMedia] = CLng('0'))"
DoCmd.ApplyFilter "", sFilter, ""
End If
End Sub
Private Sub BasicClear_Click()
txtKeywords.Value = Null
cbField = "<Any field>"
cbMedia = cbMedia.DefaultValue
End Sub
The Cancel button simply closes the form. However, the event handler for the OK button (implemented in the Close_Click method) merely hides the form. This allows the calling form to retrieve the selected item. This is the same way that you implemented the CustomerSearch form.
In a Continuous Form, each record is displayed as a mini form which is repeated as many times as necessary. When you click on a record, that specific "form" becomes the current one and the OnCurrent event is raised. The Form_Current event handler takes advantage of this and captures the ItemID of the selected record.
The BasicSearch_Click method is called when the Search button is clicked. It builds a filter string based on the input criteria and then calls the ApplyFilter method. Unlike the CustomerSearch form, it is assumed here that the only partial values are entered so the keyword is automatically prefixed and suffixed with the "*" wildcard character. The BasicClear_Click method clears the txtKeywords control and restores the default value for the ComboBox controls.
Testing the Search Function
Now you're ready to try it out. Save the code file and the form changes, then switch to the Form View. Enter a keyword or phrase and click the Search button. The form should look like Figure 9-22.
Figure 9.22. The ItemSearch dialog
Using Conditional Formatting
You probably noticed that you can't tell which item is selected. You could turn on the Record Selectors property, which will display a black arrow next to the selected item. However, I'll show you another way to highlight the selected record, using the Conditional formatting feature.
1. Open the ItemSearch form in the Design View. In the Detail section, right-click the txtAuthor control and click the Conditional Formatting link as shown in Figure 9-23.
Figure 9.23. Selecting the Conditional Formatting link
2. This will display the Condition Formatting Rules Manager, shown in Figure 9-24. The txtAuthor control is already selected, and there are no rules currently applied to this control. Click the New Rule button, which will display the New Formatting Rule dialog box.
Figure 9.24. The Conditional Formatting Rules Manager
3. Select the first option since you'll be using an expression and change the combo box to Expression Is. For the expression enter the following code. The txtSelectedItemID control is in the Form Header and the OnCurrent event handler updates this to store the ID of the item that is currently selected. The expression uses this to return True if the record being displayed has the same ID as the selected record:
[txtItemID] = [txtSelectedItemID]
4. Finally, you need to specify in the rule what to do when the expression is true. Click the Background Color dropdown and select a background. This will be used when displaying the selected record. The completed rule should look like Figure 9-25.
Figure 9.25. The New Formatting Rule dialog box
5. Click the OK button to close the dialog box. The Conditional Formatting Rules Manager will now show the rule that you just added.
6. Click the Apply button to save this rule.
7. Select the txtTitle control to see the rules defined for it, which should be none. Click the New Rule button and create the exact same rule that you did for the txtAuthor field.
8. In the same way, create a rule for the txtDescription control. All three controls should have the same rule.
9. Click OK to close the rules manager and then save the form changes.
Open the ItemSearch form using the Form View and search for some items. The selected item should be displayed in a different background, as shown in Figure 9-26.
Figure 9.26. The ItemSearch form with conditional formatting
Invoking the ItemSearch Form
Now you'll modify the Item form to use the ItemSearch form to select an item. This is a fairly simple matter of adding a Search button to the Form Header that loads the ItemSearch Form. This will work just like the customer search feature you implemented in Chapter 7.
1. In the Property Sheet, select the Form object and the Data tab. For the Filter property, enter [ItemID] = CLng('0'). Also set the Filter On Loan property to Yes. This will keep the form from loading the entire list of items. Instead, it will only load the item selected from the ItemSearch form.
2. Open the Item form using the Layout View. The layout control for the Form Header has two controls; an Image control that displays a logo and a Label that displays the form title. Select the Label control and, from the Arrange tab in the ribbon, click the Split Horizontally button. This will split the cell into two cells, leaving an empty cell on the right.
3. Create a command button and put it in the empty cell. Cancel the control wizard. Enter the Caption property as Search... and enter Search for the Name property. Resize the cells so the Form Header looks like Figure 9-27.
Figure 9.27. The modified Form Header with a Search button
4. Right-click the Search button and click the Build Event link. In the Choose Builder dialog box select Code Builder. This will create an event handler for the OnClick event and open the VBA Editor. Enter the code shown in Listing 9-4.
Example 9.4. Implementation of the Search_Click Method
Private Sub Search_Click()
Dim sForm As String
Dim ID As Integer
sForm = "ItemSearch"
' Open the search form
DoCmd.OpenForm sForm, acNormal, , , , acDialog
' If the form is not loaded, the user clicked the Cancel button
If (IsLoaded(sForm)) Then
ID = Forms(sForm)!txtSelectedItemID
DoCmd.Close acForm, sForm
DoCmd.ApplyFilter "", "[ItemID] = CLng(" & ID & ")", ""
End If
End Sub
Private Sub Form_Load()
DoCmd.ApplyFilter "", "[ItemID] = CLng(0)", ""
End Sub
This code first opens the ItemSearch form. Because it is a modal dialog, the call does not return until the form is closed or hidden. It then checks to see if the form is still loaded. If it is, it uses the txtSelectedItemID control to get the ID of the selected item. It then applies a filter that returns only the selected item.
This code also provides the implementation for the Form_Load method. Because the search feature is manipulating the Filter property, Access tries to remember the last filter that was applied. This code ensures that the initial filter is used when the form is started.
Open the Item form using the Form View. You should notice that no record is displayed. Click the Search button, enter a search, select one of the items, and click the OK button. The ItemSearch form should disappear and the selected item displayed in the Item form, as demonstrated in Figure 9-28.
Figure 9.28. The Item form showing the selected item
Enhancing the Item Form
There are a few more enhancements that I want to show you. You may have noticed that there are no inventory items for the item selected in Figure 9-28. The item has been set up in the database and the author, title, and other details have been defined. But you don't actually have a copy of it in your inventory to lend out. You'll add a facility to this form to receive a copy of the item and add it to the inventory.
There is also a URL set up for this item where additional information can be obtained. You'll add an embedded web browser to this form and bind it to this URL. This will cause the browser to automatically display that web page.
Finally, before we finish the chapter, I'll show you how to add the item image to the Item On Loan tab of the CustomerAdmin form. You implemented this in the previous chapter and left a place on the form to display an image. You'll add that to finish this page.
Adding an Inventory Item
Adding an inventory item is actually really easy to do. In Chapter 4 you created a query called AddInventoryItem, which inserts a record into the InventoryItem table. It requires an ItemID parameter so it knows which item to add. You'll now add a command button to the Item form to run that query. For a little variety, you'll implement the logic behind the button with a macro.
1. Open the Item form using the Layout View. Add a command button to empty cell just to the right of the txtPicture control. Cancel the wizard when it starts. Set the Name properties to AddInventory and the Caption property to Add Inventory.
2. Right-click this control and click the Build Event link. In the Choose Builder dialog, select Macro Builder, which will start the Macro Designer. This macro will perform three actions:
Suppress the normal Access warnings about records being updated
Run the query
Re-query the subform that lists the inventory items so it will refresh its contents
3. The "Add New Action" dropdown list shows the actions that can be added to the macro. Some actions are restricted if the document is not trusted. The SetWarnings action is one of these. To be able to select this action, you must first click the Show All Actions button in the ribbon, as shown in Figure 9-29.
Figure 9.29. The "Show All Actions" button
4. In the "Add New Action" dropdown list, select the SetWarnings action. Make sure the Warnings On parameter is set to No.
5. For the next action, select the OpenQuery action. For the Query Name parameter, select the AddInventoryItem query. This will display the itemID parameter. Enter [ItemID] for its value.
6. For the last action, select Requery and enter InventoryItem for the Control Name parameter.
The final macro design should look like Figure 9-30.
Figure 9.30. The AddInventory macro design
Save the macro and close the Macro Designer. To test this, open the Item form using the Form View, select an item and then click the Add Inventory button. A new record should be added to the subform, as demonstrated in Figure 9-31.
Figure 9.31. A new inventory item added to the subform
Adding a Web Browser
Now you'll add a WebBrowser control to the Item form. You'll use the URL control to automatically navigate to the associated web page.
1. Open the Item form in the Layout View. You'll need to first create a cell to drop the browser in. Select the InventoryItem subform, because it's on the last row. In the Arrange tab of the ribbon, click the Insert Below button add a new row.
WARNING
If the Insert Below button is not enabled, it's probably because you clicked inside the subform, selecting one of its cells. Try clicking on the border of the subform. The Property Sheet will indicate if you have selected the InventoryItem subform.
2. The bottom row will have two cells, merge these together into one. Expand the height of the cell to be about 5 inches.
3. In the Design tab of the ribbon, click the Web Browser Control button, as shown in Figure 9-32.
Figure 9.32. Clicking the Web Browser Control button
4. Then select the large empty cell that you just created. Cancel the control wizard when it starts. In the Property Sheet set the name of this control to webBrowser.
5. Now you'll add a couple of event handlers. Select the webBrowser control. In the Event tab of the Property Sheet, for the On Navigate Error property, select Event Procedure and click on the ellipses. Enter the following code for the implementation. This simply navigates the web browser to a home page should the specified URL be invalid.
webBrowser.Object.Navigate http://apress.com
6. While in the VBA Editor, add the following code to the Form_Current method:
If (Len(URL) > 0) Then
webBrowser.Object.Navigate URL
webBrowser.Visible = True
Else
webBrowser.Visible = False
End If
This causes the browser to navigate to the address specified by the URL field. If a URL is not specified, the browser is hidden.
There is no place to type a URL so the user can't navigate somewhere else. However, links on the page could take them to other sites. If you want to control where they are navigating to, implement an event handler for the OnBeforeNavigate event. To do so, in the Property Sheet, select the On Before Navigate property, select Event Procedure and then click the ellipses. Add the following code for this event's implementation.
If (Left(CStr(URL), 17) <> "http://apress.com") Then
Cancel = True
End If
This code will prevent navigating to any URL that doesn't start with "apress.com."
NOTE
You might want to comment out this code and not leave it in your application. It is here for demonstration purposes only.
Open the Item form in the Form View and search to one of the Apress titles. The embedded web browser should display the associated page on the Access site, as demonstrated in Figure 9-33.
Figure 9.33. The Item form with the web browser enabled
Using Page Breaks
Now that you've added the web browser, the Item form has become too large to display all of it at once. Of course you can scroll down to see the rest of the form. As an alternative, you can insert page breaks in the form and then navigate between pages by using buttons.
1. Open the Item form using the Design View. From the Design tab of the ribbon, click on the Page Break button, as shown in Figure 9-34.
Figure 9.34. Clicking the Page Break button
2. Then click on the Item form, just above the webBrowser control. You should see a series of dots, shown in Figure 9-35, which indicates a page break.
Figure 9.35. The page break indicator
3. Now you'll add buttons to the Form Footer that the user can use to navigate to each page. Click the Button button in the ribbon and then click on the Form Footer. Cancel the control wizard. Set the Name property to Page1 and the Caption property to Details. Right-click the control and click the Build Event link. In the Choose Builder dialog box, select the Macro Builder.
4. In the Macro Designer, select the GoToPage action and enter the Page Number parameter as 1. The macro should look like Figure 9-36. Save and close the Macro Designer.
Figure 9.36. Implementing the Page1 button
5. In the same way, add another button named Page2 and set the Caption property to Browser. Implement the button with a macro, setting the Page Number parameter to 2.
6. Now you'll need to arrange the buttons in a layout control. The Form Footer should look like Figure 9-37.
Figure 9.37. The layout of the Form Footer
Save the form changes and switch to the Form View. You will have buttons at the bottom of the form that you can use to easily switch between pages as shown in Figure 9-38.
Figure 9.38. The Item form with page buttons
Modifying the CustomerLoan Form
The last thing you'll need to do is modify the CustomerLoan form and add an image control.
1. Open the LoanDetail query in the Design View. Double-click the Picture field in the Item table to add this column to the query. Save the query and then close it.
2. Open the CustomerLoans form in the Layout View. Click the Add Existing Fields button in the ribbon.
3. Drag the Picture field to the empty cell underneath the Renew button. This will insert a new column for the associated label. Right-click the label and click the Delete Column link.
4. Change the Name property of the Picture control to txtPicture. Set the Visible property to No.
5. In the Design tab of the ribbon, click the Image button and then click in the Detail section. Cancel the control wizard. Drag this control to the empty cell on the right side of the form. Set the Name property to imgPicture.
6. In the Data tab of the Property Sheet, set the Control Source property to =GetFullImagePath(txtPicture).
7. Save the form.
To test the change, open the CustomerAdmin form using the Form View, select a customer and select the Item On Loan tab. The form should look like Figure 9-39.
Figure 9.39. The modified CustomerAdmin form
Summary
In this chapter you made some significant enhancements to the Item form, including:
Adding images to the item data
Providing a search facility to look for items in the database
Embedding a web browser for additional item information
Some of the other Access techniques that you learned include:
Importing tables from another Access database
Using conditional formatting
Calling an action query from a command button
Using page breaks
In the next chapter, you will make changes to this application to prepare it for the end users. This will include creating a navigation form and locking down the design and development features.
Chapter 10. Enhancing the User Experience
At this point, you have developed a well-functioned application and you're ready to turn it over to the end users... well, not quite yet. When you open the Access file, there are lots of neat things like tables and queries, macros, and VBA – all the sorts of items that we developers like to work with. Most end users are not going to want to see this. More important, if you have to support it, you don't want them seeing it either, much less have the ability to change any of it.
In this chapter, I'll show you some ways to package the application with the end user in mind. The first step is to provide a way for them to navigate to the forms that they will be using. I'll demonstrate two ways to accomplish this using a custom navigation form and a custom ribbon. With that in place, you'll then need to remove the standard navigation and lock down the application to prevent unwanted alterations.
Form Navigation
When the application is first loaded, you will need some sort of "welcome" form that will present to the user their choices of things they can do such as check out a customer, look for an item, and so on. Access 2010 provides a really nice facility for creating a navigation form by simply dragging the forms and reports to the appropriate navigation structure. However, the complex forms you have developed so far will not work with this technique. Essentially, the navigation form becomes the main form and all your other forms are added as subforms. As I explained in Chapter 8, there are limitations with subforms. For example, you cannot use a Split Form as a subform.
NOTE
I will demonstrate the built-in Navigation Form template in Chapter 15. You will use this to organize the web forms.
So you'll need to create your own custom navigation form. Fortunately, this is simple to do – almost surprisingly so. The hardest part is usually deciding what options should be allowed and how to best organize them. Although there is no right way to organize these, I suggest the following structure:
Operations (tasks that you do all day long)
Checkout a customer
Set up a new customer or view/modify an existing one
Lookup an item
Administration (updating configurable objects)
Items
Categories
Media types
Maintenance (routine tasks for ongoing support and maintenance)
Calculate late fees
Cancel old requests
Creating the Menu Form
You'll create a blank form and place command buttons on it. You can take advantage of the control wizards that will set up a macro to open the associated form when the user clicks a command button. You will use a TabControl to organize the buttons into the three top level items (Operations, Administration, and Maintenance).
1. Click the Blank Form button in the Create tab of the ribbon.
2. In the Design tab of the ribbon, click the Tab Control button, and then draw a rectangle on the form. This will create a TabControl with two pages. Right-click this control and click the Insert Page link. Select each page and set the Name property of each to Operations, Administration, and Maintenance. The form should look like Figure 10-1.
Figure 10.1. The initial Menu form layout
3. In the Property Sheet, select the Form object and set the Record Selectors and Navigation Buttons properties to No. These are not applicable for this form, because it does not access a table.
TIP
Make sure that the control wizards are turned on. To check, click the dropdown icon near the bottom-right corner of the Controls section of the ribbon. This will show all of the available controls. The icon next to the Use Control Wizards icon should be highlighted as shown in Figure 10-2. If not, click the Use Control Wizards link to turn them on.
Figure 10.2. Checking the status of the control wizards
4. Click the Button button and then click the Operations page. This will launch the Command Button Wizard. In the first dialog box, select the OpenForm action as shown in Figure 10-3.
Figure 10.3. Selecting the OpenForm action
5. In the second dialog box, select the CheckOut form, as shown in Figure 10-4.
Figure 10.4. Selecting the CheckOut form
6. The third dialog box, shown in Figure 10-5, provides an option to filter the record when opening the form. This doesn't apply in this scenario, so select the second option, which is to show all records.
Figure 10.5. Selecting the option to show all records
7. The fourth dialog box is used to specify the text or picture that should be displayed on the button. Select the Text option and enter Check Out, as shown in Figure 10-6.
Figure 10.6. Specifying the button caption
8. In the final dialog box, enter the name CheckOut, as shown in Figure 10-7.
Figure 10.7. Specifying the name of the CheckOut button
9. You should now have a button on the Operations page that will open the CheckOut form. Repeat this process to add buttons that will open the CustomerAdmin and Item forms. The page design should look like Figure 10-8.
Figure 10.8. The Operations page layout
10. Using the same approach, add buttons to the Administration page to open the following forms:
Item
Category
Media
On the Maintenance page, you'll create buttons that will call a macro. There are two macros included in the Navigation pane: CalculateLateFees and CancelOldRequests. You created these in Chapters 3 and 4 to demonstrate how to call a data macro and an action query. Now you'll provide buttons in the Menu form to allow the user to call these.
11. Add a command button to the Maintenance page. In the Command Button Wizard, select the RunMacro action, which you'll find in the Miscellaneous category, as shown in Figure 10-9.
Figure 10.9. Selecting the RunMacro action
12. In the next dialog box, select the CalculateLateFees macro, as shown in Figure 10-10.
Figure 10.10. Selecting the CalculateLateFees maco
13. Fill out the rest of the dialog boxes as you did for the other command buttons.
14. Repeat this process to create another button that will call the CancelOldRequests macro.
15. The Maintenance page should look like Figure 10-11.
Figure 10.11. The completed Maintenance page
Save the form and enter the name Menu when prompted. Switch to the Form view. Try clicking the buttons and verify that the appropriate forms are loaded and that they work as expected.
Auto-Loading the Menu Form
The Menu form is a handy way for the user to start any of the forms designed for them to access. Now you'll configure Access to load this form automatically when the application is started.
1. Go to the File tab of the ribbon, which displays the Backstage View.
2. Click the Options button to display the Access Options dialog box.
3. Select the Current Database tab and select the Menu form in the Display Form combo box as shown in Figure 10-12. This tells Access to load the Menu form whenever this file is opened.
Figure 10.12. Selecting the Menu form to be auto-loaded
When you save the changes, you'll get the pop-up dialog shown in Figure 10-13, which lets you know that this change will take affect the next time the file is loaded.
Figure 10.13. Reminder to close and reopen the database
Close the Access application and re-open the Library.accdb file. You should see the Menu form start automatically.
Ribbon Navigation
Another approach to providing navigation to your forms is to customize the ribbon. You can add a tab to the existing ribbon or create your own custom ribbon, with an XML file and a little bit of VBA code. I'll first demonstrate how to add a new tab to the ribbon, and then I will show you how to build your own data-driven menu using a custom ribbon.
Implementing a Sample Ribbon Tab
A sample XML file that creates a new ribbon tab is shown in Listing 10-1.
Example 10.1. Sample Script to Create a Custom Ribbon Tab
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon startFromScratch="false">
<tabs>
<tab id="demo1" label="Demo Tab">
<group id="group1" label="Sample">
<button id="button1" size="large" label="Sample1"
screentip="Sample 1" supertip="This is a sample button"
getImage="GetImage" onAction="OnMenuAction" />
<button id="button2" size="normal" label="Sample2"
screentip="Sample 2" supertip="This is a sample button"
getImage="GetImage" onAction="OnMenuAction" />
<button id="button3" size="normal" label="Sample3"
screentip="Sample 3" supertip="This is a sample button"
getImage="GetImage" onAction="OnMenuAction" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
The ribbon element has a single attribute called startFromScratch. If this is set to false, as it is here, the defined tabs are added to the existing ribbon. Use this option if you want to add a custom tab but leave the standard tabs in place. If you set this to true, all the existing tabs will be removed and the ribbon will only contain whatever is defined in this XML script.
The ribbon element contains a tabs collection, which has a single tab labeled "Demo Tab." Each tab then defines one or more group elements. This script has a single group labeled "Sample," which contains three button elements.
Each button element specifies the following attributes:
id: A unique identifier for this button
size: The size of the graphic for this button (either large or normal)
label: The text that is displayed with the button
screentip: Heading for the hover text
supertip: The hover text that is displayed when the mouse is over this control
getImage: The name of a callback function that supplies the image for this button
onAction: The name of a callback function that is called when the button is clicked
tag: Not shown here, but can be used for providing details to the callback functions
To add this custom tab to your ribbon, you'll need to implement the two callback functions, GetImage and OnMenuAction. Then you will also need to install this ribbon in the database.
At the bottom of the navigation pane, you should see the Main module. Double-click this to display the VBA editor. Add the code shown in Listing 10-2 to the Main code file.
Example 10.2. The Initial Implementation of the Ribbon Callback Functions
Public Sub GetImage(ByVal control As Office.IRibbonControl, ByRef image)
image = "HappyFace"
End Sub
Public Sub OnMenuAction(ByVal control As Office.IRibbonControl)
MsgBox "You've clicked the button " & control.ID & " on the Ribbon"
End Sub
The GetImage callback returns the HappyFace icon, which I'll explain later. The OnMenuAction callback simply displays the ID of the control that was clicked.
NOTE
You will need to add a reference to the Office Object Library. In the VBA Editor, click the Tools menu and then the References link. In the References dialog box, add the Microsoft Office 14.0 Object Library, as shown in Figure 10-14.
Figure 10.14. Adding the object library reference
Now all that's left is to install the XML file. I'll show you one way to do that now, and later in this chapter, I'll show you a second way. In the first approach, you'll create a table named USysRibbons and copy the XML into this table. When a database is opened, Access looks for the USysRibbons table and will automatically install any ribbons that are defined there.
1. From the Create tab of the ribbon, click the Table Design button.
2. Add the following fields:
RibbonID: AutoNumber (set this as the primary key)
RibbonName: Text
RibbonXML: Memo
3. Save the table and enter the name USysRibbons when prompted.
4. Open the table in the Datasheet View. Enter Demo in the RibbonName field. Enter the XML shown in Listing 10-1 in the RibbonXML field and save the record.
The table should look like Figure 10-15.
Figure 10.15. The contents of the USysRibbons table
Close the Access database and then re-open it. Access should have loaded your custom ribbon, but now you need to tell Access to use it.
5. Click the File tab to display the Backstage View.
6. Click the Options button and then select the Current Database tab.
7. About halfway down, in the "Ribbon and Toolbar Options" section, select the Demo ribbon, as shown in Figure 10-16.
Figure 10.16. Selecting the Demo ribbon
Close the dialog and you will be reminded that you'll need to close the database and re-open it. Do that now and when the database is reloaded you should see a Demo tab like the one shown in Figure 10-17.
Figure 10.17. The custom Demo ribbon tab
Try clicking the buttons in the custom tab. You should see a pop-up window that tells you the name of the button that was clicked.
TIP
As you probably know, XML is not very forgiving. The slightest syntax error will prevent the file from loading. If Access encounters an error in processing the XML it will simply ignore your custom ribbon. You won't see any errors, but you won't have a custom tab. This can be frustrating when trying to find where the problem is. While developing and testing custom ribbons, you can enable these error messages. Click the Options button on the Backstage View. In the Client Settings tab, scroll down to the General section. Select the "Show add-in user interface errors" check box, as shown in Figure 10-18.
Figure 10.18. Enabling UI error messages
Displaying the System Objects
You may have noticed that, when you re-opened the database, the USysRibbons table was gone. It's not really gone, but merely hidden from the Navigation pane. Access has several system tables that it uses and these are normally hidden, that is, not included in the Navigation pane.
In addition, any table that you create that starts with "USys" is considered a user-system table and is also hidden. So when you first created the USysRibbons table it was available to you. However, when the database was re-opened, this was no longer visible.
To show both system and user-system tables, click the Options button in the Backstage View. Select the Current Database tab and click the Navigation Options button that is shown in Figure 10-19.
Figure 10.19. Displaying the Navigation Options
In the Navigation Options dialog, shown in Figure 10-20, you can control the types of objects that are included and how they are organized.
Figure 10.20. Enabling the system objects
Select the Show System Objects check box and click the OK button. Then click the OK button to close the Access Option dialog box. You'll see a pop-up saying the changes will not be applied until the database is reloaded. In this case, that's not true. The system tables are added to the Navigation pane immediately.
You should see the USysRibbons table in the Navigation pane and you can view and update its contents. You should also notice the USysApplicationsLog that you used in Chapter 3 to diagnose data macro errors.
Building a Custom Ribbon
Now that you have the basic concepts for building a custom ribbon, you'll develop a data-driven menu feature. This will allow you to design the menu options using a form and then generate the appropriate XML based on the data. As you add new options, the ribbon will automatically adjust.
As I explained earlier, a ribbon uses the following three-level hierarchy for organizing the controls:
Tabs
Groups
Commands
To accommodate this in your design, you'll have a table for each of these levels. You'll then create a form that will allow you to define each of these elements. Once the implementation is done and the data is entered, you'll write VBA code to extract the data and generate the corresponding XML script.
Designing the Tables
You'll start by creating three tables to define the three levels of the ribbon hierarchy. These tables are not really part of the Library data but are internal tables used by the application. To differentiate these, the name will have a "mnu" prefix. The instructions below are abbreviated; you can refer to Chapter 2 if you need more help in designing tables.
1. Create a mnuTab table using the following fields:
TabID: AutoNumber, primary key
Label: Text (100)
Sequence: Number (Integer)
2. Save the table and close it. Then create the mnuGroup table including the following fields:
GroupID: AutoNumber, primary key
TabID: Lookup (use the mnuTab table)
Label: Text (100)
Description: Text (255)
Sequence: Number (Integer)
NOTE
When setting up the Lookup column, select both the TabID and Label fields. Use the defaults values for the remaining dialog boxes.
3. Save the table and close it. Finally, create the mnuCommand table as follows:
CommandID: AutoNumber, primary key
GroupID: Lookup (use the mnuGroup table)
Label: Text (10)
Graphic: Text (10)
GraphicSize: Lookup (enter the allowed choices as large and normal)
TargetType: Lookup (enter the allowed choices as Form, Report, and Macro)
Target: Text (100)
ScreenTip: Text (100)
SuperTip: Text (255)
Sequence: Number (Integer)
NOTE
When setting up the GroupID lookup column, select both the GroupID and Label fields. Use the remaining default values.
4. Open the mnuTab table in the Datasheet view. Add a single record with the Label field Library and Sequence =1.
Creating the Menu Forms
You'll start by generating a form for the mnuCommand table. Then you'll use this as a subform on the mnuGroup form. Because the number of tabs is small, you'll just enter these in the mnuTab table directly.
1. Select the mnuCommand table in the Navigation pane and click the Form button in the Create tab of the ribbon.
2. Delete the CommandID and GroupID controls and their associated labels.
3. Set the control padding to None.
4. Set the Text Align property for all of the labels to Right.
5. Remove the Form Header.
6. Change the Default View to Continuous Forms.
7. Re-arrange the controls to look like Figure 10-21.
Figure 10.21. The design of the mnuCommand form
8. Save the form and enter the name mnuCommand when prompted. Now you'll create the mnuGroup form.
9. Select the mnuGroup table in the Navigation page and click the Form button in the Create tab of the ribbon.
10. This will create Datasheet view to display the associated records of the mnuCommand table. Delete this and add a Subform control in its place. The Subform Wizard will find multiple fields in both table with the same name and will prompt you to select the correct field to use for linking the forms. Select the GroupID field, as shown in Figure 10-22.
Figure 10.22. Selecting the GroupID field for linking the forms
11. Arrange the controls as shown in Figure 10-23.
Figure 10.23. The mnuGroup form layout
Save the form and enter the name mnuGroup when prompted.
Populating the Menu Tables
Now you'll use the mnuGroup form to define the controls that will be placed on the ribbon. You'll follow the same basic structure that you used for the Menu form that you created earlier in the chapter. There will be three groups (Operations, Administration, and Maintenance). The first group will look like Figure 10-24.
Figure 10.24. The Operations group definition
NOTE
You'll be able to see all of the controls on the ribbon, so there's no need to include a link to the Item form on both the Operations and Administration groups. You'll just include it on the Administration group.
The definition of the Administration group should look like Figure 10-25.
Figure 10.25. The Administration group definition
The Maintenance group definition will look like Figure 10-26.
Figure 10.26. The Maintenance group definition
Using MSO Images
You're probably wondering about the unusual values for the Graphic fields. I also promised to explain about "HappyFace." Well, I'll take care of both of those now. Microsoft provides literally hundreds of built-in images that are available for you to use in your application. Just about any icon that exists on a ribbon in any of the Office products is included in this image library as well as icons from many other Microsoft products.
To use them all you need to do is specify the image name. The trick is knowing the correct name for each of the images. The best way to do that is to download a Word document from www.microsoft.com. Go to the following URL:
http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=2d3a18a2-2e75-4e43-
8579-d543c19d0eed
Click the Download button and save the file to your local machine. Then open the Word document and go to the Backstage View. There are two tabs that together provide a gallery of all of the built in images. Click the tabs to see the images and their names, as shown in Figure 10-27.
Figure 10.27. Sample mso image gallery
I made a quick scan of these images and picked some that seemed appropriate for the Library menu controls. Feel free to browse the gallery and choose different images. Just enter the appropriate image name in the Graphic field of the mnuGroup form.
Implementing the Custom Ribbon
Now that you have all of the ribbon elements defined (tabs, groups, and commands) you're ready to write the logic that reads this data and builds the appropriate ribbon XML. You'll start by designing a query that combines the three tables into a single record set. Then you'll execute this query in VBA code.
DESIGNING THE MNUCOMMANDS QUERY
In the Create tab of the ribbon, click the Query Design button. Add the following tables to the query:
mnuTab
mnuGroup
mnuCommand
The query should automatically setup the relationships between the tables. Add the following fields to the query:
mnuTab.TabID
mnuTab.Label
mnuTab.Sequence
mnuGroup.ID
mnuGroup.Label
mnuGoup.Description
mnuGroup.Sequence
mnuCommand.ID
mnuCommand.Label
mnuCommand.Graphic
mnuCommand.GraphicSize
mnuCommand.TargetType
mnuCommand.Target
mnuCommand.ScreenTip
mnuCommand.SuperTip
mnuCommand.Sequence
Uncheck the three Sequence fields so they are not returned in the result set and set the Sort property on these fields to Ascending. The Sequence fields are only included in the query to be used to sort the records.
There is a Label field from all three tables. To avoid ambiguity, you'll give then unique names in the result set. For the mnuTab.Label field, enter the Field name as TabLabel: Label. Likewise, for the mnuGroup.Label field, enter GroupLabel: Label.
Save the query and enter the name mnuCommands when prompted. The query design should look like Figure 10-28.
Figure 10.28. The mnuCommands query design
Switch to the Datasheet View and verify that there are seven rows returned.
GENERATING THE RIBBON XML
Now you'll write the VBA code that will generate the appropriate XML based on the configuration data. Open the Main code file and enter the code shown in Listing 10-3.
Example 10.3. The Implementation of GetRibbonDefinition
Function GetRibbonDefinition() As String
Dim txtXML As String
Dim nTabID As Integer
Dim nGroupID As Integer
nTabID = 0
nGroupID = 0
' Add the XML header info
txtXML = "<customUI xmlns=" & Chr(34) & _
"http://schemas.microsoft.com/office/2009/07/customui" & Chr(34) & ">" & vbCrLf & _
" <ribbon startFromScratch=" & Chr(34) & "false" & Chr(34) & ">" & vbCrLf & _
" <tabs>" & vbCrLf
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("mnuCommands")
Do Until rs.EOF
If (rs.Fields("GroupID") <> nGroupID And nGroupID <> 0) Then
' Close the previous group
txtXML = txtXML & " </group>" & vbCrLf
End If
If (rs.Fields("TabID") <> nTabID And nTabID <> 0) Then
' Close the previous tab
txtXML = txtXML & " </tab>" & vbCrLf
End If
If (rs.Fields("TabID") <> nTabID) Then
' Save the current tab ID
nTabID = rs.Fields("TabID")
' Open the next tab
txtXML = txtXML & " <tab id=" & Chr(34) & "tab" & nTabID & _
Chr(34) & " label=" & Chr(34) & rs.Fields("TabLabel") & _
Chr(34) & ">" & vbCrLf
End If
If (rs.Fields("GroupID") <> nGroupID) Then
' Save the current group ID
nGroupID = rs.Fields("GroupID")
' Open the next group
txtXML = txtXML & " <group id=" & Chr(34) & "group" & _
nGroupID & Chr(34) & " label=" & Chr(34) & _
rs.Fields("GroupLabel") & Chr(34) & ">" & vbCrLf
If (Len(rs.Fields("Description")) > 1) Then
txtXML = txtXML & " <labelControl id=" & Chr(34) & _
"label" & nGroupID & Chr(34) & " label=" & Chr(34) & _
rs.Fields("Description") & Chr(34) & " />" & vbCrLf
End If
End If
' Add a command button
txtXML = txtXML & _
" <button " & _
"id=" & Chr(34) & "button" & _
rs.Fields("CommandID") & Chr(34) & vbCrLf & _
" size=" & Chr(34) & rs.Fields("GraphicSize") & _
Chr(34) & vbCrLf & _
" label=" & Chr(34) & rs.Fields("Label") & _
Chr(34) & vbCrLf & _
" getImage=" & Chr(34) & "GetImage" & _
Chr(34) & vbCrLf & _
" screentip=" & Chr(34) & rs.Fields("ScreenTip") & _
Chr(34) & vbCrLf & _
" supertip=" & Chr(34) & rs.Fields("SuperTip") & _
Chr(34) & vbCrLf & _
" tag=" & Chr(34) & rs.Fields("Graphic") & _
Chr(34) & vbCrLf & _
" onAction=" & Chr(34) & "OnMenuAction" & _
Chr(34) & " />" & vbCrLf
rs.MoveNext
Loop
txtXML = txtXML & " </group>" & vbCrLf
txtXML = txtXML & " </tab>" & vbCrLf
txtXML = txtXML & " </tabs>" & vbCrLf
txtXML = txtXML & " </ribbon>" & vbCrLf
txtXML = txtXML & "</customUI>"
GetRibbonDefinition = txtXML
rs.Close
Set rs = Nothing
End Function
This code may look complicated, but it just executes the mnuCommands query and does a lot of string manipulation to construct a properly formed XML file. The structure of the XML is identical to the one I presented at the beginning of the chapter. Instead of hard-coded data, it is read from the query results and it allows for multiple tabs and groups.
The next step is to write code to load the ribbon. To do that, add another method to the Main module using the following code:
Public Function LoadRibbon() As Integer
On Error Resume Next
Application.LoadCustomUI "Library", GetRibbonDefinition
End Function
This code calls the LoadCustomUI method passing in a hard-coded ribbon name of Library and calling the GetRibbonDefinition method, which supplies the XML. At the beginning of the chapter you populated the USysRibbons table with a RibbonName and RibbonXML fields. Access reads this table on startup and does the same thing as the LoadCustomUI call. The LoadCustomUI method will fail if a ribbon with the same name already exists. The On Error Resume Next statement is used to ignore this error.
IMPLEMENTING THE CALLBACK METHODS
You already have an implementation for the two callback methods, GetImage and OnMenuAction. Now you'll need to replace that demo code with a real implementation. Replace these with the code shown in Listing 10-4.
Example 10.4. The Implementation of the Callback Methods
Public Sub GetImage(ByVal control As Office.IRibbonControl, ByRef image)
image = control.Tag
End Sub
Public Sub OnMenuAction(ByVal control As Office.IRibbonControl)
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("mnuCommand", dbOpenDynaset)
rs.FindFirst "[CommandID]=" & Mid(control.ID, 7)
If rs.NoMatch Then
MsgBox "You've clicked the button " & control.ID & " on the Ribbon"
Else
Select Case rs.Fields("TargetType")
Case "Form"
DoCmd.OpenForm rs.Fields("Target"), acNormal
Case "Report"
DoCmd.OpenReport rs.Fields("Target"), acViewPreview
Case "Macro"
DoCmd.RunMacro rs.Fields("Target")
Case Else
MsgBox "You've clicked the button " & control.ID & " on the Ribbon"
End Select
End If
End Sub
For the GetImage callback, instead of supplying a hard-coded image name, it uses the Tag property. If you look at the implementation of the GetRibbonDefinition method, you'll notice the tag attribute is set using the Graphic field from the mnuCommands query.
The implementation of OnMenuAction uses the ID property to determine which button was clicked. The id attribute was generated using the CommandID field prefixed with the "button" text. This prefix is stripped off and then the mnuCommand table is queried using the CommandID. This method then calls either OpenForm, OpenReport, or RunMacro depending on the TargetType column. The Target column contains the name of the form, report or macro that should be used. If the mnuCommand record was not found or is not one of the supported types, this method just displays the button ID.
USING THE AUTOEXEC MACRO
Now you'll need to call the LoadRibbon method when the database is opened. Access provides an easy way to do that by using the autoexec macro. If you create a macro with that exact name, autoexec, Access will run that for you when the database is opened. You can then put whatever startup logic you need inside this macro.
From the Create tab of the ribbon, click the Macro button, which will display the Macro Designer. Select the RunCode action. Enter LoadRibbon() for the Function Name parameter.
WARNING
Make sure that you include the parentheses. These are needed even if there are no parameters being passed to the function.
Save the macro and enter the name autoexec when prompted. The macro should look like Figure 10-29.
Figure 10.29. The autoexec macro
Now try running this macro. You can click the Run button in the Design tab of the ribbon or double-click it in the Navigation pane. You won't notice it doing anything. You can add a breakpoint to the GetRibbonDefinition method if you want to step through this code.
SELECTING THE LIBRARY RIBBON
At this point the Library ribbon has been installed as well as the Demo ribbon that you added to the USysRibbons table. As before, you have to tell access to use this ribbon before it is visible.
Click the Options button in the Backstage View. On the Current Database tab, expand the Ribbon Name combo box and select the Library ribbon as shown in Figure 10-30.
Figure 10.30. Selecting the Library ribbon
The database is now configured to use the new Library ribbon when it is restarted.
NOTE
You should remove the existing Demo ribbon that you loaded earlier. Open the USysRibbons table and delete the row that is in this table.
Close the Access database and then re-open it. You should have a new Library tab that looks like Figure 10-31.
Figure 10.31. The new Library ribbon tab
Try clicking some of the buttons in the Library ribbon. This should open the associated form. If you hover the mouse over one of the buttons, the text you supplied for the ScreenTip and SuperTip fields should be displayed as demonstrated in Figure 10-32.
Figure 10.32. The custom hover text
TIP
I have shown you two ways to provide custom navigation; a custom form with buttons that use macros to launch a form and a custom ribbon. I'll show you a third way in Chapter 15, which is to use a Navigation Form. As I mentioned, it doesn't work for many of your existing forms. There is also a fourth approach, which is to use a Switchboard. In Access 2010 this feature is still available but they didn't make it easy to find. This is provided for legacy purpose so I didn't cover it but wanted you to know about, for completeness.
Locking Down the Database
You have provided two different ways for the user to navigate to the various forms that are available to them. Now that this is in place you can lock down the database by removing access to the developer-oriented functions. You'll do this in two phases: first by removing much of the menu and navigation. The user will then only see the items that they should be using. Second, because a knowledgeable user can easily circumvent this, you'll compile a executable-only version of the database that has most of the developer features removed.
TIP
I will show you how to lock the database and you should try these options to see what the effect is. However, be sure you undo the changes when you're done as there is still more development to do.
Removing Navigation
By unchecking a few check boxes, you can drastically limit the features available to the end users. This will make Access look more like an application and less like a development platform.
Let's start with the Windows title bar, which probably reads something like "Library : Database (Access 2007) – Microsoft Access." This gives the impression that you have opened an Office document, which, of course, you have. However, you can change this title so it looks like you have launched an application.
1. Open the Backstage View, click the Options button, and select the Current Database tab.
2. At the top of the dialog, enter Library Management System (LMS) for the Application Title.
3. While you're here, you should fix the icon as well. You've probably noticed that the icon in the task bar is the standard Microsoft Access 2010 icon. Click the Browse button and select an icon file. (If you've downloaded my Images\Static folder, you can use the Library.ico file. Otherwise just search the local drive for *.ico files.)
4. Select the Use As Form And Report Icon option. The top portion of the Application Options section should look like Figure 10-33.
Figure 10.33. Setting the application options
5. Click the OK button to save the changes. Notice the icon in the top-left corner of the application and the task bar has changed as well as the title bar.
Now you'll remove the standard navigation options.
6. Go back to the Backstage View, click the Options button and select the Current Database tab.
7. Unselect the following options, as highlighted in Figure 10-34:
Use Access Special Keys
Enable Layout View
Enable design changes for tables in Datasheet view
Display Navigation Pane
Allow Full Menus
Allow Default Shortcut Menus
Figure 10.34. Removing the navigation options
NOTE
Some of these options are redundant. For example, the "Enable Design Changes for Tables in Datasheet View" doesn't really need to be turned off, because the user can't get to the tables anyway. But it's still a good idea to disable this feature. If you decide later to allow navigation to the tables, you'll want to prevent the user from changing them.
8. Click the OK button to close the form. You will need to close the application and re-open it for these changes to take effect. Your database should look like Figure 10-35.
Figure 10.35. The database with navigation removed
Click the File tab to see the Backstage View. You'll notice that is has been changed to pretty much bare bones as well. You may be wondering how you are going to turn these options back on so you can keep developing. Well, don't panic. Click the Privacy Options button as shown in Figure 10-36. This displays the Access Options dialog box that you can use to enable the standard navigation.
Figure 10.36. Using the Privacy Options button
Compiling the Database
As you can see, even though you have removed access for most users, there's nothing to prevent someone from bypassing this and turning the navigation and menus back on. However, Access gives you a way to create a version of the database that does not contain any of the code and that does not allow the Design Views. First, turn on the "Allow Full Menus" option, if they're not currently enabled.
1. Go to the Backstage View and click the "Save & Publish" tab.
2. Select the "Save Database As" option and then select the Make ACCDE format in the Advanced section as shown in Figure 10-37.
Figure 10.37. Saving as an ACCDE file
3. Click the Save As button, which will display a Save As dialog box. You can use the default file name, which should be Library.accde. This will create a new file with the .accde extension. When this has finished, close the existing database. Then open the new Library.accde file. You'll need to turn off the Allow Full Menus options.
The Library.accde is now ready for the end users. If you turn on the Display Navigation Pane option, for example, you'll be able to browse the various database objects but you cannot modify them. You will still need to have Access installed to be able to open this file. In Chapter 14, I will show you how to distribute this file and use the Access Runtime so you can deploy this without needing to purchase a copy of Access for every end user.
Summary
In this chapter you packaged your application so it is "user-ready." This implemented two methods for navigating to the various forms: a simple navigation form and a custom ribbon. You also developed a data-driven menu system so the ribbon controls are generated automatically based on the menu configuration. As you add new features to the database, you'll just need to add them to the menu configuration and when the database is reloaded, the associated control will be added to the ribbon.
You also locked down the database to remove the developer features. The first step was to remove the navigation to these features. You then compiled a .accde file that has the development features removed.
The key concepts that were introduced in the chapter include:
Using the Command Button Wizard to create macros to launch a form or run a macro
Creating a custom UI ribbon
Adding custom ribbons to the USysRibbons table
Using mso images
Creating an autoexec macro
Configuring the Access navigation options
Compiling a locked-down version of your database
In the next chapter you will look at some more of the visual aspects of your Access database such as colors, fonts and images. This will enable you to quickly give your application a whole new look.
Chapter 11. Branding with Themes and Styles
Branding is a marketing concept that that focuses on product identification or familiarity. It goes as far back as the days of the large cattle-drives, where a cow was branded with a symbol so everyone would know whom it belonged to. The idea is that people recognize colors and shapes that they are familiar with. You can go to a store and pick out a product without even reading the label, just because you recognize the color or shape of the package.
People expect that same familiarity when it comes to software applications. If you go to pizzahut.com to order lunch and don't see the red roof logo, you will probably consider the site suspect and order somewhere else. There is comfort in familiarity. When you develop your Access applications, you will likely have to deal with the necessity for branding.
In this chapter, you will brand your Library application to look like the apress.com website. It's fairly easy to do with just a few simple techniques. I will first explain how to use themes, as this will go a long way to mirror the colors and fonts. Then I'll show you how to use the image gallery to place a few graphics on the forms. The end result will be a metamorphosis of the user experience.
Using Office Themes
Each Office product allows you to create, view, and edit a type of document, such as a text document (Word), spreadsheet (Excel), or presentation (PowerPoint). There is a fairly clear distinction between the content (the data supplied by and maintained by the users) and the application (the menus, ribbons, and forms) provided by the Office application. Office themes allow users to customize the colors and fonts used in the application areas but have no effect on the content. Thus, two users can open up the exact same document and have a completely different look and feel.
Access is sort of the odd man out when it comes to Microsoft Office products, because your Access database includes forms. So the users will see both built-in Access UI elements as well as UI elements that are included in the content, such as forms. In the last chapter, I showed you how to remove as much of the Access UI as possible. However, you can't remove everything (and you don't want to). So you'll have your own custom UI elements, but they will still run under the Office UI framework.
I will first explain how to customize the standard Office UI, and then we'll look at how that spills over into your form design.
Understanding Office Themes
I think the best way to fully understand themes is to create one for yourself. I'll show you how to define a custom theme, and then we'll look into how it works. If you open any form in the Design View or the Layout View, in the Design tab of the ribbon you'll see the Themes group shown in Figure 11-1.
Figure 11.1. The Themes group of the Design tab
Click the Themes button and you'll see a list of themes, shown in Figure 11-2, that you can select from. As you hover the mouse over each of the choices, you can see your form redrawn based on the selected theme.
Figure 11.2. The available themes
This dialog is divided into several sections. The first shows the theme that is currently defined for this database. If you have any custom themes, they will be displayed next. Finally, the built-in themes are listed. Just click off this dialog box to close it; you won't change the theme at this time.
CREATING A CUSTOM COLOR THEME
A theme is comprised of a color scheme and a font scheme. You define these separately and then later you can save them as a theme. Let's start with the color scheme.
1. Click the Colors button in the ribbon. You should see a fairly long list of existing color schemes, as shown in Figure 11-3.
Figure 11.3. Viewing the available color schemes
2. As you can see, the custom and built-in color schemes are listed separately. I have already created a color scheme named Apress. Click the Create New Theme Colors link at the bottom of this list, which will display the Create New Theme Colors dialog box shown in Figure 11-4.
Figure 11.4. The Create New Theme Colors dialog box
NOTE
A color scheme is simply a collection of twelve colors. There are two pairs of background/foreground colors. Normally you will use the light foreground with the dark background and vice versa. This allows you to visually toggle between selected items and non-selected items. There are also six accent colors and two hyperlink colors.
In my opinion, the names given to the first four colors are difficult to follow. The first two are the primary foreground and background colors. The next two are the alternate foreground and background colors. When you need to select a color from either the Property Sheet or the color picker, different names are used. Fortunately, those names actually make sense (Text 1, Background 1, Text 2, Background 2). Also, note that the preview picture is wrong and doesn't display the colors correctly. Just ignore this.
3. The first two colors are just standard black and white, and you can leave this as it is. The fourth color, which is called Text/Background – Light 2, should be changed. Click the dropdown icon next to it, which will reveal the standard color picker, shown in Figure 11-5.
Figure 11.5. The standard color picker
4. The top portion of the color picker shows the theme colors. There are a row of twelve colors, each one representing the current theme colors. Below each are five shades of that color. The idea is that once you have picked the colors you will use, from then on you normally pick from those colors or possibly a lighter or darker shade of one of these colors. However, at this point you are still defining the colors, so this is not helpful. Instead, click the More Colors link to display the Colors dialog box shown in Figure 11-6.
Figure 11.6. Defining the theme color
5. Go to the Custom tab to select the appropriate color. What I did to eyeball the correct color is drag this dialog on top of the color I was trying to match. Then I moved the crosshairs to get the correct color and moved the slider to adjust the brightness. I did that until the preview of the New color matched. Normally, however, your client should be able to tell you the correct settings for each color. For this color enter the following values:
Red: 209
Green: 209
Blue: 209
6. Click OK to update this color.
7. Repeat this procedure to set the color for the remaining colors using the values in Table 11-1. You can leave the hyperlink colors as they are.
Table 11.1. Accent Color Definitions
Theme Color
Red
Green
Blue
Text1
0
0
0
Background 1
255
255
255
Text 2
255
255
255
Background 2
209
209
209
Accent 1
227
184
61
Accent 2
157
157
157
Accent 3
71
88
137
Accent 4
205
61
57
Accent 5
75
175
198
Accent 6
61
58
68
8. When you have finished, enter the Name Apress, and click the Save button.
DEFINING THE FONT SCHEME
Setting up a font scheme is pretty simple: there are only two font families that you'll need to define. At first glance, that may seem too simplistic. However, a good UI design uses a consistent font throughout the application. Too many different fonts can make the design seem cluttered and disorganized. It is a really good design practice to limit the design to only two fonts. You can always use fonts that are not included in the theme, but in most cases you should stay with these two font families.
1. Click the Fonts button on the ribbon, which will list the existing font schemes as shown in Figure 11-7.
Figure 11.7. The existing font schemes
2. Again, the custom and built-in schemes are separated. Click the Create New Theme Fonts link, which will display the Create New Theme Fonts dialog box.
3. Select Arial for the heading font and Calibri for the body font.
4. Enter Apress for the Name, as shown in Figure 11-8. Click the Save button to update the font scheme.
Figure 11.8. Defining a new font scheme
CREATING AN OFFICE THEME
At this point, you have created a color scheme and a font scheme. Access has already selected these as the schemes defined for the Library database. You've probably already noticed that your forms have changed appearance because they are now using the new color and font schemes that you defined. Now you'll save the current configuration as a custom theme.
Click the Themes button in the ribbon, which will display the list of existing themes, as shown in Figure 11-2. At the bottom of this list, click the Save Current Theme link. In the Save Current Theme dialog box, enter the name Apress.thmx, as shown in Figure 11-9.
Figure 11.9. Saving the Apress.thmx file
The theme definition is saved in a file with a .thmx extension. Like all Office files ending in "x" this is actually a compressed folder that contains several files and subfolders. It includes a .jpg file that is used for the icon that is displayed in the list of themes. There is also an .xml file that provides the theme definition. A portion of that is shown in Listing 11-1.
Example 11.1. Partial Listing of theme.xml
<a:themeElements>
<a:clrScheme name="Apress">
<a:dk1><a:sysClr val="windowText" lastClr="000000"/></a:dk1>
<a:lt1><a:sysClr val="window" lastClr="FFFFFF"/></a:lt1>
<a:dk2><a:srgbClr val="FFFFFF"/></a:dk2>
<a:lt2><a:srgbClr val="D1D1D1"/></a:lt2>
<a:accent1><a:srgbClr val="E3B83D"/></a:accent1>
<a:accent2><a:srgbClr val="9D9D9D"/></a:accent2>
<a:accent3><a:srgbClr val="475889"/></a:accent3>
<a:accent4><a:srgbClr val="CD3D39"/></a:accent4>
<a:accent5><a:srgbClr val="4BACC6"/></a:accent5>
<a:accent6><a:srgbClr val="3D3A44"/></a:accent6>
<a:hlink><a:srgbClr val="0000FF"/></a:hlink>
<a:folHlink><a:srgbClr val="800080"/></a:folHlink>
</a:clrScheme>
<a:fontScheme name="Apress">
<a:majorFont>
<a:latin typeface="Arial"/><a:ea typeface=""/><a:cs typeface=""/>
</a:majorFont>
<a:minorFont>
<a:latin typeface="Calibri"/><a:ea typeface=""/><a:cs typeface=""/>
</a:minorFont>
</a:fontScheme>
<a:fmtScheme name="Composite">
<a:fillStyleLst>
<a:solidFill><a:schemeClr val="phClr"/></a:solidFill>
<a:gradFill rotWithShape="1">
This theme definition specifies the twelve colors and the two fonts as you would expect. It also defines some formatting schemes including fills and gradients. These are inherited from the standard Office effects.
NOTE
In Word and Excel, you can also define effects, which control how lines and fills are drawn. This is not possible in Access. Your custom theme will include the effects defined by the default theme.
Notice the location of the Apress.thmx file. It is in the following folder:
%USERPROFILE%\AppData\Roaming\Microsoft\Templates\Document Themes
If you want to share this theme with other users, you'll need to send a copy for them to install on their local machine. As an alternative, you can place this in a shared location. Any user who wants to use this theme can add it by browsing to that location and selecting this file.
The extra step of creating the Apress.thmx file is not necessary for the Library application. In your Access database, all you need to do is define the color and font scheme. The color and font schemes are stored in the database and will be used when other users open the database file. The theme file is used to share these schemes with other Office applications. For example, open Word or Excel and notice the Apress theme is now available, as demonstrated in Figure 11-10.
Figure 11.10. The theme is available in all Office applications.
The reverse of this also true; any theme that was created in any Office application can be used in Access as well. Before designing a custom theme, you should check to see if an appropriate theme has already been defined. This could save you some time by simply selecting that theme.
You can apply a theme to only a single form, leaving the others as they are. Perhaps you'll want to use a different theme for the main navigation form. Of maybe the data entry forms should have a different look and feel from the administrative forms.
To do that, right-click the theme that you want to apply as shown in Figure 11-11. Then click the Apply Theme to This Object Only link.
Figure 11.11. Applying a theme to a single form
The first link, Apply Theme to All Matching Objects, can be useful if you have already applied a different theme to a subset of forms. If you want to change all of these at once, this option will update the current form and all others that also use the same theme.
Applying an Office Theme
So far, you have defined the color and font schemes, which Access will use in its UI elements. Now you'll need to adjust your forms to use the appropriate colors. You have 10 colors to work with (ignoring the two that are used for hyperlinks). The forms created by Access will use the theme colors but may not use the ones you intended. For example, it may use Accent 1 and you wanted Accent 3 to be used instead.
Open the Menu form in the Layout View and look in the Property Sheet for the Tab Control object. The default color and font definitions will look like Figure 11-12.
Figure 11.12. The default color and font selections
Notice all the foreground colors use Text 1 and the background colors use Background 1. To achieve the desired effect, the Lighter or Darker attributes are used. However, the look you're trying to match uses a different approach. The main navigation area for the www.apress.com site is shown in Figure 11-13.
Figure 11.13. Sample Apress menu
The background is black and the menu options are in white. Actually, the menu text is a light gray but they turn white when the mouse is hovered over that option. For the following properties, click the ellipses to display the color picker and then select the standard Black color:
Back Color
Hover Color
Pressed Color
TIP
There are several ways that you can select the Black color. There is a Theme color that is Black, which is the primary text color. There is also a Standard color that you can use. This is probably just a matter of personal preference, but to use a Theme color that is intended as foreground text, as a background color seems to be violating the purpose of a Theme. However, by using the Standard color, the background will not change if someone modifies the Theme.
For the corresponding foreground colors (Hover Fore Color, Pressed Fore Color, and Fore Color) use the Text 2 theme color. For the Fore Color property, add the Darker 15% modifier.
The final Property Sheet will look like Figure 11-14.
Figure 11.14. The updated color properties
TIP
If you're not sure which color to use, click the ellipses next to the particular property that you're setting, which will display the color picker. If you hover the mouse over one of the theme colors, it will tell you the color name, as shown in Figure 11-15. Also, if you hover over one of the various shades that are displayed, it will indicate the corresponding Lighter or Darker attribute. You can click on one of these colors and the property will be updated with the corresponding value.
Figure 11.15. Using the color picker
The buttons on the form use the Accent 1 color, which is what you'll want but they use a lighter shade. By default, the background colors for buttons are:
Back Color: Accent 1, Lighter 40%
Hover Color: Accent 1, Lighter 60%
Pressed Color: Accent 1, Darker 25%
Remove the Lighter 40% modifier on the Back Color property. And change the Hover Color property to use Lighter 25%. You'll need to make these changes to all eight buttons on this form.
Making Other Visual Adjustments
If you compare the Access form with the apress.com site, you'll notice a few visual differences. You'll make some minor adjustments to closer match the UI. The menu on the website uses links instead of tabs. However, by squaring off the tabs and removing the border, you can approximate the same effect.
1. In Access, select the Tab Control and set the Border Style property to Transparent.
2. From the Format tab of the ribbon, click the Change Shape button and click the Rectangle shape, as shown in Figure 11-16.
Figure 11.16. Change the TabControl to use square corners.
The final Menu from should look like Figure 11-17. If you compare this to the website sample shown in Figure 11-13, you can see that that it's a pretty close match.
Figure 11.17. The final Menu form
Using Graphics
The other principle factor in branding an application is using familiar graphics. I mentioned the Pizza Hut logo at the beginning of the chapter. Most organizations have at least one widely known symbol that will give instant brand recognition. For Apress, the yellow and black banner is a key element.
Adding static images in Access 2010 is really easy. When you first use a graphic file it is added to the image gallery. Reusing it is as simple as selecting it from a dropdown list. I have created a few graphic files that you'll use for this project. If you haven't already, download my Images folder. The Static subfolder contains the graphics you'll need.
Adding a Banner Graphic
You'll add this black and yellow banner to the Menu form. You'll first need to create an empty cell to put the graphic in. Then you'll use the image gallery to add the images to the form.
1. Open the Menu form in the Layout View and select the TabControl. From the Arrange tab of the ribbon, click the Insert Above button. This will create an empty cell; expand its height to be about 1 inch.
2. From the Design tab of the ribbon, click the Insert Image button, as shown in Figure 11-18. This will list the images that are currently in your image gallery; but since there are none, it will just have a Browse link.
Figure 11.18. Selecting the Browse link
3. Click the Browse link, navigate to the Images\Static folder, and select the AppressLogo.jpg file.
4. Next click the empty cell. This will add the image to this cell. The Size Mode property defaults to Zoom. Change this to Clip. This graphic was made quite long to accommodate some of the wider forms. Rather than zooming out to fit the entire graphic, you just want to clip off whatever doesn't fit on the form.
5. Now you'll eliminate the white space between the banner and the Tab Control. In the Property Sheet, select the Detail object and the Format tab. For the Back Color property, select the standard Black color.
Open the Menu form using the Form View. The final design should look like Figure 11-19.
Figure 11.19. The final Menu form design
No go back to the Layout View and click the Insert Image button again, this time you'll see the logo that you just used as demonstrated in Figure 11-20. When you need to add this to other form, you can simply select it from this list.
Figure 11.20. The logo included in the image gallery
If you need to modify an image that has been loaded in the gallery, right-click the image in the gallery and click the Update link, as shown in Figure 11-21.
Figure 11.21. Updating an existing gallery image
Then browse to and select the modified file. The new version will be stored in the gallery, replacing the existing one. Also, any place that this image has been used will be updated automatically.
Using a Background Image
Another design element that you'll want to mimic is the use of a header graphic that has a curved top-right corner as shown in Figure 11-22. You'll add this effect to the Category form.
Figure 11.22. Sample Apress heading
1. Open the Category form in the Design View. In the Property Sheet, select the Form object.
2. The Picture property is used to specify the background image for the form. Click the dropdown icon next to this property. Notice that the ApressLogo image is listed here, as demonstrated in Figure 11-23.
Figure 11.23. Selecting the image gallery from the Property Sheet
NOTE
The Property Sheet is integrated into the image gallery. Any items that are already in the gallery are available in the dropdown list. As I'll show you, when you select a new image, it will be loaded into the gallery as well.
3. Change the Picture Type property to Shared. This will indicate that you want to use the image gallery.
4. Select the Picture property and click the ellipses and browse to and select the ApressArc.jpg file.
5. The image will be displayed in the middle of the form. Change the Picture Alignment property to Top Right.
6. Shrink the height of the Form Header so the background picture just fits in the header section. You'll probably have to first shrink the height of the Label control.
7. Select the Label control and set the Fore Color property to Text 2.
Notice that when you click the Insert Image button in the ribbon, the new file is now included in the list. Open the Category form using the Form View. The form should look like Figure 11-24.
Figure 11.24. The modified Category form
The other forms will need similar modifications; I'll leave that for you to do on your own.
Summary
In this chapter you "branded" your Library application to mimic the www.apress.com website. Anyone familiar with that site will easily recognize your application as being from the same organization. While this is a somewhat fictional exercise, the concept is important. End users tend to respond first to how a system looks before they notice how well it works. Developers need to concentrate effort on the UI as well as the functionality of the systems they build.
You created a custom theme, following the same color and fonts schemes found on the website. You then modified the forms to use the appropriate colors in the right places. You added a few graphics to the forms making use of the image gallery. With some simple techniques, the effective use of color, fonts, and graphics turned an average looking application into one with some "wow" factor. More important, the instant brand recognition will go a long way in providing acceptance and comfort.
In the next chapter you'll create reports for the Library application. As you'll see, reports are essentially read-only forms that are designed for print output.
In this chapter, you'll add the final piece of significant form development. So far you have completed the following:
Provided forms for setting up categories and media types, plus a simple form for defining items (Chapter 6)
Implemented a check-out feature for creating new loans (Chapter 7)
Created a customer administration form (Chapter 8)
Now you'll significantly enhance the Item form to provide the following:
Data-bound images
Item search capability
Embedded web browser
Using Data-Bound Images
The saying goes, "a picture is worth a thousand words." I think in the technologically advanced society that we live in, pictures are nearly essential. Even if the images do not provide any "required" information, the "wow" factor alone will necessitate them. Undoubtedly, you will be asked to include images in your applications.
To be clear, in this chapter I'm addressing data-bound images; support for static images uses an entirely different approach, which I will cover in Chapter 11. By data-bound images, I mean images that are part of your data. For example, in the Library application that you're building, you will provide the ability to store an image for each item in the database.
Image Support in Access
Access provides the following three methods for including images in your database:
Storing bitmap files in the database
Storing OLE objects in the database
Storing images in the file system and storing a reference in the database
I will now briefly explain each of these techniques.
STORING BITMAPS IN ACCESS
The simplest approach is to store the bitmap images directly in the Access table. Add a field with a data type of OLE Object and include this field on a form, just like you would a Text field. To store the image, right-click the data-bound control on the form and click the Insert Object link. In the dialog box, select the Create From File option and browse to the file containing bitmap image, as shown in Figure 9-1.
Figure 9.1. Loading a bitmap image
The images must be bitmaps (.bmp) or device independent bitmaps (.dib). Once loaded in the database, your forms will display the images automatically. The advantage of this approach is that it's easy and requires no coding.
The disadvantages, however, are significant. The primary one is size. Bitmaps do not use any compression and can be three or four times the size of the same image in other file types such as JPEG, TIFF or PNG. While theoretically the file size limit of Access databases is quite high, generally the performance deteriorates as the file grows. Another disadvantage is that tables with image (OLE Object) fields cannot be published as a web database.
STORING OLE OBJECTS
As an alternative, you could store images that use compressed formats such as JPEG, which may take less space than bitmaps. There are numerous issues with this approach.
Object Linking and Embedding (OLE) is a technology that allows you to store data in a document that is rendered by another application. For example, you can embed an Excel spreadsheet inside a Word document. When you view or modify that spreadsheet in Word, you are actually running an Excel application. Excel is running as an OLE Server, rendering the embedded data on behalf of the Word application.
Similarly, when you store an OLE object in Access, an OLE Server will be launched to render the object. For this to work, there must be an OLE Server available to handle the type of object that was loaded. Earlier versions of Microsoft Office (earlier than Office 2003) included a Photo Editor application, which also served as an OLE Server. Image files saved as JPEG and GIF, for example, were rendered by the Photo Editor application.
With Office 2003 and later, the Photo Editor application was replaced with the Microsoft Picture Manager. This does not provide the OLE Server support that the Photo Editor did, so Access can no longer display these types of files. You can still store them, but Access does not know how to display them.
NOTE
You can install the old Photo Editor application from the Office 2000 or Office XP installation disk. Of course there is no 64-bit version of this application, so this is not an option if you are using the 64-bit version of Office 2010.
STORING IMAGES IN THE FILE SYSTEM
In the Access table, you'll use a Text field that will store the file path and name of the image. This takes virtually no space in the database. This is the most practical solution for many scenarios and the only one that is compatible with web databases.
There are some disadvantages with this approach, however, including the following:
You'll need to do a little bit of work to display the image on a form. You'll use an un-bound image control and set its path/filename using VBA code.
Because the images are not stored in the database, you'll need to make sure they are copied along with the database when deploying the solution.
These limitations are manageable and, considering the alternatives, this will be the optimum choice in almost any environment.
Adding a Picture to the Item Table
You'll now add images to the items in your database using the recommended approach I just described. You'll add a Text field to the Item table that will store the filename of the associated item.
TIP
This design will allow a single image for each item. If you needed to support two images – say, front and back cover – you would add two fields to store both filenames. If an undetermined number of images were required, you would create a child table and store the filenames there.
1. Open the Item table using the Design View.
2. Add a new field named Picture with a Data Type of Text. The default Field Size of 255 is fine. You can leave all the other default properties.
3. While you're here, add another field named URL and select the Hyperlink Data Type. You will use this field later in the chapter.
4. Save the table changes. The table design should look like Figure 9-2.
Figure 9.2. The updated Item table design
Modifying the Item Form
Now you'll add these two fields to the existing Item form so you can enter data for them. You will also add an Image control that will display the image on the form.
1. Open the Item form in the Layout View. Click the Add Existing Fields button in the Design tab of the ribbon. Drag the Picture field from the Field List pane to below the ReplacementCost control. An orange bar should appear indicating where the control will be added, as shown in Figure 9-3.
Figure 9.3. Dragging the Picture field onto the Item form
2. In the Other tab of the Property Sheet change the name of this control to txtPicture.
3. Merge the Picture control with the empty cell next to it.
4. In the same way, drag the URL field to just below the Picture control. After the row has been added, you may need to move the URL control and its label to the two leftmost cells on that row. Merge the URL control with the two empty cells next to it.
5. At the top-right corner there are six empty cells; merge these into one cell. This is where the image will be displayed.
6. In the Design tab of the ribbon, click the Image button, as shown in Figure 9-4. Then click on the large empty cell. After adding the Image control, a dialog will appear for you to select the image. Just cancel this dialog as this will be defined programmatically.
Figure 9.4. Clicking the Image button
WARNING
Just to the right of the Image button is another button labeled Insert Image. This is not part of the list of form controls. It is used to add a static image to your form, which I'll explain in Chapter 11.
7. In the Property Sheet, enter the name for this control as imgPicture.
8. Save the form changes. The updated form design should look like Figure 9-5.
Figure 9.5. The modified form layout
Displaying an Image
You have added a Text control to the form for entering the filename of the associated picture. You also have an Image control that will display this file. Now you'll need to connect the two controls with a little bit of VBA code.
ADDING THE CODE TO DISPLAY AN IMAGE
Switch to the Design View. In the Design tab of the ribbon, click the View Code button, which will create a code file for this form and display the VBA editor. Open the Main module and add code shown in Listing 9-1.
Example 9.1. Implementation of the GetFullImagePath Function
Function GetFullImagePath(ByVal sImage As String) As String
Dim sPath As String
Dim n As Integer
sPath = CurrentProject.FullName
n = InStrRev(sPath, "\", Len(sPath))
sPath = Left(sPath, n)
GetFullImagePath = sPath + "Images\" & sImage
End Function
This function gets the CurrentProject.FullName property, which specifies the full path and filename of the Access file that is being executed. It then strips off the filename to get just the path. It then adds the hardcoded Images subfolder and the image file name that was passed in as a parameter. The final string is provided to the caller as the return value.
With this implementation, you can deploy the application anywhere, as long as you put the images in an Images subfolder. Any place that needs to retrieve the images should use this function. If you decide later to change the location of the images, you only need to modify this function.
Now add the code shown in Listing 9-2 to the code file for the Item form.
Example 9.2. Implementation of the Item Form File
Private Sub Form_Current()
DisplayImage
End Sub
Private Sub txtPicture_LostFocus()
DisplayImage
End Sub
Private Sub DisplayImage()
If (Len(txtPicture) > 0) Then
On Error GoTo ErrorExit
imgPicture.Picture = GetFullImagePath(txtPicture)
Else
imgPicture.Picture = ""
End If
Exit Sub
ErrorExit:
imgPicture.Picture = GetFullImagePath("Static\NotFound.tif")
End Sub
This code implements an event handler for both the form's OnCurrent event and the txtPicture control's LostFocus event. The OnCurrent event is raised whenever a record is displayed on the form. If the filename is changed, the LostFocus event will also fire. Both of these event handlers call the DisplayImage method, which sets the Picture property of the Image control. If no picture is defined for the item, the Picture property is set to an empty string, which clears the control.
Because the images are stored outside of the database, it's a good idea to handle the situation where the specified filename is not valid. If an error occurs accessing the file, the On Error Goto ErrorExit statement moves the execution to the ErrorExit label. This displays a static image.
NOTE
Make sure you have an Images\Static subfolder that contains a NotFound.tif file, or you will generate an error trying to display the static image. You can download my Static folder from www.apress.com. I will cover that in more detail in Chapter 11.
LOADING THE IMAGE FILES
Now you'll need to set up a folder and store some image files. Then you will enter the filenames into your database.
1. Create an Images subfolder where your Access file is. If you have loaded your Item table with my rather eclectic list of items (or are using the downloaded database at the start of each chapter), you can download pictures of these items from www.apress.com. Otherwise, you should be able to get some images from the Internet.
2. Open the Item form using the Form View, which will display the first item.
3. Enter the filename for the associated picture. When you tab off this field, the picture should appear, as shown in Figure 9-6.
Figure 9.6. The Item form displaying a picture
4. Enter a filename for each of the other items so you'll have some data to test with later.
Implementing Item Search
Now you'll implement a form for searching the Item table. It will use a modal dialog like the CustomerSearch form that you implemented in Chapter 7. In the Form Header, you'll use a TabControl so you can implement multiple ways to search.
The Detail section will use the Continuous Form view so you can arrange the search results, including images. You'll simulate a record selector using conditional formatting.
Importing Item Data
It's difficult to test the search feature with just a handful of records. To load a larger sample of items, I have created an Access database with the entire list of titles published by Apress. Download this Item.accdb file from www.apress.com. I'll show you how to import these records into your Item table.
1. You'll need a new category for the items you're importing. Open the Category form in the Form View and add a new category, as shown in Figure 9-7.
Figure 9.7. Adding a category for technical books
2. From the External Data tab of the ribbon, click the Access button, which will display the dialog box shown in Figure 9-8.
Figure 9.8. Selecting the external Access database options
3. Browse to the Item.accdb file that you downloaded and select the first option, which is to import the data rather than link to it. The next dialog box, shown in Figure 9-9, allows you to select the objects that you want to import. Select the Item table.
Figure 9.9. Selecting to import the Item table
4. Click the OK button, which will start the import. When the confirmation dialog appears, just close it. This will have created a new table named Item1, because an Item table already existed. Now you'll create a query to copy the records from Item1 to Item.
5. From the Create tab of the ribbon, click the Query Design button. Select the Item1 table and close the Show Table dialog box. Click the Append button in the ribbon to change this to an append query. This will display the Append dialog box to select the table to be appended to. Select the Item table, as shown in Figure 9-10.
Figure 9.10. Appending to the Item table
6. Double-click each of the columns in the Item1 table, except for ItemID and LostFee. You won't specify a value for ItemID because this is an AutoNumber field and the database will assign unique IDs for you. Also, the LostFee column is calculated based on the ReplacementCost, so you'll let the data macro handle that for you. The completed query design should look like Figure 9-11.
Figure 9.11. The completed append query
TIP
The records in the Item1 table have the CategoryID set to 6. It's possible that your Technical Books category has a different ID. If so, instead of using the value from the Item1 table, in the Field row, replace CategoryID with an expression that has the appropriate value for your database.
7. Run the query and you should see a confirmation dialog, shown in Figure 9-12, telling you that 1,344 records were added to the Item table.
Figure 9.12. Confirmation of the records added
8. Close the query without saving it. Delete the Item1 table that was imported. Open the Item form in the Form View and page down through some of the new records. You should see the Image Not Found graphic, because the referenced file is not in your Images folder. You can also download the image files from www.apress.com.
Designing the Search Form
You'll start by designing the layout of the ItemSearch form. You'll use the Modal Dialog template and then place controls on the Form Header and Detail section.
1. From the Create tab of the ribbon, click the More Forms button and then click the Modal Dialog link. Right-click the form and click the Form Header/Footer link.
2. Cut and paste the OK and Cancel button from the Detail section to the Form Footer. Shrink the Detail section to about 1-inch high and expand the Form Header to be about 1½-inch high.
3. Click the Tab Control button in the Design tab of the ribbon then draw a rectangle that takes up the entire Form Header.
4. Change the Name and Caption properties on the first page to Basic. The Name and Caption properties of the second page should be Advanced.
5. Add a TextBox control at the top of the Form Header. Delete the associated label that is generated. Change the Name property to txtSelectedItemID and set the Visible property to No. This control will be used to keep track of which item in the Detail section has been selected.
The Form Header should look like Figure 9-13.
Figure 9.13. The initial Form Header layout
DESIGNING THE BASIC SEARCH PAGE
The basic search feature will accept a single keyword or phrase. You'll provide a ComboBox control to select which fields should be searched in such as Author, Title, or Description. You'll provide another ComboBox control to select which media types should be included.
1. Add a TextBox control to the Basic page (make sure the page is selected before you add the TextBox). Set the Name property to txtKeywords and set the Caption of the associated label to Keywords.
2. Select the txtKeywords control and its associated label. From the Arrange tab of the ribbon, click the Stacked button. This will create a layout control with two columns. Click the Insert Right button three times to create a total of five columns. Click the Insert Below button to add a second row.
3. Select the txtKeywords control and the two columns to the right and merge them into one cell.
4. Make sure the control wizards are turned on by clicking the dropdown icon to see all the form controls. The Use Control Wizards icon should be highlighted.
5. Click the Combo Box button and then click the Basic page. In the first Combo Box Wizard dialog box, select the second option as shown in Figure 9-14.
Figure 9.14. Selecting the option to specify the combo options
6. In the next dialog box, enter the following values, as shown in Figure 9-15.
<Any field>
Author
Title
Description
Figure 9.15. Specifying the allowed values
7. In the final dialog box enter Look In as the label text. Set the Name property of this control to cbField. In the Data tab, set the Default Value property to "<Any field>." Set the Limit To List property to Yes. Set the Allow Value Lists Edits to No. Drag the control to the second cell of the bottom row.
8. Add another ComboBox control to the Basic page. This time, in the Combo Box Wizard, select the first option, which is to get the values from a table or query.
9. In the second dialog box, select the Media table, as shown in Figure 9-16.
Figure 9.16. Selecting the Media table for the combo box source
10. In the third dialog box, shown in Figure 9-17, select the MediaID and MediaDescription columns to be included.
Figure 9.17. Selecting the columns to be included
11. In the fourth dialog box, shown in Figure 9-18, select the MediaDescription field for the sort option.
Figure 9.18. Sorting by the MediaDescription column
12. The fifth dialog box, shown in Figure 9-19, shows a preview of what the Combo Box will look like. Leave all the default settings.
Figure 9.19. The Combo Box preview
13. In the final dialog box enter Include as the label text. Set the control's Name property to cbMedia. In the Data tab, set the Default Value property to 0. Set the Allow Value Lists Edits to No. Drag the control to the fourth cell of the bottom row.
This cbMedia Combo Box will allow you user to select one of the media types. You also want to provide an option to include all of the media types in the search, which will require another row in the dropdown list. To do that you'll modify the query that populates this control.
1. Select the cbMedia control and in the Data tab of the Property Sheet, select the Row Source property. Click the ellipses, which will launch the Query Builder. Select the SQL View. Replace the existing SQL with the following code:
SELECT [Media].[MediaID], [Media].[MediaDescription] FROM [Media]
UNION SELECT 0, "<All media>" FROM [Media]
ORDER BY [MediaDescription];
TIP
This SQL uses a UNION clause to add an additional hard-coded value to the values supplied by the Media table. The value 0, with a description of <All media>, will indicate that the search should look in all media types.
2. Close the Query Builder and click the Yes button when prompted to update the property.
3. Select all the labels and set the Text Align property to Right.
4. Add a command button to each of the cells in the far-right column. Just cancel the control wizards when they launch. For the top button, enter BasicSearch for the Name property and Search for the Caption. For the lower button, enter the BasicClear for the Name property and Clear for the Caption.
Save the form and enter the name ItemSearch when prompted. The layout of the Form Header should look like Figure 9-20.
Figure 9.20. The layout of the Form Header
NOTE
To add other search methods, such as the Advanced tab, you would add unbound controls for specifying the search criteria. You then implement a Search button that formats a filter based on those criteria. I will leave the implementation details for you to work out on your own.
DESIGNING THE DETAIL SECTION
Now you'll design the results section of the ItemSearch form. It will use the Continuous Form View, so all of the controls that you place here will be repeated for each record returned by the search. Just like with the CustomerSearch form you created in Chapter 7, the detail section will contain data-bound controls. You will limit the records that are displayed with a filter that is generated based on the specified search criteria.
1. In the Property Sheet, select the Form object and the Data tab. For the Record Source property select the Item table.
2. Set the following form properties:
Filter On Load: Yes
Allow Additions: No
Allow Deletions: No
Allow Filters: No
Allow Edits: Yes (this is needed to be able to enter the search criteria)
Default View: Continuous Forms
Record Selectors: No
Navigation Buttons: Yes
Close Button: No
Filter: [ItemID] = CLng('0') (This will prevent items from displaying until the search criteria is entered.)
3. In the Design tab of the ribbon, click the Add Existing Fields button. Double-click the ItemID and Picture fields to add them to the Detail section. Delete their associated labels. Prefix the Name property of both of these controls with "txt." For example, the Name property of the Picture control should be txtPicture. Set the Visible property to both of these controls to No. Drag both of these controls to the top-left corner of the Detail section.
4. Click the Add Existing Fields button in the ribbon. Double-click the following fields to add them to the form and then delete the associated labels:
Author
Title
Description
5. In the Other tab of the Property Sheet, change the Name property for all three controls to prefix them with "txt." For example, txtAuthor.
6. Select all three of these controls and set their Locked property to Yes to prevent the user from modifying these fields.
7. With these controls still selected, from the Arrange tab of the ribbon, click the Stacked button to create a layout control. Click the Insert Left button to add a column to the left of the existing column. Merge all three cells of the left column into one cell.
8. Select the entire layout, click the Control Padding button, and then click the None link. This will remove the spaces between the controls.
9. Drag the layout to the top-left corner of the Detail section.
10. Add an Image control to the left cell of the layout. Set the Name property of this control to imgPicture. In the Data tab, for the Control Source property enter =GetFullImagePath([txtPicture]). This binds the Picture property of the Image control to the txtPicture control that specifies the filename. The layout should look like Figure 9-21.
Figure 9.21. The layout of the Detail section
11. Right-click the Cancel button and click the Build Event link. This should display the Macro Designer. The existing macro calls the Close Window action. Change the Save parameter from Prompt to No. Save the macro changes and close the Macro Designer.
12. Select the OK button. In the Event tab of the Property Sheet, change the On Click property from Embedded Macro to Event Procedure. Change the Name property to Close.
ADDING THE VBA CODE
In the Design tab of the ribbon, click the View Code button, which will generate a code file for this form and display the VBA Editor. Enter the code shown in Listing 9-3.
Example 9.3. Initial Implementation of the ItemSearch Form
Private Sub Close_Click()
If (Me.CurrentRecord = 0) Then
MsgBox "Please select an item first", vbExclamation, "No Item Selected"
Else
Me.Visible = False
End If
End Sub
Private Sub Form_Current()
txtSelectedItemID = Me.ItemID
End Sub
Private Sub BasicSearch_Click()
Dim s As String
Dim sFilter As String
If (IsNull(Me.txtKeywords) Or Len(Me.txtKeywords) <= 1) Then
MsgBox "Please enter a keyword", vbExclamation, "No Keyword Specified"
Else
s = "*" + Me.txtKeywords + "*"
sFilter = "(([Author] Like '" + s + "' And ([Forms]![ItemSearch]![cbField] " & _
"= '<Any field>' Or [Forms]![ItemSearch]![cbField] = 'Author'))" & _
"Or ([Title] Like '" + s + "' And ([Forms]![ItemSearch]![cbField] " & _
"= '<Any field>' Or [Forms]![ItemSearch]![cbField] = 'Title'))" & _
"Or ([Description] Like '" + s + "' And ([Forms]![ItemSearch]![cbField] " & _
"= '<Any field>' Or [Forms]![ItemSearch]![cbField] = 'Description')))" & _
"And ([MediaID] = [Forms]![ItemSearch]![cbMedia] Or " & _
"[Forms]![ItemSearch]![cbMedia] = CLng('0'))"
DoCmd.ApplyFilter "", sFilter, ""
End If
End Sub
Private Sub BasicClear_Click()
txtKeywords.Value = Null
cbField = "<Any field>"
cbMedia = cbMedia.DefaultValue
End Sub
The Cancel button simply closes the form. However, the event handler for the OK button (implemented in the Close_Click method) merely hides the form. This allows the calling form to retrieve the selected item. This is the same way that you implemented the CustomerSearch form.
In a Continuous Form, each record is displayed as a mini form which is repeated as many times as necessary. When you click on a record, that specific "form" becomes the current one and the OnCurrent event is raised. The Form_Current event handler takes advantage of this and captures the ItemID of the selected record.
The BasicSearch_Click method is called when the Search button is clicked. It builds a filter string based on the input criteria and then calls the ApplyFilter method. Unlike the CustomerSearch form, it is assumed here that the only partial values are entered so the keyword is automatically prefixed and suffixed with the "*" wildcard character. The BasicClear_Click method clears the txtKeywords control and restores the default value for the ComboBox controls.
Testing the Search Function
Now you're ready to try it out. Save the code file and the form changes, then switch to the Form View. Enter a keyword or phrase and click the Search button. The form should look like Figure 9-22.
Figure 9.22. The ItemSearch dialog
Using Conditional Formatting
You probably noticed that you can't tell which item is selected. You could turn on the Record Selectors property, which will display a black arrow next to the selected item. However, I'll show you another way to highlight the selected record, using the Conditional formatting feature.
1. Open the ItemSearch form in the Design View. In the Detail section, right-click the txtAuthor control and click the Conditional Formatting link as shown in Figure 9-23.
Figure 9.23. Selecting the Conditional Formatting link
2. This will display the Condition Formatting Rules Manager, shown in Figure 9-24. The txtAuthor control is already selected, and there are no rules currently applied to this control. Click the New Rule button, which will display the New Formatting Rule dialog box.
Figure 9.24. The Conditional Formatting Rules Manager
3. Select the first option since you'll be using an expression and change the combo box to Expression Is. For the expression enter the following code. The txtSelectedItemID control is in the Form Header and the OnCurrent event handler updates this to store the ID of the item that is currently selected. The expression uses this to return True if the record being displayed has the same ID as the selected record:
[txtItemID] = [txtSelectedItemID]
4. Finally, you need to specify in the rule what to do when the expression is true. Click the Background Color dropdown and select a background. This will be used when displaying the selected record. The completed rule should look like Figure 9-25.
Figure 9.25. The New Formatting Rule dialog box
5. Click the OK button to close the dialog box. The Conditional Formatting Rules Manager will now show the rule that you just added.
6. Click the Apply button to save this rule.
7. Select the txtTitle control to see the rules defined for it, which should be none. Click the New Rule button and create the exact same rule that you did for the txtAuthor field.
8. In the same way, create a rule for the txtDescription control. All three controls should have the same rule.
9. Click OK to close the rules manager and then save the form changes.
Open the ItemSearch form using the Form View and search for some items. The selected item should be displayed in a different background, as shown in Figure 9-26.
Figure 9.26. The ItemSearch form with conditional formatting
Invoking the ItemSearch Form
Now you'll modify the Item form to use the ItemSearch form to select an item. This is a fairly simple matter of adding a Search button to the Form Header that loads the ItemSearch Form. This will work just like the customer search feature you implemented in Chapter 7.
1. In the Property Sheet, select the Form object and the Data tab. For the Filter property, enter [ItemID] = CLng('0'). Also set the Filter On Loan property to Yes. This will keep the form from loading the entire list of items. Instead, it will only load the item selected from the ItemSearch form.
2. Open the Item form using the Layout View. The layout control for the Form Header has two controls; an Image control that displays a logo and a Label that displays the form title. Select the Label control and, from the Arrange tab in the ribbon, click the Split Horizontally button. This will split the cell into two cells, leaving an empty cell on the right.
3. Create a command button and put it in the empty cell. Cancel the control wizard. Enter the Caption property as Search... and enter Search for the Name property. Resize the cells so the Form Header looks like Figure 9-27.
Figure 9.27. The modified Form Header with a Search button
4. Right-click the Search button and click the Build Event link. In the Choose Builder dialog box select Code Builder. This will create an event handler for the OnClick event and open the VBA Editor. Enter the code shown in Listing 9-4.
Example 9.4. Implementation of the Search_Click Method
Private Sub Search_Click()
Dim sForm As String
Dim ID As Integer
sForm = "ItemSearch"
' Open the search form
DoCmd.OpenForm sForm, acNormal, , , , acDialog
' If the form is not loaded, the user clicked the Cancel button
If (IsLoaded(sForm)) Then
ID = Forms(sForm)!txtSelectedItemID
DoCmd.Close acForm, sForm
DoCmd.ApplyFilter "", "[ItemID] = CLng(" & ID & ")", ""
End If
End Sub
Private Sub Form_Load()
DoCmd.ApplyFilter "", "[ItemID] = CLng(0)", ""
End Sub
This code first opens the ItemSearch form. Because it is a modal dialog, the call does not return until the form is closed or hidden. It then checks to see if the form is still loaded. If it is, it uses the txtSelectedItemID control to get the ID of the selected item. It then applies a filter that returns only the selected item.
This code also provides the implementation for the Form_Load method. Because the search feature is manipulating the Filter property, Access tries to remember the last filter that was applied. This code ensures that the initial filter is used when the form is started.
Open the Item form using the Form View. You should notice that no record is displayed. Click the Search button, enter a search, select one of the items, and click the OK button. The ItemSearch form should disappear and the selected item displayed in the Item form, as demonstrated in Figure 9-28.
Figure 9.28. The Item form showing the selected item
Enhancing the Item Form
There are a few more enhancements that I want to show you. You may have noticed that there are no inventory items for the item selected in Figure 9-28. The item has been set up in the database and the author, title, and other details have been defined. But you don't actually have a copy of it in your inventory to lend out. You'll add a facility to this form to receive a copy of the item and add it to the inventory.
There is also a URL set up for this item where additional information can be obtained. You'll add an embedded web browser to this form and bind it to this URL. This will cause the browser to automatically display that web page.
Finally, before we finish the chapter, I'll show you how to add the item image to the Item On Loan tab of the CustomerAdmin form. You implemented this in the previous chapter and left a place on the form to display an image. You'll add that to finish this page.
Adding an Inventory Item
Adding an inventory item is actually really easy to do. In Chapter 4 you created a query called AddInventoryItem, which inserts a record into the InventoryItem table. It requires an ItemID parameter so it knows which item to add. You'll now add a command button to the Item form to run that query. For a little variety, you'll implement the logic behind the button with a macro.
1. Open the Item form using the Layout View. Add a command button to empty cell just to the right of the txtPicture control. Cancel the wizard when it starts. Set the Name properties to AddInventory and the Caption property to Add Inventory.
2. Right-click this control and click the Build Event link. In the Choose Builder dialog, select Macro Builder, which will start the Macro Designer. This macro will perform three actions:
Suppress the normal Access warnings about records being updated
Run the query
Re-query the subform that lists the inventory items so it will refresh its contents
3. The "Add New Action" dropdown list shows the actions that can be added to the macro. Some actions are restricted if the document is not trusted. The SetWarnings action is one of these. To be able to select this action, you must first click the Show All Actions button in the ribbon, as shown in Figure 9-29.
Figure 9.29. The "Show All Actions" button
4. In the "Add New Action" dropdown list, select the SetWarnings action. Make sure the Warnings On parameter is set to No.
5. For the next action, select the OpenQuery action. For the Query Name parameter, select the AddInventoryItem query. This will display the itemID parameter. Enter [ItemID] for its value.
6. For the last action, select Requery and enter InventoryItem for the Control Name parameter.
The final macro design should look like Figure 9-30.
Figure 9.30. The AddInventory macro design
Save the macro and close the Macro Designer. To test this, open the Item form using the Form View, select an item and then click the Add Inventory button. A new record should be added to the subform, as demonstrated in Figure 9-31.
Figure 9.31. A new inventory item added to the subform
Adding a Web Browser
Now you'll add a WebBrowser control to the Item form. You'll use the URL control to automatically navigate to the associated web page.
1. Open the Item form in the Layout View. You'll need to first create a cell to drop the browser in. Select the InventoryItem subform, because it's on the last row. In the Arrange tab of the ribbon, click the Insert Below button add a new row.
WARNING
If the Insert Below button is not enabled, it's probably because you clicked inside the subform, selecting one of its cells. Try clicking on the border of the subform. The Property Sheet will indicate if you have selected the InventoryItem subform.
2. The bottom row will have two cells, merge these together into one. Expand the height of the cell to be about 5 inches.
3. In the Design tab of the ribbon, click the Web Browser Control button, as shown in Figure 9-32.
Figure 9.32. Clicking the Web Browser Control button
4. Then select the large empty cell that you just created. Cancel the control wizard when it starts. In the Property Sheet set the name of this control to webBrowser.
5. Now you'll add a couple of event handlers. Select the webBrowser control. In the Event tab of the Property Sheet, for the On Navigate Error property, select Event Procedure and click on the ellipses. Enter the following code for the implementation. This simply navigates the web browser to a home page should the specified URL be invalid.
webBrowser.Object.Navigate http://apress.com
6. While in the VBA Editor, add the following code to the Form_Current method:
If (Len(URL) > 0) Then
webBrowser.Object.Navigate URL
webBrowser.Visible = True
Else
webBrowser.Visible = False
End If
This causes the browser to navigate to the address specified by the URL field. If a URL is not specified, the browser is hidden.
There is no place to type a URL so the user can't navigate somewhere else. However, links on the page could take them to other sites. If you want to control where they are navigating to, implement an event handler for the OnBeforeNavigate event. To do so, in the Property Sheet, select the On Before Navigate property, select Event Procedure and then click the ellipses. Add the following code for this event's implementation.
If (Left(CStr(URL), 17) <> "http://apress.com") Then
Cancel = True
End If
This code will prevent navigating to any URL that doesn't start with "apress.com."
NOTE
You might want to comment out this code and not leave it in your application. It is here for demonstration purposes only.
Open the Item form in the Form View and search to one of the Apress titles. The embedded web browser should display the associated page on the Access site, as demonstrated in Figure 9-33.
Figure 9.33. The Item form with the web browser enabled
Using Page Breaks
Now that you've added the web browser, the Item form has become too large to display all of it at once. Of course you can scroll down to see the rest of the form. As an alternative, you can insert page breaks in the form and then navigate between pages by using buttons.
1. Open the Item form using the Design View. From the Design tab of the ribbon, click on the Page Break button, as shown in Figure 9-34.
Figure 9.34. Clicking the Page Break button
2. Then click on the Item form, just above the webBrowser control. You should see a series of dots, shown in Figure 9-35, which indicates a page break.
Figure 9.35. The page break indicator
3. Now you'll add buttons to the Form Footer that the user can use to navigate to each page. Click the Button button in the ribbon and then click on the Form Footer. Cancel the control wizard. Set the Name property to Page1 and the Caption property to Details. Right-click the control and click the Build Event link. In the Choose Builder dialog box, select the Macro Builder.
4. In the Macro Designer, select the GoToPage action and enter the Page Number parameter as 1. The macro should look like Figure 9-36. Save and close the Macro Designer.
Figure 9.36. Implementing the Page1 button
5. In the same way, add another button named Page2 and set the Caption property to Browser. Implement the button with a macro, setting the Page Number parameter to 2.
6. Now you'll need to arrange the buttons in a layout control. The Form Footer should look like Figure 9-37.
Figure 9.37. The layout of the Form Footer
Save the form changes and switch to the Form View. You will have buttons at the bottom of the form that you can use to easily switch between pages as shown in Figure 9-38.
Figure 9.38. The Item form with page buttons
Modifying the CustomerLoan Form
The last thing you'll need to do is modify the CustomerLoan form and add an image control.
1. Open the LoanDetail query in the Design View. Double-click the Picture field in the Item table to add this column to the query. Save the query and then close it.
2. Open the CustomerLoans form in the Layout View. Click the Add Existing Fields button in the ribbon.
3. Drag the Picture field to the empty cell underneath the Renew button. This will insert a new column for the associated label. Right-click the label and click the Delete Column link.
4. Change the Name property of the Picture control to txtPicture. Set the Visible property to No.
5. In the Design tab of the ribbon, click the Image button and then click in the Detail section. Cancel the control wizard. Drag this control to the empty cell on the right side of the form. Set the Name property to imgPicture.
6. In the Data tab of the Property Sheet, set the Control Source property to =GetFullImagePath(txtPicture).
7. Save the form.
To test the change, open the CustomerAdmin form using the Form View, select a customer and select the Item On Loan tab. The form should look like Figure 9-39.
Figure 9.39. The modified CustomerAdmin form
Summary
In this chapter you made some significant enhancements to the Item form, including:
Adding images to the item data
Providing a search facility to look for items in the database
Embedding a web browser for additional item information
Some of the other Access techniques that you learned include:
Importing tables from another Access database
Using conditional formatting
Calling an action query from a command button
Using page breaks
In the next chapter, you will make changes to this application to prepare it for the end users. This will include creating a navigation form and locking down the design and development features.
Chapter 10. Enhancing the User Experience
At this point, you have developed a well-functioned application and you're ready to turn it over to the end users... well, not quite yet. When you open the Access file, there are lots of neat things like tables and queries, macros, and VBA – all the sorts of items that we developers like to work with. Most end users are not going to want to see this. More important, if you have to support it, you don't want them seeing it either, much less have the ability to change any of it.
In this chapter, I'll show you some ways to package the application with the end user in mind. The first step is to provide a way for them to navigate to the forms that they will be using. I'll demonstrate two ways to accomplish this using a custom navigation form and a custom ribbon. With that in place, you'll then need to remove the standard navigation and lock down the application to prevent unwanted alterations.
Form Navigation
When the application is first loaded, you will need some sort of "welcome" form that will present to the user their choices of things they can do such as check out a customer, look for an item, and so on. Access 2010 provides a really nice facility for creating a navigation form by simply dragging the forms and reports to the appropriate navigation structure. However, the complex forms you have developed so far will not work with this technique. Essentially, the navigation form becomes the main form and all your other forms are added as subforms. As I explained in Chapter 8, there are limitations with subforms. For example, you cannot use a Split Form as a subform.
NOTE
I will demonstrate the built-in Navigation Form template in Chapter 15. You will use this to organize the web forms.
So you'll need to create your own custom navigation form. Fortunately, this is simple to do – almost surprisingly so. The hardest part is usually deciding what options should be allowed and how to best organize them. Although there is no right way to organize these, I suggest the following structure:
Operations (tasks that you do all day long)
Checkout a customer
Set up a new customer or view/modify an existing one
Lookup an item
Administration (updating configurable objects)
Items
Categories
Media types
Maintenance (routine tasks for ongoing support and maintenance)
Calculate late fees
Cancel old requests
Creating the Menu Form
You'll create a blank form and place command buttons on it. You can take advantage of the control wizards that will set up a macro to open the associated form when the user clicks a command button. You will use a TabControl to organize the buttons into the three top level items (Operations, Administration, and Maintenance).
1. Click the Blank Form button in the Create tab of the ribbon.
2. In the Design tab of the ribbon, click the Tab Control button, and then draw a rectangle on the form. This will create a TabControl with two pages. Right-click this control and click the Insert Page link. Select each page and set the Name property of each to Operations, Administration, and Maintenance. The form should look like Figure 10-1.
Figure 10.1. The initial Menu form layout
3. In the Property Sheet, select the Form object and set the Record Selectors and Navigation Buttons properties to No. These are not applicable for this form, because it does not access a table.
TIP
Make sure that the control wizards are turned on. To check, click the dropdown icon near the bottom-right corner of the Controls section of the ribbon. This will show all of the available controls. The icon next to the Use Control Wizards icon should be highlighted as shown in Figure 10-2. If not, click the Use Control Wizards link to turn them on.
Figure 10.2. Checking the status of the control wizards
4. Click the Button button and then click the Operations page. This will launch the Command Button Wizard. In the first dialog box, select the OpenForm action as shown in Figure 10-3.
Figure 10.3. Selecting the OpenForm action
5. In the second dialog box, select the CheckOut form, as shown in Figure 10-4.
Figure 10.4. Selecting the CheckOut form
6. The third dialog box, shown in Figure 10-5, provides an option to filter the record when opening the form. This doesn't apply in this scenario, so select the second option, which is to show all records.
Figure 10.5. Selecting the option to show all records
7. The fourth dialog box is used to specify the text or picture that should be displayed on the button. Select the Text option and enter Check Out, as shown in Figure 10-6.
Figure 10.6. Specifying the button caption
8. In the final dialog box, enter the name CheckOut, as shown in Figure 10-7.
Figure 10.7. Specifying the name of the CheckOut button
9. You should now have a button on the Operations page that will open the CheckOut form. Repeat this process to add buttons that will open the CustomerAdmin and Item forms. The page design should look like Figure 10-8.
Figure 10.8. The Operations page layout
10. Using the same approach, add buttons to the Administration page to open the following forms:
Item
Category
Media
On the Maintenance page, you'll create buttons that will call a macro. There are two macros included in the Navigation pane: CalculateLateFees and CancelOldRequests. You created these in Chapters 3 and 4 to demonstrate how to call a data macro and an action query. Now you'll provide buttons in the Menu form to allow the user to call these.
11. Add a command button to the Maintenance page. In the Command Button Wizard, select the RunMacro action, which you'll find in the Miscellaneous category, as shown in Figure 10-9.
Figure 10.9. Selecting the RunMacro action
12. In the next dialog box, select the CalculateLateFees macro, as shown in Figure 10-10.
Figure 10.10. Selecting the CalculateLateFees maco
13. Fill out the rest of the dialog boxes as you did for the other command buttons.
14. Repeat this process to create another button that will call the CancelOldRequests macro.
15. The Maintenance page should look like Figure 10-11.
Figure 10.11. The completed Maintenance page
Save the form and enter the name Menu when prompted. Switch to the Form view. Try clicking the buttons and verify that the appropriate forms are loaded and that they work as expected.
Auto-Loading the Menu Form
The Menu form is a handy way for the user to start any of the forms designed for them to access. Now you'll configure Access to load this form automatically when the application is started.
1. Go to the File tab of the ribbon, which displays the Backstage View.
2. Click the Options button to display the Access Options dialog box.
3. Select the Current Database tab and select the Menu form in the Display Form combo box as shown in Figure 10-12. This tells Access to load the Menu form whenever this file is opened.
Figure 10.12. Selecting the Menu form to be auto-loaded
When you save the changes, you'll get the pop-up dialog shown in Figure 10-13, which lets you know that this change will take affect the next time the file is loaded.
Figure 10.13. Reminder to close and reopen the database
Close the Access application and re-open the Library.accdb file. You should see the Menu form start automatically.
Ribbon Navigation
Another approach to providing navigation to your forms is to customize the ribbon. You can add a tab to the existing ribbon or create your own custom ribbon, with an XML file and a little bit of VBA code. I'll first demonstrate how to add a new tab to the ribbon, and then I will show you how to build your own data-driven menu using a custom ribbon.
Implementing a Sample Ribbon Tab
A sample XML file that creates a new ribbon tab is shown in Listing 10-1.
Example 10.1. Sample Script to Create a Custom Ribbon Tab
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon startFromScratch="false">
<tabs>
<tab id="demo1" label="Demo Tab">
<group id="group1" label="Sample">
<button id="button1" size="large" label="Sample1"
screentip="Sample 1" supertip="This is a sample button"
getImage="GetImage" onAction="OnMenuAction" />
<button id="button2" size="normal" label="Sample2"
screentip="Sample 2" supertip="This is a sample button"
getImage="GetImage" onAction="OnMenuAction" />
<button id="button3" size="normal" label="Sample3"
screentip="Sample 3" supertip="This is a sample button"
getImage="GetImage" onAction="OnMenuAction" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
The ribbon element has a single attribute called startFromScratch. If this is set to false, as it is here, the defined tabs are added to the existing ribbon. Use this option if you want to add a custom tab but leave the standard tabs in place. If you set this to true, all the existing tabs will be removed and the ribbon will only contain whatever is defined in this XML script.
The ribbon element contains a tabs collection, which has a single tab labeled "Demo Tab." Each tab then defines one or more group elements. This script has a single group labeled "Sample," which contains three button elements.
Each button element specifies the following attributes:
id: A unique identifier for this button
size: The size of the graphic for this button (either large or normal)
label: The text that is displayed with the button
screentip: Heading for the hover text
supertip: The hover text that is displayed when the mouse is over this control
getImage: The name of a callback function that supplies the image for this button
onAction: The name of a callback function that is called when the button is clicked
tag: Not shown here, but can be used for providing details to the callback functions
To add this custom tab to your ribbon, you'll need to implement the two callback functions, GetImage and OnMenuAction. Then you will also need to install this ribbon in the database.
At the bottom of the navigation pane, you should see the Main module. Double-click this to display the VBA editor. Add the code shown in Listing 10-2 to the Main code file.
Example 10.2. The Initial Implementation of the Ribbon Callback Functions
Public Sub GetImage(ByVal control As Office.IRibbonControl, ByRef image)
image = "HappyFace"
End Sub
Public Sub OnMenuAction(ByVal control As Office.IRibbonControl)
MsgBox "You've clicked the button " & control.ID & " on the Ribbon"
End Sub
The GetImage callback returns the HappyFace icon, which I'll explain later. The OnMenuAction callback simply displays the ID of the control that was clicked.
NOTE
You will need to add a reference to the Office Object Library. In the VBA Editor, click the Tools menu and then the References link. In the References dialog box, add the Microsoft Office 14.0 Object Library, as shown in Figure 10-14.
Figure 10.14. Adding the object library reference
Now all that's left is to install the XML file. I'll show you one way to do that now, and later in this chapter, I'll show you a second way. In the first approach, you'll create a table named USysRibbons and copy the XML into this table. When a database is opened, Access looks for the USysRibbons table and will automatically install any ribbons that are defined there.
1. From the Create tab of the ribbon, click the Table Design button.
2. Add the following fields:
RibbonID: AutoNumber (set this as the primary key)
RibbonName: Text
RibbonXML: Memo
3. Save the table and enter the name USysRibbons when prompted.
4. Open the table in the Datasheet View. Enter Demo in the RibbonName field. Enter the XML shown in Listing 10-1 in the RibbonXML field and save the record.
The table should look like Figure 10-15.
Figure 10.15. The contents of the USysRibbons table
Close the Access database and then re-open it. Access should have loaded your custom ribbon, but now you need to tell Access to use it.
5. Click the File tab to display the Backstage View.
6. Click the Options button and then select the Current Database tab.
7. About halfway down, in the "Ribbon and Toolbar Options" section, select the Demo ribbon, as shown in Figure 10-16.
Figure 10.16. Selecting the Demo ribbon
Close the dialog and you will be reminded that you'll need to close the database and re-open it. Do that now and when the database is reloaded you should see a Demo tab like the one shown in Figure 10-17.
Figure 10.17. The custom Demo ribbon tab
Try clicking the buttons in the custom tab. You should see a pop-up window that tells you the name of the button that was clicked.
TIP
As you probably know, XML is not very forgiving. The slightest syntax error will prevent the file from loading. If Access encounters an error in processing the XML it will simply ignore your custom ribbon. You won't see any errors, but you won't have a custom tab. This can be frustrating when trying to find where the problem is. While developing and testing custom ribbons, you can enable these error messages. Click the Options button on the Backstage View. In the Client Settings tab, scroll down to the General section. Select the "Show add-in user interface errors" check box, as shown in Figure 10-18.
Figure 10.18. Enabling UI error messages
Displaying the System Objects
You may have noticed that, when you re-opened the database, the USysRibbons table was gone. It's not really gone, but merely hidden from the Navigation pane. Access has several system tables that it uses and these are normally hidden, that is, not included in the Navigation pane.
In addition, any table that you create that starts with "USys" is considered a user-system table and is also hidden. So when you first created the USysRibbons table it was available to you. However, when the database was re-opened, this was no longer visible.
To show both system and user-system tables, click the Options button in the Backstage View. Select the Current Database tab and click the Navigation Options button that is shown in Figure 10-19.
Figure 10.19. Displaying the Navigation Options
In the Navigation Options dialog, shown in Figure 10-20, you can control the types of objects that are included and how they are organized.
Figure 10.20. Enabling the system objects
Select the Show System Objects check box and click the OK button. Then click the OK button to close the Access Option dialog box. You'll see a pop-up saying the changes will not be applied until the database is reloaded. In this case, that's not true. The system tables are added to the Navigation pane immediately.
You should see the USysRibbons table in the Navigation pane and you can view and update its contents. You should also notice the USysApplicationsLog that you used in Chapter 3 to diagnose data macro errors.
Building a Custom Ribbon
Now that you have the basic concepts for building a custom ribbon, you'll develop a data-driven menu feature. This will allow you to design the menu options using a form and then generate the appropriate XML based on the data. As you add new options, the ribbon will automatically adjust.
As I explained earlier, a ribbon uses the following three-level hierarchy for organizing the controls:
Tabs
Groups
Commands
To accommodate this in your design, you'll have a table for each of these levels. You'll then create a form that will allow you to define each of these elements. Once the implementation is done and the data is entered, you'll write VBA code to extract the data and generate the corresponding XML script.
Designing the Tables
You'll start by creating three tables to define the three levels of the ribbon hierarchy. These tables are not really part of the Library data but are internal tables used by the application. To differentiate these, the name will have a "mnu" prefix. The instructions below are abbreviated; you can refer to Chapter 2 if you need more help in designing tables.
1. Create a mnuTab table using the following fields:
TabID: AutoNumber, primary key
Label: Text (100)
Sequence: Number (Integer)
2. Save the table and close it. Then create the mnuGroup table including the following fields:
GroupID: AutoNumber, primary key
TabID: Lookup (use the mnuTab table)
Label: Text (100)
Description: Text (255)
Sequence: Number (Integer)
NOTE
When setting up the Lookup column, select both the TabID and Label fields. Use the defaults values for the remaining dialog boxes.
3. Save the table and close it. Finally, create the mnuCommand table as follows:
CommandID: AutoNumber, primary key
GroupID: Lookup (use the mnuGroup table)
Label: Text (10)
Graphic: Text (10)
GraphicSize: Lookup (enter the allowed choices as large and normal)
TargetType: Lookup (enter the allowed choices as Form, Report, and Macro)
Target: Text (100)
ScreenTip: Text (100)
SuperTip: Text (255)
Sequence: Number (Integer)
NOTE
When setting up the GroupID lookup column, select both the GroupID and Label fields. Use the remaining default values.
4. Open the mnuTab table in the Datasheet view. Add a single record with the Label field Library and Sequence =1.
Creating the Menu Forms
You'll start by generating a form for the mnuCommand table. Then you'll use this as a subform on the mnuGroup form. Because the number of tabs is small, you'll just enter these in the mnuTab table directly.
1. Select the mnuCommand table in the Navigation pane and click the Form button in the Create tab of the ribbon.
2. Delete the CommandID and GroupID controls and their associated labels.
3. Set the control padding to None.
4. Set the Text Align property for all of the labels to Right.
5. Remove the Form Header.
6. Change the Default View to Continuous Forms.
7. Re-arrange the controls to look like Figure 10-21.
Figure 10.21. The design of the mnuCommand form
8. Save the form and enter the name mnuCommand when prompted. Now you'll create the mnuGroup form.
9. Select the mnuGroup table in the Navigation page and click the Form button in the Create tab of the ribbon.
10. This will create Datasheet view to display the associated records of the mnuCommand table. Delete this and add a Subform control in its place. The Subform Wizard will find multiple fields in both table with the same name and will prompt you to select the correct field to use for linking the forms. Select the GroupID field, as shown in Figure 10-22.
Figure 10.22. Selecting the GroupID field for linking the forms
11. Arrange the controls as shown in Figure 10-23.
Figure 10.23. The mnuGroup form layout
Save the form and enter the name mnuGroup when prompted.
Populating the Menu Tables
Now you'll use the mnuGroup form to define the controls that will be placed on the ribbon. You'll follow the same basic structure that you used for the Menu form that you created earlier in the chapter. There will be three groups (Operations, Administration, and Maintenance). The first group will look like Figure 10-24.
Figure 10.24. The Operations group definition
NOTE
You'll be able to see all of the controls on the ribbon, so there's no need to include a link to the Item form on both the Operations and Administration groups. You'll just include it on the Administration group.
The definition of the Administration group should look like Figure 10-25.
Figure 10.25. The Administration group definition
The Maintenance group definition will look like Figure 10-26.
Figure 10.26. The Maintenance group definition
Using MSO Images
You're probably wondering about the unusual values for the Graphic fields. I also promised to explain about "HappyFace." Well, I'll take care of both of those now. Microsoft provides literally hundreds of built-in images that are available for you to use in your application. Just about any icon that exists on a ribbon in any of the Office products is included in this image library as well as icons from many other Microsoft products.
To use them all you need to do is specify the image name. The trick is knowing the correct name for each of the images. The best way to do that is to download a Word document from www.microsoft.com. Go to the following URL:
http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=2d3a18a2-2e75-4e43-
8579-d543c19d0eed
Click the Download button and save the file to your local machine. Then open the Word document and go to the Backstage View. There are two tabs that together provide a gallery of all of the built in images. Click the tabs to see the images and their names, as shown in Figure 10-27.
Figure 10.27. Sample mso image gallery
I made a quick scan of these images and picked some that seemed appropriate for the Library menu controls. Feel free to browse the gallery and choose different images. Just enter the appropriate image name in the Graphic field of the mnuGroup form.
Implementing the Custom Ribbon
Now that you have all of the ribbon elements defined (tabs, groups, and commands) you're ready to write the logic that reads this data and builds the appropriate ribbon XML. You'll start by designing a query that combines the three tables into a single record set. Then you'll execute this query in VBA code.
DESIGNING THE MNUCOMMANDS QUERY
In the Create tab of the ribbon, click the Query Design button. Add the following tables to the query:
mnuTab
mnuGroup
mnuCommand
The query should automatically setup the relationships between the tables. Add the following fields to the query:
mnuTab.TabID
mnuTab.Label
mnuTab.Sequence
mnuGroup.ID
mnuGroup.Label
mnuGoup.Description
mnuGroup.Sequence
mnuCommand.ID
mnuCommand.Label
mnuCommand.Graphic
mnuCommand.GraphicSize
mnuCommand.TargetType
mnuCommand.Target
mnuCommand.ScreenTip
mnuCommand.SuperTip
mnuCommand.Sequence
Uncheck the three Sequence fields so they are not returned in the result set and set the Sort property on these fields to Ascending. The Sequence fields are only included in the query to be used to sort the records.
There is a Label field from all three tables. To avoid ambiguity, you'll give then unique names in the result set. For the mnuTab.Label field, enter the Field name as TabLabel: Label. Likewise, for the mnuGroup.Label field, enter GroupLabel: Label.
Save the query and enter the name mnuCommands when prompted. The query design should look like Figure 10-28.
Figure 10.28. The mnuCommands query design
Switch to the Datasheet View and verify that there are seven rows returned.
GENERATING THE RIBBON XML
Now you'll write the VBA code that will generate the appropriate XML based on the configuration data. Open the Main code file and enter the code shown in Listing 10-3.
Example 10.3. The Implementation of GetRibbonDefinition
Function GetRibbonDefinition() As String
Dim txtXML As String
Dim nTabID As Integer
Dim nGroupID As Integer
nTabID = 0
nGroupID = 0
' Add the XML header info
txtXML = "<customUI xmlns=" & Chr(34) & _
"http://schemas.microsoft.com/office/2009/07/customui" & Chr(34) & ">" & vbCrLf & _
" <ribbon startFromScratch=" & Chr(34) & "false" & Chr(34) & ">" & vbCrLf & _
" <tabs>" & vbCrLf
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("mnuCommands")
Do Until rs.EOF
If (rs.Fields("GroupID") <> nGroupID And nGroupID <> 0) Then
' Close the previous group
txtXML = txtXML & " </group>" & vbCrLf
End If
If (rs.Fields("TabID") <> nTabID And nTabID <> 0) Then
' Close the previous tab
txtXML = txtXML & " </tab>" & vbCrLf
End If
If (rs.Fields("TabID") <> nTabID) Then
' Save the current tab ID
nTabID = rs.Fields("TabID")
' Open the next tab
txtXML = txtXML & " <tab id=" & Chr(34) & "tab" & nTabID & _
Chr(34) & " label=" & Chr(34) & rs.Fields("TabLabel") & _
Chr(34) & ">" & vbCrLf
End If
If (rs.Fields("GroupID") <> nGroupID) Then
' Save the current group ID
nGroupID = rs.Fields("GroupID")
' Open the next group
txtXML = txtXML & " <group id=" & Chr(34) & "group" & _
nGroupID & Chr(34) & " label=" & Chr(34) & _
rs.Fields("GroupLabel") & Chr(34) & ">" & vbCrLf
If (Len(rs.Fields("Description")) > 1) Then
txtXML = txtXML & " <labelControl id=" & Chr(34) & _
"label" & nGroupID & Chr(34) & " label=" & Chr(34) & _
rs.Fields("Description") & Chr(34) & " />" & vbCrLf
End If
End If
' Add a command button
txtXML = txtXML & _
" <button " & _
"id=" & Chr(34) & "button" & _
rs.Fields("CommandID") & Chr(34) & vbCrLf & _
" size=" & Chr(34) & rs.Fields("GraphicSize") & _
Chr(34) & vbCrLf & _
" label=" & Chr(34) & rs.Fields("Label") & _
Chr(34) & vbCrLf & _
" getImage=" & Chr(34) & "GetImage" & _
Chr(34) & vbCrLf & _
" screentip=" & Chr(34) & rs.Fields("ScreenTip") & _
Chr(34) & vbCrLf & _
" supertip=" & Chr(34) & rs.Fields("SuperTip") & _
Chr(34) & vbCrLf & _
" tag=" & Chr(34) & rs.Fields("Graphic") & _
Chr(34) & vbCrLf & _
" onAction=" & Chr(34) & "OnMenuAction" & _
Chr(34) & " />" & vbCrLf
rs.MoveNext
Loop
txtXML = txtXML & " </group>" & vbCrLf
txtXML = txtXML & " </tab>" & vbCrLf
txtXML = txtXML & " </tabs>" & vbCrLf
txtXML = txtXML & " </ribbon>" & vbCrLf
txtXML = txtXML & "</customUI>"
GetRibbonDefinition = txtXML
rs.Close
Set rs = Nothing
End Function
This code may look complicated, but it just executes the mnuCommands query and does a lot of string manipulation to construct a properly formed XML file. The structure of the XML is identical to the one I presented at the beginning of the chapter. Instead of hard-coded data, it is read from the query results and it allows for multiple tabs and groups.
The next step is to write code to load the ribbon. To do that, add another method to the Main module using the following code:
Public Function LoadRibbon() As Integer
On Error Resume Next
Application.LoadCustomUI "Library", GetRibbonDefinition
End Function
This code calls the LoadCustomUI method passing in a hard-coded ribbon name of Library and calling the GetRibbonDefinition method, which supplies the XML. At the beginning of the chapter you populated the USysRibbons table with a RibbonName and RibbonXML fields. Access reads this table on startup and does the same thing as the LoadCustomUI call. The LoadCustomUI method will fail if a ribbon with the same name already exists. The On Error Resume Next statement is used to ignore this error.
IMPLEMENTING THE CALLBACK METHODS
You already have an implementation for the two callback methods, GetImage and OnMenuAction. Now you'll need to replace that demo code with a real implementation. Replace these with the code shown in Listing 10-4.
Example 10.4. The Implementation of the Callback Methods
Public Sub GetImage(ByVal control As Office.IRibbonControl, ByRef image)
image = control.Tag
End Sub
Public Sub OnMenuAction(ByVal control As Office.IRibbonControl)
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("mnuCommand", dbOpenDynaset)
rs.FindFirst "[CommandID]=" & Mid(control.ID, 7)
If rs.NoMatch Then
MsgBox "You've clicked the button " & control.ID & " on the Ribbon"
Else
Select Case rs.Fields("TargetType")
Case "Form"
DoCmd.OpenForm rs.Fields("Target"), acNormal
Case "Report"
DoCmd.OpenReport rs.Fields("Target"), acViewPreview
Case "Macro"
DoCmd.RunMacro rs.Fields("Target")
Case Else
MsgBox "You've clicked the button " & control.ID & " on the Ribbon"
End Select
End If
End Sub
For the GetImage callback, instead of supplying a hard-coded image name, it uses the Tag property. If you look at the implementation of the GetRibbonDefinition method, you'll notice the tag attribute is set using the Graphic field from the mnuCommands query.
The implementation of OnMenuAction uses the ID property to determine which button was clicked. The id attribute was generated using the CommandID field prefixed with the "button" text. This prefix is stripped off and then the mnuCommand table is queried using the CommandID. This method then calls either OpenForm, OpenReport, or RunMacro depending on the TargetType column. The Target column contains the name of the form, report or macro that should be used. If the mnuCommand record was not found or is not one of the supported types, this method just displays the button ID.
USING THE AUTOEXEC MACRO
Now you'll need to call the LoadRibbon method when the database is opened. Access provides an easy way to do that by using the autoexec macro. If you create a macro with that exact name, autoexec, Access will run that for you when the database is opened. You can then put whatever startup logic you need inside this macro.
From the Create tab of the ribbon, click the Macro button, which will display the Macro Designer. Select the RunCode action. Enter LoadRibbon() for the Function Name parameter.
WARNING
Make sure that you include the parentheses. These are needed even if there are no parameters being passed to the function.
Save the macro and enter the name autoexec when prompted. The macro should look like Figure 10-29.
Figure 10.29. The autoexec macro
Now try running this macro. You can click the Run button in the Design tab of the ribbon or double-click it in the Navigation pane. You won't notice it doing anything. You can add a breakpoint to the GetRibbonDefinition method if you want to step through this code.
SELECTING THE LIBRARY RIBBON
At this point the Library ribbon has been installed as well as the Demo ribbon that you added to the USysRibbons table. As before, you have to tell access to use this ribbon before it is visible.
Click the Options button in the Backstage View. On the Current Database tab, expand the Ribbon Name combo box and select the Library ribbon as shown in Figure 10-30.
Figure 10.30. Selecting the Library ribbon
The database is now configured to use the new Library ribbon when it is restarted.
NOTE
You should remove the existing Demo ribbon that you loaded earlier. Open the USysRibbons table and delete the row that is in this table.
Close the Access database and then re-open it. You should have a new Library tab that looks like Figure 10-31.
Figure 10.31. The new Library ribbon tab
Try clicking some of the buttons in the Library ribbon. This should open the associated form. If you hover the mouse over one of the buttons, the text you supplied for the ScreenTip and SuperTip fields should be displayed as demonstrated in Figure 10-32.
Figure 10.32. The custom hover text
TIP
I have shown you two ways to provide custom navigation; a custom form with buttons that use macros to launch a form and a custom ribbon. I'll show you a third way in Chapter 15, which is to use a Navigation Form. As I mentioned, it doesn't work for many of your existing forms. There is also a fourth approach, which is to use a Switchboard. In Access 2010 this feature is still available but they didn't make it easy to find. This is provided for legacy purpose so I didn't cover it but wanted you to know about, for completeness.
Locking Down the Database
You have provided two different ways for the user to navigate to the various forms that are available to them. Now that this is in place you can lock down the database by removing access to the developer-oriented functions. You'll do this in two phases: first by removing much of the menu and navigation. The user will then only see the items that they should be using. Second, because a knowledgeable user can easily circumvent this, you'll compile a executable-only version of the database that has most of the developer features removed.
TIP
I will show you how to lock the database and you should try these options to see what the effect is. However, be sure you undo the changes when you're done as there is still more development to do.
Removing Navigation
By unchecking a few check boxes, you can drastically limit the features available to the end users. This will make Access look more like an application and less like a development platform.
Let's start with the Windows title bar, which probably reads something like "Library : Database (Access 2007) – Microsoft Access." This gives the impression that you have opened an Office document, which, of course, you have. However, you can change this title so it looks like you have launched an application.
1. Open the Backstage View, click the Options button, and select the Current Database tab.
2. At the top of the dialog, enter Library Management System (LMS) for the Application Title.
3. While you're here, you should fix the icon as well. You've probably noticed that the icon in the task bar is the standard Microsoft Access 2010 icon. Click the Browse button and select an icon file. (If you've downloaded my Images\Static folder, you can use the Library.ico file. Otherwise just search the local drive for *.ico files.)
4. Select the Use As Form And Report Icon option. The top portion of the Application Options section should look like Figure 10-33.
Figure 10.33. Setting the application options
5. Click the OK button to save the changes. Notice the icon in the top-left corner of the application and the task bar has changed as well as the title bar.
Now you'll remove the standard navigation options.
6. Go back to the Backstage View, click the Options button and select the Current Database tab.
7. Unselect the following options, as highlighted in Figure 10-34:
Use Access Special Keys
Enable Layout View
Enable design changes for tables in Datasheet view
Display Navigation Pane
Allow Full Menus
Allow Default Shortcut Menus
Figure 10.34. Removing the navigation options
NOTE
Some of these options are redundant. For example, the "Enable Design Changes for Tables in Datasheet View" doesn't really need to be turned off, because the user can't get to the tables anyway. But it's still a good idea to disable this feature. If you decide later to allow navigation to the tables, you'll want to prevent the user from changing them.
8. Click the OK button to close the form. You will need to close the application and re-open it for these changes to take effect. Your database should look like Figure 10-35.
Figure 10.35. The database with navigation removed
Click the File tab to see the Backstage View. You'll notice that is has been changed to pretty much bare bones as well. You may be wondering how you are going to turn these options back on so you can keep developing. Well, don't panic. Click the Privacy Options button as shown in Figure 10-36. This displays the Access Options dialog box that you can use to enable the standard navigation.
Figure 10.36. Using the Privacy Options button
Compiling the Database
As you can see, even though you have removed access for most users, there's nothing to prevent someone from bypassing this and turning the navigation and menus back on. However, Access gives you a way to create a version of the database that does not contain any of the code and that does not allow the Design Views. First, turn on the "Allow Full Menus" option, if they're not currently enabled.
1. Go to the Backstage View and click the "Save & Publish" tab.
2. Select the "Save Database As" option and then select the Make ACCDE format in the Advanced section as shown in Figure 10-37.
Figure 10.37. Saving as an ACCDE file
3. Click the Save As button, which will display a Save As dialog box. You can use the default file name, which should be Library.accde. This will create a new file with the .accde extension. When this has finished, close the existing database. Then open the new Library.accde file. You'll need to turn off the Allow Full Menus options.
The Library.accde is now ready for the end users. If you turn on the Display Navigation Pane option, for example, you'll be able to browse the various database objects but you cannot modify them. You will still need to have Access installed to be able to open this file. In Chapter 14, I will show you how to distribute this file and use the Access Runtime so you can deploy this without needing to purchase a copy of Access for every end user.
Summary
In this chapter you packaged your application so it is "user-ready." This implemented two methods for navigating to the various forms: a simple navigation form and a custom ribbon. You also developed a data-driven menu system so the ribbon controls are generated automatically based on the menu configuration. As you add new features to the database, you'll just need to add them to the menu configuration and when the database is reloaded, the associated control will be added to the ribbon.
You also locked down the database to remove the developer features. The first step was to remove the navigation to these features. You then compiled a .accde file that has the development features removed.
The key concepts that were introduced in the chapter include:
Using the Command Button Wizard to create macros to launch a form or run a macro
Creating a custom UI ribbon
Adding custom ribbons to the USysRibbons table
Using mso images
Creating an autoexec macro
Configuring the Access navigation options
Compiling a locked-down version of your database
In the next chapter you will look at some more of the visual aspects of your Access database such as colors, fonts and images. This will enable you to quickly give your application a whole new look.
Chapter 11. Branding with Themes and Styles
Branding is a marketing concept that that focuses on product identification or familiarity. It goes as far back as the days of the large cattle-drives, where a cow was branded with a symbol so everyone would know whom it belonged to. The idea is that people recognize colors and shapes that they are familiar with. You can go to a store and pick out a product without even reading the label, just because you recognize the color or shape of the package.
People expect that same familiarity when it comes to software applications. If you go to pizzahut.com to order lunch and don't see the red roof logo, you will probably consider the site suspect and order somewhere else. There is comfort in familiarity. When you develop your Access applications, you will likely have to deal with the necessity for branding.
In this chapter, you will brand your Library application to look like the apress.com website. It's fairly easy to do with just a few simple techniques. I will first explain how to use themes, as this will go a long way to mirror the colors and fonts. Then I'll show you how to use the image gallery to place a few graphics on the forms. The end result will be a metamorphosis of the user experience.
Using Office Themes
Each Office product allows you to create, view, and edit a type of document, such as a text document (Word), spreadsheet (Excel), or presentation (PowerPoint). There is a fairly clear distinction between the content (the data supplied by and maintained by the users) and the application (the menus, ribbons, and forms) provided by the Office application. Office themes allow users to customize the colors and fonts used in the application areas but have no effect on the content. Thus, two users can open up the exact same document and have a completely different look and feel.
Access is sort of the odd man out when it comes to Microsoft Office products, because your Access database includes forms. So the users will see both built-in Access UI elements as well as UI elements that are included in the content, such as forms. In the last chapter, I showed you how to remove as much of the Access UI as possible. However, you can't remove everything (and you don't want to). So you'll have your own custom UI elements, but they will still run under the Office UI framework.
I will first explain how to customize the standard Office UI, and then we'll look at how that spills over into your form design.
Understanding Office Themes
I think the best way to fully understand themes is to create one for yourself. I'll show you how to define a custom theme, and then we'll look into how it works. If you open any form in the Design View or the Layout View, in the Design tab of the ribbon you'll see the Themes group shown in Figure 11-1.
Figure 11.1. The Themes group of the Design tab
Click the Themes button and you'll see a list of themes, shown in Figure 11-2, that you can select from. As you hover the mouse over each of the choices, you can see your form redrawn based on the selected theme.
Figure 11.2. The available themes
This dialog is divided into several sections. The first shows the theme that is currently defined for this database. If you have any custom themes, they will be displayed next. Finally, the built-in themes are listed. Just click off this dialog box to close it; you won't change the theme at this time.
CREATING A CUSTOM COLOR THEME
A theme is comprised of a color scheme and a font scheme. You define these separately and then later you can save them as a theme. Let's start with the color scheme.
1. Click the Colors button in the ribbon. You should see a fairly long list of existing color schemes, as shown in Figure 11-3.
Figure 11.3. Viewing the available color schemes
2. As you can see, the custom and built-in color schemes are listed separately. I have already created a color scheme named Apress. Click the Create New Theme Colors link at the bottom of this list, which will display the Create New Theme Colors dialog box shown in Figure 11-4.
Figure 11.4. The Create New Theme Colors dialog box
NOTE
A color scheme is simply a collection of twelve colors. There are two pairs of background/foreground colors. Normally you will use the light foreground with the dark background and vice versa. This allows you to visually toggle between selected items and non-selected items. There are also six accent colors and two hyperlink colors.
In my opinion, the names given to the first four colors are difficult to follow. The first two are the primary foreground and background colors. The next two are the alternate foreground and background colors. When you need to select a color from either the Property Sheet or the color picker, different names are used. Fortunately, those names actually make sense (Text 1, Background 1, Text 2, Background 2). Also, note that the preview picture is wrong and doesn't display the colors correctly. Just ignore this.
3. The first two colors are just standard black and white, and you can leave this as it is. The fourth color, which is called Text/Background – Light 2, should be changed. Click the dropdown icon next to it, which will reveal the standard color picker, shown in Figure 11-5.
Figure 11.5. The standard color picker
4. The top portion of the color picker shows the theme colors. There are a row of twelve colors, each one representing the current theme colors. Below each are five shades of that color. The idea is that once you have picked the colors you will use, from then on you normally pick from those colors or possibly a lighter or darker shade of one of these colors. However, at this point you are still defining the colors, so this is not helpful. Instead, click the More Colors link to display the Colors dialog box shown in Figure 11-6.
Figure 11.6. Defining the theme color
5. Go to the Custom tab to select the appropriate color. What I did to eyeball the correct color is drag this dialog on top of the color I was trying to match. Then I moved the crosshairs to get the correct color and moved the slider to adjust the brightness. I did that until the preview of the New color matched. Normally, however, your client should be able to tell you the correct settings for each color. For this color enter the following values:
Red: 209
Green: 209
Blue: 209
6. Click OK to update this color.
7. Repeat this procedure to set the color for the remaining colors using the values in Table 11-1. You can leave the hyperlink colors as they are.
Table 11.1. Accent Color Definitions
Theme Color
Red
Green
Blue
Text1
0
0
0
Background 1
255
255
255
Text 2
255
255
255
Background 2
209
209
209
Accent 1
227
184
61
Accent 2
157
157
157
Accent 3
71
88
137
Accent 4
205
61
57
Accent 5
75
175
198
Accent 6
61
58
68
8. When you have finished, enter the Name Apress, and click the Save button.
DEFINING THE FONT SCHEME
Setting up a font scheme is pretty simple: there are only two font families that you'll need to define. At first glance, that may seem too simplistic. However, a good UI design uses a consistent font throughout the application. Too many different fonts can make the design seem cluttered and disorganized. It is a really good design practice to limit the design to only two fonts. You can always use fonts that are not included in the theme, but in most cases you should stay with these two font families.
1. Click the Fonts button on the ribbon, which will list the existing font schemes as shown in Figure 11-7.
Figure 11.7. The existing font schemes
2. Again, the custom and built-in schemes are separated. Click the Create New Theme Fonts link, which will display the Create New Theme Fonts dialog box.
3. Select Arial for the heading font and Calibri for the body font.
4. Enter Apress for the Name, as shown in Figure 11-8. Click the Save button to update the font scheme.
Figure 11.8. Defining a new font scheme
CREATING AN OFFICE THEME
At this point, you have created a color scheme and a font scheme. Access has already selected these as the schemes defined for the Library database. You've probably already noticed that your forms have changed appearance because they are now using the new color and font schemes that you defined. Now you'll save the current configuration as a custom theme.
Click the Themes button in the ribbon, which will display the list of existing themes, as shown in Figure 11-2. At the bottom of this list, click the Save Current Theme link. In the Save Current Theme dialog box, enter the name Apress.thmx, as shown in Figure 11-9.
Figure 11.9. Saving the Apress.thmx file
The theme definition is saved in a file with a .thmx extension. Like all Office files ending in "x" this is actually a compressed folder that contains several files and subfolders. It includes a .jpg file that is used for the icon that is displayed in the list of themes. There is also an .xml file that provides the theme definition. A portion of that is shown in Listing 11-1.
Example 11.1. Partial Listing of theme.xml
<a:themeElements>
<a:clrScheme name="Apress">
<a:dk1><a:sysClr val="windowText" lastClr="000000"/></a:dk1>
<a:lt1><a:sysClr val="window" lastClr="FFFFFF"/></a:lt1>
<a:dk2><a:srgbClr val="FFFFFF"/></a:dk2>
<a:lt2><a:srgbClr val="D1D1D1"/></a:lt2>
<a:accent1><a:srgbClr val="E3B83D"/></a:accent1>
<a:accent2><a:srgbClr val="9D9D9D"/></a:accent2>
<a:accent3><a:srgbClr val="475889"/></a:accent3>
<a:accent4><a:srgbClr val="CD3D39"/></a:accent4>
<a:accent5><a:srgbClr val="4BACC6"/></a:accent5>
<a:accent6><a:srgbClr val="3D3A44"/></a:accent6>
<a:hlink><a:srgbClr val="0000FF"/></a:hlink>
<a:folHlink><a:srgbClr val="800080"/></a:folHlink>
</a:clrScheme>
<a:fontScheme name="Apress">
<a:majorFont>
<a:latin typeface="Arial"/><a:ea typeface=""/><a:cs typeface=""/>
</a:majorFont>
<a:minorFont>
<a:latin typeface="Calibri"/><a:ea typeface=""/><a:cs typeface=""/>
</a:minorFont>
</a:fontScheme>
<a:fmtScheme name="Composite">
<a:fillStyleLst>
<a:solidFill><a:schemeClr val="phClr"/></a:solidFill>
<a:gradFill rotWithShape="1">
This theme definition specifies the twelve colors and the two fonts as you would expect. It also defines some formatting schemes including fills and gradients. These are inherited from the standard Office effects.
NOTE
In Word and Excel, you can also define effects, which control how lines and fills are drawn. This is not possible in Access. Your custom theme will include the effects defined by the default theme.
Notice the location of the Apress.thmx file. It is in the following folder:
%USERPROFILE%\AppData\Roaming\Microsoft\Templates\Document Themes
If you want to share this theme with other users, you'll need to send a copy for them to install on their local machine. As an alternative, you can place this in a shared location. Any user who wants to use this theme can add it by browsing to that location and selecting this file.
The extra step of creating the Apress.thmx file is not necessary for the Library application. In your Access database, all you need to do is define the color and font scheme. The color and font schemes are stored in the database and will be used when other users open the database file. The theme file is used to share these schemes with other Office applications. For example, open Word or Excel and notice the Apress theme is now available, as demonstrated in Figure 11-10.
Figure 11.10. The theme is available in all Office applications.
The reverse of this also true; any theme that was created in any Office application can be used in Access as well. Before designing a custom theme, you should check to see if an appropriate theme has already been defined. This could save you some time by simply selecting that theme.
You can apply a theme to only a single form, leaving the others as they are. Perhaps you'll want to use a different theme for the main navigation form. Of maybe the data entry forms should have a different look and feel from the administrative forms.
To do that, right-click the theme that you want to apply as shown in Figure 11-11. Then click the Apply Theme to This Object Only link.
Figure 11.11. Applying a theme to a single form
The first link, Apply Theme to All Matching Objects, can be useful if you have already applied a different theme to a subset of forms. If you want to change all of these at once, this option will update the current form and all others that also use the same theme.
Applying an Office Theme
So far, you have defined the color and font schemes, which Access will use in its UI elements. Now you'll need to adjust your forms to use the appropriate colors. You have 10 colors to work with (ignoring the two that are used for hyperlinks). The forms created by Access will use the theme colors but may not use the ones you intended. For example, it may use Accent 1 and you wanted Accent 3 to be used instead.
Open the Menu form in the Layout View and look in the Property Sheet for the Tab Control object. The default color and font definitions will look like Figure 11-12.
Figure 11.12. The default color and font selections
Notice all the foreground colors use Text 1 and the background colors use Background 1. To achieve the desired effect, the Lighter or Darker attributes are used. However, the look you're trying to match uses a different approach. The main navigation area for the www.apress.com site is shown in Figure 11-13.
Figure 11.13. Sample Apress menu
The background is black and the menu options are in white. Actually, the menu text is a light gray but they turn white when the mouse is hovered over that option. For the following properties, click the ellipses to display the color picker and then select the standard Black color:
Back Color
Hover Color
Pressed Color
TIP
There are several ways that you can select the Black color. There is a Theme color that is Black, which is the primary text color. There is also a Standard color that you can use. This is probably just a matter of personal preference, but to use a Theme color that is intended as foreground text, as a background color seems to be violating the purpose of a Theme. However, by using the Standard color, the background will not change if someone modifies the Theme.
For the corresponding foreground colors (Hover Fore Color, Pressed Fore Color, and Fore Color) use the Text 2 theme color. For the Fore Color property, add the Darker 15% modifier.
The final Property Sheet will look like Figure 11-14.
Figure 11.14. The updated color properties
TIP
If you're not sure which color to use, click the ellipses next to the particular property that you're setting, which will display the color picker. If you hover the mouse over one of the theme colors, it will tell you the color name, as shown in Figure 11-15. Also, if you hover over one of the various shades that are displayed, it will indicate the corresponding Lighter or Darker attribute. You can click on one of these colors and the property will be updated with the corresponding value.
Figure 11.15. Using the color picker
The buttons on the form use the Accent 1 color, which is what you'll want but they use a lighter shade. By default, the background colors for buttons are:
Back Color: Accent 1, Lighter 40%
Hover Color: Accent 1, Lighter 60%
Pressed Color: Accent 1, Darker 25%
Remove the Lighter 40% modifier on the Back Color property. And change the Hover Color property to use Lighter 25%. You'll need to make these changes to all eight buttons on this form.
Making Other Visual Adjustments
If you compare the Access form with the apress.com site, you'll notice a few visual differences. You'll make some minor adjustments to closer match the UI. The menu on the website uses links instead of tabs. However, by squaring off the tabs and removing the border, you can approximate the same effect.
1. In Access, select the Tab Control and set the Border Style property to Transparent.
2. From the Format tab of the ribbon, click the Change Shape button and click the Rectangle shape, as shown in Figure 11-16.
Figure 11.16. Change the TabControl to use square corners.
The final Menu from should look like Figure 11-17. If you compare this to the website sample shown in Figure 11-13, you can see that that it's a pretty close match.
Figure 11.17. The final Menu form
Using Graphics
The other principle factor in branding an application is using familiar graphics. I mentioned the Pizza Hut logo at the beginning of the chapter. Most organizations have at least one widely known symbol that will give instant brand recognition. For Apress, the yellow and black banner is a key element.
Adding static images in Access 2010 is really easy. When you first use a graphic file it is added to the image gallery. Reusing it is as simple as selecting it from a dropdown list. I have created a few graphic files that you'll use for this project. If you haven't already, download my Images folder. The Static subfolder contains the graphics you'll need.
Adding a Banner Graphic
You'll add this black and yellow banner to the Menu form. You'll first need to create an empty cell to put the graphic in. Then you'll use the image gallery to add the images to the form.
1. Open the Menu form in the Layout View and select the TabControl. From the Arrange tab of the ribbon, click the Insert Above button. This will create an empty cell; expand its height to be about 1 inch.
2. From the Design tab of the ribbon, click the Insert Image button, as shown in Figure 11-18. This will list the images that are currently in your image gallery; but since there are none, it will just have a Browse link.
Figure 11.18. Selecting the Browse link
3. Click the Browse link, navigate to the Images\Static folder, and select the AppressLogo.jpg file.
4. Next click the empty cell. This will add the image to this cell. The Size Mode property defaults to Zoom. Change this to Clip. This graphic was made quite long to accommodate some of the wider forms. Rather than zooming out to fit the entire graphic, you just want to clip off whatever doesn't fit on the form.
5. Now you'll eliminate the white space between the banner and the Tab Control. In the Property Sheet, select the Detail object and the Format tab. For the Back Color property, select the standard Black color.
Open the Menu form using the Form View. The final design should look like Figure 11-19.
Figure 11.19. The final Menu form design
No go back to the Layout View and click the Insert Image button again, this time you'll see the logo that you just used as demonstrated in Figure 11-20. When you need to add this to other form, you can simply select it from this list.
Figure 11.20. The logo included in the image gallery
If you need to modify an image that has been loaded in the gallery, right-click the image in the gallery and click the Update link, as shown in Figure 11-21.
Figure 11.21. Updating an existing gallery image
Then browse to and select the modified file. The new version will be stored in the gallery, replacing the existing one. Also, any place that this image has been used will be updated automatically.
Using a Background Image
Another design element that you'll want to mimic is the use of a header graphic that has a curved top-right corner as shown in Figure 11-22. You'll add this effect to the Category form.
Figure 11.22. Sample Apress heading
1. Open the Category form in the Design View. In the Property Sheet, select the Form object.
2. The Picture property is used to specify the background image for the form. Click the dropdown icon next to this property. Notice that the ApressLogo image is listed here, as demonstrated in Figure 11-23.
Figure 11.23. Selecting the image gallery from the Property Sheet
NOTE
The Property Sheet is integrated into the image gallery. Any items that are already in the gallery are available in the dropdown list. As I'll show you, when you select a new image, it will be loaded into the gallery as well.
3. Change the Picture Type property to Shared. This will indicate that you want to use the image gallery.
4. Select the Picture property and click the ellipses and browse to and select the ApressArc.jpg file.
5. The image will be displayed in the middle of the form. Change the Picture Alignment property to Top Right.
6. Shrink the height of the Form Header so the background picture just fits in the header section. You'll probably have to first shrink the height of the Label control.
7. Select the Label control and set the Fore Color property to Text 2.
Notice that when you click the Insert Image button in the ribbon, the new file is now included in the list. Open the Category form using the Form View. The form should look like Figure 11-24.
Figure 11.24. The modified Category form
The other forms will need similar modifications; I'll leave that for you to do on your own.
Summary
In this chapter you "branded" your Library application to mimic the www.apress.com website. Anyone familiar with that site will easily recognize your application as being from the same organization. While this is a somewhat fictional exercise, the concept is important. End users tend to respond first to how a system looks before they notice how well it works. Developers need to concentrate effort on the UI as well as the functionality of the systems they build.
You created a custom theme, following the same color and fonts schemes found on the website. You then modified the forms to use the appropriate colors in the right places. You added a few graphics to the forms making use of the image gallery. With some simple techniques, the effective use of color, fonts, and graphics turned an average looking application into one with some "wow" factor. More important, the instant brand recognition will go a long way in providing acceptance and comfort.
In the next chapter you'll create reports for the Library application. As you'll see, reports are essentially read-only forms that are designed for print output.
