Access Developer 42
Parameter Arrays, Product Options (Size, Color, etc.)
Welcome
Today's class is going to focus first on parameter arrays. These allow you to pass an array of values to a function. This will allow you to pass any number of values to a function even if you don't know how many options there are going to be up front.
Then we are going to look at how to handle order entry involving products that have options. For example let's say you run a clothing business and you have a bunch of shirts in your inventory and each type of shirt can have multiple sizes and colors. We'll see how to handle that today.
Resources
Topics Covered
In Lesson 1, we are going to learn how to find the maximum value from multiple fields in the same record. For example, let's say you have 3 quiz grades, and you want to find the maximum grade. To do this we're going to use nested IIF functions, which we know how to do. We've done this in the Expert series but this is building up to something cool we're going to do in the next lesson with VBA.
In Lesson 2 we're going to take the example from Lesson 1 in which we found the greatest of three specific values and we're now going to turn it into a function so we can send any number of values to the function - could be 5, could be 50 - using something called a parameter array.
In Lesson 3 we are continuing with parameter arrays. We're going to learn more parameter array rules. We're going to learn how to loop through the items in the area with a For Each loop (instead of a For Next loop). We're going to see how we can pass different things to the array like objects. You can pass a list of fields to the function. We'll learn how to check for an empty array and the nothing value.
In Lesson 4 is a free bonus lesson. If you are a TechHelp member on my website (Silver, Gold, Platinum) then you may have seen this already, but this is the database that I'm going to use for the rest of today's class. So if you've watched this already, feel free to skip to Lesson 5. If you have not watched this, I explain how I build the order entry and invoicing system from my TechHelp videos. It's a simpler version of the order entry system that we build in the developer classes but it's perfect for the lessons that we're going over today, so watch this if you haven't seen it already.
In Lesson 5 we're going to talk about building a database that has products that have options. For example, let's say you're building a clothing database for a clothing company. You've got shirts that might have a size and a color option. So, it's the same shirt, but it's small and red, right? Medium and yellow, and so on. In this database, we're going to build something like that. Now in Lesson 5, I'm going to walk you through my first failed attempt. I was trying to build a "perfect" system where you didn't have to have specific fields for size and color. They could all be in a subtable with different options. And while this solution works, and I could get it to work, it's going to be a data entry nightmare when it comes to managing the inventory. So, we'll talk about that in this lesson.
In Lesson 6 we're going to build some new tables for size and color options. Then we're going to make an actual option table to store each product's options for size, color, price, and quantity on hand. Then we'll build a subform so we can enter all of the options for each product.
In Lesson 7 we're continuing on with the products with options. We're going to update our Order Detail table and subform and add new code so that we can select the product and then pick the product's options. Pick a product, select the options, click the Add button, and Bam! The item is added to the order.
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, access 2016, access 2019, access 2021, access 365, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, parameter arrays, paramarray, array is empty, lbound, ubound, for each, is nothing, products with options, size, color
Intro In this lesson, you will learn how to use parameter arrays in Microsoft Access, including creating functions that accept a variable number of arguments and looping through those parameters. We will also cover building a product database with multiple options, such as tracking items like shirts with different sizes and colors. You'll see practical examples for handling maximum values, learn about managing arrays, and walk through the process of setting up tables and forms to support products with customizable options. This is level 42 of the Access Developer series.Transcript Welcome to Microsoft Access Developer Level 42, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
Today's class is going to focus on two things: parameter arrays. We are going to teach you how to pass parameters around, which is pretty cool. Then we are going to do products with options, a very popular request by people taking my inventory classes. I have products like t-shirts where each t-shirt can have multiple options like different colors, different sizes, and combinations thereof. We are going to see how to do that in today's class.
This is level 42 of the developer series, so that means there are plenty of prerequisites. You should have finished the beginner, expert, advanced, and developer classes, especially Developer 16 (record sets) and Developer 23 (inventory). Those are very important lessons. The inventory lesson especially is going to be useful for this one.
I strongly recommend you do not skip levels, not because I am trying to get you to buy more classes, but because there is stuff covered in the previous lessons that is important for the following lessons. So do not skip around.
I am using an Access 365 subscription. It is currently March of 2023, so this will be roughly equivalent to Access 2021. Pretty much everything that I have covered in today's class is good for just about every version of Access going back to at least 2007. I cannot see them changing this stuff anytime soon, so if you are watching this in 2026, it is probably still good. Check the course notes down below on the course page, and I will put any notes for any changes down there.
If you have any questions regarding the material covered in today's class, just scroll down to the bottom of the page that you are on right now and post your questions there. Also be sure to take a minute to read through any other questions that have been posted already, as your question may have already been answered.
Be sure to click the subscribe button to get notified if any other questions or comments are posted for this class. Of course, make sure to visit the Access Forum where you can join in conversations with me, the moderators, and the other students.
Let's take a closer look at exactly what is covered in today's class.
In lesson one, which is a free bonus lesson, we are going to learn how to find the maximum value from multiple fields in the same record. For example, let's say you have three quiz grades and you want to find the maximum quiz grade because you are only going to take the best one. To do this, we are going to use nested if functions, which we know how to do. We have done this in the expert series, but this is building up to something we are going to do with VBA in lessons two and three.
In lesson two, we are going to take the example from lesson one, where we found the greatest of three specific values. We are now going to turn it into a function so we can send any number of values to the function, which could be five, could be fifty, using something called a parameter array.
In lesson three, we are continuing with parameter arrays. We are going to learn more parameter array rules. We are going to learn how to loop through the items in the array with a for each loop instead of a for next loop. We are going to see how we can pass different things to the array, like objects. You can pass an array of fields to the function. Then we will learn how to check for an empty array and for nothing if the user passes the nothing value.
Lesson four is a free bonus lesson. If you are a TechHelp member on my website (silver, gold, platinum, etc.), then you may have seen this already, but this is the database that I am going to use for the rest of today's class. If you have watched this already, feel free to skip to lesson five. If you have not watched this, I explain how I build the order entry and invoicing system for my TechHelp videos. It is a simpler version of the order entry system that we build in the developer classes, but it is perfect for the lesson we are going over today. So watch this if you have not seen it already. If not, skip to lesson five.
In lesson five, we are going to talk about building a database that has products that have options. For example, let's say you are doing a clothing database or clothing company. You have shirts that might have a size and a color option. So it is the same shirt, but it is small and red, medium and yellow, and so on. In this database, we are going to build something like that.
In lesson five, I am going to walk you through my first failed attempt. I was trying to build a perfect system where you did not have to have specific fields for size and color. They could all be in a subtable with different options. While this solution works and I could get it to work, it is going to be a data entry nightmare when it comes to managing the inventory. We will talk about that in this lesson.
In lesson six, we are going to build some new tables for size and color options. Then we are going to make an actual option table to store each product's options for size, color, price, and quantity on hand. Then we will build a subform so we can track that for each product.
In lesson seven, we are continuing on with the products with options. We are going to update our order detail table and form and add new code so that we can select the product and then pick the product's options.Quiz Q1. What are the two main topics covered in this class?
A. Parameter arrays and products with options
B. Record sets and inventory management
C. Reports and label printing
D. Data validation and importing data
Q2. Which previous Developer lessons are strongly recommended as prerequisites for this class?
A. Developer 1 and Developer 10
B. Developer 16 and Developer 23
C. Developer 5 and Developer 12
D. Developer 35 and Developer 40
Q3. Why does Richard recommend not skipping levels in the Developer series?
A. Each level is independent and can be skipped
B. Each lesson builds on concepts from previous lessons
C. Earlier lessons are only about history
D. There are frequent sales promotions
Q4. What feature of Access is primarily being demonstrated to handle variable numbers of values in a function?
A. Query design with wildcards
B. Parameter arrays
C. Custom macros
D. Linked tables
Q5. In lesson one, what is the goal when evaluating multiple fields in the same record?
A. Find the minimum value
B. Find the average value
C. Find the maximum value
D. Find the sum of values
Q6. How do parameter arrays improve upon the method used in lesson one?
A. They allow only three values to be evaluated
B. They allow for a fixed range of values
C. They allow any number of values to be passed to a function
D. They disable looping through values
Q7. Which type of loop is mentioned as a way to go through items in a parameter array?
A. While loop
B. Do Until loop
C. For Each loop
D. Switch loop
Q8. In which lesson does the class start building tables for size and color options?
A. Lesson three
B. Lesson four
C. Lesson five
D. Lesson six
Q9. What is identified as a challenge with the instructor's first attempt at building a flexible product options system?
A. It was not compatible with Access 2010
B. It did not allow for custom reports
C. It turned into a data entry nightmare
D. It required too much VBA code
Q10. What is added to the database in lesson seven to improve handling products with options?
A. A new inventory tracking system
B. An updated order detail table and form with added code
C. A new product categories table
D. A custom report wizard
Answers: 1-A; 2-B; 3-B; 4-B; 5-C; 6-C; 7-C; 8-D; 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 42. I am Richard Rost, your instructor.
In this class, the main focus is on parameter arrays and managing products with multiple options in your database. This is especially useful for those of you working with inventory systems and want to offer products that come in different sizes, colors, or other variants, like t-shirts.
Since this is Developer Level 42, there are several important prerequisites. You should have completed the beginner, expert, advanced, and all previous developer courses, with special emphasis on Developer 16, which deals with recordsets, and Developer 23, which focuses on inventory. These lessons are essential for understanding the topics in today's class.
I encourage you not to skip any levels. This is not simply to encourage the purchase of more classes, but because each lesson builds on knowledge from those before it. Skipping ahead will leave you missing key information.
I am using an Access 365 subscription, recorded in March 2023, which should match closely with Access 2021. Everything we cover today is also compatible with older versions of Access, all the way back to 2007. If Microsoft changes anything in the future, I will update the notes on this course's page.
If you have any questions as you go through the material, just scroll down to the bottom of the course page and leave your question there. Take a look at questions and answers already posted – your concern may already have been addressed.
You can also subscribe to receive notifications when new questions or comments come in for this class, and be sure to check out the Access Forum where you can connect with me and fellow students.
Let me break down what we will cover in each lesson today.
The first lesson is a bonus and is available for free. Here, I will show you how to find the maximum value from multiple fields within the same record. For example, if you have three quiz grades and want to record only the highest one, I will demonstrate how to use nested If functions to accomplish this. We have already talked about this method in the expert series, but it sets the groundwork for the VBA topics coming up next.
In the second lesson, we will expand on the first example. Instead of working with just three specific values, we will create a function that can accept any number of values, thanks to a parameter array. Whether you want to pass five values or fifty, parameter arrays make this process very manageable.
Lesson three takes parameter arrays a step further. We will go over the rules for using parameter arrays, discover how to loop through array items using a For Each loop instead of For Next, and see how you can pass objects (like arrays of fields) to your functions. We will also cover how to handle cases where the array is empty or the user passes a value of Nothing.
Lesson four is another free bonus. TechHelp members may recognize this lesson, since it introduces the database I will use for the rest of today's class. Anyone who is familiar with my TechHelp order entry and invoicing system will see a simpler version here, which is perfect for the example we are building today. If you have not watched this lesson before, make sure you do. If you have already seen it, you can skip to lesson five.
Lesson five covers building a database that handles products with multiple options. For instance, we will look at a scenario for a clothing business, where shirts may come in various sizes and colors, and you need to account for all combinations. I will first share my initial attempt at designing a flexible system where all options—such as size and color—are stored generically in a subtable. While this technically works, we will talk through its limitations, especially how it becomes difficult for data entry and inventory management.
In lesson six, we will build new tables specifically for size and color options. Then we will create an option table that stores each product's available options, including size, color, price, and quantity on hand. I will also show you how to set up a subform to manage these options for each product.
Lesson seven continues with products that have options. We will update the order detail table and its associated form, adding new code so that you can select a product and then pick from its available options directly.
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 Finding the maximum value from multiple fields
Using nested if functions for comparisons
Creating a VBA function to find the max from parameter arrays
Passing a variable number of arguments to a function
Using parameter arrays in VBA
Looping through parameter arrays with For Each
Passing different types of data to parameter arrays
Checking for empty or Nothing in parameter arrays
Building tables for products with size and color options
Creating a product options table for inventory management
Designing a subform to track product options
Updating order detail tables and forms to handle options
Adding code to select products and their options in orders |
