Microsoft Access

Applying Advanced Form Techniques

Purpose: To demonstrate the ability to use advanced form techniques.

Problem: Your boss wants you to add some advanced forms to the HaveMore Electronics database. She wants a form for the Sales Rep table that will allow users to update data in the table. She also wants a form to display Inventory information.

Instructions: Open the Access_Chapter_7_-_Lab_Test_A_-_HaveMore_Electronics file provided in a location specified by your instructor and execute the following tasks:

1. Create the form shown in Figure A7A - 1. The form includes command buttons, a combo box for the Outlet ID field, and a combo box to search for sales representatives by name. Be sure to sort the sales representative names in ascending order, place a rectangle around the Name to find combo box, and update the combo box. The user should not be able to tab to the combo box. When the Add Record button is clicked, the insertion point should be in the Sales Rep ID field. Save the form as Sales Rep Master Form.

2. Create a query that joins the Inventory and Items tables. Include the Outlet ID and Qty on Hand fields from the Inventory table, and the Item ID, Item Descr, and Price fields from the Items table. Add a calculated field called Total Value that multiplies Qty on Hand times Price. Sort the query in ascending order by Outlet ID and Item ID. Save the query as Inventory and Item Values.

3. Create the Inventory and Item Data form shown in Figure A7A – 2. The main form uses Outlets as its row source. The subform that appears in the Datasheet tab uses the Inventory and Item Values query (Figure A7A – 2a). Name the subform Inventory Item Information. The chart that appears in the Chart tab uses the same query (Figure A7A – 2b). Name the chart Total Value per Item.

4. Submit the revised database in the format specified by your instructor.

Figure A7A – 1

Figure A7A – 2a

Figure A7A – 2b

Page 1

© 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.