Welcome

In this class we will start out by fixing a bug from Developer 25 that one of the students discovered. Then we will build two different search forms. The first will use filter boxes over the column headers. These filter boxes will be used to construct a dynamic SQL statement that feeds a list box. The column headers will also be used to control the sort. Then the second search form will have criteria stored in a table that you can easily manipulate. We will then use a recordset to read in the criteria from the table to build an SQL statement. Cool, new stuff!

Resources

Topics Covered

In Lesson 1, we will fix a bug that one of our students found from D25. When you go to ship an order, the system sees the product groups as products which have zero stock. We'll fix that.

In Lesson 2, we will begin building a search form that will filter records in a list box using a Dynamic SQL Statement. This essentially creates an SQL statement based on the values selected on the form. In this lesson we'll create the form, set up header labels that will control the sort with an ORDER BY condition. Click on a header to sort by that column. We'll add spiffy colors. We'll see how to always sort NULL values on the bottom of the list.

In Lesson 3, we will add filter boxes and build the dynamic WHERE condition. Filter by whatever columns you want. We'll add a combo box so you can select either an OR or AND condition.

In Lesson 4, we will take the search form to a whole new level. We will create a criteria table. We'll use a recordset to read the conditions out of the table and construct the SQL statement. This way you can add as many different criteria as you want for multiple fields, or the same field. For example you could say "State equals Florida AND FirstName starts with R AND CreditLimit <= 5000 AND CreditLimit > 1000." Sky's the limit with this one! We'll learn how to read field data types from a TableDef.

In Lesson 5, we will create a combo box that has a list of all of the fields in the selected table. We will read that list of fields from the TableDef Fields collection. Then we will make a button to add the criteria to the table and update the results.

Enroll Today

Enroll now so that you can watch these lessons, learn with us, post questions, and more.

Questions?

Please feel free to post your questions or comments below. If you are not sure as to whether or not this product will meet your needs, I'd rather help you before you buy it. Remember, all sales are final. Thank you.

Comments for Access Developer 26

Age	Subject	From
2 years	Extended Search	Abraham Breuer
5 years	Recordset in recordset	Ole Simonsen
5 years	Dim and Resources	Ole Simonsen
5 years	Developer 26 Lessons Up	Richard Rost

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

Intro

In this lesson, you will learn how to build advanced search forms in Microsoft Access, starting with a simple customer search form that includes custom sorting and filter boxes. We will address a bug fix from the previous lesson involving inventory calculations, then create a more powerful search form using a separate criteria table and recordsets to filter customer data. You will see how to add dynamic filtering options, use AND/OR conditions, colorize sorted columns, and handle different data types when building flexible search tools. This is Microsoft Access Developer Level 26.

Transcript

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

In today's class, we are going to start off with a bug fix that one of my students discovered from Developer 25. Then we are going to build two different types of search forms. The first one is a simpler form that has some custom sorting on it and some custom filter boxes.

The second search form is going to be that one on steroids. We are actually going to make a criteria table where you can put all kinds of criteria in a separate table and then use that table with a record set to filter the original list of customers. There is some really cool stuff in this lesson.

This class, of course, follows Access Developer Level 25. I strongly recommend you take that class first before this one. It is also recommended that you have taken all my beginner, expert, advanced, and other developer classes too.

I am using Microsoft Access 365, part of a 365 subscription. It is roughly equivalent to Access 2019. Access 2016 will work just fine too. In fact, the lessons covered in this class should go all the way back to about 2007 without any problems, but do not quote me. I only support 2016 and 2019 at this level.

Got questions about the material covered in this lesson? Just scroll down to the bottom of the page and post them right there in the questions box. As always, if you have any questions about Access that are not related to the material in this class, go ahead and post it in the Access Forum.

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

In lesson one, we are going to start off with a bug fix, something that was discovered in Developer 25. We are going to have to make inventory calculations ignore product groups when we go to ship an order.

In lesson two, we are going to begin building an advanced search form to search through our customers. We are going to start off building a basic, unbound form with a customer list box on it. The first thing we are going to do is work on sorting. So we can click on any of these labels here in the column headers to sort based on a field.

We are going to sort the null values at the bottom, which is different from how Access normally treats them. It normally sorts null values up at the top, which I do not like. We will make it so it colorizes whichever one of these columns is the sort field, and then we will reverse that from blue to red when we sort descending. So lots of cool stuff in this lesson.

In lesson three, we will continue on with our search form. We are going to put field filter boxes across the top of each column, so you can type in a letter or a string and it will filter based on whatever you type in. We will put a clear button at the end so you can clear those filters.

We will build dynamic where conditions into our SQL, so we can say use this field and/or this field and/or this field, and we will use an and/or drop down box. You can pick an and or or condition for your search. Lots of cool stuff in this lesson too.

In lesson four, we are continuing with our search form. We are doing something completely and totally brand new. We are going to make a table, and the table will store the search criteria. We are going to loop through the records in that table to create the where condition for the search form using, guess what, a record set. You did not think you were getting out of Developer 26 without at least one record set, did you? Lots of stuff in lesson four here. Brand new stuff.

Here we go.

In lesson five, we are continuing with the search form. We are going to loop through all the fields in the customer table and fill them into a combo box so we can pick from any field in the list. Then, based on the data type of that field, if it is text, we will allow the like condition to be possible. If not, it will just be the equality stuff.

