Introduction

Welcome! Bound Multi-Select Lists & Nested Data

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Developer Level 19. In this course we will discuss working with bound multi-select list boxes, handling nested record sets, and building a Chef's Kitchen Helper project to determine which recipes can be made based on available ingredients. After covering record set basics, we will walk through reading and writing selections between forms and tables, working with multiple record sets at once, and progressing from simple to advanced approaches for recipe management using both queries and record sets. Prior experience with topics from Developer Levels 1 and 13 through 18 is recommended before starting this course.

Navigation

Prev: D19
Next: Lesson 01

Keywords

Access Developer, bound multi-select list box, nested record sets, list box selection, chef's kitchen helper, recipe ingredient query, pantry inventory, loop through customers, subform alternatives, VBA recordset, component inventory, make meals from pant

Subscribe to Introduction
Get notifications when this page is updated

Intro

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. And then a fun project, the chef's kitchen helper.

Lesson 1 is about bound multi-select list boxes. Now, 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 - laptops, computers, hard drives, all that stuff. Now, normally you would set this up with a form and a subform, but in a subform you really cannot see the entire list of options and then pick them.

You can see here in the example. Here is what I did 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. Then, lesson 3 is going to cover nested record sets. It is having a record set inside of a record set and you are going to loop between them.

The sample that we will do in class is we have customers and their contact messages. We will loop through all of our customers and then we will 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, we are going to do a thing called the Chef's Kitchen Helper. Here is how it works. You have got a list of recipes - pancakes, chicken parm, whatever. You have got ingredients for those recipes. So chicken parm might include chicken breasts, marinara sauce, parmesan cheese, and so on.

Then we will have another table that has all of the ingredients that I have in my pantry. We want to be able to generate a query that will say, 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. So in lesson 5 we are going to make the query say, okay, I have got this much of it. I have got this quantity on hand. I need this much to make this dish. What can I still make?

Lesson 6. We are going to take everything that we did in lesson 5. Delete it. Not everything - I am just kidding. We are going to take the queries that we built, and instead of using queries, we are going to do this with record sets, which is what we are learning in class.

So 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. So we are going to use the nested record set concepts that we learned in lesson 3 and make nested record sets to do our "What can I make?" And then we will fill a list box with the available meals.

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

You make one chicken parm. It removes the pasta, the marinara sauce, the parmesan cheese, the chicken breast. These techniques, by the way, work whether you are selling food in a kitchen or whether you are selling computers. You have got a product that is made of components. This stuff will work.

I am using Microsoft 365, and it is currently 2020, so roughly equivalent to the Access 2019 retail set. Although the stuff that I am covering in this class should work all the way back as far as I can think. At least 2007. But 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 on 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 it in the form. 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 primary advantage of using a multi-select list box instead of a subform for tracking customer interests in Access?
A. You can see all options at a glance and select multiple items more easily
B. It is faster to build forms with subforms
C. Subforms allow for better data validation
D. List boxes automatically update related tables with no coding required

Q2. In the video, why are the multi-select list boxes referred to as 'bound' even though they are not technically bound to the table?
A. Because they automatically reflect changes in the table structure
B. Because they are physically linked to the table's fields
C. Because they are programmed to read from and write to the table using record sets
D. Because all controls in Access are considered 'bound' by default

Q3. How are the changes made in the multi-select list box saved back to the related table?
A. By using VBA code to write values using record sets
B. By Access automatically saving all list box selections
C. By saving the selections inside the form's properties
D. By dragging and dropping values into the table

Q4. What concept is taught in Lesson 3 of the class?
A. Creating aggregate queries
B. Nested record sets (a record set inside another record set)
C. Using macro actions for automation
D. Importing data from other databases

Q5. In the Chef's Kitchen Helper project, what is the primary purpose of checking which recipes can be made?
A. To create a list of available meals based on pantry ingredients
B. To track sales of kitchen appliances
C. To generate nutritional information for meals
D. To automate online recipe searches

