Introduction

Welcome! Macros, Events & Automation Tips

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

Welcome to Microsoft Access Advanced Level 1. In this course we will review the prerequisites for the advanced series, discuss the transition from beginner and expert levels, and outline what will be covered in each lesson. We will talk about working with macros and events, discuss the benefits and limitations of macros, and look at their role in the PC resale customer database. Future lessons will include creating both standalone and embedded macros, making macros more dynamic using if-then-else logic, and exploring different event types that occur when working with forms in Microsoft Access.

Navigation

Prev: A01
Next: Lesson 01

Keywords

Access Advanced, macros, events, form design, macro benefits, macro limitations, embedded macro, standalone macro, open specific records, if then else, form button, advanced, developer series, VBA programming, relationships, PC resale customer database

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Advanced Level 1. Brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

Welcome to the Advanced Series of Lessons. This is the first lesson in the Advanced Series. If you have never taken any of my classes before, there are nine beginner lessons. There are 32, I believe, expert lessons.

This is the first advanced lesson, where we are going to talk about macros and events and things like that. The series after this is the developer series, where we are going to talk about Visual Basic programming.

The Advanced Series will focus on things that are a little more difficult than just building a database, but do not quite involve programming yet. We are going to get into macros, events, and a whole lot of other stuff.

As far as prerequisites go, I strongly recommend that you take my beginner series and my expert classes levels 1 through 32 before taking this course. I assume you know certain things at the advanced class level. I assume you know most of the stuff that we covered in the expert series. If you do not know basic form design or relationships, then definitely watch the expert classes first.

This class was recorded using Microsoft Access 2016, Office 365. Everything covered should work from 2007 until 2013. If you are using anything before 2007, then this class will not work for you because Microsoft dramatically changed the way that macros work in Access 2007.

Today's class is all about macros. Let's take a look at what is covered in each lesson.

In lesson 1, we are going to learn what macros are, the benefits of macros, the limitations of macros, and we will review some of the macros we have already created in our PC resale customer database.

In lesson 2, we are going to build some macros. We are going to build a standalone macro and a macro that is embedded in a form button.

In lesson 3, we are going to learn how to make our macros smart. We will learn how to open specific records from our macros, and we will learn about the if, then, else statement.

In lesson 4, we will learn about a bunch of different events that occur when you open and close forms and switch between them.

Thanks for watching.

Quiz

Q1. What is the main focus of the Microsoft Access Advanced Level 1 course?
A. Building basic tables and queries
B. Learning Visual Basic programming
C. Working with macros and events
D. Website design integration

Q2. What should students ideally complete before taking this advanced course?
A. Only the beginner lessons
B. Only the developer series
C. The beginner and expert lessons (levels 1-32)
D. No prerequisites are needed

Q3. Which version of Microsoft Access introduced major changes to the way macros work, making the course incompatible with older versions?
A. Access 2010
B. Access 2013
C. Access 2007
D. Access 2003

Q4. What is the difference between the Advanced Series and the Developer Series discussed in the video?
A. Advanced covers table design, Developer covers macros
B. Advanced covers basic database concepts, Developer covers relationships
C. Advanced covers macros and events, Developer covers Visual Basic programming
D. Advanced covers reports, Developer covers queries

Q5. In which lesson will students learn about the if, then, else statement within macros?
A. Lesson 1
B. Lesson 2
C. Lesson 3
D. Lesson 4

Q6. What is one of the benefits of macros mentioned in the overview for lesson 1?
A. They require extensive programming knowledge
B. They make databases more dynamic without complex code
C. They are only useful for form design
D. They are exclusive to Access 2016

Q7. Which topic is NOT specifically listed as part of the Advanced Series focus?
A. Macros
B. Events
C. Basic table creation
D. Opening and closing forms

Q8. What will lesson 2 primarily teach students?
A. How to create relationships between tables
B. How to build both standalone and embedded macros
C. How to design reports
D. How to write Visual Basic code

Answers: 1-C; 2-C; 3-C; 4-C; 5-C; 6-B; 7-C; 8-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 marks the beginning of the Microsoft Access Advanced Level 1 course. I'm Richard Rost, and I'll be guiding you through this next stage in your Access education.

This Advanced Series is designed for those who have already completed the nine beginner lessons and the full set of 32 expert lessons I offer. In these advanced classes, we cover topics that are more challenging than simply building databases, but we have not reached the level of full-scale programming yet. If you are not familiar with core Access concepts like form design or database relationships, I strongly encourage you to finish the expert series first, as I will assume you already know that material.

In the Advanced Series, you can expect to work with macros, events, and other features that give your databases more power and flexibility. Following this series, the Developer Series will get into Visual Basic programming for those looking to push their skills even further.

For this class, I used Microsoft Access 2016 included with Office 365. The information I cover here should be applicable if you are using Access 2007, 2010, or 2013 as well. However, if you are working with a version of Access earlier than 2007, this class will not apply to you because Microsoft made significant changes to the way macros functioned in Access 2007.

The main focus of today's class is macros. I will explain what macros are, why you might want to use them, and some of their limitations. We'll also review a few macros we've already set up in the PC resale customer database.

Here is how the lessons break down:

