Tutorial 11 Using and Writing Visual Basic for Applications Code

1. Tutorial 11Using and Writing Visual Basic for Applications Code

2. Objectives • 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

3. Objectives • 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

4. Introduction 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

5. Introduction to Visual Basic for Applications (Cont.) New Perspectives on Microsoft Access 2013

6. Introduction to Visual Basic for Applications (Cont.) 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 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 New Perspectives on Microsoft Access 2013

9. Introduction to Visual Basic for Applications (Cont.) • Events • A state, condition, or action that Access recognizes 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 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 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 New Perspectives on Microsoft Access 2013

13. Using an Existing Procedure 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 New Perspectives on Microsoft Access 2013

15. Using an Existing Procedure (Cont.) New Perspectives on Microsoft Access 2013

16. Using an Existing Procedure (Cont.) New Perspectives on Microsoft Access 2013

17. Using an Existing Procedure (Cont.) • 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 New Perspectives on Microsoft Access 2013

18. Using an Existing Procedure (Cont.) • 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 New Perspectives on Microsoft Access 2013

19. Using an Existing Procedure (Cont.) New Perspectives on Microsoft Access 2013

20. Using an Existing Procedure (Cont.) • Modifying an Event Procedure New Perspectives on Microsoft Access 2013

21. Creating Functions in a Standard Module • 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 New Perspectives on Microsoft Access 2013

22. Creating Functions in a Standard Module (Cont.) • 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 New Perspectives on Microsoft Access 2013

23. Creating Functions in a Standard Module (Cont.) New Perspectives on Microsoft Access 2013

24. Creating Functions in a Standard Module (Cont.) New Perspectives on Microsoft Access 2013

25. Testing a Procedure in the Immediate Window • Logic Error • Occurs when a procedure produces incorrect results • Immediate window • Allows you to test VBA procedures without changing any data in the database New Perspectives on Microsoft Access 2013

26. Creating an Event Procedure New Perspectives on Microsoft Access 2013

27. Creating an Event Procedure (Cont.) New Perspectives on Microsoft Access 2013

28. Creating an Event Procedure (Cont.) • 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 New Perspectives on Microsoft Access 2013

29. Creating an Event Procedure (Cont.) • Adding an Event Procedure New Perspectives on Microsoft Access 2013

30. Creating an Event Procedure (Cont.) • Compiling Modules New Perspectives on Microsoft Access 2013

31. Creating an Event Procedure (Cont.) • Testing an Event Procedure New Perspectives on Microsoft Access 2013

32. Adding a Second Procedure to a Class Module • Designing the Field Validation Procedure New Perspectives on Microsoft Access 2013

33. Adding a Second Procedure to a Class Module (Cont.) New Perspectives on Microsoft Access 2013

34. Adding a Second Procedure to a Class Module (Cont.) • 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 New Perspectives on Microsoft Access 2013

35. Adding a Second Procedure to a Class Module (Cont.) • Adding a Second Event Procedure New Perspectives on Microsoft Access 2013

36. Adding a Second Procedure to a Class Module (Cont.) New Perspectives on Microsoft Access 2013

37. Changing the Case of a Field Value • 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 New Perspectives on Microsoft Access 2013

38. Changing the Case of a Field Value (Cont.) New Perspectives on Microsoft Access 2013

39. Hiding a Control and Changing a Control’s Color New Perspectives on Microsoft Access 2013

40. Hiding a Control and Changing a Control’s Color (Cont.) New Perspectives on Microsoft Access 2013

41. Hiding a Control and Changing a Control’s Color (Cont.) New Perspectives on Microsoft Access 2013