Exploring Microsoft Access

1. Exploring Microsoft Access Chapter 8 Creating More Powerful Applications: Introduction to VBA By Robert T. Grauer Maryann Barber

2. Objectives (1 of 2) • Describe relationship of VBA to Microsoft Office • Describe components of the Module window • Describe two ways to create an event procedure • Explain how the Quick Info and Complete Word features simplify entry of VBA statements • Create a combo box to locate a record on a form Exploring Microsoft Office - Microsoft Access Chapter 8

3. Objectives (2 of 2) • Describe MsgBox parameters • Create an event procedure to facilitate data entry through keyboard shortcuts • Create an event procedure that substitutes an application-specific message for the standard Access error messages • Describe several types of data validation Exploring Microsoft Office - Microsoft Access Chapter 8

4. Overview • VBA is a programming language to build truly useful Access applications • VBA is event driven: Procedures respond to specific events • VBA can modify procedures that Access has already created • Provide an appreciation for what can be accomplished with VBA Exploring Microsoft Office - Microsoft Access Chapter 8

5. Introduction to VBA • Data validation • Facilitate data entry, including creating keyboard shortcuts • Error trapping • Enhance communication with the user by using message boxes Exploring Microsoft Office - Microsoft Access Chapter 8

6. Modules and Procedures • Visual Basic code is developed in units called procedures • Event procedures: run automatically in response to an event • General procedures: called explicitly from within another procedure Exploring Microsoft Office - Microsoft Access Chapter 8

7. Modules • All procedures are stored in modules; one module contains one or more procedures • Every form in Access has its own module (Class 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 Microsoft Office - Microsoft Access Chapter 8

8. Module Window Name of procedure (Form_Close) MsgBox statement End of procedure Name of next Procedure (Form_Current) Exploring Microsoft Office - Microsoft Access Chapter 8

9. MsgBox Statement • MsgBox takes three arguments (known as 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 Microsoft Office - Microsoft Access Chapter 8

10. Create an Event Procedure Right click on Form Selector box and choose Properties Select Event tab Select Code Builder Click On Current Exploring Microsoft Office - Microsoft Access Chapter 8

11. On Current Event Procedure Procedure header Name of Combo Box from step 3 Complete Word will provide completion options Exploring Microsoft Office - Microsoft Access Chapter 8

12. 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 Microsoft Office - Microsoft Access Chapter 8

13. KeyDown Event Procedure Key words Appear in blue Select Form in the Object list box Select KeyDown from the Procedure List box Exploring Microsoft Office - Microsoft Access Chapter 8

14. ShortCut Command Button Select the command button Properties All tab Change Name to ShortCutButton Change Caption to &ShortCuts Command Button tool Exploring Microsoft Office - Microsoft Access Chapter 8

15. Error Trapping • Produce more user-friendly error message • Find the error number using the Immediate window • Use case statements to test the value of an incoming variable 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 Microsoft Office - Microsoft Access Chapter 8

16. Error Event Procedure Error numbers Case 1 Case 2 Else statement Exploring Microsoft Office - Microsoft Access Chapter 8

17. 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 Microsoft Office - Microsoft Access Chapter 8

18. MsgBox Function vs. 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 Microsoft Office - Microsoft Access Chapter 8

19. Chapter 8 Summary (1 of 2) • 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 Microsoft Office - Microsoft Access Chapter 8

20. Chapter 8 Summary (2 of 2) • All procedures are either public or private:Private—accessible only from 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, and title bar message Exploring Microsoft Office - Microsoft Access Chapter 8