Q6. In Lesson 5 of the Chef's Kitchen Helper, how is the query improved from Lesson 4?
A. By adding support for recipe sorting
B. By taking into account the quantity of each ingredient available and needed
C. By using multi-select checkboxes for recipes
D. By connecting to online grocery stores

Q7. Why does the instructor prefer using record sets over complex queries in Lesson 6?
A. Record sets are easier to program and more flexible
B. Queries cannot handle large amounts of data
C. Record sets require no knowledge of VBA
D. Queries are only available in Access 2021 and later

Q8. What happens in Lesson 7 when you "make" a meal in the Chef's Kitchen Helper?
A. The related product components are removed from pantry inventory
B. The recipe is automatically duplicated in the recipe table
C. The meal is emailed to the customer
D. The quantities of all recipes are set to zero

Q9. Which previous lessons are recommended before taking this Developer Level 19 class?
A. Beginner, Expert 1 and 2, Developer 1, Developer 13 and higher, Developer 16, 17, and 18
B. Only Beginner and Expert 1
C. Only Developer Level 18
D. There are no prerequisites

Q10. For which versions of Microsoft Access should the material in this class work?
A. Anything from Access 2007 and up
B. Only Access 365
C. Only Access 2022 or later
D. Only Access 2016 and earlier

Answers: 1-A; 2-C; 3-A; 4-B; 5-A; 6-B; 7-A; 8-A; 9-A; 10-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 is Developer Level 19, where I am your instructor, Richard Rost. In this class, I cover three main areas. We start with bound multi-select list boxes, then talk about nested record sets, and finish up with a fun Chef's Kitchen Helper project.

In the first lesson, I explain how to set up bound multi-select list boxes. While these list boxes are not technically bound to a table, I call them "bound" because they will behave as if they are. Imagine you have customers and you want to track their interests, such as laptops, computers, hard drives, and so on. Normally, you might use a form with a subform to track this, but subforms make it hard to see all options at once and select them easily.

With the method I show in this class, you will be able to display all possible options in a list box, letting the user select multiple interests at a glance. Although this list box is not directly tied to the customer table, we will use record sets to read the customer's current interests and write any changes back to the related table once the user updates their selection. This approach gives the same effect as having a bound control, just with more flexibility and a better interface.

Lesson two builds on these concepts, preparing you for the next big topic: nested record sets.

In lesson three, I cover nested record sets, which means having one record set inside another and looping through both. As a sample, I use a setup with customers and their contact messages. The goal here is to loop through each customer and then display all their contacts, teaching you to manage multiple record sets at different levels.

From lessons four to seven, we build the Chef's Kitchen Helper project. This involves recipes, ingredients, and pantry inventory. For example, you might have a chicken parmigiana recipe that requires chicken breasts, marinara sauce, parmesan cheese, and more. We start by building tables for recipes and their ingredients, as well as for what you have in your pantry.

Lesson four focuses on generating a query that tells you, given the ingredients in your pantry, which dishes you can make. This is a simple yes or no query - can I make it or not? In lesson five, we add more detail, making the query analyze exact quantities. With this, we check how much of an ingredient you need for a recipe and compare that to what you have on hand, refining your list of possible dishes.

In lesson six, I show you how to set aside the queries we have been using and accomplish all these checks using record sets instead. In my experience, record sets are easier to work with in code than trying to manage increasingly complex queries. Here, you will use the nested record set techniques from earlier to work out which meals you can make, then display those options in a list box.

The reason for all this becomes clear in lesson seven, where we let you pick from the list of all the meals you can make. When you choose a meal and click to "make" it, the program automatically subtracts the necessary ingredients from your inventory. For example, if you make one chicken parm, the needed ingredients get removed from your supply list. This same approach works for other scenarios as well, like managing computer components in a product inventory.

