Welcome

Welcome! Param Arrays & Product Options

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

Welcome to Microsoft Access Developer Level 42. In this course we will focus on parameter arrays, where I will show you how to pass multiple parameters to functions and use them with VBA, as well as building a database that handles products with customizable options like color and size. We will discuss finding maximum values from fields, building functions for flexible input, using parameter arrays, looping techniques, checking for empty arrays, and creating tables and forms to manage product options. Some lessons will review order entry and invoicing systems, and we will talk about structuring options for inventory management.

Navigation

Prev: D42
Next: Lesson 01

Keywords

Access Developer, parameter arrays, VBA parameter array, passing parameters, array functions, max value from fields, nested IF VBA, product options, inventory management, products with size and color, order entry system, subform options, option tables, Fo

Subscribe to Welcome
Get notifications when this page is updated

Intro

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 that are taking my inventory classes. I have got products like t-shirts where each t-shirt can have multiple options, like different colors, different sizes, and combinations thereof. So 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 and recordsets, Developer 23 and 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. But 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 and the moderators and the other students.

Let us 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 us 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 cool 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 - 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 nothing if the user passes the Nothing value, very, very valuable - whatever you want to call it.

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. So 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 this lesson that 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 us 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. So in this database, we are going to build something like that.

Now, 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. So 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, and price, and then 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 is the main focus of Developer Level 42 in this course?
A. Parameter arrays and products with options
B. Building a customer order form
C. Creating charts in Access
D. Advanced query optimization

Q2. Why is it important not to skip course levels, according to the instructor?
A. Because courses are expensive
B. Because each lesson covers topics needed for the following ones
C. To get discounts on future courses
D. Because skipping confuses Access

Q3. What Microsoft Access version does the instructor use in the video?
A. Access 2003
B. Access 2010
C. Access 365 subscription (2021 equivalent)
D. Access 2016

Q4. What is the purpose of using a parameter array in VBA as discussed in the course?
A. To store data in a table
B. To pass a variable number of values to a function
C. To create charts with multiple data points
D. To filter queries dynamically

Q5. Which previous developer lessons are especially important for this course?
A. Developer 5 and Developer 30
B. Developer 16 and Developer 23
C. Developer 1 and Developer 50
D. Developer 12 and Developer 27

Q6. In lesson one, what is the example used to introduce the concept of finding a maximum value?
A. Calculating tax due
B. Finding the highest quiz grade among three fields
C. Determining customer region
D. Creating an inventory report

Q7. What is the advantage of using nested IF functions in lesson one?
A. To create complex reports
B. To find maximum or specific values among fields
C. To filter records by date
D. To link multiple tables

Q8. When introducing products with options, which of the following examples is specifically mentioned?
A. Electronic devices with warranty options
B. T-shirts with size and color options
C. Books with different editions
D. Software with multiple licenses

Q9. Why does the instructor refer to his first attempt at building a generic options system for products as a failure?
A. The system could not handle colors
B. It was a data entry nightmare for managing inventory
C. It did not allow price tracking
D. It failed when printing reports

Q10. When setting up products with options, which tables are added or modified in the database?
A. Sales and Customer tables
B. Reports and Macros tables
C. Size, Color, and Option tables
D. Supplier and Invoice tables

Q11. For what reason should students review the course page and forum according to the instructor?
A. To download sample Access databases
B. To check for updates, post questions, and see answered questions
C. To buy more courses
D. To see promotional material

Q12. What coding technique is emphasized for working with parameter arrays in lesson three?
A. For Each loop to process array items
B. Static variables within functions
C. Dynamic SQL query strings
D. Do While loops for record navigation

Answers: 1-A; 2-B; 3-C; 4-B; 5-B; 6-B; 7-B; 8-B; 9-B; 10-C; 11-B; 12-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 Microsoft Access Developer Level 42. I am Richard Rost, your instructor for this course.

