Return Multiple Values
By Richard Rost
2 days ago
Using Functions to Return Multiple Values in VBA In this lesson, we will walk through how to return multiple values from a VBA function in Microsoft Access using ByRef parameters. We will create a reusable customer lookup function that returns a Boolean success value while filling address, city, state, zip, and country variables, using a recordset to retrieve the related customer information in one place. Holden from Honolulu, Hawaii (a Platinum Member) asks: On my Order form, choosing a customer means I need to pull in their address, city, state, and ZIP code. I can get each piece separately, but I need the same information in several places and the duplicated code is getting messy. Is there a clean way for one routine to give my code all of those related values? MembersIn the extended cut, we will learn other ways to return multiple values in VBA, including using a Variant array, a Public Type with named members, collections, and a class module. We will walk through using collections as an alternative to ByRef parameters. Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, VBA function multiple return values, VBA ByRef parameters, return multiple values VBA, VBA Boolean function return, VBA recordset lookup, reusable VBA function, DAO Recordset, customer lookup VBA, VBA output parameters, VBA DLookup alternative
More InformationTranscript Ever have one little lookup turn into a whole pile of separate values you need to drag back into your code? Maybe you just want one routine to get all of the related customer information without leaving little bits of logic scattered all over the place.
Welcome to another TechHelp video brought to you by Access Learning Zone. I'm your instructor, Richard Rost.
Today, we're going to talk about how to get multiple values back from a VBA function in Microsoft Access.
Now, a function normally gives you one result, but sometimes one job naturally produces several useful pieces of information. We'll look at a clean, practical pattern for keeping those related values together, checking whether the lookup worked, and making the same code easy to reuse elsewhere in your database.
Today's question comes from Holden in Honolulu, Hawaii, one of my Platinum members. He says, "On my order form, choosing a customer means I need to pull in their address, city, state, and zip code. I can get each piece separately, but I need the same information in several places, and the duplicated code is getting messy. Is there a clean way for one routine to give my code all of those related values?"
Well, yes, Holden, there is. A function can give you a clear success result while also making several related values available to the code that called it. Let's look at how that works and why it's a handy pattern to keep in your toolbox.
Now, normally when you think of a VBA function, you think of it returning one value. You pass something in, it does a little bit of work, and it gives one thing back. But real databases don't always work that neatly.
Let's say we're on the order form. The user picks a customer, and we want to retrieve that customer's address, city, state, and zip code. Sure, we could write four separate DLookup expressions: one for address, one for city, one for state, and one for zip.
And for a quick one-time job, there's absolutely nothing wrong with that. In fact, if you're just using it here, you could load those short text values directly into the customer's combo box. That's what I teach in some of my classes. You put address, city, state, and zip as hidden columns in the combo box, and you drop them right into the order.
You can't do that for long text, but for short text, sure, why not?
But if you need the same customer lookup information on multiple forms or from different buttons, or you've got lots of long text fields, copying all these DLookups all over the place gets messy really fast. It's like keeping four separate sticky notes with different pieces of information in four different rooms of your house.
Don't eat tomorrow. You've got a doctor's appointment. Don't eat tomorrow. You've got blood work. You put messages. I've done it before. Put a sticky note right on the coffee maker: don't drink coffee first thing in the morning, you have blood work. Then you've got to put it all over the place: on the fridge, on the garage fridge, on the cagurator. No, I'm just kidding. No cagurator.
But anyways, eventually one of those sticky notes is going to get lost. So the question is, how can one VBA function give us several related results back?
So here's the key idea. This is where ByRef comes in. If you don't know ByRef versus ByVal, pause the video now and go watch this video. I'm going to talk about some prerequisites in a minute before we get to the code. But this is, go watch this by all means, please.
So technically, a function still has only one normal return value. And in our example, we're going to use that return value as a Boolean. Boolean means it can return True or False. True means we found the customer and successfully got the information. False means we didn't find the customer or the operation failed.
And then for address, city, state, and zip code, we'll use parameters passed ByRef. ByRef means by reference. So instead of handing the function a copy of the value, we're giving it access to the caller's actual variable. So the function can change that variable, and then when the function is finished, the calling procedure sees the changed value.
So conceptually, our function looks like this. GetCustomerInfo takes a CustomerID plus the ByRef variables named address, city, state, and zip. And then it's going to return a Boolean True/False, whether it's successful or not.
So we're not really returning five things through the function name. We're returning one True/False value directly, but we're also filling those named variables with the data that we need. So in practical, day-to-day VBA terms, we're getting multiple outputs from one function call.
Now, there's a few reasons why I love this pattern. First, every output has a meaningful name and data type. So when you see address, city, state, and zip in the calling code, you won't have to decode what position number three looks like. The code tells its own story, which is always nice when you come back six months later and wonder what past you was thinking.
I do that all the time. I look at code that I wrote 10 years ago, and I'm like, "What was I doing?" And of course, 10 years ago, I wasn't very good at commenting my code.
And second, all of the lookup logic stays together. If your customer table changes or if you decide to format something differently, you've got one function to update instead of hunting through forms and button events and random bits of VBA, and you don't know where you put it and who goes where and is it September on a Tuesday with a full moon.
And third, the Boolean return value gives you a clean success test. We can say if GetCustomerInfo returns True, use the values. Otherwise, tell the user the customer wasn't found or some other error occurred.
And just to be clear, this isn't a blanket claim that this is always better or faster than using four separate DLookups. Performance depends on the database, of course, the network, the data, and what else is going on. But the big win here is reusability, readability, and keeping one related lookup operation in one place. You don't have different blocks of code all over your database. You've got one GetCustomerInfo function.
All right, so before we get to the demo today, again, if you haven't watched my ByRef versus ByVal, go watch this. And of course, if you're unfamiliar with VBA, go watch this video first. It's about 20 minutes long. It'll teach you everything you need to get started.
Watch this video. If you don't know how to create your own function, this is the DLookup function, which we're going to use. We're also going to use a recordset to look up data.
If you need a refresher between what a local module is in your form or report and a global module, go watch this, this, this, this visibility, this, this video on variable scope and invisibility. Same thing applies to variables as it does to modules as well.
And if you don't know what my status box is, go watch this video too. These are all free videos. They're on my website. They're on my YouTube channel. Go watch all of these and then come on back.
All right, here I am in my TechHelp free template. This is a free database. You can grab a copy off my website if you want to.
And in here, we've got a customer form. The customer's got an address. Now, on my order form, I don't have an address for where this order was shipped. And that's sometimes handy to store. I've got separate videos where I explain how to do this in detail.
But basically, we need to put those fields in the order table as well. So go to your customer table, design this. We're just going to copy address, city, state, zip. Let me scroll down here a little bit. Right there. We're just going to copy these.
We're going to slide over to the order table design view, and we're just going to paste those right in there. So now I've got address, city, state, zip in my order table as well. So now I know where each order was shipped.
Save that. And now we've got to do the same thing with the fields. So here's the customer form. We're going to Design View. We are going to grab, whoop, I went a bit too far. We're going to grab these right here. Copy, go to my order form Design View. Maybe slide this down out of the way just a little bit, and paste. And there we go.
All right, so now I've got the order fields, excuse me, the address fields on my order form. So now when I pick a customer, I can look that stuff up and drop it in here.
Let's save that, close it, close it, open it. There we go.
Now, the easy way to do it, the simple way, is in the After Update event for this box, just look up those fields and drop them here. But again, you get the problem that I mentioned earlier, where if you've got to do this in several different forms throughout your database, you're going to have duplicated code. And we don't want duplicated code.
So we're going to make one function that handles that for us. So we're going to right-click, go to Design View. I'm going to open this guy up. We're going to go into his After Update event. This is going to fire whenever this is updated.
There we go. It came in really big. Let me just resize it here.
So we're in the customer combo. And the customer combo is bound to CustomerID. So I've got a CustomerID. And like I said, normally we could come in here and say, okay, address equals, we're going to DLookup the address field from a customer table, where the CustomerID equals the customer combo. And we do that for each field.
But then we're going to have it duplicated in multiple places. Plus, DLookup four times, or in my case, five times, we've got address, city, state, zip, and country. That's five separate trips to the table.
We could do it easier with a recordset lookup. With a recordset, we get rid of all these separate DLookups.
So here's what we're going to do. We're going to Dim RS As Recordset. We're going to Set RS Equals CurrentDb.OpenRecordset. What are we looking for? We're looking to SELECT * FROM CustomerT WHERE the CustomerID equals CustomerCombo, and close parentheses.
And if you've got a really big table, when you're all done, change that star to exactly what fields you need. But for now, we just leave it to star.
Now, here I'm going to say, If Not RS.EOF, in other words, if I've got a record, then do some stuff. And If RS.Close, Set RS Equals Nothing.
There's our recordset. Now we put the jelly filling in here.
Address Equals, we're going to check for nulls here too. So Nz, and then RS Address, comma, empty string. That means address is going to be set equal to the address in the customer record. And then if it's Null, make it an empty string.
We're going to do the same thing with city, state, zip, and country. So we just replace these. We've got city, state, zip, and country. Notice how I'm typing them all in lowercase.
Let me just copy, copy, paste, copy, paste.
Save that. Let's Debug Compile once in a while. And let's make sure this works. Let's make sure we're quick on my gas here.
I'm going to go to me, go to my orders, pick me again, and beautiful. What we've got right now is working so far.
Now, I want to take this logic and put it in my global module. So we're going to take all of this. We're going to cut it out. We're going to go over to, let's make some room here. There we go. Come over here, global module. If you don't have one, create one.
And I'm going to come right down here on the bottom. I always put new stuff that I'm working on on the bottom of the module. That way, I can easily get to it just by sliding down the bottom.
So we're going to do the same thing. But we're going to ask for it by CustomerID. And then we're going to send back the values in those fields.
So here's what we're going to say. We're going to say Public Function GetCustomerInfo. Who's the customer we want? CustomerID As Long. And then we're going to send back stuff ByRef.
So, ByRef Address As String, ByRef City As String, next line, ByRef State As String, ByRef Zip As String, and ByRef Country As String. And then this whole function is going to return a True or False value, whether or not it was successful or not, so As Boolean.
And what did I miss? Let's see here. Oh, whoopsie. Don't need that ampersand there. I'm so used to doing string concatenation at the end of a line, I always used to put an ampersand at the end.
So there's our function. We're just going to paste the stuff we just borrowed from the other thing.
Now, one thing I always like to do in my functions before we try to get these values here is I'm going to initialize them. Address equals empty, City equals empty string, State, Zip, Country. Just so we're starting off with empties.
Because remember, it's going to ask us for stuff, and if we don't get it, we want to return back nothing, empty strings.
We're also going to initialize the value of our function. We're going to start it off up here as False. So now if we get into here and there is no customer found, if we send in CustomerID 65213 and it doesn't exist, it'll return back a False saying, "I didn't get it."
But if we come into here and we do get it, then we'll set this to True.
So we ask for CustomerID, come through here, we initialize everything. If we get it, if there is a record, it'll set all this stuff, set that equal to True, and then exit out. And that's it. That's all you need to do, really.
Now, how does this work on the other side? How do we get this? Well, let's save this, and let's Debug Compile.
Oh, variable not defined. Oh, look what happened. My old code is still using CustomerCombo. This guy doesn't know anything about CustomerCombo because it's not on that form. It's a global module. But we're asking for it right here. Good.
So we'll replace that with that. That's why we Debug Compile.
So let's go back to our other code, which is now in here. We're in our After Update event for this guy, After Update event.
So now here all we have to say is, If GetCustomerInfo, which customer, CustomerID. Now we've got to have values to put these, or places to put these things.
Now we have to make strings in here. All right, so to put string values in here to grab all this stuff back, you can't put this right in the form fields.
And I don't like calling them the same things. You don't want to call these address, city, state, because you've got fields on this form named that stuff.
So in this case, what I like to do is Dim AddressString As String. And then we've got CityString As String, StateString As String, ZipString As String, and CountryString As String.
We'll put these two down on the next line.
Now we have variables we can grab these things into: AddressString, CityString, StateString, ZipString, and CountryString.
Now this is going to return a variable, a True or False. If we get that stuff back, then now we can put the string values into those fields. Address equals AddressString, City equals CityString, State equals StateString. Oh, statue. See, that's why we keep everything lowercase, because we're going to watch it capitalize. Zip equals ZipString, and Country equals CountryString.
Otherwise, message box or status, whichever one you use, customer not found, which in this particular case shouldn't happen because we're picking the customer from a combo box. But you never know. The customer might have, if you've got a multi-user database, someone else might have deleted the customer between the time that the combo box loaded and now. There's all kinds of things that can happen.
But let's save it, Debug Compile once in a while. Let's see here.
Close that. Close it, close it, close it. It's open, everybody fresh, ready? Open, open.
Now I'm going to pick, I'll pick William Riker. Oh man, look at that. It filled in his information. And I'll pick somebody else. Let's go back to me and fill my information in.
But now the benefit is we have a reusable function we can use. We can take this and we can put this in other places throughout our database. Just GetCustomerInfo. We're filling these strings in with data, and if we get it back, we put them in the fields.
Every one of these isn't a separate set of DLookups. It's one function that returns those values. See that?
And yes, you absolutely could use four separate DLookups here. And that's how I teach it in my course the first time around, as I'm trying to introduce these topics to new students and keep it simple. There's nothing wrong with that for a simple one-off situation.
But the point of this lesson is to learn another Lego. When you've got some related values and they're all retrieved together, one reusable function keeps all that logic in one place instead of different parts of your database doing different things. And if you add something else in here later on, it's easy to put it in one spot.
Now, are there other ways to do this stuff? Yeah, sure. There's lots of other ways. You could use global variables. They're simple, and sometimes they're exactly what you need. But globals are shared state. That means any procedure that can reach them can change them. And that can make a database harder to follow when it gets bigger.
You could also use TempVars. TempVars are handy when a value needs to be visible across forms, reports, macros, procedures, and they survive a debug issue. If you get an error popping up. And again, useful tool for the right job.
TempVars are also application-wide. They share a state, and a name can be reused accidentally. I've done that myself. And old values can hang around longer than you expected.
And I'm not saying globals and TempVars are bad. I use them all the time. They solve different problems, though. But when you simply want one routine to take an input and hand several related results back to the code that called it, ByRef parameters are a little more self-contained. Everything you need is right there in the function call.
It's like handing someone a labeled folder instead of leaving papers scattered around the office or those sticky notes all over the place like we talked about earlier.
Now, in the extended cut for the members, we're going to talk about a couple of different ways you can do this. The method I just showed you now is one way. There's lots of different ways you can do this, lots of different Legos, lots of different tools for your box.
We can use a Variant array. You can actually pass arrays back and forth. You can use named members. Create a public type. We can use collections. That's my favorite one. We're going to walk through collections in the extended cut. And of course, you could set up a class module for it as well.
There's lots of different ways to do this. And we're going to talk about all of these and walk through one of them in the extended cut.
Silver members and up get access to all of my extended cut videos, not just this one, all of them. There's lots of them. Of course, everybody gets some free training, and it's a big party. So come on and join.
But to wrap today's lesson up, you've got a VBA function that has one direct return value. We used a return value of a Boolean: True when the customer was found, False when it wasn't.
Then we used ByRef parameters as additional outputs for address, city, state, zip, and country because those variables are passed ByRef. The function can fill them in, and then the calling code can use them.
It's a nice, clean pattern where several related values naturally belong together. It keeps your lookup logic, lookup logic. Try saying that 10 times faster. Lookup logic. I just did it.
Anyways, it keeps your lookup logic in one reusable place, makes the calling code easier to read, and gives you a straightforward success or failure.
And remember, multiple DLookups aren't evil. For a simple one-off situation, they're perfectly fine. But this is just another Lego in your VBA toolbox. I'm mixing analogies now, but okay.
When the job calls for it, now you know how to get multiple useful outputs from one function call.
And that, my friends, is going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time, and members, I'll see you in the extended cut. Intro In this lesson, we will walk through how to return multiple values from a VBA function in Microsoft Access using ByRef parameters. We will create a reusable customer lookup function that returns a Boolean success value while filling address, city, state, zip, and country variables, using a recordset to retrieve the related customer information in one place. Quiz Q1. What is the normal direct return value of a VBA function?
A. One value
B. An unlimited number of values
C. Only a recordset
D. Only a string
Q2. In the example, what does the GetCustomerInfo function return directly?
A. The customer's address
B. A Boolean success or failure value
C. A complete Customer table record
D. An array of all customer fields
Q3. What does a True return value from GetCustomerInfo indicate?
A. The customer was found and the requested information was retrieved
B. The customer record was deleted
C. The address fields were cleared
D. The lookup was skipped
Q4. Why are address, city, state, zip, and country passed ByRef to the function?
A. So the function can change the caller's variables
B. So the values are permanently stored in the module
C. So the function runs without a CustomerID
D. So Access automatically saves the form record
Q5. What is the main advantage of using a reusable function for related customer lookup values?
A. It keeps lookup logic in one place and reduces duplicated code
B. It eliminates the need for a Customer table
C. It prevents users from editing order records
D. It makes all fields required automatically
Q6. Why should the output variables be initialized to empty strings at the start of the function?
A. To ensure no old or unwanted values are returned if the lookup fails
B. To force every customer to have an address
C. To convert all fields to numeric values
D. To close the recordset automatically
Q7. Why is the function return value initialized to False before the recordset lookup?
A. So the function safely indicates failure unless a matching record is found
B. So the customer record is deleted if it is missing
C. So the form automatically creates a new customer
D. So all output values become Null
Q8. What does the condition If Not RS.EOF mean after opening the recordset?
A. A matching record was found
B. The recordset has been closed
C. The table has no fields
D. The customer has no address
Q9. What is the purpose of Nz(RS!Address, "") in the lookup code?
A. It converts a Null address value to an empty string
B. It encrypts the address before storing it
C. It checks whether the address is unique
D. It changes the address to a number
Q10. Why must CustomerCombo be replaced with CustomerID when moving the code into a global module?
A. A global module does not know about controls on a specific form
B. CustomerCombo can only be used in table design view
C. CustomerID cannot be used in a query
D. Global modules cannot use recordsets
Q11. Why does the calling procedure use separate variables such as AddressString and CityString?
A. They provide places for the ByRef output values to be stored
B. They automatically bind directly to the Customer table
C. They make the function return multiple Boolean values
D. They eliminate the need to check for success
Q12. Which statement best describes the difference between this ByRef approach and using global variables or TempVars?
A. ByRef outputs are self-contained in the function call, while globals and TempVars are shared state
B. ByRef outputs can only contain numeric values
C. Global variables are always safer than function parameters
D. TempVars can only be used in reports
Q13. Are multiple DLookup calls always wrong for retrieving several fields?
A. No, they can be fine for a simple one-time situation
B. Yes, DLookup should never be used in VBA
C. Yes, Access cannot perform more than one DLookup
D. No, because DLookup automatically creates a recordset
Q14. What should the calling code do if GetCustomerInfo returns False?
A. Handle the failure, such as notifying the user that the customer was not found
B. Assume the address fields contain valid values
C. Delete the current order
D. Set the CustomerID to zero automatically
Answers: 1-A; 2-B; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-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 covers how to return multiple values from a VBA function in Microsoft Access.
A VBA function normally returns one value. You pass information into the function, the function performs some work, and it gives you a result. However, many database tasks naturally involve more than one related piece of information.
For example, when a user selects a customer on an order form, I may want to retrieve that customer's address, city, state, ZIP code, and country. I could use a separate DLookup for each field, and there is nothing inherently wrong with doing that for a quick, simple task. In some situations, especially with short text fields, I can also include those fields as hidden columns in a combo box and copy the values directly into the form.
However, when I need the same customer information in several places throughout the database, repeated DLookup expressions can become difficult to maintain. If the lookup logic changes later, I would have to find and update every form, button, event procedure, or report where I copied that code. That is where a reusable function becomes useful.
The technique I am demonstrating uses a function with a Boolean return value plus several ByRef parameters. The Boolean value tells the calling procedure whether the lookup was successful. The ByRef parameters allow the function to fill in multiple related variables, such as the address, city, state, ZIP code, and country.
Technically, the function still returns only one value through its function name. In this case, that value is True or False. True means the customer was found and the information was retrieved successfully. False means the customer could not be found or the lookup did not succeed.
The additional information is passed back through variables that are supplied to the function ByRef. ByRef means "by reference." Instead of passing the function a copy of a value, I give the function access to the actual variable used by the calling code. The function can change that variable, and when the function finishes, the calling procedure can use the updated value.
In this example, the function accepts a CustomerID as its input. It also accepts ByRef variables for Address, City, State, ZIP, and Country. The function then returns a Boolean value indicating whether the customer record was found.
This gives me a clean and readable design. The calling code can ask for customer information, receive all of the related values together, and then decide what to do based on whether the function returned True or False.
There are several advantages to this approach.
First, every output has a meaningful name and an appropriate data type. When I see variables such as AddressString, CityString, StateString, ZipString, and CountryString in my calling code, I immediately know what they contain. I do not have to remember that item number three in an array represents the state or that item number five represents the country.
Second, all of the lookup logic stays in one location. If I later change the structure of my CustomerT table, rename a field, add a new field, or decide to format the data differently, I only need to update one function. The rest of the database can continue calling that same function.
Third, the Boolean return value gives me a simple success or failure test. If the function returns True, I know the customer data was found and I can place the returned values into the appropriate controls on the form. If it returns False, I can display a message, write to a status box, or otherwise handle the missing customer record.
Before using this technique, it helps to understand the difference between ByRef and ByVal. ByRef allows a procedure to modify the caller's variable directly. ByVal passes only a copy of the value, so changes made inside the procedure do not affect the original variable. Since the purpose of this function is to send multiple values back to the calling procedure, ByRef is the appropriate choice.
It is also useful to understand basic VBA functions, recordsets, DLookup, variable scope, and the difference between form modules and standard modules. In this example, I place the reusable function in a standard global module so that it can be called from multiple forms and other VBA procedures throughout the database.
To demonstrate the idea, I start with a customer table containing address-related fields such as Address, City, State, ZIP, and Country. I then add matching fields to the order table. Storing this information in the order record is often important because a customer's address can change over time. An order should usually preserve the shipping address that was used when that order was created.
After adding the address fields to the order table, I add corresponding controls to the order form. The goal is that when the user selects a customer on the order form, the customer's address information is copied into the order fields.
The simple approach would be to place several DLookup expressions in the After Update event of the customer combo box. Each expression could retrieve one field from the customer table based on the selected CustomerID. That works, but it can lead to duplicated code if I need the same process on multiple forms.
Instead, I use a recordset to retrieve the customer record. A recordset allows me to open the customer record once and read several fields from it. This is generally cleaner than using several separate DLookup expressions when I need multiple values from the same record.
Within the reusable function, I first initialize the output variables to empty strings. This is important because I do not want old values to remain in the calling variables if the customer cannot be found. I also initialize the function's return value to False.
Next, the function opens a recordset based on the CustomerID that was passed in. If the recordset contains a record, the function retrieves the address fields from that customer record. I use Nz when appropriate so that Null values from the table are converted to empty strings rather than causing problems when assigning values to String variables.
Once the fields have been copied into the ByRef output variables, the function sets its return value to True. The recordset is then closed and released properly.
If the customer record does not exist, the function leaves the output variables empty and returns False. This makes the function predictable and safe to use. The calling procedure always knows that a True result means valid information was retrieved, while a False result means it should not use the output values.
Back in the order form's customer combo box After Update event, I create local String variables to receive the information from the function. I prefer names such as AddressString and CityString rather than simply Address and City because the form itself may already have controls with those names. Distinct variable names help prevent confusion between VBA variables and form controls.
The calling code passes the selected CustomerID along with the local String variables to the customer information function. If the function returns True, the code copies those local variables into the address controls on the order form. If the function returns False, the code can notify the user that the customer was not found.
In a single-user database, a missing customer may be unlikely if the user selected the customer from a combo box. In a multi-user database, however, another user could potentially delete or modify a customer record between the time the combo box was loaded and the time the lookup occurs. It is always good practice to account for the possibility that a record may no longer exist.
This approach gives me one central GetCustomerInfo routine that I can reuse anywhere in the database. If I have another order form, a shipping form, a customer summary form, or a button that needs customer address information, I can call the same function rather than duplicating several DLookup expressions or recordset procedures.
I want to emphasize that multiple DLookup expressions are not bad. For a one-time lookup or a beginner-level example, several DLookup statements may be perfectly reasonable and easy to understand. The goal here is not to replace every DLookup in every database. The goal is to add another useful VBA technique to your toolbox.
When several related values belong together and need to be retrieved as a group, a function with ByRef output parameters can be an excellent solution. It improves organization, makes the calling code easier to read, and keeps the underlying lookup logic in one reusable place.
There are other ways to return multiple values in VBA as well. I could use global variables, TempVars, Variant arrays, a Public Type, collections, or a class module. Global variables and TempVars can be useful, but they represent shared state. Any procedure that can access them may be able to change them, which can make larger databases harder to follow and troubleshoot.
TempVars are especially useful when values need to be available across forms, reports, macros, and procedures. They can also survive certain debugging situations. However, because they are application-wide, an old TempVar value may remain longer than expected, or a name might accidentally be reused elsewhere in the database.
ByRef parameters are more self-contained. Everything needed by the procedure is visible directly in the function call. The calling code supplies the input value, supplies variables to receive the output values, and receives a clear True or False result.
Also, in today's Extended Cut, we will look at additional ways to return multiple values from VBA procedures, including Variant arrays, named members with a Public Type, collections, and class modules. Each method has advantages depending on the type of data you are working with and how you plan to use it elsewhere in your application.
The key idea is simple. A VBA function can return one direct value, such as a Boolean success result, while using ByRef parameters to fill in several related output values. In this example, the function returns True when a customer is found and fills variables with the customer's address, city, state, ZIP code, and country. If the customer is not found, it returns False and leaves those output values empty.
This pattern is clean, reusable, and easy to maintain. It keeps related lookup logic together and prevents the same blocks of code from being scattered throughout the database.
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 Returning multiple values from VBA functions
Using ByRef parameters as function outputs
Using a Boolean success return value
Customer lookup with a DAO recordset
Handling Null customer fields with Nz
Creating a reusable GetCustomerInfo function
Calling a ByRef function from a form event
Populating order address fields from customer data Article A VBA function normally returns one value. For example, a function might accept a CustomerID and return the customer's name. However, many database tasks naturally produce several related values at once. When a user selects a customer for an order, you may need the address, city, state, ZIP code, and country together.
You could retrieve each value separately with individual lookup expressions. That is acceptable for a quick, one-time task. However, when the same lookup is needed on several forms, buttons, or reports, repeating those lookups can make your database difficult to maintain. If the table structure changes or you need to adjust the lookup logic, you may have to find and update the same code in many places.
A cleaner approach is to create one reusable function whose job is to retrieve all of the related customer information. The function can return a simple True or False result to indicate whether it succeeded. At the same time, it can fill several variables supplied by the code that called it.
This pattern uses ByRef parameters. ByRef means "by reference." Instead of passing a copy of a variable into a function, the calling procedure gives the function access to the original variable. The function can assign a new value to that variable, and the calling procedure sees the changed value after the function finishes.
For example, a customer lookup routine could accept a CustomerID as its input. It could also accept variables for address, city, state, ZIP code, and country as ByRef parameters. The function would begin by clearing those output variables so that they contain empty strings. It would also start with its own return value set to False.
The function would then search the Customer table for the requested CustomerID. A recordset is a good choice for this because it can retrieve all of the needed fields in one operation. If the customer record is found, the function would copy the customer's address-related fields into the ByRef variables. It should handle Null values safely by converting them to empty strings where appropriate. Once the values have been assigned, the function would return True.
If the customer cannot be found, the output variables would remain empty and the function would return False. This gives the calling code a clear and reliable way to determine whether it should use the retrieved values.
On an order form, the process would work like this. When the user selects a customer, the form creates local string variables to hold the address, city, state, ZIP code, and country. It then calls the customer lookup function, passing the selected CustomerID and those variables.
If the function reports success, the form copies the values from the local variables into the corresponding fields on the order. This allows you to store the shipping address with the order, which is useful because a customer's address may change later. The order should normally retain the address that was used when that particular order was placed.
If the function reports failure, the form can display a message such as "Customer not found" or otherwise handle the missing record. In many cases, this should be rare if the customer is selected from a valid combo box, but it is still good practice to account for it, especially in a multi-user database where records may be changed or deleted by another user.
The main advantage of this approach is organization. The logic for retrieving customer information lives in one place. Forms and other procedures do not need to know how the Customer table is searched or how Null values are handled. They simply call the function, check whether it succeeded, and use the results.
This also makes future changes easier. If you later decide to retrieve an additional field, such as a phone number, email address, shipping notes, or tax region, you can update the reusable function instead of rewriting separate lookup logic throughout the database.
Using multiple individual lookups is not wrong. For a simple one-time situation, separate lookups can be easy to understand and perfectly adequate. The reusable function approach is most useful when the values are related, are commonly needed together, and may be used from more than one location in the database.
There are other ways to share multiple values in VBA. Global variables and TempVars can make values available across forms and procedures, but they are shared application state. Any code that can access them can change them, and older values may remain in place longer than expected. Arrays, collections, user-defined types, and class modules can also be used in more advanced situations.
For a straightforward routine that accepts one input and returns several related outputs to the code that called it, a Boolean return value combined with ByRef output parameters is a clear and practical pattern. It keeps related lookup logic together, makes the calling code easier to read, and provides a simple success or failure test. Primary Topics VBA functions, multiple output values, ByRef parameters, Boolean return values, reusable lookup logic, DAO recordsets, customer address lookup, Microsoft Access forms Secondary Topics DLookup comparison, Nz null handling, global modules, variable naming, Debug Compile, duplicate code avoidance, globals and TempVars comparison |
