Introduction

Welcome! Replace Multi-Valued Fields

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

Welcome to Microsoft Access Developer Level 38. In this course we will cover multi-valued fields in Microsoft Access, including why you should avoid them as a developer and how to replace them with multi-select list boxes. We will discuss how to read and update multi-valued fields with VBA, migrate data into a proper junction table, and handle related errors. We will also work on creating a multi-select list box interface, setting up a customer catalog with multiple images per customer, and explore how to display multiple columns in forms and reports, including functionality for editing images. Prerequisites are strongly recommended.

Navigation

Prev: D38
Next: Lesson 01

Keywords

Access Developer, multi-valued fields, avoid multi-valued fields, multi-select list box, replace multi-valued fields, VBA multi-valued field, record set, junction table, DLookupPlus, Object Invalid or No Longer Set error, multi-column forms, subform image

Subscribe to Introduction
Get notifications when this page is updated

Intro

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 record set programming and a lot of cool stuff. This was not designed 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 record sets. If you do not know record sets 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 record set 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 DLookupPlus 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. So, 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. Of course, 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. Yamanichite 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. So 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 sub-report along with a multiple column reports. You can have multiple columns in your sub-report 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, 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. Why should developers avoid multi-valued fields in Microsoft Access?
A. They make databases prone to data corruption and are difficult to work with programmatically
B. They are too advanced for most users
C. They are not available in Access 365
D. They automatically encrypt your data

Q2. What is the recommended alternative to using multi-valued fields, according to the lesson?
A. Multi-select list boxes with a junction table
B. Single-value drop-downs
C. Lookup fields in the table directly
D. Using Excel spreadsheets instead

Q3. If you are faced with an existing multi-valued field in a database, what does the course recommend?
A. Replace it with a proper junction table and related forms
B. Leave it as-is and ignore the issue
C. Convert it directly to a single-value field
D. Export it to Word and then re-import

Q4. What is usually required to programmatically interact with multi-valued fields in Access?
A. Record set programming, including reading and adding values with VBA
B. SQL-only queries
C. Table macros
D. Manual data entry only

Q5. Why is creating multi-column forms considered a challenge in Access?
A. Access forms do not natively support displaying data in multiple columns like reports do
B. Access cannot display images in forms
C. Forms can only be single-colored
D. Multi-column forms automatically update without any work

Q6. What is used to help display information from a related table, such as sales reps associated with a customer, in a form?
A. The DLookupPlus function
B. The Lookup Wizard
C. A direct table join in the query
D. Hidden macros

Q7. What is the final result of converting an Access multi-valued field into a proper structure?
A. Data is moved to a junction table, allowing for better normalization and programmability
B. Data is only viewable in datasheet view
C. All information is lost
D. The form becomes read-only

Q8. In regards to lesson five and onward, what is the main focus?
A. Displaying multiple images per customer or product in forms and reports
B. Importing Excel customers into Access
C. Adding search filters to customer forms
D. Automating email notifications

Q9. What Access prerequisite knowledge does Richard strongly suggest before taking this class?
A. Knowledge of record sets, as covered in Developer 15 and 16
B. Familiarity with Windows 11
C. Prior completion of any introductory IT course
D. Web development experience

Q10. What extra feature is added in lesson eight of the class regarding images?
A. The ability to click an image to edit, pick a different one, delete, or add new ones
B. Automatic resizing of customer forms
C. Encryption of all picture files
D. Exporting all images to PDF

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 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 38, and I am your instructor, Richard Rost.

In this class, we will focus on two primary topics. First, we will be talking about multi-valued fields in Microsoft Access. Although they are easy to set up and can let users select multiple items, as developers you should avoid using them. They might seem convenient for beginners, but they create problems and are not suited for proper database design. I will explain exactly what multi-valued fields are, why they cause trouble, and introduce a better approach using multi-select list boxes instead.

We will also discuss what steps to take if you already have a database with multi-valued fields, whether you built it yourself long ago or inherited it from someone else. Fixing this situation is not just a matter of tweaking some settings. It requires understanding the underlying structure and writing some code, which we will go through together in this lesson.

The second major topic is setting up multi-column forms. While Access allows you to show images across multiple columns in reports very easily, it was not designed to do this in forms. I will show you my technique to work around this limitation. We will write some recordset programming to lay out pictures in a subform using multiple columns. This takes some creative work, but I will walk you through how to achieve it.

