Exploring Microsoft Access

1
Exploring Microsoft Access

• Chapter 8
• Creating More Powerful Applications
• Introduction to VBA

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

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

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

5
Introduction to VBA

• Data validation
• Facilitate data entry, including creating
  keyboard shortcuts
• Error trapping
• Enhance communication with the user by using
  message boxes

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

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

8
Module Window
Name of procedure (Form_Close)
MsgBox statement
End of procedure
Name of next Procedure (Form_Current)
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

10
Create an Event Procedure
Right click on Form Selector box and choose
Properties
Select Event tab
Select Code Builder
Click On Current
11
On Current Event Procedure
Procedure header
Name of Combo Box from step 3
Complete Word will provide completion options
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

13
KeyDown Event Procedure
Key words Appear in blue
Select Form in the Object list box
Select KeyDown from the Procedure List box
14
ShortCut Command Button
Select the command button Properties All tab
Change Name to ShortCutButton
Change Caption to ShortCuts
Command Button tool
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

16
Error Event Procedure
Error numbers
Case 1
Case 2
Else statement
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

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

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

20
Chapter 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