The OLeary Series Microsoft Access 2002 Lab 2

The O’Leary Series Microsoft Access 2002 Lab 2 Modifying a Table and Creating a Form Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 2 Objectives 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 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. Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 3 Concepts 1. Format Property - Use the Format property to create custom formats that change the way numbers, dates, times, and text display and print. 2. 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. 3. 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. Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 4 Concepts 4. Find and Replace - The Find and Replace feature helps you quickly find specific information and automatically replace it with new information. 5. Sort - You can quickly rearrange a table’s records by sorting the table data in a different order. 6. 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. Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 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 Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 6 Outline • Finding and Replacing Data – Finding Data – Using Undo – Replacing Data • Sorting Records – Sorting on a Single Field – Sorting on Multiple Fields Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 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 Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 8 NAVIGATING A LARGE TABLE Using the Keyboard Keys [Page Down] [Page Up] [Ctrl] + [Page Up] Effect Down one page Up one page Left one window [Ctrl] + [Page Down] [End] [Home] [Ctrl] + [End] [Ctrl] + [Home] [Ctrl] + [up arrow] [Ctrl] + [down arrow] Right one window Last field in record First field in record Last field of last record First field of first record Current field of last record Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 9 NAVIGATING A LARGE TABLE Using the Navigation Buttons Button Effect First record, same field Previous record, same field Next record, same field Last record, same field New (blank) record Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 10 CUSTOMIZING AND INSERTING FIELDS • Add and delete fields • Add restrictions on data • Define how data will be displayed Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 11 Concept 1: FORMAT PROPERTY Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 12 PREDEFINED FORMATS Text and Memo Data Types Symbol Meaning Example @@@-@@-@@@@ 123456789 as 123 -45 -6789 @ Required text character or space > Forces to uppercase smith as SMITH < Forces to lowercase SMITH as smith & Optional text character Mc. Graw-Hill/Irwin @@-@@& 12345 as 12 -345 © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 13 Concept 2: DEFAULT VALUE PROPERTY Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 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 Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 15 Concept 3: VALIDITY CHECK q Validation check • Automatic checks on data q Validation rules • Expression that defines acceptable data q Validation text • Message that appears if invalid data is entered Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 16 VALIDITY CHECK • Values • Operator • Comparison operators Mc. Graw-Hill/Irwin Operator = <> < Meaning Equal to Not equal to Less than > <= >= Greater than Less than or equal to Greater than or equal to © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 17 VALIDITY CHECKS Identifiers and Expressions Expression Result =[Sales Amount] + [Sales Tax] Sums value in two fields =“M” OR “F” Includes M or F entries only >=#1/1/95# AND <=#12/31/95# Greater than or equal to 1/1/95 and less than or equal to 12/31/95 =“Tennis Rackets” Includes the entry Tennis Rackets only Entered in the Validation Rule field of the field's property. Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 18 HIDING AND REDISPLAYING FIELDS • Hide fields to see more on screen – Choose Format/Hide • Redisplay hidden fields – Choose Format/Unhide Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 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 Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 20 FIND AND REPLACE Dialog Box Options Option Effect Look in Searches current field or entire table Match Locates matches Search Specifies the direction table will be searched Match case Case-sensitive search Search fields as formatted Finds data based on its display format Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 21 Concept 5: SORT • Sorting on a single field • Sorting on multiple fields Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 22 SORTING Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 23 MODIFYING THE DATASHEET • Formatting the datasheet • Changing background and gridline color • Changing the font color Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 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 Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 25 Concept 5: FORM • Based on underlying tables • Include design control elements – Descriptive text – Titles – Labels – Lines – Boxes – Pictures • Can use calculations Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 26 CREATING A FORM • Use Design View • Use the Form Wizard – Tab order – Form layout – Form style – Form title Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 27 BASIC FORM LAYOUTS Form Layout Style Description Columnar Presents data in columns Tabular Table layout Datasheet Selected data in rows and columns Justified Presents data in rows Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 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 Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 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. Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 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. Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 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. Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 32 Discussion Questions 1. Discuss several different format properties and how they are used in a database. 2. Discuss the different types of form layouts and why you would use one layout type over another. 3. Discuss how validity checks work. What are some advantages of adding validity checks to a field? Include several examples. 4. Discuss the different ways records can be sorted. What are some advantages of sorting records? Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 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? Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 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? Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 35 Web Links • Microsoft Access Tutorials – Creating Basic Forms – http: //mis. bus. sfu. ca/tutorials/MSAccess/tutoria ls. html • Access Tutorial – Introduction to Forms – http: //www. functionx. com/access/lesson 8. htm • Access 97/2000 – Forms – http: //www 1. harlingen. isd. tenet. edu/images/hel ppdf/dwa 19. pdf Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 36 Web Links • Microsoft Access Forms; Tek-Tips – http: //www. tektips. com/gthreadminder. cfm/lev 2/4/lev 3/27/pid/ 702 • Filtering Data with Access Forms – http: //www. sqlmag. com/Articles/Index. cfm? Art icle. ID=9037 • Gaining Access – What is a Form? – http: //www. psu. edu/dept/cac/ets/projects/modul es/ga/form_index. html Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.

The O’Leary Series 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: //s 9000. furman. edu/DD/labs/Access/access _3. htm Mc. Graw-Hill/Irwin © 2002 The Mc. Graw-Hill Companies, Inc. All rights reserved.