Access 2019 Module 11 Creating Modules and VBA

Access 2019 Module 11 Creating Modules and VBA © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Module Objectives • Understand modules and VBA • Compare macros and modules • Create functions • Use VBA If statements • Document procedures • Build class modules • Modify procedures • Troubleshoot VBA © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 2

Understand Modules and VBA (1 of 6) • Module - an Access object that stores Visual Basic for Applications (VBA) programming code • Visual Basic Editor (VBE) - program for writing VBA code • Two kinds of modules in an Access database • Standard modules - contain global code executable from anywhere in the database - Displayed as module objects in the Navigation Pane • Class modules - code stored within the form or report object itself; can only be executed in that object © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 3

Understand Modules and VBA (2 of 6) • Module - contains VBA programming code organized in procedures • Procedure - contains several lines of code; each line is a statement. • Comments - text that helps explain and document the code • Procedure: a series of VBA statements • Performs an operation or calculates an answer • Two types of procedures: • Functions • Subs (sub procedure) © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 4

Understand Modules and VBA (3 of 6) • Function - a procedure that returns a value • Sub (sub procedure) - performs a series of VBA statements to manipulate controls and objects • Arguments - constants, variables, or expressions • Passed to a procedure; required in order for the procedure to execute • Object - any item that can be identified or manipulated • Includes Access objects (table, query, form, etc. ) as well as other items (e. g. , controls, sections, and existing procedures) that have properties • Method - an action that an object can perform © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 5

Understand Modules and VBA (4 of 6) © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 6

Understand Modules and VBA (5 of 6) Table 11. 1: Components of the Visual Basic Editor window component or color description Visual Basic Editor, V BE Comprises the entire Microsoft Visual Basic program window that contains smaller windows, including the Code window and Project Explorer window Code Window Contains the VBA for the project selected in the Project Explorer window Displays a hierarchical list of the projects in the database; a project can be a module object or a form or report object that contains a class module Declaration statements Includes statements that apply to every procedure in the module, such as declarations for variables, constants, user-defined data types, and external procedures in a dynamic-link library Object list In a class module, lists the objects associated with the current form or report © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 7

Understand Modules and VBA (6 of 6) Table 11. 1: Components of the Visual Basic Editor window component or color description Procedure list In a standard module, lists the procedures in the module; in a class module, lists events (such as Click or Dblclick) Blue Indicates a VBA keyword; blue words are reserved by VBA and are already assigned specific meanings Black Indicates normal text; black text is the unique VBA code created by the developer Red Indicates syntax error text; a red statement indicates that it will not execute correctly because of a syntax error (perhaps a missing parenthesis or a spelling error) Green Indicates comment text; any text after an apostrophe is considered documentation, or a comment, and is therefore ignored in the execution of the procedure © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 8

Compare Macros and Modules (1 of 5) • Macros - easy way to handle common, repetitive, and simple tasks • Examples are: open and close forms, show and hide toolbars, and print reports • Macros - generally easier to create than modules • Macro Design View is more structured than the VBE © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 9

Compare Macros and Modules (2 of 5) • Creating a module • Requires VBA programming language knowledge • Must apply correct syntax (rules) for each VBA statement • Macros • Simpler to create • VBA • More powerful • Macro • Must be used to make global, shortcut key assignments © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 10

Compare Macros and Modules (3 of 5) • Auto. Exec - special macro name • Automatically executes when the database first opens • Use a module to • Create a unique function • Detect Access error and display custom message • Apply more powerful If-Then logic • Declare variables • Work in conjunction with SQL (Structured Query Language) to select, update, append, and delete data © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 11

Compare Macros and Modules (4 of 5) © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 12

Compare Macros and Modules (5 of 5) Table 11 -2: Common VBA keywords object or control type prefix example Table tbl. Products Query Qry qry. Sales. By. Region Form frm. Products Report rpt. Sales. By. Category Macro mcr. Closelnventory Module bas. Retirement Label Ibl. Full. Name Text Box txt. Last. Name Combo box cbo. States Command button cmd. Print © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 13

Create Functions (1 of 3) • Many built-in functions such as: Sum, Count, IIf, First, Last, Date, and Hour • Can create a custom function in a module • Can use in any query, form, or report throughout the database • End Function statement • Automatically added by VBA • Required statement marking the end of a function • Common VBA programming practice to apply three-character prefixes to name objects and controls • Prefix bas: short for Basic; applies to global modules © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 14

Create Functions (2 of 3) © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 15

Create Functions (3 of 3) Table 11 -3: Common three-character prefix naming conventions object or control type Table Query Form Report Macro Module Label Text Box Combo box Command button prefix tbl qry frm rpt mcr bas Ibl txt cbo cmd example tbl. Products qry. Sales. By. Region frm. Products rpt. Sales. By. Category mcr. Closelnventory bas. Retirement Ibl. Full. Name txt. Last. Name cbo. States cmd. Print © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 16

Use VBA If Statements If. . . Then. . . Else logic - tests logical conditions and executes statements only if the conditions are true © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 17

Document Procedures (1 of 3) • Comment lines - statements in the code that document the code; do not affect how the code runs • Start with an apostrophe and are green in the VBE • Using comments for debugging • Temporarily hide statements that are needed in module but do not want to execute • Keeps a trail of every line of code written • Used in development, debugging, and sharing with other developers © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 18

Document Procedures (2 of 3) © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 19

Document Procedures (3 of 3) Table 11 -4 Standard Toolbar Buttons in the Visual Basic Windowa © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 20

Build Class Modules (1 of 3) • Class modules - contained and executed within specific forms and reports • Most commonly run in response to an event (a specific action that occurs as the result of a user action) • A sub procedure triggered by an event is often called an event handler © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 21

Build Class Modules (2 of 3) © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 22

Build Class Modules (3 of 3) Branch of Service combo box is visible when Veteran box is checked © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 23

Modify Procedures (1 of 3) • Sub procedures can be triggered on any event identified in Property Sheet • Examples: On Got Focus (when control gets focus), After Update (after a field is updated), or On Dbl Click (when control is double-clicked) • Item’s set of event properties depends on type of item • Do. Cmd – VBA object that supports many methods to run common Access commands • Intelli. Sense technology - Visual aid that help you complete Do. Cmd statements © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 24

Modify Procedures (2 of 3) © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 25

Modify Procedures (3 of 3) © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 26

Troubleshoot VBA (1 of 3) • Several techniques for debugging different types of V B A errors • Types of errors • Syntax error - cannot be read by the Visual Basic Editor - Occurs immediately as V B A statement is written • Compile-time errors - incorrectly constructed code - Detected as soon as code is run or when Compile option selected on the Debug menu • Run-time errors - incorrectly constructed code; stops code execution - Examples: attempting an illegal operation e. g. , dividing by zero • Logic errors - code runs without obvious problems; procedure does not produce the desired result - Most difficult errors to troubleshoot © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 27

Troubleshoot VBA (2 of 3) © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 28

Troubleshoot VBA (3 of 3) © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 29