Then, put the criteria in, add the criteria, save it in the table, refresh the main list, and then, of course, we will make a delete button. Lots of cool stuff in this one too.

Quiz

Q1. What is the first topic covered in Access Developer Level 26?
A. Creating a new report layout
B. Implementing a bug fix from Developer 25
C. Importing Excel data
D. Setting up security permissions

Q2. What is the primary function of the advanced search forms discussed in this class?
A. Importing customer data
B. Searching and filtering customer lists
C. Scheduling database backups
D. Designing custom reports

Q3. How does Access typically sort null values, according to the video?
A. In the middle of the results
B. Randomly within the dataset
C. At the top of the results
D. At the bottom of the results

Q4. When building the advanced search form, how is sorting visually indicated?
A. By showing a symbol next to the sorted column
B. By changing the column font size
C. By colorizing the sorted column, with blue for ascending and red for descending
D. By hiding unsorted columns

Q5. What is a key new feature described in lesson four?
A. Exporting search results to Excel
B. Creating a table to store search criteria and building dynamic queries using a record set
C. Emailing filtered customer lists directly from Access
D. Integrating with Microsoft Teams

Q6. What function does the 'clear' button serve on the search form?
A. Resets the entire database
B. Removes all sorting indicators
C. Clears all filter boxes to remove applied filters
D. Exports results to PDF

Q7. What SQL feature is dynamically built into the advanced search form for custom filtering?
A. JOIN statements for related tables
B. WHERE conditions using selected fields and logical operators
C. GROUP BY clauses for summaries
D. HAVING clauses for post-filtering

Q8. In lesson five, what determines whether a "like" condition is possible for a field?
A. If the field is set as a primary key
B. If the field data type is text
C. If the field is numeric
D. If the field contains dates

Q9. What is done with the list of customer fields in lesson five?
A. They are used to generate detailed reports
B. They are populated into a combo box for user selection
C. They are all removed from the database
D. They are encrypted for security

Q10. What does the delete button created in the final lesson do?
A. Deletes customer records from the table
B. Deletes search criteria entries from the criteria table
C. Deletes the entire database
D. Deletes unused forms

Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-C; 7-B; 8-B; 9-B; 10-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 26. I am your instructor, Richard Rost.

This class gets started with a bug fix that was brought to my attention by a student from the previous Developer Level 25 course. After we address that issue, we will move on to building two different types of search forms. The first is a simpler search form that allows custom sorting and includes custom filter boxes.

The second search form builds upon the first and adds much greater flexibility. We will create a criteria table where you can define multiple criteria in a separate location, and use that table in combination with a record set to filter the main list of customers. This approach opens up a lot of new and powerful possibilities.

As always, this course builds directly on the material covered in Developer Level 25, so I strongly suggest completing that class first. It is also ideal to have finished the beginner, expert, advanced, and other developer courses beforehand to ensure you have the necessary foundation.

For this class, I am using Microsoft Access 365, which is very similar to Access 2019. Access 2016 will also work well. In fact, what you learn in this class should be compatible all the way back to Access 2007, but officially I only support 2016 and 2019 at this level.

If you have questions about anything taught in this class, just scroll to the bottom of the page on my website and ask in the questions box. For any general Access questions not related to this lesson, feel free to use the Access Forum.

Here's what we'll be covering in today's lessons:

Lesson one focuses on fixing a bug found in Developer 25. Specifically, we are going to adjust the inventory calculations to make sure product groups are ignored when processing an order for shipment.

In lesson two, we will start building an advanced customer search form. We'll create a basic, unbound form with a list box for customers. One of our early goals is to implement custom sorting. You'll be able to sort based on any field by interacting with the column headers, and unlike Access's default behavior, we will have null values appear at the bottom instead of the top. There will also be a color indicator to show which field is being sorted, switching from blue to red when you sort in descending order.

Lesson three digs deeper into the search form by adding filter boxes at the top of each column. This allows you to filter the displayed customers based on any text you enter. We'll include a clear button to reset those filters. We will make the SQL statement build dynamic where conditions, so you can define your search using multiple fields at once. An "and/or" dropdown will give you control over how fields are combined in the search.

Lesson four introduces a completely new technique. Here, we will build a table to store the search criteria and then loop through the records in that table to build the where condition for our search form, using a record set. If you have been following along with my developer courses, you know how powerful record sets can be, and we will definitely put them to good use here.

Lesson five continues enhancing the search form. We will loop through all the fields in the customer table and display them in a combo box, so you can select any field for your search filter. Depending on the data type of the field, if it is a text field, we will allow the "like" condition to be available; otherwise, comparisons will be handled by equality. You will then be able to enter criteria, add them, save them in the criteria table, refresh the main customer list, and we'll also add a delete button for any criteria you want to remove.

If you want to see a full video tutorial that walks through every step of these lessons in detail, you can find it on my website at the link below. Live long and prosper, my friends.

Topic List

Bug fix for inventory calculations with product groups
Building a basic unbound customer search form
Implementing custom sorting in a list box
Sorting null values at the bottom of the list
Colorizing sorted column headers
Creating field filter boxes for each column
Clearing search filters with a button
Building dynamic WHERE conditions in SQL
Using AND/OR dropdowns for search conditions
Storing search criteria in a table
Looping through criteria records with a recordset
Generating the WHERE clause from table data
Populating a combo box with all customer table fields
Adjusting search operators based on field data type
Adding and saving search criteria dynamically
Refreshing the customer list based on criteria
Deleting search criteria from the criteria table