Welcome

This class has two primary goals. First, we're going to learn how to work with Multivalued Fields. Now, yes, I know I've been telling you from the beginning not to use them. They're evil. However you may have already used them, or you may encounter a database that someone else has built that used them. In either case, I'm going to teach you how to programmatically access the data in those fields and to move it over to a properly relational second table, and then use a multi-select listbox to handle them properly.

Then we will take a look at a problem that one of my students asked me about. It's very easy to display multiple images side by side (in columns) in a report, but that same functionality doesn't exist for forms. If you want to create an "image gallery" it's very difficult. But... I'll show you how to do it in today's lesson.

Resources

Topics Covered

In Lesson 1, we will learn about multivalued fields (MVF), what they are, how to use them, and why you should avoid them at all costs. Yes, they're on my Evil Access list.

In Lesson 2, we will learn how to read the values from an MVF using a recordset loop. We'll also see how to programmatically add values to a multivalued field.

In Lesson 3, we will replace the MVF with a proper junction table. We'll learn how to loop through all of the customer records and export the MVF data to the junction table. We will create a text box to display the items as a comma separated list, like an MVF does.

In Lesson 4, we will learn how to create a popup form with a multi-select list box to modify the data in the new MVF replacement (junction table). You could do this just with a list box on the main form, but I like the popup. :)

In Lesson 5, we will learn how to create a product catalog showing multiple images per product. Yeah, we're going to cheat and use a customer form, but the concept is the same whether it's products, customers, employees, properties, or whatever you want. It's basically displaying multiple images per record.

In Lesson 6, we will learn how to display the products in multiple columns in the report using a subreport.

In Lesson 7, we will learn how to display the images in multiple columns in a subform. This isn't as easy as it sounds. There's no setting for this and Access can't handle it natively. This will involve some fancy recordset programming, a temporary table, and several events... so I hope you watched Developer 16. We're essentially creating an "image gallery" in our Access forms.

In Lesson 8, we'll add some embellishments to our image gallery. We'll add buttons to add, edit, delete, and browse through the images. We'll create a popup form to handle most of that. We'll learn how to use a global function as an event handler instead of using event procedures all the time. We'll learn about passing variables to functions as variants so they can accept null values.

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, image gallery, multivalue fields, mvf, object invalid or no longer set, multiple images per record, multiple columns in a form, function as an event handler, event procedure, passing variants to allow null values

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

Intro

In this lesson, you will learn about the pitfalls of using multi-valued fields in Microsoft Access, why developers should avoid them, and how to replace them with proper relational structures using multi-select list boxes and junction tables. I will show you how to work with multi-valued field data in VBA, migrate that data to a junction table, and enhance your forms with user-friendly features. We will also cover setting up catalogs with multiple images per customer, including how to display those images in multi-column reports and forms, and add image-editing functionality. This is Access Developer Level 38.

Transcript

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

In today's class, we are going to cover two main topics. First, we are going to cover multi-valued fields. I know they are backwards on the screen. I flipped them around because they fit better that way. The pictures did. Multi-valued fields are first.

We are going to learn what multi-valued fields are and why you should avoid them. Like the plague, they are easy to set up. They are there for beginners to be able to pick multiple items, but you do not want to use them as a developer. They are bad. They are bad, bad, bad.

I am going to teach you an alternative using multi-select list boxes. Then we are going to spend some time learning how to replace multi-valued fields if you encounter one in a database that maybe you built earlier or someone else gives you and you have to fix it.

Making a multi-valued field is kind of crazy. It requires some code. We are going to go over it in today's lesson.

Then we are going to cover multi-column forms. It is getting pictures to display in a subform in multiple columns. You could do it easily in reports, but you cannot do it in a form unless you know my trick. It is going to involve some recordset programming and a lot of cool stuff. This was in design to do it, but we are going to make it do it. That is covered in today's class.

This class, of course, has prerequisites. I strongly recommend you have taken all my previous classes: beginner, expert, advanced, developer. I do not recommend you skip levels. If you want to know why, watch this video on skipping levels.

I especially suggest you have taken Developer 15 and 16, where I cover recordsets. If you do not know recordsets and how they work, you will be lost in today's class.

I am using Access 365. I have a subscription roughly equivalent to Access 2021 right now.

If you have questions pertaining to today's class, feel free to scroll down to the bottom of the page that you are on and post them right on my website. We will do our best to answer your questions as soon as we can.

