Introduction

Welcome! VBA Basics: Write Your First Code

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Developer Level 1. In this course we will begin the Developer Series by introducing Access VBA programming and how it compares to macros. We will discuss building your first VBA program, using the message box command, and understanding the DoCommand OpenForm command. You will learn about creating subroutines with multiple commands, recreating an Add Product button with If Then logic, and working with the After Update event to update shipping addresses when billing information changes. This course assumes you have completed the Beginner and Expert Series, and we will review key concepts as needed.

Navigation

Prev: D01
Next: Lesson 01

Keywords

Access Developer, VBA programming, Visual Basic for Applications, Access macros, OpenForm command, message box, DoCommand, subroutine, After Update event, Add Product button, If Then statement, events, Access 2016, database download, shipping address upda

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Developer, level 1, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

Welcome to the Developer Series of Classes. If you have made it this far, I assume you have been through my beginner classes that covered all the basics of using Access. The Expert Series covered more things like relationships and subforms and all kinds of things that you can use to make your Access database a little more professional looking.

The Advanced Series of Classes talked about macros and events. Now we are finally to the Developer level of classes. The Developer Series is going to focus on VBA programming. That is Visual Basic for Applications. So, all of you who want to learn how to program for Access, this is the series for you.

It is strongly recommended that you have taken the Beginner, Expert, and Advanced Series classes before taking this course. If you have not, you should have at least taken the Beginner and Expert classes.

The Advanced Series went through macro programming, and I understand not a lot of people are going to have to learn how to program macros, and that is perfectly fine. I do talk about some things in there like events such as the after update event and the on-click event. I will be going through them again in this series, so you do not have to worry too much if you did not take the Advanced Series of Classes. But there are only six of them. If you have any interest in macros whatsoever, give them a watch.

If you have done no programming whatsoever, learning macros first can make learning how to program a little bit easier. Check it out if you have any questions. Let me know.

I am using Microsoft Access 2016 to record this class with Office 365. Everything should work in 2013 as well as 2010, even 2007. In fact, if you go all the way back to 2003, I am going to say 99 percent of the commands that I am using, the Visual Basic language commands, are going to be almost exactly the same.

One of the things I have been telling people for years before I came out with the Developer Series is that if you want to learn VBA, you can go back to launch my Access 2003 class. There are a lot of them out there. All of the VBA commands are pretty much the same. The interface is different, obviously. In the newer versions of Access, you will see the ribbon and all that; they have changed the interface. But the Visual Basic language really has not changed that much since 2002, 2007. The core language is the same.

I will be using a database in class that we have been building since the beginning of level 1. I recommend you watch all those classes and build the database yourself. But if you have not done that or you want to freshen up your database and use my copy, that is fine. You can find a copy of the database there on my website, www.fivelandincig.com/databases. You can download a copy there. You will need the password that you got when you purchased this class to download and install the database.

Now I will take a quick look at what is covered in today's class.

In lesson 1, we are going to have a little introduction to Access VBA, what it means, the different kinds of Visual Basics that are available, and the benefits of VBA over macro programming.

In lesson 2, we are going to build our very first VBA program, write our first VBA code, and we are going to have the computer say Hello World with the message box command.

In lesson 3, we are going to learn about the DoCommand OpenForm command.

In this lesson, we are going to take a look at some subroutine with multiple commands and some more advanced VBA programming coming your way.

In lesson 5, we are going to recreate the Add Product button that we created back in our Expert Series. We are going to learn about the If Then statement and more.

In lesson 6, we are going to learn about the After Update event. We are going to make events so that every time I change something like the address or the city or the state in a customer's record, it automatically updates the shipping address if Same as Billing is checked.

Quiz

Q1. What is the main focus of the Developer Series of Classes for Microsoft Access?
A. Learning basic data entry in Access
B. Graphics editing in Access forms
C. VBA programming with Access
D. Creating tables and queries

Q2. Which prerequisite classes are strongly recommended before taking the Developer Series?
A. Only the Beginner Series
B. Only the Expert Series
C. Beginner, Expert, and Advanced Series
D. Only the Advanced Series

Q3. What does VBA stand for?
A. Visual Basic for Automation
B. Visual Basic for Access
C. Visual Basic for Applications
D. Visual Basic for Auditing

Q4. What was the main topic covered in the Advanced Series of Classes?
A. Creating reports and charts
B. Macro programming and events
C. Backup and restoration
D. Data import/export

Q5. Which lesson in this class introduces writing the first VBA program in Access?
A. Lesson 6
B. Lesson 4
C. Lesson 2
D. Lesson 5

Q6. What command is introduced in lesson 3 of this course?
A. DoEvents Command
B. DoCommand OpenForm
C. CloseForm Command
D. PrintReport Command

