Hands-On Exercises

Download as a Word document: Hands-On Exercise Ch09 (103.0K)

Whiz Kids P.C. Repair: Adding an AutoLookup to a Form

Eddie Rose, an enterprising young PC technician, has created a company that specializes in at-home PC repairs. Eddie has created a simple database to track customer orders. The database consists of three tables (tblCustomers, tblProducts, and tblOrders) and two forms (frmCustomers and frmProducts). In this exercise, Eddie will create a new form that will summarize orders to include all customer and product info. Assume the role of Eddie and perform the steps that he identifies.

Download the AC09XTRA01 database file and save it to your personal storage location.
Open the database and review its contents.
Start by defining an AutoLookup query to use for the new form:
CLICK: Queries button in the Objects bar
DOUBLE-CLICK: Create query in Design view
DOUBLE-CLICK: tblOrders in the Show Table dialog box
DOUBLE-CLICK: tblCustomers in the Show Table dialog box
CLICK: Close command button
In the tblOrders field list:
DOUBLE-CLICK: OrderID
DOUBLE-CLICK: CustID
In the tblCustomers field list:
DOUBLE-CLICK: Last Name
DOUBLE-CLICK: First Name
DOUBLE-CLICK: Address
DOUBLE-CLICK: City
DOUBLE-CLICK: State
DOUBLE-CLICK: Zip
Your screen should now appear similar to figure 9.1.

Figure 9.1
Creating an AutoLookup query

Save the query as "qryAutolookup" and then close the query Design window.
You now use the query in creating a new form. Do the following:
CLICK: Forms button in the Objects bar
DOUBLE-CLICK: Create form by using wizard
In the first step of the Form wizard:
SELECT: "Query: qryAutolookup" in the Tables/Queries drop-down list box (which should already be displayed)
CLICK: Include All button ( )
CLICK:
In the second step of the wizard:
SELECT: "by tblOrders" in the How do you want to view your data list box
CLICK:
For the remaining steps:
SELECT: Columnar option button
CLICK:
SELECT: Standard
CLICK:
In the final step of the wizard:
TYPE: frmAutoLookup
SELECT: Open the form to view or enter information option button
CLICK:
The Form window should appear as shown in Figure 9.2.

Figure 9.2
tblOrders Form

On your own, browse through the records in the form using the navigation controls. When the value stored in the CustID text box changes, notice that the address information also changes.
Now let’s change the CustID in the first record:
DOUBLE-CLICK: CustID text box to select its contents
TYPE: 2
Because the form is based on a query, the contents of the related fields will be updated on the form when the insertion point leaves the foreign key field’s text box control. To demonstrate:
PRESS: Tab key
Notice that the address information changes
PRESS: Esc key to return the CustID value to 3
Close the Form window.
Close the database.

Nikita’s Catering: Adding a Combo fox to a Form

Nikki Garvey is just getting started in the catering business and has created and Access database to help her keep track of booked functions. She has gotten a few complaints that her entry form is hard to use because it involves entering codes for the function type and sales rep. Nikki has decided to add combo boxes to the form to make it easier to use. Assume the role of Nikki and perform the steps that she identifies.

Download the AC09XTRA02 database file and save it to your personal storage location.
Open the frmFunctionInfo form in Design view. Your screen should appear similar to Figure 9.3.

Figure 9.3
frmFunctionInfo
form

You will now change the functionTypeID and salesContactID text box controls to combo boxes. To begin:
CLICK: Select Objects button ( )in the Toolbox
In the form Design window:
RIGHT-CLICK: functionTypeID text box control
CHOOSE: Change To, Combo Box from the shortcut menu
The functionTypeID control changes shape to include a drop-down arrow.
You must now define the contents of the combo box:
RIGHT-CLICK: functionTypeID combo box control
CHOOSE: Properties
In the Properties window:
CLICK: Data tab
CLICK: in the Row Source text box
CLICK: down arrow attached to the property text box
SELECT: tblFunctionLookup
In order to display the function description, you must specify the first two columns of the tblFunctionLookup table. To begin:
CLICK: Format tab
DOUBLE-CLICK: in the Column Count text box
TYPE: 2
You can now specify the width of each column in the combo box control, separated by semicolons. The "0" for the first width indicates that we don’t want that column to show.
CLICK: in the Column Widths text box
TYPE: 0";2"
( Hint: If you leave out the quotation mark or leading zero that appears before a decimal, Access completes the entry for you.
To test the column widths for the combo box, close the Properties window and then display the form:
CLICK: View — Form button ( )
Notice that the functionTypeID combo box displays the function rather than an ID number.
Now, return to the design view. On your own, change the salesContactID into a combo box that refers to the tblSalesContactLookup.table. You finished form should resemble the one shown in figure 9.4.

Figure 9.4
frmFunctionInfo form

Close the database.

Waxing Nostalgic Records: Using the Access Snapshot Viewer

Al Lincoln, the owner of Waxing Nostalgic Records, uses Access to keep track of orders made via the Internet. He shares this information with his accountant, his wife Karen, who does not use Access. In the exercise, Al uses the Access Snapshot functions to create a graphic of the day’s orders.

Download the AC09XTRA03 database file and save it in your personal storage location.
Open the AC09XTRA03 database.
In the Reports list area of the Database window:
SELECT: rptOrders in the list area
CHOOSE: File, Export
In the Export Report dialog box, navigate to your student data folder and then:
TYPE: Orders in the File name text box
SELECT: Snapshot Format in the Save as type drop-down list box
( Note: A snapshot file ends with the extension .snp.)
Make sure that the Autostart check box is selected and then:
CLICK: Export command button
Once you click the Save command button, the Snapshot Viewer program automatically starts and displays a preview of the report. ( Note: This process may take up to one minute on slower computers.)
The Snapshot Viewer offers many of the same features as the Print Preview window in Access. On your own, zoom in and out on the report, as shown in Figure 9.5.

Figure 9.5
Snapshot Viewer window

Close the Snapshot Viewer and the database file.

Data Files: Hands-On Exercises Data Files Ch09 (143.0K)

2002 McGraw-Hill Higher Education
Any use is subject to the Terms of Use and Privacy Policy.
McGraw-Hill Higher Education is one of the many fine businesses of The McGraw-Hill Companies.