This class has some important prerequisites. I strongly recommend you work through my entire course progression: beginner, expert, advanced, and developer levels. I do not suggest skipping any levels, and I explain the reasons for this in another video about skipping levels. In particular, make sure you have completed Developer 15 and 16, where we discuss recordsets in detail. If you are not familiar with recordsets, you will find some of the material in today's class challenging.

I am using Access 365, which is similar to Access 2021, so the techniques here will be applicable to those versions.

If you have any questions directly related to today's lesson, you can post them on my website at the bottom of the relevant page. I will do my best to answer as quickly as possible. For general Access questions not specific to this class, please make use of the Access Forum.

I will give you a quick overview of what each lesson in today's class covers:

Lesson one introduces you to multi-valued fields. We will examine what they are, how you use them in Access, and most importantly, why they are best avoided. This first lesson is provided as a free bonus.

In lesson two, I will show you how to access the data stored inside a multi-valued field using VBA. You will learn how to retrieve the information by working with a recordset, and we will also look at how you can add items to a multi-valued field with code.

Lesson three will focus on exporting data from a multi-valued field into a well-designed junction table. I will demonstrate how to use my DLookupPlus function to display the relevant data, as it should appear in something like a sales reps box on a customer form. Then, we will write the code necessary to loop through all customer records, extract the multi-valued data, and put it into the junction table. I will also address a common problem you may encounter, the Object Invalid or No Longer Set error, and how to handle it.

In lesson four, we will create a list box that allows for multi-selection, offering a more appropriate alternative to using multi-valued fields in your tables. When you double-click on, say, the sales reps text box on a customer form, a list box will appear showing all possible selections, with current values pre-selected from the junction table. You will be able to make changes and either confirm with an OK button, which saves the data back, or cancel out with no changes.

Lesson five is another free bonus lesson. Here we will set up a catalog of customers, each with multiple images. Although this originated from a student request about a product catalog, I used customers because that data was already available in my database. This lesson acts as a setup for the more advanced material in lessons six and seven.

Lesson six continues with the catalog theme. I will show you how to create a sub-report that uses multiple columns, so you can see, for example, several images for each customer or product.

Lesson seven is the culmination of the previous two lessons. I will show you how to build a form that displays images in multiple columns within a subform. This is not the intended behavior in Access, but with my method, you will be able to present your pictures in a grid layout inside your forms.

Finally, in lesson eight, we will make the multi-column form interactive. You will be able to click on an image to open it in a pop-up form where you can edit the image, pick a new one, delete, or add more pictures as needed.

For a complete video tutorial with step-by-step instructions covering everything discussed here, visit my website at the link below.

Live long and prosper, my friends.

Topic List

Multi-valued fields overview and usage
Why to avoid multi-valued fields
Alternatives to multi-valued fields
Reading multi-valued fields with VBA
Using recordsets to access multi-valued data
Adding items to multi-valued fields via VBA
Converting multi-valued fields to junction tables
Using DLookupPlus with junction tables
Migrating data from multi-valued fields to junction tables
Handling Object Invalid or No Longer Set errors
Building multi-select list boxes as an alternative
Pop-up form to edit multi-value selections
Saving multi-select list box choices to a junction table
Creating OK and Cancel logic for editing selections
Setting up a customer catalog with multiple images
Displaying multiple images per customer in a subform
Building a multi-column subform for images
Programming image selection, editing, and management

Article

Today, I am going to walk you through two essential topics for serious Microsoft Access developers: multi-valued fields and multi-column forms. By the end of this tutorial, you will understand what multi-valued fields are, why you should avoid them in professional database development, and how to handle scenarios where you need to convert or interact with them. Additionally, you will learn a powerful technique to display multiple images in a form using multi-column layouts, even though this feature is not natively supported in forms.

Let us start with multi-valued fields. In Access, a multi-valued field allows users to select more than one value from a list in a single field. This feature is convenient for beginners, but as a developer, you need to understand that multi-valued fields are problematic and should not be used in well-designed databases. The main issue is that they store multiple values in a single column, breaking normalization rules and making your data difficult to query and maintain.