Q7. According to the video, what is one benefit of learning macros before VBA?
A. Macros can replace all VBA functionality
B. Macros make learning VBA a bit easier if you have done no programming before
C. Macros are required for all VBA code
D. Macros only work in the 2016 version of Access

Q8. If you want to download the class database, what do you need?
A. A Microsoft Account login
B. The password you got when you purchased the class
C. Admin rights on Windows
D. An Office365 subscription

Q9. What does the After Update event do in the example given for lesson 6?
A. Clears the customer address field after every update
B. Automatically updates the shipping address if Same as Billing is checked
C. Prompts the user to save every time an order is submitted
D. Opens a message box every time a customer name is changed

Q10. According to Richard, how much has the Visual Basic language changed since Access 2003?
A. It has changed completely
B. It is incompatible with new versions of Access
C. The core language is almost exactly the same
D. It is no longer supported in Access

Answers: 1-C; 2-C; 3-C; 4-B; 5-C; 6-B; 7-B; 8-B; 9-B; 10-C

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone is the introduction to my Microsoft Access Developer Level 1 course. My name is Richard Rost and I am your instructor for this series.

This Developer Series is designed for those who have already completed my Beginner, Expert, and preferably the Advanced Access classes. In the Beginner tutorials, we worked through all the essentials of using Access. The Expert level focused on intermediate topics such as table relationships, designing subforms, and ways to make your database appear more polished and professional.

In the Advanced Series, I introduced macros and events. Now, at the Developer level, we will focus on VBA programming - that is, Visual Basic for Applications. This is the core programming language used for Microsoft Access. If your goal is to add real programming functionality to your databases, you are in the right place.

I strongly recommend finishing the Beginner and Expert Series first to ensure you have a solid foundation. If you have also watched the Advanced Series, that's even better, especially because it covers events such as After Update and On Click. If you haven't studied macros or those events, that's not a problem. I will be covering them again here, but if you are curious about macros, the Advanced classes only take a few lessons to work through, and they may make it easier to pick up programming concepts if you are completely new to this.

For this course, I am using Microsoft Access 2016 through Office 365, but almost everything I demonstrate will work in Access 2013, 2010, or even as far back as 2007 and 2003. The Visual Basic language itself has not changed much over the years, so commands you learn here are almost identical across these versions. The main differences are found in the user interface, not in the VBA code itself.

Throughout the course, I will be using a database that has been built up during earlier classes, starting at level 1. If you have followed along previously, I recommend you continue building your own version. However, if you would like a fresh copy or prefer to use my example, you can download it from my website, www.fivelandincig.com/databases. You will need the password provided with your course registration to access these files.

Let me quickly outline what you will learn in today's class. In the first lesson, we start with an overview of Access VBA, including what it is and how it relates to other versions of Visual Basic. We will compare VBA to macro programming and discuss its advantages.

In the second lesson, we will create our very first VBA program and write our initial code. I will show you how to have Access display a simple "Hello World" message using VBA.

In the third lesson, we will go over the DoCommand OpenForm method to demonstrate how to control form behavior with VBA.

Lesson four will involve creating a subroutine that contains multiple commands, introducing you to more advanced programming concepts in VBA.

In lesson five, we will rebuild the Add Product button featured in the Expert Series, taking a closer look at control structures like the If Then statement.

Finally, lesson six will focus on the After Update event. I will explain how to automatically update related fields, such as ensuring that changing the address, city, or state in a customer's record will immediately update the shipping address if the Same as Billing option is selected.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Introduction to Access VBA programming
Differences between VBA and other Visual Basics
Benefits of VBA over macro programming
Writing your first VBA program in Access
Using MsgBox to display "Hello World"
DoCommand OpenForm command usage
Creating subroutines with multiple VBA commands
Recreating the Add Product button using VBA
Using the If Then statement in VBA
Working with the After Update event
Automatically updating shipping address based on billing info

Article

Welcome to this introductory tutorial on Microsoft Access Developer, Level 1. This guide is designed for those ready to take the next step beyond the beginner, expert, and advanced levels of Microsoft Access. By now, you should be comfortable with the basics of using Access, including creating tables, forms, queries, and reports. You should also understand how to set up relationships and subforms and how to make your database look more professional and function efficiently. If you have some experience with macros and how different events work in Access, you are in a good position to start learning VBA, or Visual Basic for Applications.

VBA is a powerful programming language that is built into Microsoft applications like Access. It allows you to create custom commands and automate complex tasks that go far beyond what macros can do. Even if you have never done any programming before, this guide is for you. Learning VBA might seem overwhelming at first, but as you go through practical examples, you will see how it adds value and flexibility to your projects. If you already have experience with macros, you will find it easier to transition to VBA. Macros are a simple way to automate basic tasks, but they have their limits. With VBA, you have much finer control over how your database behaves.

