Lessons

Resources

Learn More

Variables

Questions?

Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.

Comments for Access Developer 3

Age	Subject	From
4 months	Developer 3 Lesson 1	Ken Wykoff
4 months	Access Developer 3 Lesson 3	Ken Wykoff
2 years	You Are About to Append	John Jira
3 years	Skills Test Quiz	Mischa Graafmans
3 years	03 For Next Loops 08402147	Stamatios Papoutsis
3 years	ACD00304NonUS Number Format	Daniel de Koning
3 years	Default Commission Rate	Ryan Rillstone
4 years	course list	Audrey Mcpherson
4 years	invalid use of null	Abdulhadi Qasem
5 years	Variable Returns Always 0	Abraham Breuer

Subscribe to Access Developer 3
Get notifications when this page is updated

Intro

In this lesson, you will learn more advanced Microsoft Access VBA techniques, including how to use memory variables, generate random numbers, and create constants. I will show you how to set up a basic math quiz, explain the use of For Next loops, and cover different types of variables such as Single, Long, Integer, and Currency. We will also create a quick add link to insert new sales reps into your database when they do not appear in a combo box, all while building on skills from the previous Developer classes. This is Lesson 3.

Transcript

Welcome to Microsoft Access Developer Level 3, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. This class follows Microsoft Access Developer Level 2. I strongly recommend that you have taken Developer 1 and 2, as well as my beginner and expert series of classes.

The advanced series is recommended, as it is mostly macros, but I do cover a lot of events and material in that series. Even if you are not going to use macros, it is a good prequel to developing with Access VBA. So, I strongly recommend that you take that series as well, but it is not required.

Today's class was recorded with Access 2016. Everything covered should pretty much work in Access 2010 and 2013 as well. In fact, all of the VB code should even work all the way back to Access 2003. VBA really hasn't changed much in the last 14 years or so.

If you are not familiar with my classes, the beginner series covered the foundational material. The fundamentals are important; a lot of times, people skip over the beginner lessons if they have done some development in the past, but I cover things like formatting, form design, and lots of tips and tricks that people should know.

The expert class covers all the nitty-gritty functions and other advanced features, advanced form and report design. The advanced classes cover macros and events. The Developer series, which you are in right now, covers VBA programming and making your databases professional.

If you have been following along since the beginning, you have probably been building your own database. If you want to download my database and work from that, you can find it here at this page. You will need your course password, which was emailed to you when you purchased this class. You will use that password to unlock the databases.

Let's take a quick look at what is covered in today's class.

In lesson 1, we are going to learn about memory variables and go over the homework from the last class.

In lesson 2, we are going to learn about random numbers and constants. We are going to create a little math quiz where you type in the answer from two randomly generated numbers added together.

In lesson 3, we are going to learn about for next loops to create a loop in your code to do a certain task. We are going to learn about running tasks multiple times, and we are going to learn about the different types of variables, like single, long, integer, currency, and so on.

In lesson 4, we are going to make a quick add link to add a sales rep if you are entering in commissions and the sales rep hasn't been added to the database yet, so they do not show up in the combo box. We will make a little link that will allow the database to ask you for the sales rep first name, last name, commission, and so on.

I actually prefer this method. There is something called the on not in list event. That is fine, but I actually like doing it this way better.

Quiz

Q1. Which series is strongly recommended before taking Developer Level 3?
A. Beginner and Expert Series
B. Only the Advanced Series
C. Only the Developer Series
D. No previous series are recommended

Q2. What is the primary focus of the Developer series?
A. Learning Macros only
B. VBA programming and making databases professional
C. Report Design only
D. Data Entry techniques

Q3. What is one benefit of taking the Advanced series, even if you are not using macros?
A. It covers form design only
B. It serves as a good prequel to Access VBA development
C. It teaches web design skills
D. It is required before any Access course

Q4. The VB code taught in this course should work with which versions of Microsoft Access?
A. Only with Access 2016
B. 2010, 2013, and 2016 (and back to 2003 for VBA)
C. Only Access 2007 and earlier
D. Access Online only

Q5. What does Lesson 2 focus on?
A. Report generation
B. Creating a math quiz using random numbers and constants
C. Importing data from Excel
D. Network database sharing

Q6. In Lesson 3, which programming concept will you learn about?
A. Creating tables
B. For Next loops for repeating tasks and different types of variables
C. Designing macros only
D. Adding shortcuts to the ribbon

Q7. What is covered in Lesson 4?
A. Building tables from scratch
B. Quickly adding a missing sales rep through a special link
C. Backing up your database
D. Securing your application

Q8. What alternative does Richard prefer over the "on not in list" event for adding new sales reps?
A. Using append queries
B. Using a dedicated quick add link
C. Importing from Excel
D. Only adding via forms manually

Q9. How can students access the instructor's sample database for the course?
A. It is included on a USB mailed to them
B. They can download it online using their course password
C. It is free for anyone without any password
D. It must be installed from a CD-ROM

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

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 Microsoft Access Developer Level 3. I am your instructor, Richard Rost. This class builds directly on the skills taught in Microsoft Access Developer Levels 1 and 2, and it also assumes that you are familiar with the material from my Beginner and Expert series of classes.

While the Advanced series focuses primarily on macros, I do cover many events and related topics there as well. Even if you don't plan on using macros, I recommend going through the Advanced courses as a helpful way to prepare for Access VBA development, but it isn't strictly required.

The lessons in this class were recorded using Access 2016, but everything I cover should work just as well in Access 2010 and 2013. In fact, almost all of the VBA code will work as far back as Access 2003 since the programming basics in Access have not changed much in the past decade or so.

If you are new to my courses, here is a quick summary of the structure: The Beginner series covers all of the basic, foundational topics. I always recommend not skipping over these lessons, even if you have some prior development experience, because I include important details on formatting, form design, and a variety of tips and tricks that are essential to working efficiently in Access.

The Expert series explores more advanced functions, features, and focuses on complex form and report design. The Advanced classes introduce macros and events, two concepts that are central to workflow automation in Access. And now, the Developer series shifts the focus to VBA programming and making your databases look and function more professionally.

If you have been following along with the complete course progression, you have likely already started building your own database. However, if you want to use my sample database as a reference or follow along with the same database I use in class, you can download it from the course webpage. You will need your course password, which was sent to you by email when you purchased this class, in order to unlock the database files.

Here is what we will cover in today's sessions:

In lesson 1, we start by discussing memory variables. We will also review the homework from the previous class to tie up any loose ends.

In lesson 2, we tackle the subject of random numbers and constants. You will learn how to generate random numbers, and we will put this into practice by creating a simple math quiz. The quiz will require you to enter the sum of two randomly generated numbers.

Moving on to lesson 3, we will explore for next loops, which are essential for running a block of code repeatedly. We are going to discuss how to use loops to simplify repetitive tasks and examine different types of variables such as single, long, integer, currency, and others.

Then, in lesson 4, we will create a quick add link to make it easy to add a new sales rep while you are entering commissions. If the sales rep you need is not already in the combo box list, you will be prompted to enter their first name, last name, commission amount, and so on, directly within this workflow.

Personally, I prefer this method over using the on not in list event, which is another option for handling cases when a record does not exist in your list. I find this approach to be more straightforward and flexible.

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

Memory variables in VBA
Reviewing previous homework
Generating random numbers in VBA
Using constants in VBA
Creating a math quiz with random numbers
Implementing For Next loops
Running tasks multiple times with loops
Understanding variable data types: single, long, integer, currency
Creating a quick add link for new sales reps
Prompting user input to add missing sales reps