Access Unit O

1
Access Unit O

• Creating Modules and VBA

2
Objectives

• Understand modules and VBA
• Compare macros and modules
• Create functions
• Use If statements
• (continued)

3
Objectives (continued)

• Document procedures
• Examine class modules
• Create sub procedures
• Troubleshoot modules

4
Understanding Modules and VBA

• Module An Access object that stores VBA code
• VBA Visual Basic for Applications programming
  language
• Code window Where VBA is written
• Class module Contains code used only within a
  form or report and stores the code within that
  object

5
Understanding Modules and VBA

• Standard module Contains global code that can be
  executed from anywhere in the database
• Procedure A series of VBA codes that performs
  an operation
• A module contains VBA code organized in procedures

6
Understanding Modules and VBA

• Statement Lines of VBA code
• Comment Text that explains and documents the
  code
• Declaration statement Sets rules for how
  statements in a module are processed
• Function A procedure that produces a value

7
Understanding Modules and VBA

• Sub procedure (or sub) Performs a series of VBA
  statements, but does not return a value
• Argument An expression, constant, or variable
  required for a procedure to execute

8
Understanding Modules and VBA

• Object Any item that can be identified or
  manipulated includes the traditional Access
  objects plus others
• Method An action that an object can perform

9
Visual Basic Editor Code Windowfor a Standard
Module
Project Explorer window
Properties window
Declaration statements
Function procedure
10
Components for the Visual Basic Window
11
Text Colors for the Visual Basic Window
12
Comparing Macros and Modules

• Macros

• Modules

• You must know VBA
• More difficult to create due to programming
  syntax
• Must use to create unique functions
• VBA code stored in modules can be used in other
  MS Office products
• May declare variables
• Far more powerful

• Best for repetitive, simple tasks
• No programming
• Must use to make global, shortcut key assignments
• Macros created in Access usually cant accomplish
  many outside tasks
• No declared variables
• Simpler to create

13
Some Common VBA Keywords
14
Creating Functions

• Access supplies hundreds of functions (Sum,
  Count, Date, etc.)
• You may need to create a unique function
• Store the VBA for the new function in a standard
  module so it can be used in any query, form, or
  report

15
Creating a Function
16
Using If Statements

• If...Then...Else Logic that lets you test
  logical conditions and execute statements only if
  the conditions are true
• If...Then...Else code can be composed of one or
  several statements

17
Using an If...Then...Else Statement
Second argument
If
Then
Else
End If
18
Documenting Procedures

• Comment lines Statements in the code that
  document the code
• Useful if you want to read or modify existing
  code
• They dont affect how the code runs
• Comment lines start with an apostrophe and are
  green in the Code window

19
Standard Toolbar Buttons in theVisual Basic
Window
20
Examining Class Modules

• Class modules are contained and executed within
  specific forms and reports
• Usually contain subs and execute in response to
  an event (Clicking a button, closing a form, etc.)

21
Examining Class Modules

• Event procedure/Event handler procedure A
  procedure triggered by an event
• DoCmd A VBA object that supports many methods to
  run common Access commands (closing windows,
  opening forms, etc.)

22
Creating Sub Procedures

• Subs can be triggered on any event identified in
  the Property Sheet
• On Dbl Click
• After Update
• Before Update
• Others

23
Creating Sub Procedures

• Not all items have the same set of event
  properties to choose from
• Example A text box control has Before Update and
  After Update properties a command button does
  not have these because it doesnt update data

24
Troubleshooting Modules

• Types of errors include
• Syntax error
• Compile-time error
• Run-time error
• Logic error
• Several debugging techniques
• Debug menu
• Breakpoint
• Immediate window

25
Setting a Breakpoint
View Access button
Breakpoint
Debug menu
Reset button
26
Summary

• There are ways to create a new function or
  automate a task that are beyond the built-in
  Access tools
• Modules are much more powerful than macros
• VBA is required in order to create a module

27
Summary

• You have also learned to
• Create functions
• Use If...Then...Else
• Document procedures
• Analyze procedures
• Create subs
• Troubleshoot modules