Introduction

Welcome! Macros to VBA, Timers, Combos

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

Welcome to Microsoft Access Developer Level 6. In this course we will focus on several important topics including converting macros to VBA, creating an enhanced timer form for scheduled events, building cascading combo boxes to filter data between fields, and setting up automated backups of your database and individual objects. I will explain when to use Access's built-in macro conversion tool and when to write VBA manually, show methods for interval-based automation, and discuss best practices for safeguarding your database. This course builds on Access Developer Level 5, so previous knowledge of earlier levels is recommended.

Navigation

Prev: D06
Next: Lesson 01

Keywords

Access Developer, convert macros to VBA, timer form, enhanced timer, cascading combo boxes, backup database, automated backup, backup tables, filter combo box, interval events, pause timer, backup file, object backup, event programming, Visual Basic code

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

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

Today's class is going to be a mixture of a bunch of different topics, stuff that people have been emailing me over the past couple of months that they have wanted to see.

First, we are going to start by converting macros to VBA. We built a lot of macros in our database in the Advanced Series, and now we are going to go through and I am going to show you how to manually convert those over to VBA.

Yes, there is an option to automatically have Access convert them for you, but it is not perfect. It does not work all the time. I want to teach you how to write the VBA for some of the stuff we have done before. It is an important learning tool rather than just converting the macro with an automated function. So that is going to be the first lesson.

Then we are going to work on an enhanced timer form. Lots of people ask me how to get the database to do things on regular intervals - every 10 seconds, or a backup every 20 minutes, or something along those lines. So we are going to build an enhanced timer form, where I am going to show you how to build a form that has a clock timer on it with one second ticks.

Another fan favorite is cascading combo boxes, where you have one combo box that filters another one. For example, you pick a state and it shows you just a list of cities from that state. That is one example.

Finally, we are going to build another form to do automated backups of our database. I am going to show you how to back up both the individual objects in the database. So if you want to back up just specific tables to a backup file, you can, or you can do an automated backup of the entire database.

This class follows Access Developer Level 5. If you have not taken any of my classes before, the Beginner Series covers the basics. The Expert Series goes into relationships and more advanced design. The Advanced lessons cover mostly events and macro programming. Now we are in the Developer Series. Every series is different levels. This is Developer Level 6. So again, I recommend you watch Levels 1 through 5 before starting this class.

If you want to download the database for this lesson, you can grab it here at this page, findthatncv.com/database, as you will need your password that you got when you purchased this class.

I will be using Microsoft Access 2016 in this lesson, although everything that I cover today should work all the way back to 2007. In fact, I am thinking about it, and most of it, except for the converting macro part, should work in this class. The converting macro part had some macro options that did not work in the earlier versions, but most everything else should work.

Now let's take a more in-depth look at exactly what we are covering in today's lessons.

In Lesson 1, we are going to learn how to convert macros to Visual Basic code. We started building macros back in our Advanced Series, and today we are going to look at some of the key macros that we built then, and show how we can convert those over to Visual Basic code.

We built a very basic timer event in one of the Access Expert classes where I showed you how to download data from a web page for weather information. In this class, I am going to show you how to add a lot more functionality and build your own timer form where you can specify particular intervals of x number of seconds to have different events run and so on. You can pause the timer and do all kinds of different things with it.

In Lesson 3, we are going to be talking about cascading combo boxes. I have covered this in some of my more advanced seminars before, but not all of you have taken all of my seminars. I have covered this in my SQL Part 2 seminar, but that is a big, long, draining seminar. I get asked this question all the time, so we are going to do a real quick 10 minute lesson on cascading combo boxes.

Cascading combo boxes is where you have two combo boxes or more. We are going to do two, where the first one is a category, for example. When you pick a category, it filters the product list to show you just products from that category.

Lesson 4 is another fan favorite. Everyone always asks me how can I automatically back up my database. Again, you should have separate database backup software to back up not only your database, but your entire hard drive, whether it is simply a Google Drive backup or one of the services you can pay for. I strongly suggest that, but I am going to show you in this lesson two different methods for backing up stuff in your database and the database itself from within Access. So you do not have to rely on third party software if you do not want to, but this gives you the option to back up critical tables or back up the entire database either on a timer or manually by clicking on a button.

Thank you.

Quiz

Q1. What is the main goal of the first lesson in this class?
A. Manually converting macros to VBA code
B. Creating a backup form
C. Designing relationships between tables
D. Setting up user-level security

Q2. Why does Richard recommend learning to manually convert macros to VBA rather than using Access's automatic conversion?
A. The automatic converter does not always work perfectly
B. Automatic conversion is faster but less flexible
C. The manual method is required for all Access versions
D. Automatic conversion is not available in Access 2016

