Welcome

In this class, we will learn about preventing duplicate items in a list. For example, we'll prevent a user from adding the same product to an invoice twice. We'll review composite keys, and we'll learn about the SQL NOT IN clause. Then we'll learn how to generate a list of customers who have not ordered a specific product, any products from a list, or all products from a list.

Resources

Topics Covered

In Lesson 1, we will learn how to prevent duplicate products from being added to orders using a composite key.

In Lesson 2, we will learn another method for preventing duplicates using VBA and DCount. This will give us the opportunity to ask the user if they want to add the duplicate item, and give a custom warning message.

In Lesson 3, we will learn how to effectively remove items from the product combo selection box once they've been added to the order. We will use the NOT IN clause in SQL.

In Lesson 4, we will learn how to generate a query showing customers who have not ordered a specific product. We will do a simple text search based on the product name.

In Lesson 5, we will take our database one step further and select a single product from a list of products. We will then generate a list of customers who have ordered that product, and who have not ordered that product.

In Lesson 6, we will change our single combo box to a multi-select listbox. This way we can generate a list of customers who have ordered all of the selected products. This will require a recordset loop, and a second loop through the list of items in the listbox.

In Lesson 7, we will perform some optimizations, and then we will modify our code to allow the user to generate a list of customers who have purchased all products selected, any selected products, or none of the selected products.

Enroll Today

Enroll now so that you 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.

Keywords

microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, access developer 36, composite key, prevent duplicates, custom warning message, not in, sql, have not ordered, multi-select list box, listcount, selected, column, recordset, select all

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

Intro

In this lesson, you will learn advanced techniques for preventing duplicate records in Microsoft Access, including how to use composite keys and create custom warnings with VBA and the DCount function. I will show you how to use the SQL NOT IN clause to filter records, generate queries to find customers who have or have not ordered specific products, and work with multi-select list boxes to find customers based on products they have purchased. You will also learn how to create a temporary table, optimize your VBA code, and use various selection logic to build powerful marketing queries. This is Access Developer Level 36.

Transcript

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

In today's class, we are going to spend some time learning how to prevent duplicates. We will learn about composite keys and the SQL NOT IN clause to determine if something is already in a set of records.

For example, we are going to make sure that you cannot put the same product on an order twice. Then we are going to do some work with "have not ordered." We are going to generate a list of customers who have not ordered a particular product.

We will start off by doing a single product. Then we will do "show me customers who have ordered any products from a list," or "all products from a list," or "none of the products from a list." I can pick two or three different products, show all the customers who have not purchased those products, and then I can send them some marketing information.

This class follows Access Developer Level 35, and I strongly recommend you have taken all the Developer classes and everything before them - Beginner, Expert, and Advanced - before taking this class. I especially suggest you review Developers 15, 17, and 19 if you have not watched them already. In those classes, I cover multi-select list boxes, which we will be using a lot today. So watch those three classes if you have not yet.

Of course, I recommend you do not skip levels. My classes are designed for you to view one after the other; the material builds on the lessons before it. So make sure you watch all of them in order.

I will be using Access 365 at this point. It is roughly equivalent to Access 2019. I should probably update the slide for Access 2021, since that is the latest retail version. It is strange what they are doing with the versioning now. Access 365 is subscription based, so you are constantly getting the newest features that they release. 2019 was the last major retail update; 2021 just came out a little while ago. There, I updated it - happy?

Honestly, there is really not a lot of difference between 2021 and 2019. They are pretty much the same, with only a few minor changes here and there, but nothing that will affect the lessons I am covering today. It is all the same material. In fact, everything I cover today will probably work going back to 2007, so you are good.

If you have questions, just scroll down to the bottom of the page you are on and post them where you see the box. I think I changed it now; it says "Post a new discussion" or "Start a new discussion," something like that. That is where you put your questions. Do not email them to me. Put them there. I have a great group of admins and moderators on my website, and they are more than helpful. They will usually get to your questions before I will anyway. So put them there.

If you have general questions about Microsoft Access not related to the topics of today's class, feel free to post them in the Access Forum.

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

In lesson one, we are going to see how to prevent duplicate products from being added to an invoice using something called a composite key.

In lesson two, we will learn a different method for preventing duplicate values without a composite key. We will use a little bit of VBA and the DCount function. This will allow us to give the user the option to add the item twice if they want to, and we can use a custom warning message instead of the stock warning message that the composite key gives you.

In lesson three, we are going to learn the SQL NOT IN clause. This way, we can take our product combo box, which has a list of items to add to the order, and say "only show me the list of products that are not in the current order." In other words, we do not want the same product being added twice. The items will be removed from the product combo box if they are already on the order. That is the SQL NOT IN clause.

In lesson four, we are going to see how to generate a query showing a list of customers who have not ordered a specific product. We are going to base this on the text of the product name. We will just have the user type it in, and I will search for customers without that product.

In lesson five, we are continuing with the "have not ordered" database. We are going to switch over to a more powerful database that has a list of products, and you can pick from a list of products and say which customers have ordered this product or which customers have not ordered this product. That is great for marketing. You can say, "Show me all the people who have not ordered a phaser bank," and send them a solicitation for it.

In lesson six, we are moving on to a multiple select list box. We are going to say, "I want to see a list of all the customers who have ordered all of the products I have selected." Then, in the next couple of lessons, we will do "any of the products" and then "none of the products." We are going to start with this one first.

In lesson seven, there is a bunch of stuff we are going to do. We are going to create a customer temp table to store this data in a temporary table. We are going to optimize our loop and convert it from a For loop to a While loop. They run better, and you will see why. We will make buttons to select all of the products and clear all the product selections. Then we will put in the logic to do all products, any products, or no products. There is a whole bunch of If-Then logic we have to throw in there. We will make a function to count the number of items that are selected, and if no items are selected, we will show nothing.

