Exploring Microsoft Office Access 2010

1. Exploring Microsoft Office Access 2010 Chapter 6 Data Protection 1 1

2. Objectives Establish Data Validity Create a Lookup Modify a Lookup by Adding and Deleting Values Create and Modify a Multivalue Lookup Field Work with Input Masks Add and remove pivot fields

3. Objectives Create Forms Using the Forms Tools Create Custom Forms Using Design View Add Action Buttons and Combo Box Controls Create Subforms Fashion Functional Formats

4. Data Validity It is important data be accurate and valid It is the only way to ensure accuracy Remember: Reports, forms, queries all based on table data Garbage In = Garbage out

5. Data Validation in Access Tools that help control the type and accuracy of data entered Some validation provided in Access by default Once data type declared, data entered must match Primary key fields must be populated Data in primary key fields cannot be duplicated

6. Types of Validation Required – data must be entered in field Default Values – supplies a given value in each field automatically Default Value property Required property

7. Types of Validation Input Mask – forces data conformity Lookup Lists – values that can be chosen for a field Lookup field properties tab Input Mask property 7 7

8. Setting Required Field Properties In Design view Select the field In Field Properties pane, select Required Ensures field must be populated in all records YES value in Required property Required property

9. Setting Default Value Field Properties In Design view Select the field Select Default Value field Enter the value desired Ensures that field must be populated in all records Used primarily when the majority of records will share the same field value Value entered in Default property Default Value property

10. Validation Rule Validation Rule • Allows the construction of a logical rule that data must comply with • Mathematical rules may be built with the Expression Builder

11. Validation Text Serves as an informative message or prompt Informs the user that data entered is invalid Provides a general explanation of why validation was not met Validation Text

12. Validation Message Validation text A user friendly message Appears when data entry is violated No validation text Results in a difficult to understand message With validation text Without validation text

13. Creating Lookup Fields Lookup fields Speeds data entry Ensure data accuracy Create a lookup field in the easiest manner Select Lookup Wizard in the drop-down data type field box The Lookup Wizard will begin Select Lookup Wizard

14. Using the Lookup Wizard – Screen 1 Type your own values OR Use existing values Choose this options to type in values Field list Choose this option to use existing values

15. Using the Lookup Wizard – Screen 2 Type field values you desire OR Choose the table/query and the fields needed OR Type the values you wish to use Choose the tables and/or queries needed Choose the field you wish to pull values from

16. Using the Lookup Wizard – Screen 3 When supplying your own values You will be prompted to label (name) the column Click Finish to end the Wizard Click Enter a label for the column

17. Using the Lookup Wizard – Screen 3 Click Enter a label for the column • When using fields from a table/query • You will be prompted to choose the sort order for your field

18. Using the Lookup Wizard – Screen 4 When using fields from an existing table or query You will be prompted to adjust the width of your column Drag column edge to adjust width

19. Using the Lookup Wizard – Screen 5 When using existing fields You will be prompted to label (name) the column Click Finish to end the Wizard Click Enter a label for the column

20. Modifying a Lookup Field Identify the record source of the data Change the data in the source object (add or delete values) Data immediately added or deleted to the field This step does not reclassify existing data Existing data must be updated manually or with a query

21. Trouble Identifying Record Source? Row Source value Click the Lookup tab in the Field Properties section Locate the Row Source property Object name after the word FROM identifies source

22. Create a Multivalue LookupField To allow for more than one value Select the Allow Multiple value check box Click Enter a label for the column

23. Input Masks • Specifies exact formatting of input data • Uses special characters for format specification • Only used with text and date fields • Common mask characters • 0 Digit (0 to 9, entry required) • 9 Digit or space (with ‘+’ and ‘-’ not allowed ) • # Digit or space (with ‘+’ and ‘-’ allowed )

24. Create an Input Mask Enter mask characters directly into Field Properties Use the Input Mask Wizard May be entered directly Or May use the Input Mask Wizard

25. Forms Provide an attractive user interface Aids in data entry

26. Methods of Form Creation You can create a form using: Form, Split and Multiple Form Tools Datasheet Tool Form Wizard Blank Form Tool Pivot Chart and Table Tools Form Design View

27. The Form Tool Drop data field Form created with Form tool One click method Creates a very simple and basic form Can be altered and enhanced in Design view

28. The Split Form Tool Creates a two part form including all fields One section is in Form view One section is in Datasheet view Edits are synchronized Form View Split Form Tool Datasheet view

29. Multiple Items Tool Resembles a datasheet Faster navigation/data entry than normal forms Multiple Items Tool

30. Datasheet Forms Identical copy of a form in terms of looks Has different protection features applied than the original form To create Choose More Forms from the Forms group Select Datasheet from the drop down list More Form command

31. PivotTable and PivotChart Forms Work the same as in query/table view Create from PivotChart button on the Forms group OR Click More Forms Tool in the Forms group Choose PivotTable from the drop down list More Form Tool

32. Using Design View to Create a Form Allows for customization and personalization of forms Click the Blank Form tool in the Forms group Drag Fields onto the Design grid Field List Design Grid

33. Action Buttons Navigational Button Button tool Allows you to add navigational/other types of buttons Wizard guides you through the creation

34. Subforms Primary form (parent) Subform (child) Form that exists within another form Updated/viewed separately or with its parent form Normally, a one-to-many relationship exists between the two forms

35. Form Customization • To customize a form • First remove the default layout • Accomplished with the Remove Layout tool • Reposition controls as desired • Reposition several controls at once by selecting multiple (shift-click) controls

36. Form Customization • Also customize by: • Adding a theme • Click Autoformat on the Format Tab • Add additional unbound controls where useful • Use groupings, labels and pictures for visual effect