Welcome

Developer 19 continues our coverage of Recordsets and other topics. We will learn how to create bound multi-select listboxes, nested recordset loops, and the Chef's Kitchen Helper.

Resources

Topics Covered

In Lesson 1, we will learn about the different types of recordsets that you can open (Table, Dynaset, Snapshot, Forward-Only). We will then create a "bound" multi-select listbox. It's not really bound to a field in the table, because you can't do that, but we'll make it look like it is.

In Lesson 2, we will continue working with that "bound" list box. We'll save the items selected in the list box if we move off the record or close the form, utilizing the Dirty property.

In Lesson 3, we will learn how to create nested recordsets. We will loop through customers and display customer names with all of their individual contact records as well. One loop inside of another.

In Lesson 4, we will learn how to use a query to determine what dishes you can make based on the available ingredients. Not limited to just the kitchen. You can use this to calculate needed components for any product that involves multiple parts. Whether you're selling food, computers, or machine shop parts, these techniques work for everyone.

In Lesson 5, we will learn how to look at the actual quantity of the products you have on the shelf and calculate if you have enough to make a dish. Instead of just "yes, I have eggs" you can say "I have 12 eggs" and calculate accordingly.

In Lesson 6, we will learn how to do the same thing that we did in Lesson 5, but this time using nested recordsets. We will also fill an unbound listbox with the dishes that we are able to make.

In Lesson 7, we will learn how to select an item from the dishes we can possibly make, and then with one click remove the products that make up that dish from our pantry. Basic inventory control.

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.

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

Intro

In this lesson, you will learn about working with bound multi-select list boxes in Microsoft Access, using record sets to manage user selections and update related tables. We will also cover how to use nested record sets for scenarios like looping through customers and their contacts, and then apply these techniques to build a Chef's Kitchen Helper project that matches available ingredients to recipe possibilities, tracks inventory, and updates components as you make each dish. This is Microsoft Access Developer Level 19.

Transcript

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

Today's class covers three main topics. First, we are going to do bound multi-select list boxes. Then, we are going to do nested record sets, one inside the other. After that, we will work on a fun project: the chef's kitchen helper.

Lesson 1 is about bound multi-select list boxes. They are not technically bound to the table, but I call them bound because they will have the same effect.

Let's say you have a customer that you are tracking interests for, such as laptops, computers, hard drives, and all that stuff. Normally, you would set this up with a form and a subform. However, in a subform, you really cannot see the entire list of options at once and then pick them. You can see here in the example. Here is how I did it with a subform first. You could drop this down and pick laptops, drop this down and pick speakers.

This is what we are going to build in class. It is a whole lot easier to use. You can see all the options at a glance. So, it is not technically bound to the customer table, but we are going to set it up so it looks like it is. We will use a record set to read in the values. Then, after the user makes their changes, we will use a record set to write them back out again to the related table.

Lesson 2 continues on with that.

Lesson 3 is going to cover nested record sets: having a record set inside of a record set and looping between them. The sample we will do in class features customers and their contact messages. We will loop through all our customers and then display all the contacts for each customer. That is how I am going to teach you how to do multiple nested record sets.

Lessons 4 through 7 cover a project called the Chef's Kitchen Helper. Here is how it works: you have a list of recipes - pancakes, chicken parm, whatever. You have ingredients for those recipes. For example, chicken parm might include chicken breasts, marinara sauce, parmesan cheese, and so on. Then, we will have another table that has all the ingredients I have in my pantry.

We want to be able to generate a query that says, based on the ingredients you have, these are the dishes you can make. We will start that in lesson 4.

Lesson 5 takes the Kitchen Helper a little bit further. In lesson 4, we just said yes or no - I have this. In lesson 5, we will make the query say, I have this much of it. I have this quantity on hand. I need this much to make this dish. What can I still make?

Lesson 6 will take what we did in lesson 5 and delete it. Not everything - I am just kidding. We are going to take the queries we built and, instead of using queries, we are going to do this with record sets, which is what we are learning in class. We are going to use record sets. I think record sets are easier to program. I think they are a lot easier to build than making complex queries.

We will use the nested record set concepts that we learned in lesson 3 and make nested record sets to handle our "what can I make" scenario. Then we will fill a list box with the available meals.

Why? Because we are going to use it in lesson 7. In lesson 7, we will say here is a list of all the meals that you can make. Now pick one, click make one, and it will remove that information. It will use products - those components - that inventory from your pantry.

For example, if you make one chicken parm, it removes the pasta, the marinara sauce, the parmesan cheese, and the chicken breast.

These techniques, by the way, work whether you are selling food in a kitchen or whether you are selling computers. If you have a product that is made of components, this stuff will work.

I am using Microsoft 365 and it is currently 2020, so it is roughly equivalent to the Access 2019 retail set. Although, the material I am covering in this class should work all the way back as far as I can remember, at least 2007. If you are using a version that old you might want to consider upgrading.

If you are going to take this course, you definitely should have taken my Beginner series, Expert 1 and 2 at a minimum, Developer 1 for some VB basics, Developer 13 and up for the concepts I am covering now, and of course Developer 16, 17, and 18 to learn about record sets. If you have not taken those classes, you might be lost in today's class. I am not going to go over the record set basics.

Do not forget, if you have any questions about the material covered in today's class, you can post them in the forum. There is a form right down below on the lessons page. You can post all your questions related to this class right there.

Time to sit back, relax, and enjoy lesson one.

Quiz

Q1. What is the main benefit of using a multi-select list box for tracking customer interests compared to a subform?
A. You can see all options at a glance and pick multiple at once
B. It automatically updates the customer table
C. It only allows one item to be selected
D. It does not require code to work

