<< Back to Courses

Access VBA 2007


Cost: Contact us for quote.   Duration: 2 days


Contact Us to book.

Overview
This course is intended for people who wish to improve their knowledge and skills in the use of Visual
Basic for Applications to program applications in Microsoft Access 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 many ADO techniques
• use ADO to 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

Ø Recorded Macros

 Understanding Excel Macros

 Setting Macro Security

 Saving A Document As Macro Enabled

 Recording A Simple Macro

 Running A Recorded Macro

 Relative Cell References

 Running A Macro With Relative References

 Viewing A Macro

 Editing A Macro

 Assigning A Macro To The Toolbar

 Running A Macro From The Toolbar

 Assigning A Macro To The Ribbon

 Assigning A Keyboard Shortcut To A Macro

 Deleting A Macro

 Copying A Macro

Ø Recorder Workshop

 Preparing Data For An Application

 Recording A Summation Macro

 Recording Consolidations

 Recording Divisional Macros

 Testing Macros

 Creating Objects To Run Macros

 Assigning A Macro To An Object

Ø Understanding Excel VBA

 Programming In Microsoft Excel

 VBA Terminology

 Understanding Objects

 Viewing The Excel 2010 Object Model

 Using The Immediate Window

 Working With Object Collections

 Setting Property Values

 Working With Worksheets

 Using The Object Browser

 Programming With The Object Browser

 The Best VBA Help Available

Ø The VBA Editor

 The VBA Editor Screen

 Opening And Closing The Editor

 Using The Project Explorer

 Working With The Properties Window

 Using The Work Area

 Viewing Other Panes

 Working With Toolbars

 Working With A Code Module

 Running Code From The Editor

 Setting Breakpoints In Code

 Stepping Through Code

Ø Procedures

 Understanding Procedures

 Where Procedures Live

 Creating A New Sub Routine

 Making Sense Of IntelliSense

 Using The Edit Toolbar

 Commenting Statements

 Indenting Code

 Bookmarking In Procedures

Ø Using Variables

 Understanding Variables

 Creating And Using Variables

 Explicit Declarations

 The Scope Of Variables

 Procedure Level Scoping

 Module Level Scoping

 Passing Variables

 Passing Variables By Reference

 Passing Variables By Value

 Data Types For Variables

 Declaring Data Types

 Using Arrays

Ø Functions In VBA

 Understanding Functions

 Creating VBA Functions

 Using A VBA Function In A Worksheet

 Setting Function Data Types

 Using Multiple Arguments

 Modifying A VBA Function

 Creating A Function Library

 Referencing A Function Library

 Importing A VBA Module

 Using A Function In VBA Code

Ø Using Excel Objects

 The Application Object

 The Workbook Objects

 Program Testing With The Editor

 Using Workbook Objects

 The Worksheets Object

 Using The Worksheets Object

 The Range Object

 Using Range Objects

 Using Objects In A Procedure

Ø Programming Techniques

 The MsgBox Function

 Using MsgBox

 InputBox Techniques

 Using The InputBox Function

 Using The InputBox Method

 The IF Statement

 Using IF For Single Conditions

 Using IF For Multiple Conditions

 The Select Case Statement

 Using The Select Case Statement

 For Loops

 Looping With Specified Iterations

 The Do…Loop Statement

 Looping With Unknown Iterations

Ø Creating Custom Forms

 Understanding VBA Forms

 Creating A Custom Form

 Adding Text Boxes To A Form

 Changing Text Box Control Properties

 Adding Label Controls To A Form

 Adding A Combo Box Control

 Adding Option Buttons

 Adding Command Buttons

 Running A Custom Form

Ø Programming UserForms

 Handling Form Events

 Initialising A Form

 Closing A Form

 Transferring Data From A Form

 Running Form Procedures

 Creating Error Checking Procedures

 Running A Form From A Procedure

 Running A Form From The Toolbar

Ø Automatic Startup

 Programming Automatic Procedures

 Running Automatic Procedures

 Automatically Starting A Workbook

Ø Error Handling

 Understanding Error Types

 The On Error Statement

 Simple Error Trapping

 Using The Resume Statement

 Using Decision Structures In Error Handlers

 Working With Err Object

 Error Handling In Forms

 Coding Error Handling In Forms

 Defining Custom Errors

Ø Concluding Remark

 

Overview

This course is intended for people who wish to improve their knowledge and skills in the use of Visual Basic for Applications to program applications in Microsoft Access 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 many ADO techniques

