Welcome

In this class I cover the ByRef and ByVal keywords for passing values to subs and functions. I'll teach you how you can use ByRef to have a function return multiple values from a function. We'll learn about ActiveForm and ActiveControl so that you can see which form and which field are currently active allowing you to change its value. We'll add product groups to our invoicing system. We built an invoicing system back in Developer 23. In this level we're going to add product groups so you can group stuff together in packages so instead of adding 10 line items every time someone wants to buy one of your bundles you just pick it once and then BOOM! all that goes right up into the order.

Resources

Topics Covered

In Lesson 1, we're going to learn the difference between passing values ByRef or ByVal to your subs and functions. We're going to build an increment subroutine where we send it a variable X and have it increment by one.

In Lesson 2, we are continuing with ByRef or ByVal. We're going to send some optional parameters to our function so you can say "I want to increment by 7 or ten instead of just one." I'll show you how to use it with form controls so you can put a field on a form and use your subroutine with that. Then we'll determine if it's a field or a number that we're working with and then of course we'll talk about public subroutines and functions which we have covered in previous classes so this would be a good review.

In Lesson 3, we're continuing with ByRef or ByVal. We're going to learn how to use ByRef to have a function return multiple values. Up until now functions have always returned one value: a boolean, a string, or whatever. In this lesson, I'm going to show you how to have a function return multiple values. We're going to return a status code. We're going to return whether or not a ticket is valid. That's the true/false. Then we'll return a status code, like the first character is not a "T" or whatever our validation rule happens to be. Then we'll also return a third value. We'll look up the customer's name and return that too. This is really cool stuff!

In Lesson 4, we're going to learn about the ActiveForm and the ActiveControl. We're going to learn how to determine what form and what control the user is currently on so we don't have to send it as a parameter to a function. Then we'll learn how to access the previous control so if you want to use a button, say you want to click on a field and then hit multiply by 10, the button can then figure out where the user came from. What was the previous control you were on? tThen instead of sending the name of the control we're going to actually dim a control itself as an object variable and learn how to work with different control types.

In Lesson 5, we're going to build product groups. What's a product group? Well if you sell products that you want to bundle together and put a package together then you can create a product group. Now, when you go to put an order in instead of having to say "I want this part this part, this part, this part," and manually pick them all, you just pick the product group. Then in the next lesson, lesson 6, we'll click one button and it will automatically add the group and put all the line items in for you.

In Lesson 6, we're going to take the product groups that we built in lesson five we're going to add a combo box under our order form so you could pick a product group. It will add the product group up top into the order using a Recordset loop. Of course we're going to then go to the invoice. We're going to hide all the $0.00 items because you don't want to show zeros for all the items that are in the group.You just want to show the package price. We'll indent those products that are zeros in the package. We'll bold the package name and then - and you don't see it in the screenshot - but I also add some conditional formatting so those packages will show up in orange.

Enroll Today

Enroll now so that you can 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.

Comments for Access Developer 25

Age	Subject	From
5 years	Lesson 1 in Dev25	Graham Hendricks

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

Intro

In lesson 25 of Microsoft Access Developer, we will explore the difference between ByRef and ByVal when passing values to subs and functions, including using optional parameters and returning multiple values from a function. You'll learn how to determine the active form and control, and work with various control types. The tutorial also covers building product groups for an order entry system, allowing you to quickly add bundled items to orders and update your forms to support product packages and customized invoice formatting.

Transcript

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

In today's class, we're going to talk about ByRef versus ByVal passing values to a sub or function. We're going to learn how to determine the active form and control. We're going to build product groups into our order entry system that we built in Developer 23 and 24. This way we can put together packages and bundles, so you can have a computer package. It's got these components in it. You click one button. Boom. They all go into the current order.

Normally I don't say that a class has hard prerequisites, but this one kind of does. We build this order entry system in Developer 23 and then we do some more with it in 24. So if you don't at least have 23, you're going to miss out a lot on what we've built in this class. We built a very similar system to this order entry system back in the Expert classes, but this one's much, much better. So if you want to know what's going on, make sure you take at least 23. And of course, get all the rest of my classes too. Do it for your health. Do it for your country. And do it for the world. I mean, it's just the right thing to do.

I will be using Access 365. I've got a subscription, which is roughly equivalent to 2016/19. Who knows what Microsoft is doing with the versions nowadays? Get a 365. It's much better.

