VBA Access Programming

This course is designed for students with advanced Access skills who are new to Visual Basic programming. Topics & theories covered include reasons for using VBA in Access, good coding habits, using variables, constants & data types, error handling....

Course Outline

This course is designed for students with advanced Access skills who are new to Visual Basic programming. Topics and theories covered in this course include reasons for using VBA in Access, good coding habits, using variables, constants and data types, error handling, debugging code, using procedures, subroutines vs. functions, conversion, date, math, string, format and its functions, input and message boxes, logical programming commands, arrays, objects, and classes and instantiation. Other topics include working with forms, events, combo boxes, multi-select lists, text boxes, and sub forms.

Course Content Why Access? Why VBA?

• Understanding the Place of Access in Office
  • Choosing Between Access and Excel
  • Choosing Between Access and OneNote
• Understanding Access Programming Choices
  • Using Macros
  • Using SQL
  • Using VBA

Getting Around in the Visual Basic Editor

• Your First Glance at the Visual Basic Editor
• Introducing the VBA Modules  
• Entering and Running VBA Code
• Getting Help on Code
• Establishing Good Habits in Coding

Using Variables, Constants, and Data Types

• Declaring Variables and Constants
• VBA Data Types

Using Procedures

• Understanding Procedure Types
• Declaring Procedures as Public or Private
• Passing Arguments
• Giving a Function a Data Type
• Implementing Error Handling
• Debugging Code

Choosing the Right VBA Function

• Introducing VBA’s Built-in Functions
• Converting Data Types with VBA Functions
• Working with Date Functions
• Using Mathematical and Financial Functions
• Using String Functions
• Using the Format Function
• Using the Is Functions for Flawless Processing
• Interacting Functions

Using Flow-of-Control Statements

• Branching and Looping
• Using If…Then…Else
• Using Select Case
• Using For…Next
• Using Do Loops
• Using GoTo

Working with Arrays

• Introducing Array Variables
• Declaring an Array Variable
• Understanding the Array’s Index
• Working with Array Elements
• Arrays with Multiple Dimensions
• Expanding to Dynamic Arrays

Understanding Objects

• Introducing Objects
• Reading and Setting Properties
• Invoking Methods
• Working with Collections
• Working with an Object Model
• Creating Your Own Objects
• Working with Events

Understanding Scope and Lifetime

• What’s Scope?
• Measuring the Lifetime of a Variable or Constant
• Using Static Variables

Working with Forms

• Opening and Closing Forms
• The Form Module and Event Handling
• Performing Common Tasks
• Handling Errors at the Form Level
• Working with Multiple Form Instances

Analyzing the Access Event Model

• Responding to Events
• The Event Sequence for Controls
• The Event Sequence for Forms
• The Event Sequence for Reports
• Canceling Events

Working with List and Combo Boxes

• Populating a List Control
• Adding to the List – or Not
• Working with Multiselect Controls
• Considering Callback Functions

Working with Other Controls

• Working with Text Boxes
• Using Controls in Option Groups
• Working with Subforms
• Working with the Tag Property

Working with Reports

• Introducing the Report Module and Events
• Opening and Closing Reports
• Passing Arguments Using OpenArgs
• Populating the Report
• Handling Report-Level Errors
• Using VBA to Determine Group Properties

Working with the Application Collections

• Investigating the Application Collections
• Retrieving Lists of Objects
• Working with Object Properties
• Programmatically Determining Dependencies

Retrieving Data with ADO

• What’s ADO and Why Do You need It?
• Using the ADO Connection Object
• Working with Command Objects
• Understanding the Different Types of Recordsets
• Creating and Opening a Recordset
• Filtering Recordsets
• Using the Recordset Property

Manipulating Data with ADO

• Moving Through a Recordset
• Finding Data in a Recordset
• Adding Data Using a Recordset
• Deleting Data in a Recordset
• Updating Data in a Recordset
• Using Transactions to Commit Groups of Records – or Not

Creating Objects with ADOX

• What is ADOX?
• Creating Tables
• Securing Objects

Performing Advanced Data Operations

