Welcome

Welcome! ByRef, ByVal, Active Ctrl, Groups

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

Welcome to Microsoft Access Developer Level 25. In this course we will cover ByRef versus ByVal when passing values to subs and functions, how to determine the active form and control, and incorporating product groups into the order entry system from previous levels. We will discuss creating subroutines for incrementing variables, using optional parameters, returning multiple values from functions, working with active and previous controls, and managing different control types. Additionally, we will walk through setting up and using product groups to quickly add bundled items to orders and format invoices to properly display these groups.

Navigation

Prev: D25
Next: Lesson 01

Keywords

Access Developer, ByRef vs ByVal, passing parameters, active form, active control, product groups, order entry system, increment subroutine, optional parameters, public subroutines, returning multiple values, recordset loop, conditional formatting, combo

Subscribe to Welcome
Get notifications when this page is updated

More Information

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 are going to talk about ByRef versus ByVal passing values to a sub or function. We are going to learn how to determine the active form and control. We are 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. For example, you can have a computer package. It has these components in it. You click one button. Boom. They all go into the current order.

Normally, I do not say that a class has hard prerequisites, but this one kind of does. We built this order entry system in Developer 23 and then we do some more with it in 24. So if you do not at least have 23, you are going to miss out a lot on what we have built in this class. We built a very similar system to this order entry system back in the Expert classes, but this one is much, much better. So if you want to know what is 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 is just the right thing to do.

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

As always, if you have questions, post them down below. Try not to email me unless you have something like 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 have questions about stuff that is not covered in this class, go ahead and post them in the forum.

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

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

In lesson two, we are continuing with ByRef and ByVal. We are 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 will 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 is a field or a number that we are working with. Then, of course, we will talk about public subroutines and functions which we have covered in previous classes so this will be a good review.

In lesson three, we are continuing with ByRef and ByVal. We are 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, whatever. In this lesson, I am going to show you how to have a function return multiple values. We are going to return a status code. We are going to return whether or not a ticket is valid. That is the true/false. Then we will return a status code like the first character is not a T or whatever our validation will happen to be. Then we will also return a third value. We will look up the customer's name and return that too. This is really cool stuff.

In lesson four, we are going to learn about the active form and the active control. We are going to learn how to determine what form and what control the user is currently on. We do not have to send it as a parameter to a function. Then we will 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 are 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 are going to build product groups. What is 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. Then, when you go to put an order in, instead of having to say, okay, I want this part, this part, this part, this part, and manually pick them all, you just pick the product group.

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

In lesson six, we are going to take the product groups that we built in lesson five. We are 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 are then going to go to the invoice. We are going to hide all the zeros because you do not want to show zeros for all the items that are in the group. You just want to show the package price. We will indent those products that are zeros in the package. We will bold the package name. Then, and you do not 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.

Intro

Quiz

Q1. What is the main purpose of discussing ByRef versus ByVal in VBA?
A. To determine how variables are passed to subs and functions
B. To calculate the speed of different functions
C. To create new reports in Access
D. To design user interfaces for order forms

Q2. What happens when you pass a variable ByRef to a subroutine?
A. The original variable can be modified by the subroutine
B. The subroutine receives a copy of the variable and cannot change the original
C. The function does not receive any value
D. Only public variables can be passed ByRef

Q3. What is one use of optional parameters discussed in the class?
A. To allow incrementing by values other than 1
B. To create new tables automatically
C. To change form backgrounds
D. To create drop-down lists

Q4. What is covered regarding public subroutines and functions in this lesson?
A. A review of previously covered content
B. How to insert images into forms
C. Programming multi-user databases
D. Automating backup operations

Q5. How can a function return multiple values in VBA, as taught in the lesson?
A. By using ByRef parameters in addition to the function's return value
B. By returning an array only
C. By printing values directly to a message box
D. By using only ByVal parameters

Q6. What are some example outputs returned by a multi-value function described in the lesson?
A. Validity as true/false, a status code, and the customer's name
B. The current system time and date
C. User's login credentials
D. File names from external sources

Q7. How can you determine the current form and control the user is on?
A. By using the active form and active control properties
B. By searching the database for the open form
C. By scanning all forms in the database
D. By using only static variables

Q8. What is a product group, as explained in this class?
A. A combination of products bundled together for order entry
B. A report showing product sales
C. A team responsible for product design
D. A type of database backup

Q9. What feature is added to the order form for working with product groups?
A. A combo box to pick a product group
B. A query to delete product groups
C. A text field for product notes
D. Automatic emailing of orders

