ACCESS VBA BASIC

Course Overview

The Access VBA Training Course is a comprehensive introduction into VBA. The course is suitable for students who confidently understand and utilise Access to an advanced level on a daily basis and wish to automate and customise complex calculations and processes in their current work flows. Participants must be able to confidently build databases before undertaking this course. At the completion of this course the participant will be able to confidently navigate and utilise the VBA editor to run, edit and debug VBA code.

Participants will also have a solid foundation on the Access Object Model along with their associated properties, methods and events which are the building blocks of the VBA programming language.

Course Prerequisites

Microsoft Access Advanced Course or equivalent

Raising the Bar

1300 937 782

www.rtb.com.au

Getting Started

What is VBA?
Open the VBA Editor
Create a Module
Create a New Subroutine
Add Commands
Run the Subroutine
Save the File

Security

Open a File Containing Code
Create a Digital Signature
Digitally Self-Sign a File
Add the Publisher to the Trusted List
Install Certificates
Remove Trusted Publishers

Using the VBA Editor

Run Code in the VBA Window
Step Through Code
Step Over Code
Set a Break Point
Comment Code
IntelliSense and AutoComplete
Getting Help
The Project Explorer and Properties Window

Variables

What is a Variable?
Naming Variables
Store a Value in a Variable
Why Should I Declare Variables?
Procedure-Level Declarations
Module-Level Declarations
Global-Level Declarations
Static Declarations
Option Explicit
Declaring Data Types
Recognise Errors From Declaring Variables
Discover the Current Value of a Variable

Interactions With The User

Interacting With the User
MsgBox as a Method
MsgBox as a Function
Message Box using Numerical Constants
The InputBox Function
Long Messages

Making Decisions

A Note On Indenting
Line If
Block If
ElseIf and Else
Select Case

Looping

For/Next Iterations
For/Next Stepped Iterations
Do/Loop With Initial Test
Do/Loop With Final Test

Error Handling

Run-time Errors
Add a Simple Stop
The Err Object
Exit Sub
Error Handling with Another Sub
Resuming

Class and Standard Modules

Course Prerequisite
Security and Trusted Locations
Create a Standard Module
Edit a Standard Module
Edit a Class Module Locate and Run Code from the Class Module

Access Objects

The Object Hierarchy
Locate an Object
Properties and Methods
The Application Object
The DoCmd Object
The Forms and Controls Objects
The Me Object Adding Higher Hierarchical Levels

ADO

How Does ADO Work?
Setting ADO References
Connecting to the Database
Populate ADO Recordsets
Locate a Record using ADO
BOF and EOF
Filter and Count the Records
Add a Record
Modify a Record
Delete a Record
Close the Connection and Recordset
Create Objects Using ADOX
Delete Objects Using ADOX

Common VBA Techniques

Use ADO To Validate A Password
Use ADO To Create a Log
Using VBA To Populate Form Controls
Display And Hide Controls
Cascading Combo Boxes
Access Data In Hidden Combo Box Columns
Let’s Practice Common VBA Techniques

Other Applications

The DoCmd OutputTo Method
The DoCmd Transfer Methods
Define Another Appplication As An Object
Manipulating Outlook
Manipulating Excel