For this class, I am using Microsoft 365, which is similar to the Access 2019 retail version. All the techniques I teach should work in versions going as far back as 2007, although if you are still using such an old version, you might want to consider upgrading.

Before taking this course, I strongly recommend that you have already completed my Beginner series, Expert 1 and 2, and at least Developer 1 for VB basics. You will also need to be familiar with the concepts introduced in Developer 13 and up, especially Developer 16, 17, and 18, which cover the basics of record sets. I will not be reviewing those fundamentals here, so it is important to have that background.

If you have questions about anything in today's class, there is a form below the lessons page where you can post your questions, and I encourage you to use it for any discussion related to this class.

Now, get comfortable and start with lesson one. 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 in Access
Tracking customer interests with list boxes
Comparing subforms vs multi-select list boxes
Reading list box values with record sets
Writing selected list box values to related tables
Using record sets to update related data
Creating nested record sets
Looping through customers and their contacts
Chef's Kitchen Helper project overview
Setting up recipes and ingredients tables
Listing available recipes based on pantry ingredients
Querying recipes based on available ingredients
Calculating quantities for recipes and available stock
Replacing queries with record sets for logic
Updating pantry inventory after making a recipe

Article

Welcome to this developer-level Microsoft Access tutorial where we will be covering three advanced topics: how to create bound multi-select list boxes, how to work with nested record sets, and how to build a practical Chef's Kitchen Helper project to track recipes and ingredients in Access.

Let us begin by discussing bound multi-select list boxes. In Microsoft Access, multi-select list boxes are extremely useful when you want a user to be able to select multiple options from a list at once. This is particularly helpful if, for example, you want to track a customer's interests such as laptops, computers, hard drives, and so on. The standard method in Access would be to use a form and subform, where the subform lists all the customer's interests with one entry per interest. The limitation with subforms, however, is that you typically have to select each item individually, often using a dropdown, which is not the most user-friendly way to present a large range of options. It is much easier for users to see all choices at once and pick multiple items quickly.

Although the native multi-select list box in Access is not "bound" in the traditional sense to a table (meaning it does not automatically update the data source), we can make it behave as if it were bound. The approach is to use VBA to read and write the user's selections between the list box and the related table. To do that, after the user makes their selections in the list box, we will use a record set in VBA to update the related records in our junction table, which connects customers to interests.

For example, suppose you have a Customers table, an Interests table, and a CustomerInterests junction table. The Customers table has fields like CustomerID and Name. The Interests table has InterestID and InterestDescription. The CustomerInterests table links them together using CustomerID and InterestID. On your form, you can add a list box that displays all the interests from the Interests table and allow the user to select multiple items with the Multi Select property set to Simple or Extended.

To populate the list box, set its RowSource to a query like:

SELECT InterestID, InterestDescription FROM Interests;

Then, in your form's code, when a customer is selected, you want to read their currently selected interests from CustomerInterests and mark those items as selected in the list box. You can do that with code similar to this in the form's On Current event:

Dim rs As Recordset
Dim i As Integer
Set rs = CurrentDb.OpenRecordset("SELECT InterestID FROM CustomerInterests WHERE CustomerID = " & Me.CustomerID)
For i = 0 To Me.lstInterests.ListCount - 1
Me.lstInterests.Selected(i) = False
Next i
Do While Not rs.EOF
For i = 0 To Me.lstInterests.ListCount - 1
If Me.lstInterests.Column(0, i) = rs!InterestID Then
Me.lstInterests.Selected(i) = True
End If
Next i
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

When the user updates their interest selections and you want to save the changes, you will need to delete the existing interests for that customer and add the ones that are now selected. Here is how you can do it in the After Update event of the list box, or with a separate save button:

Dim db As Database
Dim i As Integer
Set db = CurrentDb
db.Execute "DELETE FROM CustomerInterests WHERE CustomerID = " & Me.CustomerID

