Introduction

Welcome! SQL Search Boxes & Data Macros

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

Welcome to Microsoft Access Advanced Level 6. In this course we will continue exploring macros, focusing on SQL search boxes for filtering customer lists and introducing table-level data macros. This class builds on concepts from the Beginner and Expert Series, as well as the previous advanced levels. We will discuss recommended prerequisites, compatibility with different versions of Access, and where to find the sample database for the course. This class contains two main lessons covering these core topics.

Navigation

Prev: A06
Next: Lesson 01

Keywords

Access Advanced, macros, data macros, VBA programming, Visual Basic for Applications, SQL search box, customer list form, filter customers, Access 2016, event macros, table level events, upgrade from 2007, Access Developer Series, search form

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Advanced Level 6, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. This is the sixth class in the Advanced Series.

The Advanced Series of classes focuses on Macros, which are tools to allow you to easily automate tasks and add functionality to your database. This will most likely be the last class in the Advanced Series because we have covered pretty much everything that I want to cover with Macros. That is not to say that I will not add a class later, but for now, we are going to put Macros to bed and jump straight to the Developer Series following this class, so we can start getting into Visual Basic for Application programming.

For now, I would say this covers about 80 percent of the Macro stuff that most people are going to use. I might do an Advanced Seminar or another class later on if you want to see something that I have not covered yet. Let me know and of course we will do it, but if I get enough material together for another class, maybe I will add level 7. I am not sure yet.

Microsoft recently dropped Access Web Apps, which is what I was hoping to use Macros for, but they made the decision recently to get rid of them. Macros are not quite as attractive as they used to be. I prefer Visual Basic programming for Desktop Access Applications.

Of course, this class follows Access Advanced Level 5. You should have taken all of the other Advanced Classes, one through five, as well as the Beginner Series and the Expert Classes. There are 32 of those before starting on the Advanced stuff with the Macros. A lot of people want to just jump ahead, but then I invariably get questions like, "What does that mean?" I covered that in the previous class, so I was going to point you to that class. I recommend you take all my classes in order.

The Beginner Series focused on building databases and the absolute fundamentals, building tables, building forms, and such. The Expert Series goes into relationships and then all kinds of extra tips and tricks with that, with forms and some forms and reports and grouping and all that good stuff. This is the Advanced Series with the Macros and an introduction to events and when certain things run and how events fire. Developers coming up next, that is going to be Visual Basic Programming. Each one of these different levels is broken up into different classes: level 1, 2, 3, 4, and so on.

Today's class uses Microsoft Access 2016 Data Macros, which are covered in Lesson 2. I believe they were added in 2010, so I do not think that they will work if you are using 2007. The good news is that I almost never used Data Macros, so you should be okay. So the 15-minute lesson you will not be able to use if you have Access 2007. It is time to upgrade if you do. 2010 and 2013 users, you should be just fine.

One of the reasons why I have not re-recorded all these lessons for the different versions of Access is honestly, they have not changed much. Access has not changed much since 2007. Since they added the ribbon, they made a bunch of changes to the interface. Even then, before that, a lot of it is the same. Macros are different. Macros have really changed. When we get into the next level, when we get into the Developer Series, the VBA code is almost exactly the same. You can go back and watch my 2003 lessons, and all the VBA is pretty much the same, it has not changed much, so let us go ahead and move forward with that.

The sample database for today's class can be found here by thenct.com-slash-databases. It is best if you build the database yourself from scratch like I did, but if you really want to cheat, you can go ahead and download my database. Go ahead, that is where you will find it, and you will need your password for the class in order to open up the database.

Now I will take a quick look at what is covered in today's class. Yes, there are only two lessons. Lesson 1 is 35 minutes long, I kind of went overboard, but there is a lot of good information in it, and I really did not want to split this up into two lessons. Lesson 2 is 17 minutes long outside of data macros. Yes, I should have probably broken those up into smaller lessons. So far no one has complained about my 20-minute lessons, so I think 35 should be okay.

Here is what is covered in today's class.

In Lesson 1, we are going to learn about SQL search boxes. We will put little search boxes on our customer list form, so we can type a few letters in, and then have the list of customers filtered to show just that particular list of customers.

In Lesson 2, we are going to learn about data macros. These are macros that fire when certain events occur at the table level.

Quiz

Q1. What is the primary focus of the Microsoft Access Advanced Series of classes?
A. Automating tasks with Macros
B. Building basic tables and forms
C. Designing web applications
D. Writing advanced SQL queries