• Coding for Concurrency
• Retrieving a User Recordset
• Using Other Schema Recordsets

Additional Details

Pre-Requisites

Access - Level III or equivalent knowledge/experience

Certificates offered

Certificate of completion

Cancellation Policy

10 business days cancellation or full course fee will be charged

Map & Reviews

Technology Training Centre
[ View Provider's Profile ]

Reviews

 

This course has not yet been rated by one of our members.

If you have taken a course through this vendor please log into your account and leave feedback for this vendor. You will be helping ensure our members get directed to the best training facilities.

Here are some reviews of the training vendor.

   

Enjoyed the day

Reviewed by Brian F. 2012

   

Concise, clear and well presented. I would request this facilitator for any future courses based on my confidence is her ability to communicate effectively and based on her knowledge of the materials.

Reviewed by Angelique W. 2012

   

We were hoping there would be more about 'forms'. (Portfolio was interesting, but not really releveant to my needs). - Actually, my boss asked if we talked about Forms Manager, I said no, and then we couldn't decide if Forms Manager is something that was discontinued in X (from 9).

Reviewed by Linda S. 2012

   

Parking facilities at the UofA are incredibly expensive, for those who need to drive there. $40/day for parking was disappointing!

Reviewed by Grace B. 2012

   

Made sure you did everyting twice, was double checked by instructor.

Reviewed by Brenda K. 2012

   

Had never done Excel before, it was all greek to me. Maybe need to repeat work sheets twice. I will come back for repeat class.

Reviewed by Brenda K. 2012

   

Great course

Reviewed by Trevor H. 2011

   

Course instuctor did not hand out the manuals until end of the day. Therefore not able to write notes in for reference later on. - kinda silly.

Reviewed by Mike C. 2011

   

You shoudl have a Excel 3 class available

Reviewed by Brandy Z. 2011

   

Great training, had to meet the various needs from no experience to some with experience. Washrooms looked tired and need of improvement. Personally prefer an early start/early finish 8am to 3pm would be great

Reviewed by David S. 2011

   

Course materials were not handed out till the end of the course and may have been useful to follow along. Some of the example/course files were missing from my folder.

Reviewed by Craig B. 2011

   

The Instructor was very knowledgable on the subject. The class moved quickly. I think an additional day would have been nice.

Reviewed by Cheryl . 2011

   

Trainer was open to answering questions and provide information in a clear manner.

Reviewed by Maureen D. 2011

   

Well presented, hands on course! The instructor was extremely knowledgable and helpful!! I have already reccommended this course to my managers.

Reviewed by Kelly D. 2011

   

The trainer I had was great. She had a great personality that kept me interested. Awesome Job! Thank you

Reviewed by Lindsey G. 2011

   

The course was excellent and the instructor did a great job but why does everybody cut the time short. I think we should take a shorter lunch and get more class time instead of leaving a half of an hour early!

Reviewed by Darlene T. 2011

   

EXCELLENT instructor!!! Very patient ... customized the class to the needs of the students!

Reviewed by Jackie G. 2011

   

The only improvement I can suggest is focusing a bit more on the basics of Visio - more on Org & flow charts as opposed to so much time spent on designing rooms.

Reviewed by Karyn G. 2011

   

George was personable and easy to follow and work with.

Reviewed by Suzanne S. 2011

   

Sue is an awesome instructor!

Reviewed by Cathy P. 2011

   

The instructor was excellent. It was evident that several of us who were competent with Excel 2003 Level 1 should have probably taken Excel 2007 Level 1 again rather than Excel 2007 Level 2, but the instructor was very patient and managed to cover basics from Level I as well as Level 2.

Reviewed by Kim C. 2011

   

With myself being very green with this program I got very useful information that I can use daily.

Reviewed by Rick . 2011

   

One more hour would allow for more hands-on training.

Reviewed by Kerstin M. 2011

   

Good course. Friendly instructor. I found the speed of the course to be a little slow for me, however I understand that other attendees required more help/detail than I did. Overall, a good experience.

Reviewed by Jenna N. 2011