For i = 0 To Me.lstInterests.ListCount - 1
If Me.lstInterests.Selected(i) Then
db.Execute "INSERT INTO CustomerInterests (CustomerID, InterestID) VALUES (" & Me.CustomerID & ", " & Me.lstInterests.Column(0, i) & ")"
End If
Next i

Set db = Nothing

With these methods, you can make your multi-select list box behave just like a bound control, even though it is not directly connected to the table.

Next, let us look at nested record sets. A record set in Access is a way to loop through the records in a table or query using VBA. Sometimes you want to loop through one set of records and, for each of those, loop through another related set. This is called nesting record sets.

Suppose you have a Customers table and a Contacts table, where each contact is a message left by a customer. You want to go through all customers and for each one, list all their contacts. Here is an example of how you would do that in VBA:

Dim rsCustomers As Recordset
Dim rsContacts As Recordset
Set rsCustomers = CurrentDb.OpenRecordset("SELECT CustomerID, Name FROM Customers")
Do While Not rsCustomers.EOF
Debug.Print "Customer: " & rsCustomers!Name
Set rsContacts = CurrentDb.OpenRecordset("SELECT Message FROM Contacts WHERE CustomerID = " & rsCustomers!CustomerID)
Do While Not rsContacts.EOF
Debug.Print " Contact: " & rsContacts!Message
rsContacts.MoveNext
Loop
rsContacts.Close
Set rsContacts = Nothing
rsCustomers.MoveNext
Loop
rsCustomers.Close
Set rsCustomers = Nothing

This technique is useful in many scenarios where you have related tables and need to process or display child records for each parent record.

Now, let us dive into a practical project, the Chef's Kitchen Helper. The idea is to have a system for managing recipes and ingredients. You might have recipes like pancakes or chicken parmesan, and each of those recipes has a list of required ingredients, such as chicken breasts, marinara sauce, parmesan cheese, pasta, and so on. In addition, you need a table that lists all the ingredients you currently have in your pantry, possibly with the quantity on hand.

The goal is to build a query or procedure that will compare your available ingredients with the requirements for each recipe and tell you which dishes you can make right now. Initially, you can use a simple query approach to return a yes or no answer for each recipe. For full functionality, you will eventually want to account for the quantity of each ingredient and determine how many servings of each recipe you can make with your current stock.

In the next step, instead of just handling whether you have an ingredient or not, you will check the quantity on hand versus the quantity required for each recipe. For example, if you have 4 chicken breasts and a recipe needs 2, you can make two servings. Your logic should calculate how many times you can make each recipe with the ingredients available.

After that, to make the code more flexible and powerful, you can move from using saved queries to using record sets and VBA code. This gives you much better control and lets you use all the programming skills you have been developing. The pattern here is to use a nested record set approach: for each recipe, loop through its ingredients, check the available quantities using another record set, and determine whether the recipe can be made or how many servings can be prepared. Once you have figured that out, you can display a list of dishes the user can prepare in a list box on the form.

Finally, you want the user to be able to select one of the recipes and "make" it, meaning you deduct the required amounts of each ingredient from your pantry inventory. If you choose to make chicken parmesan, your code will subtract the specified amounts of chicken breasts, marinara sauce, parmesan cheese, and pasta from your pantry. This basic system works not just for food and recipes, but in any scenario where you are managing products made from components, such as computers built from hardware parts.

All this work can be done in Microsoft 365 or Access 2019, and the techniques should work for versions as far back as Access 2007. If you are using a much older version, you may find certain features missing, and I would recommend upgrading.

This tutorial assumes you already have solid experience with Access VBA, especially with record sets. If any of these concepts are new to you, it would be good to review the basics of Access VBA and record sets before trying to program these solutions.

If you have specific questions as you work through these projects, there are many Access forums and communities online where you can ask for help. Now that you know the process, try building a multi-select bound list box, experimenting with nested record sets, and designing a simple recipe inventory management system with your own sample data. This is a fantastic way to deepen your practical Access skills and solve real-world problems.