Managing Images on a Data Access Page

Displaying images on data access pages has several commonalities with displaying images on forms in Access, with some qualifying distinctions. Happily, you can use an image control to display your image on a data access page. But instead of directing the control at the filename and path for an image, you're likely to use the URL for an image that can specify a protocol, path , and filename.

And just as with a LAN-based solution for an Access form, you're likely to maintain your image addresses in a separate table with a primary key that matches the primary key for the rest of the data about your entities (items that the pictures represent). However, when using data access pages, you join the two tables directly behind the page instead of in a separate query. This is because using a standalone query as the record source for a form on a data access page makes the form read-only. This is not necessarily the case for Access forms on LANs.

A third distinction between presenting images on data access pages and presenting them on Access forms is that data access pages save you from having to rely on event procedures to manage the display of images. You can merely designate a field with a URL as the ControlSource property for the Image control. The navigation bar controls on data access pages are smart enough to eliminate the need for any custom coding. With Access forms, to avoid an error, event procedures typically must detect whether a user is moving beyond the first or last record.

Creating Your Image Files for the Web

The sample described in this section works with a variation of the employee pictures that ship with the Access database file for the Northwind sample. Although the picture images ship with the sample, they are standalone files. As you'll recall, these files have a bitmap image format, which is uncommon in Web applications. Alternative graphic formats, such as those with a .jpg file type, represent images with fewer bytes than bitmap files. I used the Picture Library package that ships with Office 2003 to create another set of employee image files based on the original images that ship with the Northwind database. Picture Library permits you to open a folder of bitmap (.bmp) files and save the image files in another file type, such as .jpg. I specified a compression setting of 50 percent, which reduced the file size of the images from around 40 KB each to slightly under 10 KB each with only a modest reduction in clarity.

Note  

If Picture Library is unavailable, you can use the .jpg image files included with the companion content for this chapter. You can also convert image file types with your favorite graphics package. When applying the techniques just discussed to image files other than those in the example, request an appropriate format from the supplier of the images if you don't have a package for performing the conversion.

After you save your new image files in a format appropriate for the Web, copy them to a Web site. You can do this differently for an intranet to which you have LAN access than you would for a remote Web site that you must connect to over the Internet. However, Picture Library is well suited to this kind of task ”it can compress and copy the full set of nine images in one step to either a LAN or a remote Web site location. To use a remote Web site, create a shortcut to the Web site with the Add Network Place Wizard in Windows XP. Then browse to the shortcut in the My Network Places folder from Picture Library. When working with an intranet to which you have LAN access, use Picture Library to specify the destination folder on your LAN for the images. The steps with Picture Library include:

• Designating a Picture Shortcut that points at the folder with the images that you want to copy and/or convert by choosing File, Add Picture Shortcut and then navigating to the folder with the images.

• Choosing File, Export to open a pane for managing the export process.

• Selecting all the image files that you want to convert and copy.

• Browsing to a destination folder or network shortcut.

• Specifying an export file format and optionally specifying JPEG options.

• Clicking OK.

If you are working without Picture Library, use your favorite graphics package for the conversion. If it can copy the files like Picture Library does, you're done. Otherwise, use another program to move the converted files to a destination folder. For example, Windows Explorer for a LAN or FrontPage for a Web site with FrontPage extensions. To use FrontPage to copy the converted image files to a remote Web site, follow these steps:

• Open FrontPage to the Web site to which you want to import the image files.

• From the Folder List, select a location where you want to import the image files.

• Choose File, Import.

• Use the Add File button to successively select as many files as you want to import to the Web site from your local workstation or LAN. If all the files that you want to import reside in one folder, use the Add Folder button to specify the whole folder in one step.

• When you finish specifying the location of the files to import to the Web site, click OK.

• When the import completes, click Close to remove the Import dialog box.

After copying the image files to this book's sample Web site, the images folder of the pma11 Web site has nine image files (plus one ”thumbs.db ”to facilitate the presentation of the images in a thumbnail view). The names for the image files range from EMPID1.jpg to EMPID9.jpg. Figure 14-10 shows the Folder List window with the Web Site tab to its right providing more detail on the image folder contents.

Figure 14.10: A collection of .jpg files in the images folder of a Web site for use as images on a data access page.

Creating a Record Source with Picture Addresses

The sample application for displaying images on a data access page shows selected column values from the Employees table, along with the image for each employee. There are several ways to organize this data for use as the record source for a data access page. This presentation opts for simplicity and read/write functionality for the bound fields.

Create a table with a name such as EmployeePic_Addresses that contains EmployeeID and URL fields for the image of each employee. For example, the URL for the first employee in the current sample is cabSony1/pma11/images

/empid1.jpg. Name the column of URL values for images Pic_Address . You will need an application for adding, deleting, and possibly updating records in the table. Review Chapter 4 for sample code that describes how to create and maintain the table with Jet SQL statements.