Q2. What will the Developer Series focus on after the Advanced Series?
A. Crystal Reports
B. Visual Basic for Applications (VBA)
C. PowerPoint integration
D. SQL Server management

Q3. According to the lesson, why are Macros less attractive now than before?
A. Microsoft removed support for all Macros
B. Access Web Apps have been discontinued
C. Macros are only available in Access 2007
D. Macros cannot automate any tasks

Q4. How many levels make up the Beginner and Expert Series combined before reaching the Advanced Series?
A. 10
B. 20
C. 32
D. 40

Q5. What main topic does Lesson 1 of this class cover?
A. Form design basics
B. Building SQL search boxes for filtering customer lists
C. Automating report generation
D. Data validation in tables

Q6. Which Microsoft Access versions support Data Macros as per the lesson?
A. 2003 and 2007
B. 2007 and 2010
C. 2010, 2013, and later
D. Only 2016

Q7. What is recommended if you are still using Access 2007 and want to use Data Macros?
A. Downgrade to Access 2003
B. Stay with 2007 as it is fully supported
C. Upgrade to Access 2010 or newer
D. Use third-party macro tools

Q8. What is the preferred method for automating tasks in Desktop Access Applications, according to the instructor?
A. SQL scripts
B. Macros
C. Visual Basic for Applications programming
D. Microsoft Power Automate

Q9. What is a key difference between Macros and VBA as mentioned in the class?
A. Macros have not changed much since 2003
B. VBA code is consistent across many versions
C. Macros cannot automate any functions
D. VBA is not supported in recent Access versions

Q10. In Lesson 2, what is the focus of Data Macros?
A. Automating form-level events
B. Creating search boxes on reports
C. Automating tasks triggered by table-level events
D. Managing external database connections

Answers: 1-A; 2-B; 3-B; 4-C; 5-B; 6-C; 7-C; 8-C; 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 Microsoft Access Advanced Level 6. I am Richard Rost, your instructor, and this is the sixth course in the Advanced series.

In this Advanced series, our main focus is on Macros in Microsoft Access. Macros are essential tools that help automate tasks and enhance the functionality of your database without needing to write a lot of code. This course will most likely be the final one in the Advanced Macros Series, as we've touched on nearly everything I wanted to cover regarding Macros. Of course, if students request additional topics or if there is enough interest, I might add another class, but for now, we will consider Macros thoroughly covered. After this, we will transition into the Developer Series, which will center on Visual Basic for Applications (VBA) programming.

I estimate we've handled about 80 percent of the Macro techniques and features that most users will ever need. If you feel there is something essential that wasn't addressed, let me know, and I'll consider adding another advanced class or even hosting a seminar if there's enough demand.

An important note for those using different versions of Access: Microsoft has recently phased out Access Web Apps, which was one area where I found Macros particularly effective. With those gone, Macros are not as useful as they once were, and my personal preference has shifted toward using VBA for Desktop Access applications.

This class is a continuation from Access Advanced Level 5. Ideally, you should have completed all of the prior Advanced classes, Levels 1 through 5, as well as the Beginner and Expert series. That means there are 32 classes leading up to the Advanced Macro units. I know it can be tempting to skip ahead, but I've designed the curriculum to build on concepts as we go, so if you miss an earlier class, you might find yourself asking, "What does that mean?" which usually means it was covered in a previous lesson. I highly recommend making your way through all of the classes in order.

To recap the curriculum, the Beginner series introduces you to building databases, working with tables and forms, and covers all the fundamental skills. The Expert series builds on this by going deeper into things like relationships, advanced form and report skills, tips, tricks, and more intricate grouping and sorting options. The Advanced Series mainly covers Macros and offers a first look at events, how they operate, and when they are triggered. The upcoming Developer Series will tackle VBA programming. All of these are broken up into classes at different levels, so you can work your way up from the basics to more complex skills.

For today's course, we're working with Microsoft Access 2016 and specifically looking at Data Macros in Lesson 2. Data Macros were introduced in Access 2010, so if you're using Access 2007 or earlier, you won't be able to use these features. Fortunately, Data Macros are not essential for most of the content, so unless you need that specific 15-minute segment, you won't miss out. Users of Access 2010 and 2013 should be just fine.

Another point to mention is that the main aspects of Access have not changed much since 2007. Since the introduction of the ribbon interface, the program's core features have remained fairly consistent. Macros have certainly evolved, but overall the software hasn't required me to re-record every lesson for each new version. Especially once we start discussing VBA, most of the code you'll see is unchanged even from back in 2003.

