Welcome

Welcome! Memory Vars, Random, Const, Loops

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

Welcome to Microsoft Access Developer Level 3. In this course we will continue building on the concepts from Developer Levels 1 and 2, as well as the beginner, expert, and advanced series. We will cover topics such as memory variables, random numbers, constants, for next loops, and different variable types in VBA programming. I will also discuss working with add links for data entry, specifically how to add new sales reps when they are not yet in the combo box. This course is recorded using Access 2016, but most content is applicable to earlier versions as well.

Navigation

Prev: D03
Next: Lesson 01

Keywords

Access Developer, VBA programming, for next loop, memory variables, random numbers, math quiz, constants, variable types, add sales rep, combo box, currency variable, int variable, long variable, events, macros, on not in list event, form design, report d

Subscribe to Welcome
Get notifications when this page is updated

Intro

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 - it is mostly macros, but I do cover a lot of events and stuff in that series. Even if you are not going to use macros, it is a good prequel to develop 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 today should pretty much work in 2010 and 2013 as well. In fact, all of the VB code should even work all the way back to Access 2003. VBA really has not changed much in the last 14 years or so.

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

The expert class can tend all the nitty-gritty functions and all those good things, advanced form and report design. The advanced classes cover macros and events, and the developer series, which is what you are in right now, covers VBA programming and making your databases professional.

Now, if you have been following along since the beginning, you have probably been building your own database, but 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, and you will use that password to unlock the databases file.

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

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

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

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

In lesson four, we are going to make a quick add link to add a sales rep. If you are entering in commissions and the sales rep has not been added to the database yet, so they do not show up in the combo box, we will make a little link and an add link that will allow us - the database will 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 okay, but I actually like doing it this way better.

Quiz

Q1. What is strongly recommended before taking Microsoft Access Developer Level 3?
A. Completing Developer 1 and 2, beginner, expert, and ideally advanced series
B. Only having basic experience with Microsoft Access
C. Taking just the beginner series of classes
D. Skipping all prior lessons and starting directly with Level 3

Q2. Which Microsoft Access versions are specifically mentioned as compatible with the content covered in this class?
A. 2016, 2010, 2013, and 2003
B. Only 2016 and 2019
C. 2019, 365, and later only
D. Only Access 2003

Q3. What main topics are covered in Lesson 1 of this course?
A. Memory variables and previous class homework review
B. Creating reports and mail merges
C. Database security
D. Installation of Microsoft Access

Q4. What do you learn to create in Lesson 2?
A. A math quiz using random numbers and constants
B. A customer contact form
C. An expense report
D. A table relationship diagram

Q5. Which control structure is the primary focus of Lesson 3?
A. For Next loops
B. If Then Else statements
C. Do While loops
D. Select Case statements

Q6. What different types of variables are discussed in Lesson 3?
A. Single, Long, Int, Currency, and others
B. Boolean, Object, and String only
C. Date and Time only
D. Text and Hyperlink only

Q7. What task will you implement in Lesson 4 regarding sales reps?
A. Creating a quick add link to add a new sales rep to the database if not found in the combo box
B. Performing complex queries for sales analytics
C. Exporting sales rep data to Excel
D. Deleting sales reps from the database automatically

Q8. What alternative to the On Not In List event does the instructor prefer when adding new records from a combo box?
A. Using a custom add link that asks for required information directly
B. Only using default error handling
C. Never allowing users to add new records from forms
D. Using macros to automate the process

Q9. Where can students find the sample database for this course?
A. On the course page, using a password emailed with the class purchase
B. By downloading it from Microsofts official site without any credentials
C. On a public file-sharing platform
D. Included for free without login on the AccessLearningZone.com homepage

Q10. According to the video, why might the advanced series, which focuses on macros, be helpful even for students planning to use only VBA?
A. It covers events and concepts useful for VBA developers
B. It is required for certification
C. It only teaches non-VBA topics
D. It is necessary for form design fundamentals

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

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'm Richard Rost, and I'll be your instructor for this class.

This course builds right on top of Access Developer Level 2. I highly recommend you complete Developer Levels 1 and 2 before moving on to this class. Additionally, you should have already completed my beginner and expert series of Access classes to ensure you have a solid foundation. While the advanced classes are mostly focused on macros, I cover a number of events and related skills that are very useful when transitioning to working with VBA. Even if you do not plan on using macros frequently, those advanced lessons make a good stepping stone to the developer material we cover here, so I do suggest going through them, but they are not absolutely required.

For this class, I used Access 2016 in the demonstrations. Pretty much everything we do today will work in Access 2010 and 2013 as well. In fact, all the VBA code should run just fine way back to Access 2003. VBA has really not changed too much in about 14 years, so you should not run into trouble with older versions.

If you are new to my teaching series, I want to mention how each level fits together. The beginner series takes a slow and thorough approach to foundational skills. A lot of times people with some development experience will skip the beginner classes, but I always remind everyone I cover important elements like formatting, good form design, and all kinds of tips and tricks that are useful even for advanced users.

The expert courses dive much deeper into functions and a lot of features that make Access powerful - things like advanced form and report design. The advanced classes are focused on macros and events. Now, the Developer series, which is where you are now, brings all of this together and focuses on VBA programming, helping you take your databases to a more professional level.

If you have been following along through my courses, you have probably been building your own database by now. However, if you would prefer to download my sample database to work with, you can do that from my website. You will need the course password that was sent to you when you purchased this class. That password will unlock the database file for you.

Here is a quick overview of what I'll cover in today's class.

In lesson one, we start by talking about memory variables and we'll also review the homework from the previous class.

Lesson two is focused on random numbers and constants. We'll build a simple math quiz where you enter the answer to a problem made up of two randomly generated numbers added together.

In lesson three, I'll teach you how to use For...Next loops, so your code can perform a specific action multiple times as needed. We will also go over some of the different types of variables you'll be using, such as Single, Long, Integer, Currency, and more.

In lesson four, we'll create a quick add link to input a new sales rep. This is helpful if you are in the middle of entering commissions and notice the sales rep you're trying to assign the payment to is not in the drop-down box yet. With this feature, the database will prompt you for their first name, last name, commission, and other relevant fields.

Personally, I find this approach to adding new records preferable to using the On Not In List event. While On Not In List is okay, I think creating a direct link for adding data is more efficient and user-friendly.

You can find a complete video tutorial with step-by-step instructions on everything I 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

For next loops in VBA

Using different variable types (Single, Long, Int, Currency)

Quick add link to add sales reps

Creating prompts to enter new sales rep information