If you have other questions that are not particularly pertaining to today's class, but you want to ask them anyway, go ahead and head over to the Access forum.

I will take a quick look at exactly what is covered in today's lessons.

In lesson one, we are going to learn about multi-valued fields: what are multi-valued fields, how to use them, and why you should avoid them. This is a free bonus lesson.

In lesson two, we are going to learn how to read the data in a multi-valued field with VBA, so you can get at that information that is hidden inside of that multi-valued field. We are going to learn how to read it with a recordset, and then how to add an item to it.

In lesson three, we are going to be pulling that data out of the multi-valued field and putting it into a proper junction table. We will use my DLookup Plus function to display the data that should be shown in the sales reps box on the customer form. Then we will write the code to actually loop through all of the customer records and rip out that data and put it properly in the junction table. We will see how to deal with the object invalid or no longer set error.

In lesson four, we are going to create a multi-valued field list box that is multi-select. When we double-click on our sales reps text box on the customer form, it pops this guy up. It will automatically select the records that are in the junction table. We can change it if we want to. Then we will make OK and Cancel buttons. If we hit OK, it saves those records back to the junction table and updates the customer form. If we cancel, it does not do any of that.

Lesson five is a free bonus lesson. We are going to set up a product catalog. Instead of product catalog, it is going to be a customer catalog. It is going to basically be a customer with multiple images per customer. I had a TechHelp video where one of my students asked me to do a product catalog where it is a product with multiple pictures of a product. I did it with customers because that is the database that I had available. Lesson five is going to be that and it is going to be a setup for something a little more advanced in lessons six and seven.

In lesson six, we are continuing with our product catalog. We are going to set up a subreport along with multiple column reports. You can have multiple columns in your subreport under each customer or a product catalog or whatever you decide to do.

Lesson seven has been what the last two lessons have been building up to. I am going to show you how to build a form with multiple columns in it. In other words, we are going to display multiple images in a subform that look like they are in multiple columns. You will see what I am talking about in just a minute.

In lesson eight, we are continuing with our multi-column form. We are going to make it so we can click on a picture and it opens up another picture that lets you edit that picture. You can pick a different one, you can delete it, you can add new ones, all that stuff. All will be covered in lesson eight.

Quiz

Q1. What is the main reason you should avoid using multi-valued fields as an Access developer?
A. They are difficult to set up
B. They are designed for beginners and cause normalization problems
C. They support only text data
D. They cannot be used in forms

Q2. What is the better alternative to using multi-valued fields in Access according to the lesson?
A. Single-value fields
B. Multi-select list boxes with a junction table
C. Combo boxes
D. Multiple tables for each value

Q3. What will you learn to do with VBA in lesson two of this class?
A. Create new tables
B. Read and add data in a multi-valued field
C. Create navigation forms
D. Import data from Excel

Q4. When converting a multi-valued field to a junction table, what key function is used to display sales reps on a customer form?
A. DSum
B. DLookup Plus
C. DSN
D. DFirst

Q5. What error may you encounter and learn to fix when moving data from a multi-valued field to a junction table?
A. Divide by zero error
B. Object invalid or no longer set error
C. Syntax error in query
D. Permission denied error

Q6. What is the purpose of the OK and Cancel buttons in the multi-select list box form?
A. OK reloads the database, Cancel closes Access
B. OK saves selected records, Cancel discards changes
C. OK prints the form, Cancel deletes records
D. OK exports the data, Cancel imports records

Q7. What is the focus in lesson five of the class?
A. Building a customer form using combo boxes
B. Setting up a customer catalog with multiple images per customer
C. Importing data from a spreadsheet
D. Adding reports to the navigation pane

Q8. What special trick will you learn in this class regarding forms and images?
A. How to display a single image in a report
B. How to display multiple images in multiple columns in a subform
C. How to export images to Excel
D. How to filter images by file name

Q9. Which prior Developer lessons are especially recommended before taking this class?
A. Developer 1 and 2
B. Developer 10 and 11
C. Developer 15 and 16
D. Developer 5 and 6

Q10. In lesson eight, what additional functionality is added to the multi-column form?
A. Printing records as PDFs
B. Clicking on an image to edit, add, or delete it
C. Filtering customers by city
D. Grouping records by sales rep

