Lab 2

1. Lab 2 Modifying a Table and Creating a Form

2. Objectives • Navigate a large table. • Change field format properties. • Set default field values. • Insert a field. • Add validity checks. • Hide and redisplay fields. • Find and replace data. • Use Undo. • Sort records. • Create and enter records into a form.

3. Concepts • Format Property - Use the Format property to create custom formats that change the way numbers, dates, times, and text display and print. • Default Value Property - The Default Value property is used to specify a value to be automatically entered in a field when a new record is created. • Validity Check - Access automatically performs certain checks, called validity checks, on values entered in a field to make sure that the values are valid for the field type.

4. Concepts • Find and Replace - The Find and Replace feature helps you quickly find specific information and automatically replace it with new information. • Sort - You can quickly rearrange a table’s records by sorting the table data in a different order. • Form - A form is a database object used primarily to display records onscreen to make it easier to enter new records and to make changes to existing records.

5. Outline • Navigating a Large Table • Customizing and Inserting Fields • Setting Display Formats • Setting Default Values • Inserting a Field • Adding Validity Checks • Hiding and Redisplaying Fields • Hiding Fields • Redisplaying Hidden Fields

6. Outline • Finding and Replacing Data • Finding Data • Using Undo • Replacing Data • Sorting Records • Sorting on a Single Field • Sorting on Multiple Fields

7. Outline • Creating And Using Forms • Using the Form Wizard • Navigating in Form View • Adding Records in a Form • Previewing and Printing a Form • Closing and Saving a Form • Lab Review

8. Keys Effect [Page Down] Down one page [Page Up] Up one page [Ctrl] + [Page Up] Left one window [Ctrl] + [Page Down] Right one window [End] Last field in record [Home] First field in record [Ctrl] + [End] Last field of last record [Ctrl] + [Home] First field of first record [Ctrl] + [up arrow] Current field of first record [Ctrl] + [down arrow] Current field of last record NAVIGATING A LARGE TABLE Using the Keyboard

9. Button Effect First record, same field Previous record, same field Next record, same field Last record, same field New (blank) record NAVIGATING A LARGE TABLE Using the Navigation Buttons

10. CUSTOMIZING AND INSERTING FIELDS • Add and delete fields • Add restrictions on data • Define how data will be displayed

11. Concept 1: FORMAT PROPERTY

12. PREDEFINED FORMATS Text and Memo Data Types

13. Concept 2: DEFAULT VALUE PROPERTY

14. WORKING WITH FIELDS • What if you forget to include a field? • Can insert one at a later time • But I don't want the field to be at the end! • Can insert a new blank row where you want

15. Concept 3: VALIDITY CHECK • Validation check • Automatic checks on data • Validation rules • Expression that defines acceptable data • Validation text • Message that appears if invalid data is entered

16. VALIDITY CHECK • Values • Operator • Comparison operators

17. VALIDITY CHECKS Identifiers and Expressions Entered in the Validation Rule field of the field's property.

18. HIDING AND REDISPLAYING FIELDS • Hide fields to see more on screen • Choose Format/Hide • Redisplay hidden fields • Choose Format/Unhide

19. Concept 4: FIND AND REPLACE • Quickly find specific information • Automatically replace with new information • Find Command • Locates all specified values in a field • Replace Command • Finds a value and replaces it another

20. FIND AND REPLACE Dialog Box Options

21. Concept 5: SORT • Sort • Sorting on a single field • Sorting on multiple fields

22. SORTING

23. MODIFYING THE DATASHEET • Formatting the datasheet • Changing background and gridline color • Changing the font color

24. CREATING AND USING FORMS • What is a form? • Database object used to display records onscreen • Why use a form? • Easier to enter new records and make changes to existing ones

25. Concept 5: FORM • Based on underlying tables • Include design control elements • Descriptive text • Titles • Labels • Lines • Boxes • Pictures • Can use calculations

26. CREATING A FORM • Use Design View • Use the Form Wizard • Tab order • Form layout • Form style • Form title

27. BASIC FORM LAYOUTS

28. MORE ON FORMS • Navigating in Form View • Adding records in a form • Previewing and printing a form • Access prints out all records in the table in Form View • Select only the record you want printed • Edit/Select Record • Print selected record

29. Key Terms • Character string– A group of characters. • Comparison operator– Symbol that you to make comparisons between values. • Expression - Description of acceptable values in a validity check, which can contain any combination of the following elements: operators, identifiers, and values. • Form - A database object used primarily for data entry and making changes to existing records.

30. Key Terms • Format– To enhance the appearance of an object. • Identifier - A part of an expression that refers to the value of a field, control, or property. • Operator- A symbol or word used to make a comparison. • Sort - A temporary record order in the Datasheet that reorders records in a table. • Tab order - The order in which Access moves through a form or table when the Tab key is pressed.

31. Key Terms • Validation rule –A restriction placed on entering text. • Validation text - Text that is displayed when a validation rule is violated. • Validity check - Process of checking to see whether data meets certain criteria. • Value - A part of an expression that is a number, date, or character.

32. Discussion Questions • Discuss several different format properties and how they are used in a database. • Discuss the different types of form layouts and why you would use one layout type over another. • Discuss how validity checks work. What are some advantages of adding validity checks to a field? Include several examples. • Discuss the different ways records can be sorted. What are some advantages of sorting records?

33. Frequently Asked Questions • I typed the name data in lower case in my table and now I would like the name to be in upper case. Is there an easy way to do this? • How do I make sure a certain value is entered in a field? • My table is large and I can't see all the fields on the screen. Any suggestions? • How does the Undo command work in Access?

34. Frequently Asked Questions • How can I sort on more than one field? • How can I enhance the appearance of my datasheet? • What is a form and how do I use it? • Are forms required?

35. Web Links • Microsoft Access Tutorials – Creating Basic Forms • http://mis.bus.sfu.ca/tutorials/MSAccess/tutorials.html • Access Tutorial – Introduction to Forms • http://www.functionx.com/access/lesson8.htm • Access 97/2000 – Forms • http://www1.harlingen.isd.tenet.edu/images/helppdf/dwa19.pdf

36. Web Links • Microsoft Access Forms; Tek-Tips • http://www.tek-tips.com/gthreadminder.cfm/lev2/4/lev3/27/pid/702 • Filtering Data with Access Forms • http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9037 • Gaining Access – What is a Form? • http://www.psu.edu/dept/cac/ets/projects/modules/ga/form_index.html

37. Web Links • Practical Advanced Microsoft Access Forms and macros • http://www.cse.bris.ac.uk/~ccmjs/accform.htm • Creating and Using Access Forms • http://s9000.furman.edu/DD/labs/Access/access_3.htm