Q3. What is the purpose of the enhanced timer form covered in this class?
A. To allow database events to automatically run at specified intervals
B. To display the current system time to users
C. To automatically log users in and out
D. To synchronize multiple databases

Q4. Which of the following is a use case for cascading combo boxes?
A. Filtering a list of cities based on the selected state
B. Automatically increasing the value of a numeric field
C. Backing up specific database tables
D. Importing data from Excel

Q5. In the context of backing up your database, what does Richard recommend?
A. Use third-party backup software, but also learn to back up from within Access
B. Only use the Access built-in backup feature
C. Back up only forms and reports
D. You do not need to back up your Access database

Q6. What is a cascading combo box?
A. A combo box whose available choices depend on the selection in another combo box
B. A combo box that automatically refreshes its data every minute
C. A combo box for entering date and time values
D. A combo box that stores multiple values separated by commas

Q7. What versions of Access are most lessons in this class compatible with?
A. Access 2007 and newer
B. Access 97 and newer
C. Only Access 2016
D. Only Access 2013 and 2016

Q8. Which lesson will give a quick, focused coverage of cascading combo boxes?
A. Lesson 3
B. Lesson 1
C. Lesson 2
D. Lesson 4

Q9. What is included as a resource for students taking this class?
A. A downloadable database file
B. A printed textbook mailed to students
C. One-on-one online tutoring sessions
D. Access installation media

Q10. What is the relationship between the Developer Series and earlier series Richard mentions?
A. Developer follows Advanced, which follows Expert, which follows Beginner
B. Advanced follows Developer, which follows Expert, which follows Beginner
C. Expert follows Developer, which follows Advanced, which follows Beginner
D. Beginner follows Advanced, which follows Developer, which follows Expert

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 6. I'm Richard Rost, your instructor, and in this class I'll be covering a variety of advanced topics that many students have been asking about in recent months.

We'll begin by looking at how to convert macros to VBA code. Previously, in the Advanced Series, we built a lot of macros for our database. Now, it's time to see how to manually translate those macros into VBA. While Access offers an automatic conversion feature, it isn't always reliable or perfect. I believe it's important to learn how to write the VBA code yourself, so I'll walk you through that process step by step using examples from earlier lessons.

Next, I'll demonstrate how to set up an enhanced timer form. Many people want to know how to automate database actions at specific intervals, such as performing a task every 10 seconds or running backups every 20 minutes. We'll build a form with a timer that ticks every second, allowing you to schedule all kinds of actions and even pause or modify the timer as needed.

One of the most popular topics is cascading combo boxes. This is when the selection in one combo box influences the options in another, such as picking a state and only showing cities from that state. I'll show you how to set up a pair of combo boxes where the first filters the second to display relevant records, like filtering products by category.

Finally, we'll build a form for automated database backups. I'll explain two methods: backing up specific database objects (such as certain tables) and backing up the entire database. You'll see how to make these backups either automatically on a schedule or manually with the click of a button.

This course is designed to follow Access Developer Level 5. If you're new to my classes, I recommend starting with my Beginner Series for the basics. The Expert Series goes deeper into relationships and more advanced design, and the Advanced lessons focus mostly on events and macro programming. Now, in the Developer Series, each level builds on the last. This is Developer Level 6, so it's best to watch Levels 1 through 5 first if you haven't already.

You can download the database used in the lessons from findthatncv.com/database. Remember, you'll need the password you received when you purchased the class.

I'll be using Microsoft Access 2016 during the lessons, but everything I'll cover should work in versions as far back as Access 2007. The only exception is the macro conversion, as some macro functions weren't available in earlier versions.

Here is a breakdown of the main topics for today's class:

Lesson 1 covers converting macros to Visual Basic code. We'll revisit key macros from earlier lessons and work through how to rewrite them in VBA.

Lesson 2 builds on a basic timer event we set up in previous classes, such as when we downloaded weather data from a web page. Now, I'll show you how to expand on this, creating a timer form that allows you to set specific intervals, run different events, and even pause the timer if needed.

Lesson 3 is all about cascading combo boxes. I've demonstrated this in some of my more in-depth seminars, like the SQL Part 2 seminar, but I know not everyone has taken those. Since this is always a highly requested topic, I'll give you a concise lesson dedicated to building cascading combo boxes.

Lesson 4 addresses automated database backups. While I always recommend maintaining thorough backups for your entire database and hard drive using external software or cloud services, I'll show you two ways to back up either specific tables or the entire database from within Access itself. You can set these backups to run on a timer or launch them manually.