You can download the sample database for today's lesson from the link I provide. In general, I suggest building your own database as you follow along for the best learning experience, but if you want to use my pre-built database to save time, that option is there as well. Just remember you will need your class password to get access to the file.

Now, let's take a look at what's included in today's course. There are only two main lessons in this class. Lesson 1 runs for 35 minutes because there's a lot to cover, and I didn't want to break it up further. Lesson 2 is seventeen minutes and focuses on Data Macros. I probably should have split these into shorter segments, but so far no one has complained about longer lessons, so I think they will be fine.

Here's an outline of today's topics:

In Lesson 1, I will show you how to create SQL search boxes. We will add these search boxes to a customer list form. This will allow you to type in part of a name and instantly filter your list of customers to show just the ones that match your search criteria.

In Lesson 2, the topic is Data Macros. These are special Macros that are triggered by certain events at the table level.

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

Topic List

SQL search boxes on customer list forms
Filtering customers using search boxes
Using data macros in Access
Data macros triggered by table events

Article

Welcome to this advanced Microsoft Access tutorial. Here, we will focus on two main topics: using SQL search boxes to filter records on a form and understanding data macros in Access. You should already be comfortable with database fundamentals, including how to build tables, forms, and reports, and you should be familiar with relationships and more advanced tips and tricks in Access. If not, it is a good idea to review beginner and expert concepts first.

Let us start with SQL search boxes. Imagine you have a customer list form and you want to quickly search for a specific customer without scrolling through the entire list. To do this, you can add a search box to your form, which allows you to type in a few letters or a name, and the list automatically filters to show only matching records. This technique saves time and makes it much easier to find information.

To set this up, start by opening your customer list form in design view. Add a text box to your form header, and name it something like txtSearch. Next, you will use VBA code to filter the records based on what you type in the search box. For example, you might add a button next to the search box labeled "Search." When the button is clicked, it will run a small bit of VBA to perform the filter. Here is some sample VBA code that you could use:

Private Sub btnSearch_Click()
Dim strFilter As String
If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then
Me.FilterOn = False
Else
strFilter = "CustomerName LIKE '*" & Me.txtSearch & "*'"
Me.Filter = strFilter
Me.FilterOn = True
End If
End Sub

This code looks at the txtSearch box. If it is empty, it turns off any existing filter and shows all the records. If you have typed something in, it builds a filter string that finds any customer name containing the text you entered and applies it to the form. This way, as you type a partial name and hit the search button, you will see only matching customers.

If you want the filter to update automatically as you type, you can use the AfterUpdate event of the txtSearch box instead of a separate button. Modify the code above and put it in the AfterUpdate event for txtSearch. This will make your search form even more user friendly.

Now let us talk about data macros in Access. Data macros are special macros that you attach directly to tables rather than forms or reports. They allow you to automate actions whenever data in your tables change, for example when a record is added, edited, or deleted. Data macros are similar to triggers in SQL Server and can help enforce business rules at the data level, no matter where the data is being changed.

For example, say you have an Orders table and you want to automatically update the LastOrderDate field in your related Customers table every time a new order is added. You could use a data macro attached to the After Insert event of the Orders table. In Access 2010 and later, open the Orders table in design view and click on the Create Data Macros button. Choose the After Insert event. Now you can write actions that will run each time a new order is entered.

A typical data macro action might look like this:

SetField
Name: [Customer].[LastOrderDate]
Value: Now()

This command updates the LastOrderDate field in the Customers table with the current date and time whenever a new order is added. You can use similar macros for other events, such as After Update and After Delete, making it easy to build logic right into your tables.

Data macros are available in Access 2010 and newer, but not in Access 2007. They were designed to work especially well with Access Web Apps, which Microsoft no longer supports, but you can still use them in desktop databases to automate data changes and validations.

One thing to keep in mind is that data macros run at the table layer, not at the form or interface layer, so they can enforce rules regardless of whether a user is interacting with your forms or modifying tables directly. This helps protect data integrity in your applications.

Most of the macro automation you will need can be handled with regular form macros and VBA, but data macros are a powerful tool when you need to enforce rules right at the data level. As you work more with Access, you might find yourself preferring to use Visual Basic for Applications, or VBA, for more advanced logic because it is even more flexible. The basics of using VBA in Access have not changed much since the early versions, so techniques you learn now will continue to work in the future.

If you would like to experiment further, you can try building your own sample database from scratch using these techniques, or you can download a sample database if available. Either way, practicing these features will deepen your understanding. Try adding a search box to one of your forms and experiment with simple data macros on your tables. You will quickly see how these tools help automate tasks and improve the user experience in your database applications.