In this lesson, we are going to cover two major topics. First, I will show you how to use parameter arrays in Access VBA, allowing you to pass multiple parameters around your database. This is a powerful feature that lets you work with a flexible number of arguments in your functions. Second, we are going to look at a common scenario for those managing products that come with various options, such as t-shirts available in different colors and sizes. I know many of you who are following along with my inventory classes have requested this, so we will break down how to manage products with options and their inventory in your Access database.

This is level 42 in the Developer series, so there are several prerequisites. You should have already completed the Beginner, Expert, Advanced, and earlier Developer classes. Developer 16, which covers recordsets, and Developer 23, which focuses on inventory, are especially important for this lesson. The inventory lesson will be particularly relevant to what we are doing today.

I cannot stress enough the importance of not skipping levels. I am not saying that just so you buy more classes, but because each lesson builds on knowledge and techniques taught earlier in the series. If you skip ahead, you might miss key concepts that I assume you already know.

For reference, I am using an Access 365 subscription as of March 2023. This is roughly equivalent to Access 2021, but pretty much everything we are doing in this class will work on any version of Access going back to 2007. If you are watching this a few years down the road, it should still be valid. Be sure to check the course notes on my website, because if there are any updates or changes, I will mention them there.

If you have questions about today's material, just scroll to the bottom of the course page and post your question there. I also recommend taking a look through the questions others have posted, since your question may have already been answered.

If you want to get notified of new questions and comments, make sure to subscribe on the course site. I also encourage you to participate in the Access Forum, where you can interact with me, my moderators, and other students.

Let's take a closer look at the lessons for today's class:

Lesson one is a free bonus lesson. I will show you how to find the maximum value from multiple fields within a single record. This is useful for cases like tracking quiz grades, where you only care about the highest grade of several attempts. We will use nested IF functions here, building on techniques from the Expert series. This sets the stage for using VBA to make it more efficient in the next lessons.

In lesson two, we will take the solution from lesson one further by turning it into a function. Instead of just looking for the greatest of three numbers, I will show you how to build a function that accepts any number of values by using a parameter array. You could pass five, ten, or even fifty values to the function and it will return the maximum.

Lesson three continues with parameter arrays. We will go into some of the more advanced rules and uses. You will see how to loop through the items in the array using a For Each loop, which can be more flexible than a For Next loop. I will also cover how you can pass different types of items through the array, like objects or arrays of fields. We will talk about how to check for empty arrays and what happens if a user passes in the special Nothing value, which can be extremely useful for your code.

Lesson four is another free bonus lesson. If you are a TechHelp member on my website, you might already have seen this. In this lesson, I explain the order entry and invoicing system that we will use as the foundation for the rest of the course. It is a simplified version of the system from the Developer series and is perfect for what we will tackle today. If you have watched this before, you can skip ahead, but if not, I recommend seeing how this database structure works.

Lesson five is where we shift our focus to products with options, like you would find in a clothing database. Here, a shirt could have size and color as options, so you might have the same shirt in small-red, medium-yellow, and so on. I will show you my first attempt to simplify things by keeping all options in a subtable, rather than having fields for size and color. While this approach can work, I found out it can make data entry and inventory tracking much more complicated, so we will discuss the pros and cons here.

In lesson six, I will show you how to properly design new tables for handling size and color options. We will create an options table that stores each combination of product, size, color, price, and the quantity on hand. Then we will build a subform to track and manage all this information for each product.

Lesson seven is the continuation of adding options to products. We will update the order details table and form, and I will walk you through the necessary VBA to allow users to select products and their specific options, keeping everything well organized in your order entry system.

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 to compare values
Creating a VBA function for maximum of multiple values
Using parameter arrays in VBA functions
Passing variable numbers of parameters to a function
Looping through parameter arrays with For Each
Passing different data types to parameter arrays
Checking for empty arrays and the Nothing value
Overview of order entry and invoicing system database
Designing product option tables for size and color
Storing product options with price and quantity
Creating a subform for product options
Updating order detail tables to handle product options
Adding code to select product and its options in forms