Creating More Powerful Applications: Introduction to VBA(Wk9)

1. Creating More Powerful Applications: Introduction to VBA(Wk9) Exploring Office 2003 - Grauer and Barber

2. Objectives • Describe the relationship of VBA to Microsoft Office • Create an event procedure • Describe the VBA editor • Distinguish between Procedure and Full Module view • Create a combo box to locate a record on a form Exploring Office 2003 - Grauer and Barber

3. Objectives (continued) • Describe MsgBox parameters • Facilitate data entry through keyboard shortcuts • Create an event procedure that substitutes an application-specific message for a standard Access error message • Describe several types of data validation Exploring Office 2003 - Grauer and Barber

4. Case Study: Back to Natalie’s Students will modify a database to include: • Modify Add Records procedure to move focus to the appropriate field on the form • Create a Find Record combo box and data entry shortcuts • Display a message reminding the user to backup the database upon close Exploring Office 2003 - Grauer and Barber

5. Introduction to VBA • VBA is a subset of Visual Basic for all Microsoft Office applications • VBA is event driven • An event is any action recognized by Access • Procedures are sets of VBA statements that respond to specific events Exploring Office 2003 - Grauer and Barber

6. A Better Student Form A form used throughout this chapter that: • Improves on data validation • Facilitates data entry, including creating keyboard shortcuts • Employs error trapping • Enhances communication with the user by displaying dialog boxes Exploring Office 2003 - Grauer and Barber

7. Procedures • Visual Basic code is developed in units called procedures • Event procedures: run automatically in response to events • General procedures: called explicitly from within another procedure Exploring Office 2003 - Grauer and Barber

8. Modules • All procedures are stored in modules; one or more procedures per module • Every form in Access has its own module which contains procedures for that form • Private procedure: accessible only from within the module in which it is contained • Public procedure: accessible from anywhere • Module window: where procedures in a module are displayed and edited Exploring Office 2003 - Grauer and Barber

9. Module Window Name of procedure (Form_Close) MsgBox statement End of procedure Name of second procedure (Form_Current) Exploring Office 2003 - Grauer and Barber

10. MsgBox function • MsgBox takes three arguments (parameters): • Prompt: Specifies the message text • Buttons: Type of command buttons and icon • Title: Text that appears in title bar of the message box Exploring Office 2003 - Grauer and Barber

11. Hands-on Exercise 1 • Title of Exercise: Create a Combo Box and Associated VBA Procedure • Objective: to create a combo box to locate a record; to create a VBA procedure to synchronize the combo box with the current record • Input file: Introduction to VBA • Output file: Introduction to VBA (modified) Exploring Office 2003 - Grauer and Barber

12. Create an Event Procedure Right click on Form Selector box and choose properties Select Event tab Click On Current Select Code Builder Exploring Office 2003 - Grauer and Barber

13. On Current Event Procedure Procedure header Name of Combo Box from step 3 Complete Word will provide completion options Procedure View button Exploring Office 2003 - Grauer and Barber

14. Facilitating Data Entry • Use the Default property to specify default values for certain fields • Use VBA to create keyboard shortcuts • A KeyDown event procedure can implement keyboard shortcuts Exploring Office 2003 - Grauer and Barber

15. Hands-on Exercise 2 • Title of Exercise: Facilitating Data Entry • Objective: Create keyboard shortcuts to facilitate data entry • Input file: Introduction to VBA (after Exercise 1) • Output file: Introduction to VBA (modified) Exploring Office 2003 - Grauer and Barber

16. KeyDown Event Procedure Click drop-down arrow And select Form Key words appear in blue Click drop-down arrow And select KeyDown Procedure View button Exploring Office 2003 - Grauer and Barber

17. ShortCut Command Button Select the command Button Properties All tab Change Name to ShortCutButton Change Caption to &ShortCuts Command button tool Click and drag to create command button Exploring Office 2003 - Grauer and Barber

18. Error Trapping • Produce more user-friendly error message • Find error number using the Immediate window • Case statements test incoming variable value and produce the appropriate statement • Once error is detected, call MsgBox statement to display your user-friendly error message • Else will execute if all Case statements fail Exploring Office 2003 - Grauer and Barber

19. Error Event Procedure Error number Case 1 Print MsgBox Else statement Exploring Office 2003 - Grauer and Barber

20. Hands-on Exercise 3 • Title of Exercise: Error Trapping • Objective: to create an event procedure that substitutes application-specific messages for the standard Access error messages • Input file: Introduction to VBA (after Exercise 2) • Output file: Introduction to VBA (modified) Exploring Office 2003 - Grauer and Barber

21. Data Validation • Invalid data corrupts validity of information • Data validation is therefore crucial • Use VBA to extend data validation capabilities within Access • Use a nested IF statement to remind users to leave no fields empty Exploring Office 2003 - Grauer and Barber

22. MsgBox Functionvs.MsgBox Statement • MsgBox function: displays a prompt to the user, then returns a value • Requires parentheses around arguments • MsgBox statement: simply displays a message • Does not use parentheses Exploring Office 2003 - Grauer and Barber

23. Hands-on Exercise 4 • Title of Exercise: Data Validation • Objective: to use Field and Table properties to implement different types of data validation • Input file: Introduction to VBA (after Exercise 3) • Output file: Introduction to VBA (modified) Exploring Office 2003 - Grauer and Barber

24. Summary • VBA is a subset of Visual Basic • VBA is accessible by all Microsoft Office applications • All VBA procedures are stored in modules • Every form in Access has its own module that contains the event procedures for that form Exploring Office 2003 - Grauer and Barber

25. Summary (continued) • All procedures are either public or private: • Private: accessible only within its module • Public: accessible from anywhere • Event procedures were created to illustrate how VBA is used to improve an application • MsgBox function has three arguments: • Message • Intrinsic constant • Title bar message Exploring Office 2003 - Grauer and Barber

26. End-of-chapter Exercises • Multiple Choice • Practice With Access • Exercise 1 – MsgBox Examples • Exercise 2 – Expanded Student Form • Exercise 3 – Return to Soccer • Exercise 4 – Enhancing a Report • Exercise 5 – Acme Computers Exploring Office 2003 - Grauer and Barber

27. End-of-chapter Exercises (continued) • On Your Own • Expanding Soccer • The VBA Primer • Debugging • Help for VBA Exploring Office 2003 - Grauer and Barber

28. Questions? Exploring Office 2003 - Grauer and Barber