Practice Activities

Lesson 5: Form and Subform Design, part 3

Try This: Do the following steps
1. Open the Brown Bag Lunch database you have been working on.

Or, you may download BBL Int ver5.accdb.

2. Create a new Table. Add the following fields and data types:
SpecialtyID, AutoNumber
Specialty, Text
Make SpecialtyID the Primary Key.

3. Save the Table as tblSpecialty.

4. Go to Datasheet View and add the following to the Specialty column:
Gluten Free, Vegetarian, Low-Fat.
Save and close tblSpecialty.

5. Using the Query Design command, create a new Query.

Using the Show Table dialogue box, add the following Tables: tblProducts, tblType, and tblSpecialty. Close the Show Table dialogue box.

6. Join the Tables. Select field Type from tblProducts and drag it field Type in tblType. Then, select the Specialty Field in tblProducts and drag it to the Specialty field in tblSpecialty.

7. In the QBE grid, add the following fields to the Query:
from tblSpecialty: Specialty
from tblType: Type
from tblProduct: Item

8. Sort the Specialty Field in Ascending order

9. Save the Query as TypeSearchSQ. Run the Query to test it.

 

10. Return to Design View. Delete the Field: Item

11. Use the Show/Hide Totals command. Keep the setting “Group By”

12. Add the Criteria “Is Not Null” for the Specialty Field.

13. In the Criteria for the Type Field, right click and select Build. Select the current Database, the Form: Product Search, and the Control: Type Combo. Double Click the TypeCombo to add it to the Expression.

Save and close the Query.

14. Open the Product Search Form in Design View. Add a new Combo Box in the Detail section of the Form.

15. In the Combo Box Wizard, choose to get the values from TypeSearchSQ. Add the available Field: Specialty. Sort by Specialty in Ascending order.

16. Store the value in the Specialty Field.

17. Add the Label Specialty and Finish the Wizard.

16. Review the Property Sheet and rename the Combo Box as SpecialtyCombo. Save the Form.

17. Select the Subform. In the Property Sheet, edit the option for Link Master Field. Add a second field: Specialty to link.

18. Select the Specialty Combo Box. Add an event: After Update. Select an Event Procedure, then use the 3-Dot Builder to edit the Event.

19. Using the Visual Basic editor, add the code Refresh. Save and close the Macro.

20. Return to Form View and test the form. Change the Type to Snacks. Select an option in the Specialty box. Change the Type to Sandwich and select Gluten Free. What do you see?

21. Save the database.