That is all covered in lesson seven.

Quiz

Q1. What is the main objective of using a composite key in an Access database as discussed in this lesson?
A. To allow multiple users to access the database simultaneously
B. To prevent the same product from being added to an order twice
C. To speed up database queries
D. To backup the database automatically

Q2. Which SQL clause is taught in this class to find records not present in a current set?
A. IN
B. WHERE
C. NOT IN
D. BETWEEN

Q3. What feature allows you to generate a list of customers who have not ordered a specific product?
A. Export Data Wizard
B. Custom Reports
C. A query searching for customers without the product
D. Pivot Table

Q4. What is the benefit of using a multi-select list box as covered in this class?
A. It lets you store data from multiple tables automatically
B. It creates backup copies of your forms
C. It allows you to select multiple products to query customers against
D. It changes the color scheme of your database

Q5. Why is the DCount function used in lesson two?
A. To delete duplicate records automatically
B. To check for duplicate values before adding a record and allow custom warnings
C. To count the number of orders placed by a customer
D. To sum the total sales for a product

Q6. What is one of the marketing uses of the queries created in this class?
A. To generate invoices for all customers
B. To send solicitations to customers who have not ordered selected products
C. To merge duplicate customer records
D. To change product prices

Q7. "All the people who have not ordered a phaser bank" would be found using which database feature explained in the lessons?
A. An export spreadsheet tool
B. A query with filter for customers missing that product
C. A VBA subroutine to delete invoices
D. A report summary

Q8. Optimizing loop performance in VBA, as mentioned in lesson seven, involves converting which type of loop?
A. For loop to Do-Until loop
B. While loop to For loop
C. For loop to While loop
D. Do-While loop to For loop

Q9. What happens if no items are selected in the multi-select list according to lesson seven?
A. The program displays all customers
B. The program shows a default message
C. The program shows nothing
D. The program adds all products to the order

Q10. Where are you advised to post questions related to the course and its material?
A. Email the instructor directly
B. Post in the comments of any video online
C. Use the discussion area or forum on the course website
D. Call customer support

Answers: 1-B; 2-C; 3-C; 4-C; 5-B; 6-B; 7-B; 8-C; 9-C; 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 focuses on how to prevent duplicate entries in Microsoft Access databases and how to work with lists of customers and products for analysis and marketing purposes.

I begin this class by teaching you techniques for ensuring that duplicate products cannot be added to a single order. The first concept we cover is how to use composite keys in tables, which is a straightforward way to enforce that you do not enter identical product records for an invoice.

Next, we move on to an alternative approach for handling duplicates that does not rely solely on composite keys. Here, I show you how to use VBA in combination with the DCount function. This method is more flexible, allowing you to present a custom warning to the user if they try to add a duplicate item, and even letting them choose to add it again if they really want to, instead of just blocking it automatically with the default database message.

For the third lesson, I cover the SQL NOT IN clause. This plays a big role when you work with forms and want to present a combo box containing only products that have not already been added to the current order. This helps to ensure that users only see what's available for selection, reducing the chance of double entries by preventing them at the source.

In the fourth lesson, we start working with queries designed to identify customers who have not ordered specific products. Here, you will see how to set up a simple search based on the text name of a product, allowing you to quickly find and analyze customers who might be targets for additional marketing.

Lesson five expands on this by introducing a more advanced database setup with a product list, so you are able to select one or more products and see which customers have or have not purchased them. This is especially useful for marketing purposes, such as targeting customers who have not ordered certain items.

Lesson six takes this even further with a multi-select list box that lets you identify customers who have ordered all selected products. After that, subsequent lessons show you how to check for any of the products or none of the products from your selection, making it very flexible to analyze your customer data in different ways.

Finally, in lesson seven, I guide you through several improvements and optimizations. We set up a temporary table to store intermediate data for processing. I show you why switching to a While loop from a For loop can improve performance, and we add buttons to quickly select or clear all product choices in the interface. We then complete the logic to handle all, any, or no product selections, and build a function to count selected items so the results are accurate and responsive.

Before you start with this level, you should have completed all previous Developer classes as well as the Beginner, Expert, and Advanced series, especially Developer levels 15, 17, and 19. These cover essential skills like using multi-select list boxes, which we use extensively in this course. My classes are intended to be viewed in sequence, so I recommend you avoid skipping ahead.

For these lessons, I use Access 365, which is very similar to Access 2019 and 2021. The material should be compatible even with older versions back to Access 2007, so you should have no trouble following along regardless of your version.

If you have any questions about this class, please use the discussion area at the bottom of the webpage where the course is hosted. There, you will usually get help from the community or site moderators even faster than from me directly. For general Access questions not related to today's lessons, please use the Access Forum.

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

Topic List

Preventing duplicate products on an invoice using composite keys
Preventing duplicate values with VBA and DCount
Custom warning messages for duplicate entries
Using the SQL NOT IN clause to filter combo box products
Displaying only products not already on the order
Querying customers who have not ordered a specific product
Searching customers without a product by product name input
Selecting products from a list to check customer orders
Identifying customers who have not ordered selected products
Using multi-select list boxes for product selection
Showing customers who ordered all selected products
Showing customers who ordered any selected products
Showing customers who ordered none of the selected products
Creating a customer temp table for storing query results
Optimizing VBA loops from For to While for efficiency
Adding buttons to select or clear all product selections
Implementing If-Then logic for product selection queries
Counting selected items in a multi-select list box
Handling cases with no products selected in queries