Access - Programming in VBA 2007

Learning Outcomes

At the completion of Microsoft Access 2007 Programming in VBA you should be able to:

• be familiar with the basics of developing applications in Access 2007
• create and convert macros into VBA
• make Access start up with the form you select
• create subroutines
• understand how to use variables to generate an output
• understand and create input boxes and message boxes
• create and use decision-making programs
• create and use programming loops
• understand and use the DoCmd object
• recognise and understand manyADOtechniques
• useADOto work with forms and recordsets
• apply a range of debugging tools to your application
• replace obscure error messages with meaningful ones

Target Audience

This course aims to increase your productivity by providing the skills and knowledge to use VBA to program in Microsoft Access 2007.           

Prerequisites

Microsoft Access 2007 Programming in VBA assumes little or no knowledge of the software. However, it would be beneficial to have a general understanding of personal computers and the Windows operating system environment.

Duration

Scheduled classroom training (2 Days)

Contents

• Understanding The Development Environment
• Understanding Splitting Databases
• Linking To Data Files
• Maintaining Links
• Using The Database Splitter
• Using Queries For Reports And Forms
• Developing Systems For Other Users

Macros And VBA

• Understanding Macros
• Creating Macros
• Assigning Macros
• Creating More Complex Macros
• Creating Complex Macros
• Converting Macros To VBA

VBA Essentials

• Understanding The Programming Environment
• Exploring The Object Model
• Navigating With Forms
• Creating A Startup Form
• The VBA Editor Window
• Working With The Project Explorer
• Working With The Properties Window
• Understanding Modules I
• Viewing The Code Behind Forms
• Standard Code Modules
• Understanding Modules II
• Understanding Naming Conventions I
• Understanding Naming Conventions II

VBA Subroutines

• Understanding Modules In Access
• Understanding Procedures
• Anatomy Of A Module
• Creating A Subroutine
• Creating A Function
• Calling Procedures
• Passing Parameters
• Returning Values
• coping Procedures

Using Variables

• Understanding Variables
• Understanding Data Types
  • Creating And Using Variables
  • Implicit And Explicit Declarations
  • Working With Numbers In VBA
  • Dealing With Overflows
  • Working With Decimals In VBA
  • Working With Dates In VBA
  • Default Values
  • Understanding The Scope Of Variables
  • Procedure Level Scoping
  • Module Level Scoping
  • Passing Variables By Reference
  • Passing Variables By Value

Interacting With Users

• Understanding Input Boxes
• Using The InputBox Function
• Using InputBox Function Arguments
• Understanding Message Boxes
• Creating A Simple Message Box
• Creating A Longer Message
• Breaking A Message Line
• Displaying Buttons And Icons
• Using The Message Box Function
• Responding To Different Buttons

Making Decisions

• Understanding The IF Statement
• Creating A Single-Line If Statement
• Creating A Block If Statement
• Creating Multiple If Statements
• Creating If Statements
• Using If For Multiple Text Conditions
• Understanding The Select Case Statement
• A Simple Select Case Statement
• Value Ranges In Select Case Statements
• Using Select Case Statements With OR

Looping In VBA

• Understanding Loops
• Understanding For Loops
• Creating A While Loop
• Working With Counters
• Creating An Until Loop
• For…Each Loops
• Exiting From Loops
• For...Next Loops
• Creating Loops

The DoCmd Object

• Understanding DoCmd
• Navigating With DoCmd
• Moving Through Records
• Filtering Records With DoCmd
• Opening And Closing A Form With DoCmd
• Opening A Report With DoCmd
• Creating Variable Reports

ActiveX Data Objects - Tables

• Understanding ADO
• Setting References Using VBA
• Creating A Table With ADO
• Creating Tables With ADO
• Removing A Table Using ADO
• Removing A Table With ADO
• Populating A Form Using ADO
• Dealing With An Empty Table

ADO Forms And Recordsets

• Navigating Records In A Form
• Finding A Record
• Updating A Record
• Deleting A Record
• Adding A New Record
• Adding A New Record

Debugging VBA

• Understanding Debugging
• Setting A Breakpoint
• Setting Additional Breakpoints
• Stepping Through A Procedure
• Viewing The Call Stack
• Using Locals And Immediate Windows
• Adding A Watch