Q2. How are the selections from the multi-select list box written back to the related table?
A. Through a record set after user changes are made
B. Directly, since the list box is technically bound
C. By exporting the list to Excel
D. Manually entering the selections later

Q3. What does the term "bound" refer to in the context of the multi-select list box discussed in this class?
A. It appears to be bound to the table but uses record sets to save data
B. It is physically bound with duct tape to the form
C. It cannot display any options
D. It is always bound by default in Access

Q4. What is the purpose of nested record sets as covered in lesson 3?
A. To loop through customers and their related contact messages
B. To import data from another database
C. To update form decorations
D. To secure user passwords

Q5. In the Chef's Kitchen Helper project, what is the ultimate goal of the queries and record sets?
A. To determine what dishes can be made based on pantry ingredients
B. To schedule chef work shifts
C. To calculate nutritional information
D. To create a shopping list for the store

Q6. How is the Kitchen Helper's functionality advanced in lesson 5?
A. By tracking ingredient quantities needed and on hand
B. By printing recipes
C. By emailing customers
D. By creating charts

Q7. What programming technique replaces queries in lesson 6 for the "what can I make" scenario?
A. Using nested record sets
B. Using VBA to email the chef
C. Using macros only
D. Exporting lists to Word documents

Q8. In lesson 7, what happens when you select and "make" a meal using the Kitchen Helper?
A. The used ingredients are removed from your pantry inventory
B. The kitchen closes for cleaning
C. All meals become available
D. The recipe is deleted forever

Q9. Besides kitchens, the multi-component techniques shown in the course are also useful for which of the following?
A. Selling computers with components
B. Editing photographs
C. Managing staff schedules
D. Printing invoices

Q10. Before taking this course, which prior AccessLearningZone classes should a student have completed at a minimum?
A. Beginner series, Expert 1 and 2, and Developer 1, 13, 16, 17, and 18
B. Only Developer 19
C. Microsoft Word Basics
D. Just Developer 17

Q11. What is suggested if you are using a version of Access older than 2007?
A. Consider upgrading
B. Stop taking the class immediately
C. Use Excel instead
D. Uninstall Access

Q12. Where can students ask questions about the material covered in the class?
A. In the forum on the lessons page
B. By emailing Microsoft directly
C. At the end of the semester only
D. Using Facebook Messenger

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A

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 advanced techniques for Microsoft Access developers. In this lesson, I will be covering three key topics: working with bound multi-select list boxes, using nested record sets, and building an application called the Chef's Kitchen Helper.

To begin, the first lesson addresses bound multi-select list boxes. Although they are not technically bound to the table in the strictest sense, for practical purposes, they'll behave as if they are. Imagine you need to track customer interests, such as laptops, computers, or hard drives. Traditionally, this would involve a form with a subform where you select interests one at a time. While this approach works, it does not offer an efficient overview since you cannot see and pick from the entire list at once.

Instead, I will show you how to improve this process. We will set up a system where users can view all available options at a glance, select what they want, and have those selections appear to be 'bound' to the customer, even though, technically, they are not bound fields. We accomplish this by using a record set to first load the current selections and then, after changes are made, write those back to the related table.

The second lesson continues with this technique, expanding on the concepts introduced and showing how to further refine and utilize multi-select list boxes in practical scenarios.

In the third lesson, I explore nested record sets. Specifically, you will learn how to manage a record set within another record set. Using customers and their contact messages as our example, we will loop over all customers and, for each one, display all associated contact records. This technique is essential for handling related data efficiently.

Lessons four through seven focus on the Chef's Kitchen Helper project. This practical example helps you manage recipes, the ingredients they require, and the stock of ingredients available in your pantry. You will develop a query that crosschecks your pantry items against recipe requirements and tells you what dishes you are able to prepare based on current inventory.

Lesson four begins this process with a simple yes or no check for ingredient availability.

In lesson five, we add depth by incorporating quantities. The system will not only check if you have an ingredient, but also whether you have enough of it to make a particular dish.

Lesson six builds on this by transitioning from queries to record sets for the logic behind "what can I make?" scenarios. I find programming with record sets to be more straightforward than crafting intricate queries, and this approach allows us to handle more sophisticated inventory checks by leveraging the nested record set techniques introduced earlier.

Lesson seven culminates in an interactive application. After presenting a list of all meals your current pantry stock can make, you can select a meal to cook. The system then automatically deducts the necessary ingredient quantities from your inventory, reflecting the updated stock levels. This practical method is useful not just for kitchens, but for any situation where products are assembled from multiple components, such as computer parts.

I am teaching these lessons using Microsoft 365 as of 2020, which is largely equivalent to Access 2019. The material should work with versions going back at least to Access 2007. However, if you are working with such an old version, I do recommend considering an upgrade.

Before starting this course, you should already be familiar with the content from my Beginner series, Expert 1 and 2, Developer 1 for some basic VBA, Developer 13 and newer for the concepts covered here, and Developer 16, 17, and 18 for record set knowledge. I will not be repeating introductory material on record sets in this class, so prior understanding is important.

If you have questions about anything covered in today's material, please use the forum available directly on the lessons page to post your questions.

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

Bound multi-select list boxes
Simulating table binding with list boxes
Reading list box selections with record sets
Writing list box selections back to related tables
Nested record sets inside other record sets
Looping through nested record sets for related data
Using record sets to display related table information
Chef's Kitchen Helper project setup
Creating recipe and ingredient tables
Tracking pantry ingredient inventory
Generating queries for available recipes based on pantry
Calculating recipe eligibility with ingredient quantities
Determining possible meals based on inventory levels
Replacing queries with record set logic
Filling list boxes with available meal options using record sets
Implementing a "make meal" feature to update inventory
Reducing pantry stock when making a meal using VBA