Video Discription |
Access Developer 36 is 2 hours, 31 minutes long and is available on my web site at https://599cd.com/ACD36
TOPICS COVERED
Preventing Duplicates
Composite Keys
SQL NOT IN List
Have Not Ordered
Single Product
Any, All, No Products
Lots More!
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.
OUTLINE
1. Composite Key (18:00)
Composite Key to Prevent Duplicates
2. Prevent Duplicates (11:11)
No Composite Key
Allow User Option to Add Twice
VBA, DCount
Custom Warning Message
3. SQL NOT IN (9:57)
Remove Products Already On Order From Combo
SQL Not In () Clause
4. Have Not Ordered Part 1 (20:50)
Customers Who Haven't Ordered Specific Product
Text Search on Product Name
5. Have Not Ordered Part 2 (22:43)
Select from Product List
Have or Have Not Purchased
6. Have Not Ordered Part 3 (25:50)
Create Multi-Select ProductList Listbox
ProductList.ListCount
ProductList.Selected(X)
ProductList.Column(0,X)
Recordset Loop Thru Customers
Loop Thru Listbox Selected Items
7. Have Not Ordered Part 4 (35:06)
CustomerTempT for Temp Records
Optimize: Convert For to While Loop
Select All and Clear Buttons
All, Any, and None Conditional Logic
Count Items Selected Function
If No Items Selected Show Nothing
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 CMEChKT0KfU |