Lesson 1 introduces macros, their benefits, limitations, and a recap of previously created macros.
Lesson 2 focuses on building macros. We will work on creating a standalone macro and one that's embedded inside a form button.
In lesson 3, I show you how to make macros more intelligent by targeting specific records and using conditional logic with if, then, else statements.
Lesson 4 covers various events that take place when you open or close forms or navigate between them.

Thanks for joining the class. To get step-by-step instructions and watch the complete video tutorial covering everything discussed here, visit my website at the link below.

Live long and prosper, my friends.

Topic List

What macros are in Microsoft Access
Benefits of using macros
Limitations of macros
Reviewing existing macros in the database
Building a standalone macro
Creating a macro embedded in a form button
Using macros to open specific records
Using if then else statements in macros
Form and control events (open, close, switch)

Article

Welcome to Microsoft Access Advanced Level 1. In this tutorial, we will explore the world of macros in Microsoft Access and begin moving beyond basic database design. If you are comfortable designing tables, relationships, and simple forms or reports, and you have already spent some time with the more complex features of Access, you are in the right place. This lesson is intended for users who already know the basics and are ready to work at an advanced level, but we will stay just short of programming in Visual Basic for now. If you are not yet familiar with form design or creating relationships between tables, I recommend brushing up on those topics first.

Microsoft Access has a powerful set of features for automating tasks, and macros are one of the primary ways to do that. A macro in Access is a set of actions that you can use to automate repetitive tasks or add functionality to your forms, reports, or controls. For instance, you might want to use a macro to open another form when a button is clicked, to display a message, or to perform a series of steps automatically when data is entered or changed.

There are two main types of macros in Access: standalone macros and embedded macros. A standalone macro is created and saved in the Navigation Pane and can be run from anywhere in your database. An embedded macro is attached to a particular event for a form or control, such as the On Click event for a button. For example, if you have a button labeled Open Orders on your customer form, you can embed a macro in that button so that when it is clicked, it opens the Orders form filtered for the current customer.

Using macros has several benefits. First, they allow you to automate routine tasks, which saves time and reduces the chance of error. Second, they give your database a more professional polish by providing a better user experience. For example, you can use a macro to check if a required field is blank before allowing a user to continue, displaying a helpful message if necessary.

However, macros do have some limitations. While you can accomplish a wide range of tasks with macros, there are certain things that macros cannot do or cannot do as flexibly as VBA (Visual Basic for Applications). For advanced logic and more complex processes, VBA will be necessary. Macros are an excellent way to learn how automation works before stepping up to programming.

It is important to mention that Microsoft made significant changes to macros starting in Access 2007. If you are using Access 2007 or later, including Access 2016, Access 2019, or any version of Office 365, everything we cover in this lesson will work for you. Prior versions, such as Access 2003 or earlier, use different macro structures, so this tutorial will not apply to them.

Let us begin by looking at what you will learn in the lessons ahead. First, we will define what macros are in practical terms, covering their main features, benefits, and limitations. We will see examples of simple macros that might exist in a typical database, such as a customer management system for a PC resale business.

Next, I will guide you through building a basic standalone macro. For example, you might create a macro that opens a form or runs a report. Then, we move into embedding macros in form buttons. Suppose you want a button labeled Add New Record to take the user to a blank record on a form: you can do this with an embedded macro by handling the button's On Click event.

We will then cover how to make macros smarter by using arguments and conditional logic. For instance, you might want a macro that opens a form to a specific record. You can accomplish this by telling the macro which record to open, often using criteria from the current form. You will also learn about the If...Then...Else structure within macros, which allows you to perform actions only if certain conditions are true. For example, you might display a message to the user only if a certain value is entered in a field.

In our final major topic, we will discuss events and how they relate to macros. Events in Access are things that happen in your database, such as opening a form, closing a form, or changing the contents of a field. Understanding how to use macros in response to these events will allow you to make your database more interactive and responsive. As an example, you might want to display a welcome message when a user opens a form, or check that all required fields are filled in when the form is closed.

To illustrate a simple macro, consider this scenario: you are working in a customer management form, and you want to add a button that opens the orders form filtered for the current customer. You would set up the button and attach an embedded macro to its On Click event. In the macro, you would use the OpenForm action and set the criteria to filter by the customer ID from the current form.

Here is a sample of what the macro actions might look like in Access's macro designer:

Action: OpenForm
Form Name: Orders
Where Condition: [CustomerID] = Forms![Customers]![CustomerID]

This tells Access to open the Orders form and display only the records where the customer ID matches that of the customer currently displayed on the Customers form.

A key feature you will use often is the If...Then...Else statement within macros. Here is a plain-text example:

If [Forms]![Customers]![City] = "" Then
MessageBox: "Please enter a city."
Else
CloseForm

This logic checks if the City field is blank. If it is, a message appears asking the user to enter a city. Otherwise, the macro continues and closes the form.

Throughout these lessons, you will learn how to chain multiple actions together, pass values between forms, and use events to trigger the right macros at the right times. By the end of this tutorial, you will have a solid foundation for using macros in Access, and you will be ready to explore even more advanced customization with VBA programming in the future.

Thank you for learning with me. In the next lessons, we will build and customize these macros together so you can see exactly how they work in practice. If you are ready to make your Access databases more powerful and user-friendly, let us get started.