Microsoft Office Access 2007 – Illustrated

1. Microsoft Office Access 2007 – Illustrated Creating Modules and VBA

2. Objectives • Understand modules and VBA • Compare macros and modules • Create functions • Use If statements (continued) Microsoft Office Access 2007 - Illustrated

3. Objectives (continued) • Document procedures • Examine class modules • Create sub procedures • Troubleshoot modules Microsoft Office Access 2007 - Illustrated

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 Microsoft Office Access 2007 - Illustrated

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 Microsoft Office Access 2007 - Illustrated

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 Microsoft Office Access 2007 - Illustrated

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 Microsoft Office Access 2007 - Illustrated

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 Microsoft Office Access 2007 - Illustrated

9. Visual Basic Editor Code Windowfor a Standard Module Project Explorer window Properties window Declaration statements Function procedure Microsoft Office Access 2007 - Illustrated

10. Components for the Visual Basic Window Microsoft Office Access 2007 - Illustrated

11. Text Colors for the Visual Basic Window Microsoft Office Access 2007 - Illustrated

12. Comparing Macrosand 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 can’t accomplish many outside tasks • No declared variables • Simpler to create Microsoft Office Access 2007 - Illustrated

13. Some Common VBA Keywords Microsoft Office Access 2007 - Illustrated

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 Microsoft Office Access 2007 - Illustrated

15. Creating a Function Microsoft Office Access 2007 - Illustrated

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 Microsoft Office Access 2007 - Illustrated

17. Using an If...Then...Else Statement Second argument If Then Else End If Microsoft Office Access 2007 - Illustrated

18. Documenting Procedures • Comment lines: Statements in the code that document the code • Useful if you want to read or modify existing code • They don’t affect how the code runs • Comment lines start with an apostrophe and are green in the Code window Microsoft Office Access 2007 - Illustrated

19. Standard Toolbar Buttons in theVisual Basic Window Microsoft Office Access 2007 - Illustrated

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.) Microsoft Office Access 2007 - Illustrated

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.) Microsoft Office Access 2007 - Illustrated

22. Creating Sub Procedures • Subs can be triggered on any event identified in the Property Sheet • On Dbl Click • After Update • Before Update • Others Microsoft Office Access 2007 - Illustrated

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 doesn’t update data Microsoft Office Access 2007 - Illustrated

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 Microsoft Office Access 2007 - Illustrated

25. Setting a Breakpoint View Access button Breakpoint Debug menu Reset button Microsoft Office Access 2007 - Illustrated

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 Microsoft Office Access 2007 - Illustrated

27. Summary • You have also learned to: • Create functions • Use If...Then...Else • Document procedures • Analyze procedures • Create subs • Troubleshoot modules Microsoft Office Access 2007 - Illustrated