Q10. How does the invoice display items that are part of a product group package?
A. Zeros are hidden, items are indented, bolded, and formatted
B. All line items in a group show a quantity of one
C. Only the group name is displayed, with no details
D. Each item is underlined and numbered

Q11. What version of Microsoft Access does the instructor use?
A. Access 365 subscription (roughly equivalent to 2016/19)
B. Access 2007 only
C. Access for Mac 2023
D. Access Web App edition

Q12. Why does the instructor recommend having taken Developer 23 before this class?
A. The order entry system built in 23 is required for lessons in this class
B. It is needed for setting up backup procedures
C. It teaches about Excel integration
D. It covers Access web publishing fundamentals

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 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 Developer Level 25. I am Richard Rost, your instructor. In this class, we are going to focus on a few advanced concepts in Microsoft Access, including how to pass values to subs and functions using ByRef and ByVal, how to identify the active form and control, and how to implement product groups in our order entry system, which we started building back in Developer 23 and 24. This feature will let us create and work with packages or bundles, such as a computer package made up of multiple components. With a single click, all the selected items from the group can be added to an order at once.

For this class, I strongly recommend having at least Developer Level 23 under your belt since the order entry system we will be working on was started there and expanded in Level 24. While similar topics were covered in some of my prior Expert classes, this version is far more advanced and feature-rich. So to really follow along and get the most out of this session, make sure you have taken those earlier Developer classes. Of course, going through all of my classes is always the best way to build a solid foundation.

Throughout these lessons, I will be using Access 365, which closely resembles Access 2016 and 2019. Microsoft has been changing the way they handle versioning, but 365 is what I recommend because of its regular updates and features.

If you have any questions, remember to post them on the website. I usually respond faster to website posts than to emailed questions. Only email me directly if you need to send screenshots or similar attachments. If your question is about a topic not covered in this class, feel free to post it in the forum instead of in the class comments.

Let's now go over what you can expect to learn in today's course.

In the first lesson, I will discuss the differences between passing arguments ByRef and ByVal to your subs and functions. We will build a subroutine that increases a variable value, and you will see exactly how ByRef allows that variable to be modified outside the subroutine.

The second lesson continues this discussion, showing how you can use optional parameters, so for example, you can increment a value by any number you want instead of just 1. I will also cover how to use these techniques with form controls, such as incrementing a value displayed on a form field. I will explain how to determine whether we are working with a field or a straightforward number. We will also review public subs and functions.

Lesson three is still focused on ByRef and ByVal, but here I will explain how to have a function return multiple values by using ByRef parameters. Up until now, functions you have seen return just a single value, but you'll see how it's possible to return, for example, a status code, a true/false flag for validation, and a customer name lookup all at once.

In lesson four, I will teach you how to find out which form and which control are currently active - information that can be very useful for a wide range of programming tasks. You will see how to work out not just the present control but also which control the user previously interacted with. For instance, if you want a button to operate on the control the user just came from - like multiplying a previous field's value by 10 - this lesson will show you how to accomplish that. We will also go over how to define an object variable representing a control, and how to work with the various types of controls in Access.

Moving on to lesson five, we will build product groups. This handy feature is for anyone who sells products that are often bundled together. Rather than picking each item individually for an order, you can create a product group, then just pick the group when you put together an order.

Lesson six shows you how to add these product groups to your orders with a single action. We will put a combo box on the order form for selecting a product group, and by using a recordset loop, all of the items in that group will be added automatically. When you move to the invoice, I'll show you how to hide items that have a zero price so that only the package price appears. We will also indent these items, bold the package name, and apply some conditional formatting so that packages stand out visually.

All together, this class is packed with advanced techniques and useful features that will make your Access applications much more powerful and user-friendly.

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

ByRef vs ByVal parameter passing in VBA
Building an increment subroutine using ByRef
Using optional parameters with ByRef and ByVal
Applying ByRef techniques to form controls
Identifying and working with public subs and functions
Returning multiple values from a function using ByRef
Validating inputs and returning status codes
Returning additional information like customer names from functions
Determining the active form and control in Access
Accessing the previous control in a form
Declaring and working with form controls as object variables
Working with different control types in VBA
Creating and setting up product groups for order entry
Adding product groups to orders with automation
Updating invoices to display product group packages
Indenting and formatting grouped products on invoices
Applying conditional formatting to product group packages

Primary Topics

VBA ByRef vs ByVal, functions and subs, active form and active control, previous control detection, product groups, order entry enhancements, combo boxes, recordset loops, conditional formatting

Secondary Topics

optional parameters, referencing controls as objects, invoice layout adjustments