As always, if you have questions, post them down below. Try not to email me unless you've got to send me screenshots or something like that because I prefer stuff going through the website. I really do. I will answer your question faster if you send it through the website. If you've got questions about stuff that's not covered in this class, go ahead and post them in the forum.

Let's take a look at what's covered in today's class.

In lesson one, we're going to learn the difference between passing values by ref or by val to your subs and functions. We're going to build an increment subroutine where we send it a variable x and have it increment by 1, and we'll see how by ref works.

In lesson two, we are continuing with by ref and by val. We're going to send some optional parameters to it so you can say, I want to increment by 7 or 10 instead of just 1. I'll show you how to use it with form controls so you can put a field on a form and use your subroutine with that. I will determine if it's a field or a number that we're working with. Then, of course, we'll talk about public subroutines and functions, which we have covered in previous classes, so this will be a good review.

In lesson three, we're continuing with by ref and by val. We're going to learn how to use by ref to have a function return multiple values. Up until now, functions have always returned one value, a Boolean, a string, whatever. In this lesson, I'm going to show you how to have a function return multiple values. We're going to return a status code. We're going to return whether or not a ticket is valid - that's the true or false. Then we'll return a status code, like the first character is not a T, or whatever our validation will happen to be. Then we'll also return a third value - we'll look up the customer's name and return that too. This is really cool stuff.

In lesson four, we're going to learn about the active form and the active control. We're going to learn how to determine what form and what control the user is currently on. We don't have to send it as a parameter to a function. Then we'll learn how to access the previous control. If you want to use a button, say you want to click on a field and then hit multiply by 10, the button can then figure out where the user came from. What was the previous control you were on? Then, instead of sending the name of the control, we're going to actually dim a control itself as an object variable and learn how to work with different control types. Lots of cool stuff in lesson four.

In lesson five, we're going to build product groups. What's a product group? Well, if you sell products that you want to bundle together and put like a package together, then you can create a product group. Then when you go to put an order in, instead of having to say, I want this part, this part, this part, and this part, and manually pick them all, you just pick the product group.

In the next lesson, lesson six, we'll click one button, and it will automatically add the group and put all the line items in for you automatically. In this lesson, we're going to set up the product groups.

In lesson six, we're going to take the product groups that we built in lesson five. We're going to add a combo box onto our order form so you can pick a product group. It will add the product group up top into the order using a record set loop, of course. We're going to then go to the invoice. We're going to hide all the zeros because you don't want to show zeros for all the items that are in the group - you just want to show the package price. We'll indent those products that are zeros in the package. We'll bold the package name. Then, and you don't see it in the screenshot, but I also add some conditional formatting so those packages will show up in orange.

This is a cool lesson.

Quiz

Q1. What is the main topic of Microsoft Access Developer Level 25?
A. Introduction to SQL in Access
B. Working with ByRef and ByVal, active form/control, and product groups in an order system
C. Designing reports in Access
D. Creating Access database backups

Q2. What does passing a value ByRef to a subroutine or function do?
A. Makes a copy of the value and passes it
B. Passes the reference, allowing the subroutine to modify the original variable
C. Prevents any change to the value
D. Encrypts the value before passing

Q3. What is one of the goals in building product groups into the order entry system?
A. To export orders to Excel
B. To allow users to add packages or bundles of products to an order with one click
C. To import data from other databases
D. To automate sending order emails

Q4. Which prerequisite is strongly recommended before taking this class?
A. Access Beginner Level 1
B. Developer Level 23
C. Word Expert
D. Excel Power User

Q5. How does the course suggest determining the active form and control?
A. By opening Access in debug mode
B. Using VBA code to check which form and control are currently active
C. Manually tracking them in a spreadsheet
D. By email notification

Q6. In lesson two, what additional feature is introduced when incrementing a value?
A. Multiplication only
B. Optional parameters for different increment amounts
C. Only decrementing allowed
D. Automatic error correction

Q7. What does using ByRef allow a function to do in lesson three?
A. Return only one value
B. Return multiple values by modifying parameters sent to it
C. Hide error messages
D. Encrypt the function

Q8. What is a practical example of using ByRef to return multiple values as taught in lesson three?
A. Returning just a True/False status
B. Returning a status code, ticket validity, and customer name
C. Performing mathematical calculations only
D. Sending emails automatically

