EX19_AC_CH10_GRADER_CAP_HW – Specialty Foods 1.1

EX19_AC_CH10_GRADER_CAP_HW – Specialty Foods 1.1

Project Description:

You are employed at Specialty Foods, Ltd., a small international gourmet foods distributor. The company has asked you to modify the database and improve the reliability of the data entry process. You decide to create a few macros and add a menu for the common forms and reports. You will also modify the record source of one of the reports.

Start Access. Open the downloaded   Access file named Exp19_Access_Ch10_Cap_Specialty.accdb. Grader has automatically added   your last name to the beginning of the filename. Save the file to the   location where you are storing your files.

You   will create an event-driven data macro that will populate a new field   automatically each time a record is added to the table.
 

  Open the Orders table   in Datasheet view, observe the data, and then switch to Design view. Add a   new field, ExpectedShipDate   with the data type Date/Time   below the OrderDate field. Save the table.

Create   a data macro attached to the Before Change event. Use the SetField Action to populate the ExpectedShipDate in   the table. The ExpectedShipDate will always be five days after the OrderDate.   Save the macro. Close the macro. Save the table.

You   will change a value in the first record, and then move to the second record   to trigger the macro.
 

  Switch to Datasheet view of the Orders table. Retype the OrderDate in the   first record (OrderID 10248) and press DOWN ARROW.   The macro will be triggered and automatically fill in the ExpectedShipDate   with a date five days after the OrderDate.

Repeat   the test on the second and third records (10249 and 10250). Close the table.

Open   the Main Menu form in Design view. Add three buttons   below the Forms label that will open the three forms in the database: Enter Customers, Enter Orders,   and Enter Suppliers (in that order and to show all records).   Set the first one at the 2″   mark on the vertical ruler and the 1″ mark   on the horizontal ruler. Set the height of the button to 0.5” and the width to 1“. The first button should have   the caption Enter Customers   with the button named as cmdEnterCustomers.

Repeat   the same procedure for Enter Orders   and Enter Suppliers, setting each button immediately below   the one before it. For   example, set Enter Orders so that its top border is set approximately at the   2.6-inch mark, and Enter Suppliers is set approximately at the 3.1-inch mark.

Add   three buttons below the Reports label that will print preview the three   reports in the database: Employees,   Orders, and Products   (in that order). Set the first one at the 2″   mark on the vertical ruler and the 4″   mark on the horizontal ruler. Set the height of the button to 0.5” and the width to 1“. The first button should have   the caption Employees   with the button named as cmdEmployees.

Repeat   the same procedure for Orders   and Products, setting each button immediately below   the one before it. Save the form, switch to Form view, and then test the   buttons. Close all objects except the Main Menu form. For example, set Orders   so that its top border is set approximately at the 2.6-inch mark, and   Products is set approximately at the 3.1-inch mark.

Switch   to Design view, add a Close Database   button that exits Access to the top-right corner of the form, at the 0″ mark on the vertical ruler and the 5″ mark on the horizontal ruler, with a   height of 0.5”   and a width of 1“.   Name the button cmdExit.

Modify   the embedded macro in the On Click property   of the cmdExit button. Add a MessageBox action   to the macro to display the message Please check all   updates before exiting! and set the Type to Information. Move the action up to before the   QuitAccess action. Change the option under QuitAccess from Prompt to Exit.   Save and close the macro.

Save   the form, switch to Form view, and then set the Main Menu   form to display when the database opens. Test the Close Database   button.   Reopen the database.

You   want to modify the records displayed in the Employees report. You will use an   SQL statement to modify the record source so that only employees who live in   London display in the report.
 

  Open the Employees report in Design view. Open the Property Sheet and click   in the Record Source property box. Type an SQL statement into the Record   Source property of the report. The statement should select all fields (*) for employees where the City equals   (=) London. Save the report. Test the report in   Print Preview and close the report.

Close   all database objects. Close the database and then exit Access. Submit the   database as directed.