If you need a complete video tutorial with step-by-step instructions on everything I talked about here, you'll find it on my website at the link below.

Live long and prosper, my friends.

Topic List

Converting macros to VBA manually
Limitations of Access auto-convert macro tool
Writing VBA to replace common macros
Building an enhanced timer form
Creating timer events to run at custom intervals
Pausing and resuming timer events
Building cascading combo boxes
Filtering combo box data based on another combo box
Creating category and product selector using cascading combos
Automating database backups with a form
Backing up specific tables to separate files
Backing up the entire database with VBA
Scheduling automated backups at regular intervals

Article

Welcome to this tutorial on advanced Microsoft Access development techniques. Today, I will guide you through a variety of topics that have been frequently requested by Access users, including manually converting macros to VBA, creating an enhanced timer form, working with cascading combo boxes, and implementing automated database backups. This article is designed for those who already have a solid foundation in Access and are ready to expand their skills with practical examples and code you can use in your own projects.

Let's begin by looking at how to convert macros to VBA code. While Access includes a built-in feature to automatically convert macros to VBA, this tool does not always produce the best results and can sometimes miss important details. Knowing how to translate macros by hand is an excellent skill for a developer, as it helps you understand both the underlying logic and the power of writing VBA code directly.

Imagine you have a macro that opens a form and then displays a message box. In VBA, you would convert this action into code as follows. Go to the form's event property, such as OnClick for a button, and enter the following code:

Private Sub cmdOpenForm_Click()
DoCmd.OpenForm "YourFormName"
MsgBox "Form opened successfully."
End Sub

This is a basic example, but the process is similar for more complex macros. Identify each macro action, find its VBA equivalent (often starting with DoCmd.), and then write the code. This approach gives you more flexibility and control than macros, allowing you to add error handling, user prompts, and other advanced features that macros can't easily provide.

Next, let's enhance your database with a timer form. Many Access users ask how to get their databases to perform tasks at regular intervals, like refreshing data every 10 seconds or backing up data every 20 minutes. To achieve this, you need a form with a timer event. Start by creating a new blank form in Design View. In the form's property sheet, set the Timer Interval property to the number of milliseconds between timer events. For example, to run code every second, enter 1000. Then, go to the On Timer event and enter your VBA code. Here is a simple example that updates a label with the current time every second:

Private Sub Form_Timer()
Me.lblClock.Caption = Format(Now(), "hh:nn:ss")
End Sub

With this setup, every second the timer event fires and updates the label. You can easily expand this to run any procedure you need, such as triggering backups, running queries, or checking for updates.

Now let's discuss cascading combo boxes, a feature often used when you want one combo box to filter the options in a second one. For example, when selecting a state, you want a second combo box to display only the cities in that state. To create this, add two combo boxes to your form. The first combo box, cboState, should have a Row Source built from your States table. The second combo box, cboCity, needs its Row Source set dynamically based on the selected state. In the After Update event for the state combo box, add the following code:

Private Sub cboState_AfterUpdate()
Me.cboCity.RowSource = "SELECT CityName FROM Cities WHERE StateID = " & Me.cboState.Value
Me.cboCity.Requery
End Sub

Now, when you choose a state, the cities combo box updates instantly to show only the relevant cities. This technique can be adapted to multiple levels or other types of related data.

Finally, let's cover automated database backups within Access. While it is always a good idea to have regular backups of your entire system using third-party software or cloud services, it can be very useful to create a feature that lets you back up your Access database or specific tables directly from within Access. This can be done either manually with a button or automatically via the timer form described earlier.

To back up the entire database file, you can use VBA to copy the file to a backup location. For example:

FileCopy CurrentProject.FullName, "C:\Backups\MyDatabaseBackup.accdb"

If you only want to export specific tables, you can use the DoCmd.TransferDatabase method to copy tables to another Access file:

DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\Backups\MyBackupFile.accdb", acTable, "TableName", "TableName"

You can place these lines of code behind a button, or call them from the timer form's timer event to run backups automatically at intervals you choose.

Combining these techniques allows you to build robust, user-friendly Access solutions that go well beyond the basics. Converting macros to VBA puts the power of programming into your hands and enables you to automate recurring tasks and create custom functionality. Timer forms open a world of automation opportunities, while cascading combo boxes make your forms more interactive and intuitive. Built-in backup features can protect your data and give your users peace of mind.

With practice, these advanced techniques become part of your toolkit, helping you design better, smarter databases for any project. If you would like to follow along with the examples, be sure to have your own test database ready. Make modifications to fit your specific data and workflow. Remember, always keep backups and never test new code on your live database. Happy coding!