Q9. In lesson four, why is it valuable to know the previous control the user was on?
A. To log user behavior for analytics
B. To allow a button to act on the field the user last interacted with
C. To increase application security
D. To print all controls

Q10. What is the purpose of adding product groups in lessons five and six?
A. To speed up adding multiple related products to an order
B. To change the color scheme of the order form
C. To backup order data automatically
D. To enable emailing invoices

Q11. What visual enhancements are applied to the invoice for product groups in lesson six?
A. Highlighted totals and green borders
B. Hiding zeros, indenting zero-amount products, bolding package names, and using conditional formatting
C. Only changing font to cursive
D. Adding barcodes to every line

Answers: 1-B; 2-B; 3-B; 4-B; 5-B; 6-B; 7-B; 8-B; 9-B; 10-A; 11-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 Developer Level 25 for Microsoft Access. In this class, I focus on advanced topics that build directly on the order entry system completed in Developer Levels 23 and 24. If you have not taken at least Developer 23, this lesson will be difficult to follow, since we are extensively expanding and enhancing the system from that class. While a similar system was developed in the Expert series, this version is much more sophisticated. I highly recommend taking the earlier Developer classes first, both for your understanding and for best results as you proceed.

Throughout this course, I will be working with Access 365, which generally matches the features available in versions 2016 and 2019, though Microsoft is always updating their software. I strongly suggest using 365 for the best experience.

If you have any questions, please submit them using the website rather than by email, unless you need to include a screenshot or something similar. I answer questions quicker through the website, and there is also a forum for general questions that fall outside the scope of this class.

Here is a rundown of what we will cover in today's lessons.

The first lesson introduces the difference between passing values ByRef and ByVal to subs and functions. We'll create an increment subroutine where you can send a variable and increase its value, demonstrating how ByRef works in practice.

Lesson two builds on that concept. We expand the subroutine to accept optional parameters, so instead of always incrementing by one, you can specify the increment value. I'll also show how this can be applied to form controls. Depending on whether we're working with a field or a number, the subroutine adapts accordingly. This lesson also provides a refresher on public subs and functions, concepts we've covered in prior classes.

In lesson three, we look at how ByRef allows a function to return multiple values. Up to now, a function would typically return a single value, such as a Boolean or a string. Here, I demonstrate returning multiple pieces of data from one function. We'll have the function provide a status code, indicate whether a ticket is valid, and look up the customer name at the same time. This is an excellent technique for more advanced validation and feedback scenarios.

Lesson four explores how to identify which form and control are currently active in your application. Rather than passing form or control names as parameters, you'll learn how to directly find which form and control the user is interacting with. This lesson also covers accessing the previously active control, which is helpful if you want your code to respond based on where the user was last. Instead of referring to controls by name, we use object variables for controls, which then gives us the flexibility to work with different control types.

Lesson five moves into building product groups. A product group lets you bundle several items together as a single package. For example, if you sell a computer that comes with various components, you can set up a product group so that with a single selection, all components are added to an order. This streamlines order entry for these common packages or bundles.

In lesson six, we connect the concept of product groups to actual order entry. After setting things up in lesson five, we add a combo box to the order form, allowing you to pick a product group. Selecting a group will automatically populate the order with all items in the group by looping through a record set. Then we enhance the invoice display: zero-value line items (the components included in the package) are hidden, so you only see the package price, making the invoice clearer. We also indent these items beneath the package, bold the package name, and apply conditional formatting to make packages stand out.

This entire set of lessons will significantly enhance your order entry system's capabilities and user experience. 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

Passing values ByRef vs ByVal in VBA
Building an increment subroutine using ByRef
Using optional parameters in subroutines
Applying subroutines to form controls
Identifying data types for subroutine parameters
Review of public subroutines and functions
Returning multiple values from a function with ByRef
Returning status code, validation, and customer name
Determining the active form and control in Access
Accessing the previous control used by the user
Declaring and manipulating control variables as objects
Working with different control types
Creating product groups for order entry systems
Setting up product group bundles for orders
Adding a combo box for product group selection
Automatically adding grouped products to orders with VBA
Hiding zero-amount line items on invoices
Indenting and formatting package items in invoices
Applying conditional formatting to highlight product groups