Note  

A complete version of the EmployeePic_Addresses table is in this chapter's sample Access database file in the companion content for this chapter. It's imperative that the table have a primary key with values matching rows from the Employees table.

Creating a Page to Display Images

There are several different approaches to developing data access pages on record sources. This example illustrates some variations on the preceding example for the dapProducts page. Start by generating a blank data access page for editing in Design view. To do this, select Pages in the Objects bar of the Database window. Then click New on the Database window, and click OK in the New DataAccess Page dialog box. If the Field List does not show, click its control on the toolbar to make it appear. This completes the generation of a blank data access page and prepares it for editing.

Next, populate the page with fields from the Employees table. Open the Tables folder, and click the expand control for the Employees table in that folder. Successively double-click the fields to add to the form. These are EmployeeID , FirstName , LastName , Country , HomePhone , and Extension . These steps add a subset of fields from the Employees table for display on the data access page.

It will be helpful to show the URL for the image that the form will ultimately display. You can do this with a text box bound to the Pic_Address column in the EmployeePic_Addresses table. To add this text box to the page, select the text box with a ControlSource property setting of Extension. Selecting this text box enables Access to add a new text box below it. Then click the plus sign (+) to expand the EmployeePic_Addresses table in the Field List. Finally, double-click the Pic_Address column name. If the Layout Wizard dialog box appears, select Columnar and click OK. Access automatically opens a Relationship Wizard dialog box to link the EmployeePic_Addresses table to the Employees table. (See Figure 14-11.) Accepting the default setting is important for preserving the ability to update Employee table fields through the form on the page. The wizard specifies that the EmployeePic_Addresses table is in a one-to-many relationship with the Employees table and that the EmployeePic_Addresses table is on the many side of the relationship.

Figure 14.11: The Relationship Wizard for dynamically building relationships for the tables behind a form on a data access page.

The steps in the preceding paragraph complete the specification of a form with a set of text boxes similar in design to the dapProducts page. However, this example demonstrates how to make a couple of changes to this basic form. First, the example replaces the text box for the Country field with a bound drop-down list control. This makes it easy for users to change the Country field to another legitimate value. Second, the example illustrates how to add an image control to a form and base its display on a field in the current row on the record source for the data access page.

The drop-down list control for this data access page is different from the one in the preceding sample. The preceding data access page illustrated how to create an unbound control, but the drop-down list control for this data access page is bound to the Country column. Therefore, the control always shows the value of the column for the current row in the data access page's record source. In addition, you can use the drop-down list to update that value.

To add a bound drop-down list control for the Country field, delete the text box for the Country field so that the drop-down list control can take its place. Remove the text box by selecting it and then pressing the Delete key on the keyboard. Next, open the Toolbox and clear the Control Wizard's button if it's selected. Then add a drop-down list control from the Toolbox to the data access page. Select Country as the ControlSource property setting on the Data tab of the drop-down list control's Properties dialog box. This binds the control to the Country column in the record source for the page. Next, type SELECT DISTINCT Country FROM Employees into the ListRowSource property setting. This statement enables the drop-down list to show each country in the Employees table without duplicating any country. Then select Country as the ListBoundField and the ListDisplayField property values. This completes the control property settings that determines whether the control displays values and permits updates.

There are some additional settings that the control needs. Change the Id setting on the Other tab from DropdownList0 to cboCountry . Next, select the label for the drop-down list control. Make two changes on the Other tab of its Properties dialog box. First, type cboCountry_Label as the Id setting. Second, type Country : as the InnerText property setting. Finish the insertion of the bound drop-down list control into the form on the page by moving its label and control so that they take the empty slot left by the deleted text box for the Country field.

The form is now ready for the addition of an image control. Click the image control in the Toolbox, and then click the upper-left corner where you want your image to appear. At this point, a dialog box prompts you to insert a picture. It's not mandatory that you designate a picture, but choosing a file that contains an image with the right dimensions sizes the image control for you automatically. This is convenient when you don't want the image control to clip or distort the dimensions of your images. You can type the address of the image for the first employee, such as http://cabSony1/pma11/empid1.jpg . This creates a static image for the control no matter which record shows, but the control does have the right size for the image file. Next, open the Properties dialog box for the image control. Then highlight Pic_Address from the drop-down list for the ControlSource property on the Data tab. This assignment allows the image control to reflect the image for the current employee.

Complete the layout of the data access page by dragging the lower edge of the section above the navigation bar so that there is sufficient space to move the Pic_Address text box below the image control. Then drag the text box below the image control, and resize the text box so that it's wide enough to show the URLs for images. Complete the example by saving the page. The page is available as dapEmployeesPics.htm in the companion content for this chapter. Figure 14-12 shows the page in Page view.

Figure 14.12: A data access page featuring an image control that updates the picture it shows as users navigate through Employees .