Reports and Forms

After connecting to one or more remote data sources and filtering, aggregating, or combining them with other sources, you can present your results with Access 2000. Because tables, views, and stored procedures are tightly integrated with forms, reports, and modules, you can easily deliver client/server data in the same way that Access has been serving up file-server data sources since its initial release. While prior Access releases made client/server data available, they never made true client/server processing so easy. Can .adp files do for client/server data sources what .mdb files did for file-server data sources? It depends on your interest and imagination. The upcoming samples might spark your creativity.

Sorting, Formatting, and More with Reports

The Query Designer makes it relatively easy to filter, combine, and aggregate data, but it does not offer much in the way of presentation capabilities. Recall that you cannot even sort the records in a view. Reports, on the other hand, have relatively limited processing capabilities but are great at sorting and formatting data for printers (and even the World Wide Web, if you consider snapshots-see Chapter 6).

Figure 12-12 shows how to use Access Project views with Access reports so that they complement each other. The report sorts records by FamID. It also conditionally formats the color for displaying a row based on the value of FamID: Values of less than 6 appear in black, but values of 6 or greater are in red. In addition, the display shows the normal report flourishes, including a title, a bar separating the title column headings from the column entries, and formatting for column headings and the report title that contrasts with the formatting of the body of the report.

Figure 12-12. A report based on the MyUnionQuery view shown in Figure 12-11.

Aside from the union query coding for the view, this report requires no programming. After you build your view, you simply set the report's Record Source property to the name of the view. Except for the different colors based on FamID, all of the report features are default tabular report settings. The new Conditional Formatting command on the Format menu dramatically simplifies the task of conditionally applying colors to the contents of text boxes. (See Chapter 5 for details.) Such capabilities used to require Format event procedures. By bringing the view and report together, you can use each to do the task that it performs best. Since reporting is a major Access strength, the.adp file's ability to process client/server data sources can dramatically lower the cost of delivering informative, easy-to-read reports throughout an organization.

Adding Hyperlinks

There is no Hyperlink data type in SQL Server databases. (Recall that Jet supports this data type as an extension of the Memo data type-see Chapter 1.) However, you can still insert and follow hyperlinks in forms within Access Projects. There are four steps to doing this:

1. Assign a table column one of these data types: char, nchar, nvarchar, or varchar. These are fixed and variable-length string data types in Unicode or non-Unicode format.
2. Open a form in Design view and make the table the record source for the form.
3. Assign the field with the string data type to one of the form fields.
4. Set the field's IsHyperlink property to Yes. This is a new property in Access 2000 that is explicitly for hyperlinks on forms in Access Projects.

After setting up the table and form, you can add hyperlinks to the table through the form using the Insert-Hyperlink command, which opens a dialog box for setting or editing the hyperlink's URL and its display text. Once the hyperlinks are formatted, users can follow them to designated Web locations by simply clicking the entry in a hyperlink field. Their default browser takes them there. Then they can return to Access by clicking the back arrow. Access 97 introduced this functionality; it is now available for SQL Server and MSDE databases through Access 2000.

Figure 12-13 shows the MyLinks Access form in the process of having its hyperlink entry edited. You can browse to the desired Web location so that you do not have to type the URL. In addition, you can use the Text To Display text box to enter text that appears instead of the URL. Notice also that you can use this dialog box to set hyperlinks for files on a local file server or even link to e-mail addresses. The latter option starts a browser workstation's default e-mail package with the name and e-mail address that you specify in the link.

Figure 12-13. You can use the Edit Hyperlink dialog box to enter and edit hyperlinks into a shared client/server database of hyperlinks.

Viewing and Editing Data Using Forms

Access Projects offer two Recordset Type property settings for working with forms and traditional data sources, such as tables of employees or customers. These settings are unique to .adp files. The traditional .mdb files have different Recordset Type property settings.

