Title: Exploring Microsoft Access
1Exploring Microsoft Access
- Chapter 8
- Creating More Powerful Applications
- Introduction to VBA
- By
- Robert T. Grauer
- Maryann Barber
2Objectives (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
3Objectives (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
4Overview
- 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
5Introduction to VBA
- Data validation
- Facilitate data entry, including creating
keyboard shortcuts - Error trapping
- Enhance communication with the user by using
message boxes
6Modules 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
7Modules
- 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
8Module Window
Name of procedure (Form_Close)
MsgBox statement
End of procedure
Name of next Procedure (Form_Current)
9MsgBox 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
10Create an Event Procedure
Right click on Form Selector box and choose
Properties
Select Event tab
Select Code Builder
Click On Current
11On Current Event Procedure
Procedure header
Name of Combo Box from step 3
Complete Word will provide completion options
12Facilitating 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
13KeyDown Event Procedure
Key words Appear in blue
Select Form in the Object list box
Select KeyDown from the Procedure List box
14ShortCut Command Button
Select the command button Properties All tab
Change Name to ShortCutButton
Change Caption to ShortCuts
Command Button tool
15Error 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
16Error Event Procedure
Error numbers
Case 1
Case 2
Else statement
17Data 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
18MsgBox 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
19Chapter 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
20Chapter 8 Summary (2 of 2)
- All procedures are either public or
privatePrivateaccessible only from within its
module publicaccessible from anywhere - Event procedures were created to illustrate how
VBA is used to improve an application - MsgBox function has three argumentsmessage,
intrinsic constant, and title bar message