Preventing Duplicates, Have Not Ordered Products

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

• Full Course Lessons Page
• Course Topic Outline

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