·       use ADO to 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


 

Ø Recorded Macros

ü Understanding Excel Macros

ü Setting Macro Security

ü Saving A Document As Macro Enabled

ü Recording A Simple Macro

ü Running A Recorded Macro

ü Relative Cell References

ü Running A Macro With Relative References

ü Viewing A Macro

ü Editing A Macro

ü Assigning A Macro To The Toolbar

ü Running A Macro From The Toolbar

ü Assigning A Macro To The Ribbon

ü Assigning A Keyboard Shortcut To A Macro

ü Deleting A Macro

ü Copying A Macro

Ø Recorder Workshop

ü Preparing Data For An Application

ü Recording A Summation Macro

ü Recording Consolidations

ü Recording Divisional Macros

ü Testing Macros

ü Creating Objects To Run Macros

ü Assigning A Macro To An Object

Ø Understanding Excel VBA

ü Programming In Microsoft Excel

ü VBA Terminology

ü Understanding Objects

ü Viewing The Excel 2010 Object Model

ü Using The Immediate Window

ü Working With Object Collections

ü Setting Property Values

ü Working With Worksheets

ü Using The Object Browser

ü Programming With The Object Browser

ü The Best VBA Help Available

Ø The VBA Editor

ü The VBA Editor Screen

ü Opening And Closing The Editor

ü Using The Project Explorer

ü Working With The Properties Window

ü Using The Work Area

ü Viewing Other Panes

ü Working With Toolbars

 

 

 

ü Working With A Code Module

ü Running Code From The Editor

ü Setting Breakpoints In Code

ü Stepping Through Code

Ø Procedures

ü Understanding Procedures

ü Where Procedures Live

ü Creating A New Sub Routine

ü Making Sense Of IntelliSense

ü Using The Edit Toolbar

ü Commenting Statements

ü Indenting Code

ü Bookmarking In Procedures

Ø Using Variables

ü Understanding Variables

ü Creating And Using Variables

ü Explicit Declarations

ü The Scope Of Variables

ü Procedure Level Scoping

ü Module Level Scoping

ü Passing Variables

ü Passing Variables By Reference

ü Passing Variables By Value

ü Data Types For Variables

ü Declaring Data Types

ü Using Arrays

Ø Functions In VBA

ü Understanding Functions

ü Creating VBA Functions

ü Using A VBA Function In A Worksheet

ü Setting Function Data Types

ü Using Multiple Arguments

ü Modifying A VBA Function

ü Creating A Function Library

ü Referencing A Function Library

ü Importing A VBA Module

ü Using A Function In VBA Code

Ø Using Excel Objects

ü The Application Object

ü The Workbook Objects

ü Program Testing With The Editor

ü Using Workbook Objects

ü The Worksheets Object

ü Using The Worksheets Object

ü The Range Object

ü Using Range Objects

ü Using Objects In A Procedure

Ø Programming Techniques

ü The MsgBox Function

 

 

 

ü Using MsgBox

ü InputBox Techniques

ü Using The InputBox Function

ü Using The InputBox Method

ü The IF Statement

ü Using IF For Single Conditions

ü Using IF For Multiple Conditions

ü The Select Case Statement

ü Using The Select Case Statement

ü For Loops

ü Looping With Specified Iterations

ü The Do…Loop Statement

ü Looping With Unknown Iterations

Ø Creating Custom Forms

ü Understanding VBA Forms

ü Creating A Custom Form

ü Adding Text Boxes To A Form

ü Changing Text Box Control Properties

ü Adding Label Controls To A Form

ü Adding A Combo Box Control

ü Adding Option Buttons

ü Adding Command Buttons

ü Running A Custom Form

Ø Programming UserForms

ü Handling Form Events

ü Initialising A Form

ü Closing A Form

ü Transferring Data From A Form

ü Running Form Procedures

ü Creating Error Checking Procedures

ü Running A Form From A Procedure

ü Running A Form From The Toolbar

Ø Automatic Startup

ü Programming Automatic Procedures

ü Running Automatic Procedures

ü Automatically Starting A Workbook

Ø Error Handling

ü Understanding Error Types

ü The On Error Statement

ü Simple Error Trapping

ü Using The Resume Statement

ü Using Decision Structures In Error Handlers

ü Working With Err Object

ü Error Handling In Forms

ü Coding Error Handling In Forms

ü Defining Custom Errors

Ø Concluding Remark

Zip File
Download Exercise Files