When you design an application that uses forms with data from a SQL Server or MSDE database, your client application always works with a snapshot of the original data on the server. Although the locally available data is a snapshot of the data on the server, you can update it on the server. To accommodate this functionality, Access 2000 offers Updatable Snapshot and Snapshot Recordset Type settings for Access Projects. You can set these properties on the Data tab of the form's Property dialog box. You can also manipulate the Recordset Type setting using VBA or an Access macro.

When you set a form's Recordset Type property to Updatable Snapshot, users can change the form's underlying data source as if the file were on a local file server. Using the Lock property for individual controls, you can selectively enable editing on a subset of a form's controls. In addition, you can control the type of changes permitted at client workstations. There are separate Yes/No settings for Allow Edits, Allow Deletions, and Allow Additions properties. Changes you make from the Access Project file propagate to the server. Each user of an Access Project should have a separate copy of the .adp file even though each user relates to a common client/server database. While data changes propagate from the client workstations to the server database, users must choose Records-Refresh to view changes made by others.

Figure 12-14 below shows a sample form in an Access Project based on data in a remote server. Below the form is an excerpt from the form's property sheet. It exposes the two possible settings for the Recordset Type property. Since Updatable Snapshot is in effect, users can change the form's underlying record source. In fact, because of the Yes settings for Allow Edits, Allow Deletions, and Allow Additions, they can perform all three standard types of database revisions. You can turn off all three capabilities by setting the Recordset Type property to Snapshot.

Figure 12-14. You can use forms in Access Projects to modify data on a database server.

The form was prepared using the AutoForm wizard (with a minor amount of editing). Moving to the next record passes any edits that a user makes to fields back to the server. If the Recordset Type setting is Snapshot, Access responds that the recordset is not updateable. The button marked with an X lets users abort a lengthy download of many records to a local workstation.

Resynchronizing a Form with its One-To-Many Data

When the source for a form in an Access Project is based on a recordset with a one-to-many relationship between two tables, you can automatically populate all the fields on the one side by setting the foreign key on the many side. This feature is especially convenient when you add new records. For example, if you are designing a form that presents employee and sales data, the one-to-many relation follows from the fact that each employee can have multiple sales. You simply enter the employee ID for the sales table and commit the record. This action automatically populates all the employee fields on the one side of the underlying record source. Then you enter the sales data on the many side of the record source. You can update the employee information for a previously existing record just as easily.

To enable this feature, your form must use Updatable Snapshot as the Recordset Type setting. You assign to the Unique Table property the table on the many side of the relationship. You must also set the Resync Command property to a string representing a SQL statement that enables the resynchronization. You'll learn a trick for quickly constructing this statement below.

Figure 12-15 shows the one-to-many Design view for the vwForResynch view. The top panel shows the one-to-many relationship in the line connecting the Employees and Orders tables. Notice that the view links the tables using the primary key in the Employees table and the corresponding foreign key (EmployeeID) in the Orders table. The bottom panel shows the SQL for the view. The form's Resync Command property requires a simple modification of this statement.

Figure 12-15. This view represents a one-to-many relationship like that used for resynchronization on a form. The SQL statement forms the basis of the Resync Command property setting.

Figure 12-16 below shows a form just after the entry of 9 into the EmployeeID text box. The form is a standard AutoForm with some minor editing. Clicking the record selector brings up data automatically to all the other employee fields on the form. Entering a new value into the EmployeeID text box and clicking the record selector automatically brings up employee data corresponding to the new ID value. That is the benefit of the resynchronization function.

Figure 12-16. A form that demonstrates resynchronization against a remote database.

Figure 12-17 shows the form settings that enable this automatic resynchronization. The Recordset Type is Updatable Snapshot, and the form's Unique Table property points at the many side of the underlying recordset-namely, the Orders table. The zoomed Resync Command setting is exactly the same as the SQL for the underlying form with one extra line: WHERE Orders.OrderID = ?. This final step completes the tasks for enabling a form by using the resynchronization function.

Figure 12-17. The Data tab of the form's property sheet. The Updatable Snapshot and Resync Command settings are necessary for resynchronization.