For example, you might have a customer table with a field called SalesReps, set up as a multi-valued field. You can pick multiple sales reps from a list for each customer, and on the surface, this seems useful. However, under the hood, Access actually hides the complexity. The multi-valued field uses something akin to a hidden junction table, but you have very little direct control over it. Trying to retrieve, update, or work with this data using queries or VBA is much harder than if you had set up a proper relationship.

What should you do instead? The recommended solution is to normalize your data using a junction table. This involves creating a separate table that links customers to sales reps, with one record for each relationship. Then, use a multi-select list box on your form to allow users to select multiple sales reps for a customer. When the user saves the record, you update the junction table accordingly.

Let us look at how to interact with existing multi-valued fields. Sometimes you find yourself inheriting a database that already uses them, or you created them before you learned better habits. While working with multi-valued fields in VBA is not ideal, it is possible. Here is how you can read the selected values from a multi-valued field in VBA.

Suppose you have a table named CustomerT with a multi-valued field called SalesReps. To read all selected items, you can use the following VBA code:

Dim mvField As Variant
Dim item As Variant

mvField = Me.SalesReps.Value
For Each item In mvField
Debug.Print item
Next item

This code loops through each selected value in the SalesReps field and prints it to the Immediate window. If you want to add a value to the multi-valued field, you can set the value directly like this:

Me.SalesReps.Value = Array("John","Sally")

However, due to the underlying structure of multi-valued fields, things can get complicated if you need to regularly update this data or perform complex queries.

The next step is converting your multi-valued field into a proper, normalized structure. This process involves creating a new junction table, for example, CustomerSalesRepJunction, with fields for CustomerID and SalesRepID. Then, you need to extract the data from the multi-valued field and populate the junction table.

To do this, you can use a recordset in VBA. Here is an example outline of how that code would work:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsJunct As DAO.Recordset
Dim item As Variant

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT CustomerID, SalesReps FROM CustomerT")

Do While Not rs.EOF
For Each item In rs!SalesReps.Value
Set rsJunct = db.OpenRecordset("CustomerSalesRepJunction")
rsJunct.AddNew
rsJunct!CustomerID = rs!CustomerID
rsJunct!SalesRepID = item
rsJunct.Update
rsJunct.Close
Next item
rs.MoveNext
Loop

rs.Close

This code loops through each customer, reads their list of sales reps, and adds a record to the junction table for every sales rep selected. This approach restores proper normalization and makes future development much easier.

After migrating to a junction table, you can provide users with a familiar interface using a multi-select list box. On your customer form, add a list box with Multi Select set to Simple or Extended. When the user double-clicks a text box or clicks an Edit button, display a form with the list box showing all possible sales reps. Pre-select the reps linked in the junction table for this customer.

When the user saves, your code should clear all existing junction table entries for that customer and add new records corresponding to the selected sales reps. Use the following basic logic:

For Each item In ListBox.ItemsSelected
' Add each selected sales rep to the junction table
Next item

Be sure to handle OK and Cancel buttons, so the display updates or cancels as needed.

Now that we have covered how to replace multi-valued fields, let us switch focus to displaying multiple columns in forms. In reports, Access can natively arrange data in multiple columns, such as for a photo catalog. In forms, however, this is not built in; the default subform control displays records in a single column format.

Suppose you want to create a catalog where each customer or product can have multiple images, displayed in a grid inside a form. To achieve this, you need to use some creative VBA and recordset programming. The key is to prepare a recordset containing all relevant images and then use your VBA code to arrange them as you want. You might have to use an unbound form, dynamically create controls, or manipulate a continuous form in ways beyond the basic setup.

For example, you can create a subform that displays image controls in a grid. When a user clicks on a picture, code can open another form to let them edit, replace, or delete the image. Add buttons for these actions, and update the recordset as needed.

Putting all these concepts together, you now know that properly designed Access databases should avoid multi-valued fields and instead use normalized tables with multi-select list boxes for user-friendly interfaces. When you want to present data in creative layouts, such as a grid of images, careful use of VBA and recordsets allows you to build solutions that go beyond what is provided out of the box.

By following these principles, your Access applications will be robust, maintainable, and much easier to work with as your needs grow and change. If you have specific questions about these techniques, feel free to ask for help or clarification. Happy developing!