Throughout this guide, I will be using Microsoft Access 2016 with Office 365. If you are using Access 2013, 2010, or even 2007, almost all of the VBA code you learn here will work the same way. The Access interface has evolved over the years, especially with features like the Ribbon, but the core VBA language has remained mostly unchanged since the early 2000s. You can use the concepts and commands from this tutorial even if you are working with older versions like Access 2003, though the screens might look a little different.

You will get the most out of this tutorial if you build your own database as you follow along. If you prefer, you can download a starter copy of the database from www.fivelandincig.com/databases. Just make sure you have the required password if you purchased a course that includes it. Working with your own or our sample database will help you learn by doing, which is often the best way to understand these concepts.

Let us start by discussing what VBA actually is. Visual Basic for Applications, or VBA, is a programming language that lets you automate tasks and extend the functionality of Microsoft Access. VBA is just one version of Visual Basic, which has several variants. Visual Basic (VB) itself is a full-fledged programming language, but VBA is tailored for use inside applications like Access, Excel, and Word.

VBA is more powerful than macro programming. While macros let you perform simple tasks automatically, like opening forms or running queries, VBA lets you set conditions, respond to user actions, and even process data using complex logic. If you want your database to do something custom that you cannot accomplish with a macro, VBA is your solution.

Let us write our first VBA program. This is often known as the Hello World program since the goal is simply to make your computer display the words Hello World. In Access, you will need to open the VBA editor, which you can get to by pressing Alt+F11 or by clicking the Visual Basic button on the ribbon. In the VBA editor, you can create a new module and type in your first subroutine. Here is what that code looks like:

Sub HelloWorld()
MsgBox "Hello World"
End Sub

What is happening in this code? The Sub keyword starts your subroutine, and HelloWorld is the name you give to your program. Any code you want your database to run goes between Sub and End Sub. Inside, MsgBox "Hello World" pops up a message box that says Hello World. You can run this subroutine from the VBA editor, or you might assign it to a button on a form, so when a user clicks that button, they see your message.

Now let us move on to another common task in Access programming: opening a form from VBA code. In macros, you might have used the OpenForm action. In VBA, you use the DoCmd object to execute commands that mimic what you do in Access. For example, if you want to open a form called CustomerF, your code would look like this:

DoCmd.OpenForm "CustomerF"

You can place this line inside a subroutine, just like Hello World, and connect it to a control like a button in your form. Every time users click that button, your custom code will open the form you specify.

VBA lets you create subroutines that can handle several commands at once. Suppose you want to open a form, filter it for a certain customer, and display a message when done. You could write:

Sub OpenCustomer()
DoCmd.OpenForm "CustomerF", , , "CustomerID=5"
MsgBox "Customer form opened."
End Sub

This subroutine opens the CustomerF form and applies a filter so it only shows the record where CustomerID equals five. Then, it shows a message box to let the user know the form has opened. You can add as many commands as you want to a subroutine, which allows for much more complex actions than macros alone.

One of the most powerful features in VBA is the ability to make decisions in your code. Say you want to add a product only if a certain condition is met. You would use an If Then statement to control what happens. Here is a basic example:

Sub AddProduct()
If IsNull(Me.ProductName) Then
MsgBox "Please enter a product name."
Else
' Code to add the product goes here
MsgBox "Product added successfully."
End If
End Sub

In this code, Access checks if the ProductName field is empty. If it is, a message asks the user to enter a product name. Otherwise, you would place your code to add the product, followed by a success message. Using If Then statements helps you control the flow of your program, adding logic and preventing errors.

Events are another core concept that make VBA so useful in Access. An event is something that happens in your database, like a user clicking a button or updating a field. In VBA, you can write code that runs automatically when these events occur. For example, suppose you want to update a customer's shipping address automatically if their billing address changes and the Same as Billing box is checked. You could use the After Update event of the address field to write code like this:

Private Sub BillingAddress_AfterUpdate()
If Me.chkSameAsBilling = True Then
Me.ShippingAddress = Me.BillingAddress
End If
End Sub

This subroutine runs every time the billing address changes. It checks if the Same as Billing checkbox is checked. If it is, it copies the updated billing address into the shipping address field. Doing this with VBA ensures that your data stays consistent without needing the user to manually copy information.

Learning to use VBA in Microsoft Access will allow you to build databases that are not only more powerful and flexible but also easier to use. By following along, writing out these examples, and gradually experimenting with more complex code, you will discover how much more you can accomplish in Access with just a little programming knowledge. If you have questions as you go, explore the Access documentation or reach out to the Access community online. With practice, writing VBA code will quickly become second nature.