Channel: Computer Learning Zone

Category: Education

Tags: composite key#instructionrecordsetselect allaccess developer 36sql#lessonnot in#trainingcustom warning messagemicrosoft access#database#learnselected#tutorial#msaccessprevent duplicatesms accesscolumnmulti-select list box#microsoftaccess#howtohave not orderedlistcount#help

Description: Access Developer 36 is 2 hours, 31 minutes long and is available on my web site at 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