Answers: 1-B; 2-B; 3-B; 4-B; 5-B; 6-B; 7-B; 8-B; 9-C; 10-B

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 covers Microsoft Access Developer Level 38. I'm Richard Rost, your instructor for this course.

In this class, our main topics are multi-valued fields and multi-column forms. I want to start by talking about multi-valued fields. These might seem attractive because they allow users to pick more than one value from a list, and Microsoft made them easy for beginners to use. However, from a developer's standpoint, you really want to avoid using them. They introduce complications and are not considered best practice for relational database design.

Instead of relying on multi-valued fields, I'll show you a better method using multi-select list boxes. We'll go through how this alternative offers you a lot more control and flexibility. We will also look at what to do if you come across a database that already uses multi-valued fields. Whether you created it in the past or you inherit someone else's work, I'll explain how to transition away from multi-valued fields to improve your database structure. Working with these fields is a bit tricky and involves some VBA programming, so we'll spend time on that process.

The second major topic is multi-column forms. Typically, it is straightforward to display data in multiple columns in a report, but forms do not have that functionality natively. I have a method that lets you display pictures in a subform across several columns, using some creative recordset programming. This feature is not included in regular Access forms, but I'll show you how to implement it step by step in class.

Before proceeding, there are some prerequisites for this course. I strongly encourage you to take all the previous levels: beginner, expert, advanced, and developer. Skipping levels is not recommended, as each builds on the last. For a more detailed explanation, I have a separate video about why following the order is so important. For today's content, you should especially be comfortable with recordsets, which are covered in Developer Levels 15 and 16. If recordsets aren't familiar to you, today's class will be difficult to follow.

Currently, I'm using Access 365, which for my purposes is comparable to Access 2021.

If you have questions that relate directly to today's material, you can post them directly on my website below the video. For any general Access questions, the Access forum is the right spot.

Here's a breakdown of the lessons included in today's class:

Lesson one gives an introduction to multi-valued fields. I explain what they are, how you might use them, and importantly, why it's best to avoid them as a developer. This lesson is available for free as a bonus.

In the second lesson, we look at how to access the data within a multi-valued field using VBA. I'll show you how to use a recordset to read what's stored there, as well as how to add items to these fields programmatically.

Lesson three explains the process of extracting the data from a multi-valued field so it can be stored in a proper junction table, which is the preferred technique in a well-structured database. I'll demonstrate how to use the DLookupPlus function to display the right information in the customer form. We'll also work through writing the code required to loop through all customer records and move the data into the proper junction table, tackling errors such as "object invalid or no longer set" along the way.

Lesson four goes through the creation of a multi-select list box that connects to our junction table. When you double-click the sales reps text box on the customer form, a form will appear displaying all available records from the junction table, allowing you to select or unselect them. OK and Cancel buttons will ensure you only save changes when intended.

Lesson five shifts focus to creating a customer catalog, which is a variation on a product catalog, but using customers with multiple images each. I originally developed this approach for a student who wanted a product catalog with several pictures per product, but here we're applying it to customers. This will prepare us for more advanced techniques in the following lessons.

In lesson six, we continue with our catalog example by setting up a subreport that can show multiple columns in your reports. You'll be able to present several images beneath each customer or product, grouped appropriately.

Lesson seven brings the previous two lessons together, as I demonstrate how to create a form that displays images using multiple columns inside a subform. This lets you present visuals in a grid-like arrangement within your form.

Lesson eight wraps up our work with multi-column forms. Here, we'll make it possible to click on any image and open a form to edit that image. This form allows you to choose a new picture, delete the current one, or add new images as needed.

You can find a complete video tutorial with step-by-step instructions covering all of these topics on my website at the link below. Live long and prosper, my friends.

Topic List

What multi-valued fields are in Access

Why multi-valued fields should be avoided

Using VBA to read multi-valued field data

Adding items to multi-valued fields with VBA

Extracting data from multi-valued fields to a junction table

Using DLookup Plus to display related data

Looping through customer records to migrate multi-valued data

Handling 'object invalid or no longer set' errors

Creating a multi-select list box for related records

Synchronizing list box selections with a junction table

Implementing OK and Cancel button logic for list box forms

Setting up a customer catalog with multiple images

Creating subreports with multiple columns

Building forms to display multiple images in columns

Enabling image selection and editing in multi-column forms

Allowing image addition and deletion in the image editor form