microsoft access 2013 ®® tutorial 11 using and writing visual basic for applications code
TRANSCRIPT
Microsoft Access 2013
® ®
Tutorial 11Using and Writing Visual Basic for
Applications Code
XPXPXPObjectives• Session 11.1– Learn about user-defined functions, Sub
procedures, and modules – Review and modify an existing Sub procedure in an
event procedure– Create a function in a standard module– Test a procedure in the Immediate window
New Perspectives on Microsoft Access 2013 2
XPXPXPObjectives• Session 11.2– Create event procedures – Compile and test functions, Sub procedures, and
event procedures– Create a field validation procedure
New Perspectives on Microsoft Access 2013 3
XPXPXPIntroduction to Visual Basic for Applications• Case - Chatham Community Health Services
Creating VBA Code for the Health Database
• User would like to modify the frmVisit, frmPatient, and frmVisitsAndInvoices forms to make data entry easier and to highlight important information on them
• To make these modifications, you will write Visual Basic for Applications code to perform the necessary operations
New Perspectives on Microsoft Access 2013 4
XPXPXPIntroduction to Visual Basic for Applications (Cont.)
New Perspectives on Microsoft Access 2013 5
XPXPXP
New Perspectives on Microsoft Access 2013 6
Introduction to Visual Basic for Applications (Cont.)
XPXPXP
New Perspectives on Microsoft Access 2013 7
Introduction to Visual Basic for Applications (Cont.)
• Visual Basic for Applications (VBA)– The programming language provided with Access
and other Office programs– VBA has a common syntax and a set of common
features for all Microsoft Office programs, but it also has features that are unique for each Microsoft Office program due to each program’s distinct structure and components
• Coding – The process of writing instructions in a
programming language
XPXPXP
New Perspectives on Microsoft Access 2013 8
Introduction to Visual Basic for Applications (Cont.)
• Statement– The VBA instructions to respond to an event that
occurs with an object or a form control in a database• An event could be a click on a button, or activating a
textbox control.
• Event-driven language– Events in the database trigger a set of instructions
• Object-oriented language– Each set of instructions operates on objects in the
database
XPXPXP
New Perspectives on Microsoft Access 2013 9
Introduction to Visual Basic for Applications (Cont.)
• Events– A state, condition, or action that Access recognizes
XPXPXP
New Perspectives on Microsoft Access 2013 10
Introduction to Visual Basic for Applications (Cont.)
• Procedures– A group of statements to perform a set of
operations• User-Defined function– Performs operations, returns a value, accepts input
values, and can be used in expressions• Sub procedure– Executes instructions and accepts input values, but
does not return a value and cannot be used in expressions
XPXPXP
New Perspectives on Microsoft Access 2013 11
Introduction to Visual Basic for Applications (Cont.)
• Modules– A group of related procedures together in an
object• Declarations section– Contains statements that apply to all procedures in
the module• Standard module– A database object that is stored in memory with
other database objects (queries, forms, and so on) when you open the database
XPXPXP
New Perspectives on Microsoft Access 2013 12
Introduction to Visual Basic for Applications (Cont.)
• Public procedure– A procedure that more than one object can use
• Class module– Usually associated with a particular form or report– When you create the first event procedure for a form or
report, Access automatically creates an associated form or report class module
– When you add additional event procedures to the form or report, Access adds them to the class module for that form or report
– Each event procedure in a class module is a local procedure, or a private procedure
XPXPXP
New Perspectives on Microsoft Access 2013 13
Using an Existing Procedure
XPXPXP
New Perspectives on Microsoft Access 2013 14
Using an Existing Procedure (Cont.)
• Displaying an Event Procedure– The VBA procedure that controls the display of the
message and its color for each record is in the class module for the form
– Access processes the statements in the procedure when you open the form and also when the focus leaves one record and moves to another
– The event called the Current event occurs when the focus shifts to the next record loaded in a form, making it the current record.
– The OnCurrent property contains a reference to a macro, VBA code, or some other expression that runs when the Current event occurs
XPXPXPUsing an Existing Procedure (Cont.)
New Perspectives on Microsoft Access 2013 15
XPXPXPUsing an Existing Procedure (Cont.)
New Perspectives on Microsoft Access 2013 16
XPXPXPUsing an Existing Procedure (Cont.)
New Perspectives on Microsoft Access 2013 17
• Visual Basic Editor (VBE)– The program you use to create and modify VBA code
• Visual Basic Window– The program window that opens when you use VBE
• Scope– Indicates where the procedure is available– If the scope is public, the procedure is available in all
objects in the database– If the scope is private, the procedure is available only in the
object in which it is created– Event procedures are private, by default
XPXPXPUsing an Existing Procedure (Cont.)
New Perspectives on Microsoft Access 2013 18
• Control Structure– The set of VBA statements that work together as a unit
• Conditional Control Structure– The evaluates an expression—the value of the Reason field
and then performs one of several alternative actions based on the resulting value (or condition) of the evaluated expression
XPXPXPUsing an Existing Procedure (Cont.)
New Perspectives on Microsoft Access 2013 19
XPXPXPUsing an Existing Procedure (Cont.)
New Perspectives on Microsoft Access 2013 20
• Modifying an Event Procedure
XPXPXPCreating Functions in a Standard Module
New Perspectives on Microsoft Access 2013 21
• String– The or more characters that could include alphabetic
characters, numbers, spaces, and punctuation
• AfterUpdate Event– Triggered when a user enters or changes a field value in a
control or in a form and then changes the focus
XPXPXPCreating Functions in a Standard Module (Cont.)
New Perspectives on Microsoft Access 2013 22
• Creating a Function– Each function begins with a Function statement
and ends with an End Function statement– You’ll start the function with the function name
and followed by a parameter, which is the value passed to the function -- called an argument
XPXPXPCreating Functions in a Standard Module (Cont.)
New Perspectives on Microsoft Access 2013 23
XPXPXPCreating Functions in a Standard Module (Cont.)
New Perspectives on Microsoft Access 2013 24
XPXPXPTesting a Procedure in the Immediate Window
New Perspectives on Microsoft Access 2013 25
• Logic Error– Occurs when a procedure produces incorrect results
• Immediate window– Allows you to test VBA procedures without changing any
data in the database
XPXPXPCreating an Event Procedure
New Perspectives on Microsoft Access 2013 26
XPXPXPCreating an Event Procedure (Cont.)
New Perspectives on Microsoft Access 2013 27
XPXPXPCreating an Event Procedure (Cont.)
New Perspectives on Microsoft Access 2013 28
• Designing an Event Procedure– If Statement• Executes one of two groups of statements based on a
condition, similar to common English usage• True-statement group - What happens if the condition is true
• False-statement group - What happens if the condition is false
XPXPXPCreating an Event Procedure (Cont.)
New Perspectives on Microsoft Access 2013 29
• Adding an Event Procedure
XPXPXPCreating an Event Procedure (Cont.)
New Perspectives on Microsoft Access 2013 30
• Compiling Modules
XPXPXPCreating an Event Procedure (Cont.)
New Perspectives on Microsoft Access 2013 31
• Testing an Event Procedure
XPXPXPAdding a Second Procedure to a Class Module
New Perspectives on Microsoft Access 2013 32
• Designing the Field Validation Procedure
XPXPXPAdding a Second Procedure to a Class Module (Cont.)
New Perspectives on Microsoft Access 2013 33
XPXPXP
• Variable– A named location in computer memory that can contain a
value
• Dim statement– Used to declare variables and their associated data types in
a procedure
Adding a Second Procedure to a Class Module (Cont.)
New Perspectives on Microsoft Access 2013 34
XPXPXP
• Adding a Second Event Procedure
Adding a Second Procedure to a Class Module (Cont.)
New Perspectives on Microsoft Access 2013 35
XPXPXPAdding a Second Procedure to a Class Module (Cont.)
New Perspectives on Microsoft Access 2013 36
XPXPXPChanging the Case of a Field Value
New Perspectives on Microsoft Access 2013 37
• The StrConv function converts the letters in a string to all uppercase letters or to all lowercase letters, or converts the first letter of every word in the string to uppercaseletters and all other letters to lowercase letters,
[Address] = StrConv([Address], vbProperCase)• The vbProperCase constant, is a VBA constant that specifies
the conversion of the first letter in every word in a string to uppercase letters and the conversion of all other letters to lowercase letters
• Other VBA constants you can use are the vbUpperCase constant, which specifies the conversion of the string to all uppercase letters, and the vbLowerCase constant, which specifies the conversion of the string to all lowercase letters
XPXPXPChanging the Case of a Field Value (Cont.)
New Perspectives on Microsoft Access 2013 38
XPXPXPHiding a Control and Changing a Control’s Color
New Perspectives on Microsoft Access 2013 39
XPXPXPHiding a Control and Changing a Control’s Color (Cont.)
New Perspectives on Microsoft Access 2013 40
XPXPXPHiding a Control and Changing a Control’s Color (Cont.)
New Perspectives on Microsoft Access 2013 41