|
||||||
WelcomeWelcome! Replace Multi-Valued FieldsWelcome 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. NavigationKeywordsAccess 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
More InformationTranscriptWelcome 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. IntroWelcome 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. QuizQ1. 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. SummaryToday'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 ListMulti-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 Primary Topicsmulti-valued fields, multi-select list boxes, replacing multi-valued fields, junction tables, recordset VBA, multi-column forms, subforms, image display Secondary Topicserror handling, DLookupPlus usage, user interface enhancements |
||
|
| |||
| 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 PermaLink How To Replace Multi-Valued Fields and Create Multi-Column Forms in Microsoft Access |
