Tables and queries are the sources of the records in your forms and reports. When you add fields, edit criteria, and make other design changes to tables/queries, you will often want those changes included in your forms/reports. Sometimes you will need to create a new form/report from scratch to reflect the edits. But where possible, you will want to keep, modify, or copy an existing form/report to incorporate the changes.

This tutorial will teach you techniques to do just that. We’ll first look at a simple example of updating a form/report for a change in the underlying table, and then at the more interesting case of updating for a query.

Edit A Form To Reflect Changes In A Table

You will be editing the design of Northwind tables/queries, so we urge you to make a copy of the database before proceeding.Navigate to the Northwind.mdb file in the Samples subfolder of your Microsoft Office folder. Copy Northwind.mdb to the Clipboard and paste it in another folder. Rename the file CopyOfNorthwind.mdb, which you can now open and use without changing the original file.

With the Tables tab selected, double-click Shippers to open it. Review the data and close the table. With Shippers selected in the Database window, choose Insert and Form. In the New Form dialog box, click Form Wizard and click OK. Click the double arrows (>>) to add all fields to the form. Click Next three times to reach the What Title Do You Want For Your Form? field. Edit the title by typing SC Shippers. Click Finish.

Maximize the window of the new form. Click the View button at the far left of the main toolbar to go to Design view. Choose View, select Properties, and click the Data tab in the Form property sheet. Note that the Record Source property is Shippers, the underlying table. Close the property sheet and close the form.

Suppose you want to add a field to the table for the Web site address. With Shippers selected in the Tables section of the Database window, click Design. In the first empty row, type Websitein the Field Name column. Press TAB on your keyboard. In the Data Type column, open the drop-down list and select Hyperlink. Leave the optional Description column blank. Choose File and Save and then close the table.

In the Database window, click Forms, select SC Shippers, and click Design. If necessary, choose View and select Field List. The Record Source remains Shippers, but the field list has been updated to reflect the new field (see Figure 1). You can drag and drop the field from the field list to the form to display the field’s data. Close the form and save your changes.

Edit A Query Within A Report

In the Database window, click Queries, select Products By Category, and click Design. The query contains fields from the Categories and Products tables. The fifth column is Discontinued, a Yes/No field for discontinued items. Its criteria appears as <>Yes, which means “not equal to Yes.” In other words, only items that arenot discontinued are included in the query. Click the View button to see the records and then close the query.

Click the Reports button in the Database window. Double-click the Products By Category report to open and review it. Click the View button to go to Design view. If the Report property sheet isn’t visible, choose Edit and click Select Report and then choose View and click Properties. On the Data tab, note that the Record Source is Products By Category. Click in Record Source and click the three-dot ellipsis at the end of the line to view the query.

Suppose you want the report to show only products with less than 20 units in stock. Type <20 on the Criteria row of the UnitsInStock column. Choose File, Save, and then close the Query window. Click the View button; note the report contains only records for products with less than 20 units. Close the report and save your changes. Click the Queries button, select Products By Category, and click Design. The change you made to the query within the report has been saved.

Create A New Report

The Record Source property is useful for finding out what records will be in your report. But you may find it easier and better to manage your edits to queries within the Database window. Let’s create a separate query and a new report for the low inventory products.

Select <20inthe UnitsInStock column, choose Edit and Cut and then choose File and Save. The query is back to its original design. Click the Criteria row of the UnitsInStock column, choose Edit, select Paste, and then choose File and Save As. Type Low Inventory Products By Categoryin the top box; Query should appear in the box below. Close the query.

Click Reports, right-click Products By Category, and click Copy. Right-click in a blank area and choose Paste. In the Paste As dialog box, type Low Inventory Products By Category and click OK. With this new report selected, click Design. If the report’s property sheet is not visible, choose Edit and Select Report. Next, choose View and click Properties. On the Data tab, click the drop-down arrow and choose Low Inventory Products By Category (see Figure 2).

You also want to change the title in the title bar at the top, as well as in the Report Header. In the Report property sheet, click the Format tab. Edit the Caption by typing Low Inventory Products By Category. Close the property sheet. In the Report Header, click in the Products By Category label. Edit the title by typing Low Inventory Products and click anywhere outside the label. Save your changes.

Click the View button to see the report with the low-inventory products and then close it. In the Database window, open the Products By Category report. It displays all of its original records. Close the report, close the database, and exit Access.