Value From Subform
By Richard Rost
4 years ago
How to Get a Value from a Subform in Access
In this Microsoft Access tutorial, I'm going to show you how to sum up a field in a subform and display that on the parent form, instead of using the subform footer.
Andrew from Rochester, New York (a Gold Member) asks: Is there a way that I can sum up a value based on a field in a subform and display that on the parent form, instead of using the subform footer?
Pre-Requisites
Links
Recommended Courses
Syntax
- Fields on a single form: Forms!FormName!FieldName
- Fields on a subform: Forms!ParentFormName!SubFormName.Form!FieldName
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #Error, How to get a subform field value, How to Retrieve Values on a Subform, Pass value between a subform and a main form, parent, How to get a value from subform, value from parent form
Intro
In this video, I will show you how to get a calculated value from a subform and display it on the parent form in Microsoft Access. We will look at different methods for referencing a subform value on the main form, including using control sources, subform syntax, and the DSum function. I will also show you how to hide subform controls and reference data between main forms, subforms, and even parent and child forms using the right syntax.
Transcript
Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I am going to show you how to get a value from a subform in Microsoft Access.
Today's question comes from Andrew in Rochester, New York, one of my Gold members. Andrew says: Is there a way that I can sum up a value based on a field in a subform and display that on the parent form instead of using the subform footer?
Yes, Andrew, that is certainly possible. There are a couple of different ways you can do it. I am going to show you a few different methods.
For everyone else, here is what Andrew is talking about. If you go to my order entry form, you will see here is the extended price which is the quantity times the unit price for each line. Then I sum all of these up and get the order total down here. Now, this is technically the subform's footer, and it is in the subform itself. If you go to design view, you will see that it is in the footer of the subform. What Andrew wants to do is put it out here on the parent form and there are a few different reasons why you might want to do this. It does make it easier for other forms to get a hold of it.
Let me show you a couple of different techniques on how to get that value.
But first, a couple of prerequisites. First of all, if you have not watched my invoicing video, go watch this first. It explains how I built that order entry form. Also, go watch this value from an open form video. This teaches you how to get a value from another open form. This is the notation right here: Forms!FormName!FieldName. For example, Forms!OrderForm!FieldName. Down here, I do put the subform syntax, which is a little more complicated, and this is what we are going to talk about in this video. So, go watch both of those videos if you have not already. They are absolutely free. They are on my website and on my YouTube channel. I will put links down below that you can click on to go watch them.
This is my TechHelp Free Template. This is a free download. If you want to get yourself a copy, you will find a link on that invoicing page.
Now, in the order form, if I want to get a hold of this value and put it out here on the parent form, let's go to design view.
Let me just copy this guy. Click on it - notice what I just did there. Click once and you get the subform control, click a second time to get the object inside. Copy that. Now I am going to click down here and paste it and, yes, it comes up here. That is fine; move it down here.
Now, what is in there? It is Sum of Extended Price. That is the control source. Let's see what happens right now if I just do that. Save this, close it, and open it back up again. I get a pound error. Why is that? This is trying to sum up a field called Extended Price, but there is no field called Extended Price out here on the parent form.
So, I want to tell this text box to get its value from this text box right there. How do I reference that?
Let me come up here and I am going to change this guy here to Order Total. I am going to delete that control source. I will Shift+F2 to zoom in for you.
How do we refer to a value on the subform? First is the subform's name: OrderDetailF. Then: .Form. We are looking for the Form property or the form collection in OrderDetailF, and then what field you want: !SumExtendedPrice.
I will hit OK there. This control is called OrderDetailF. So it is OrderDetailF, then go inside of that, go into its form collection, and get that field. That is how you refer to it.
Looking at the field, I can already see there is a warning message popping up here. It says this control has an invalid control source. What does that mean? No such field in the field list. Hmm, okay, OrderDetailF, check that. Let's make sure we just checked that. Yes, that is definitely OrderDetailF. Then .Form!SumExtendedPrice. Let's check that and make sure. Yes, that is the name of it: SumExtendedPrice.
What is wrong? Why can't it find that? This is a common mistake that lots of people make. You have to remember, if you are referring to another field, it has to start off with an equal sign.
I left that in the video because I do it all the time still, and lots of people email me saying, "Why isn't this working?" You have to have that equal sign. Now the warning goes away.
OK, save it. Close it and open it back up again and there you go. Now, this will work fine as long as you have this calculation in the subform.
If you do not want to see both of them, that is fine. You could just hide this guy, like I have these guys over here: OrderID and OrderDetailID. We do not need to see them, so I just hid them. I did it in the other video. I make them invisible and I will change the color to red so in design view I can see that those are hidden fields. So, you could do the same thing with this guy if you wanted to, or another option is you could get rid of this guy completely and try to do the sum out here by itself.
Let's try that. Let's do the sum function out here. Go back to the control source, zoom in, let's try to sum that guy.
Sum up all of this field on this form. OK, save it, close it, open it back up again, and now that is not going to work. That is not going to work because this guy has no idea what that field is. It has to add them all up and it just does not have enough information.
So, that is not going to work. That only works with fields on the current form you are on. You cannot do that with a subform.
But, you could use one of the domain aggregate functions like DSum. DSum lets you look up values from another table or query.
If you do not know what DSum is, go watch this video. DSum is a cousin of DLookup that lets you look up a value in another table or query based on some criteria. Again, I will put a link to this down below.
How would we use DSum here? Go into here. I am just going to get rid of this.
Equals DSum. Now, what field are you summing up? I am summing up the ExtendedPrice field. Let's take a look to make sure. Yes, ExtendedPrice.
From what table or query is it in? You would think it is in the OrderDetailT, but you would be wrong. It is actually in the OrderDetailQ because we made a query for that. Be careful. In fact, let me cut this and just show you here.
Look here for the control source of that subform. It is OrderDetailQ. So you have to make sure you match that. The field has to be in there because this field is not in the table. It is calculated based on these two guys.
Back in here again.
So, get the ExtendedPrice from OrderDetailQ. What is the criteria? Where the OrderID equals the current OrderID, just like that. Just in case there are none, if there are no records, we do not want an error showing, so we are going to put that inside of NZ. That is the Null-to-Zero function. If this returns no results, it comes back with a zero.
There is also a video on my website for the NZ function. Very popular, go watch that. I think it is mentioned in the DSum video, too.
Hit OK. Save it, close it, open it back up again, and there you can see it is working. We do not have to sum up this field, because this guy is going straight out to the table or query and getting the data.
That is another option. Personally, I like the first option. I like summing the records up here, the value up here in the subform, and if you do not want to see it here, just hide it and then use this guy to read that one.
I have been trying to avoid the lookup and DSum functions lately. They slow things down a lot.
One more thing: If you want to get this guy from a different form completely, let's say you want to put it over here on the main menu and read this value.
Go to design view. Let's just use this guy here: CurrentOrderTotal. Slide you down here. Let's make this not a date, delete that. Let me make this a currency.
How do I refer to this field on this form? What am I looking at? I am looking at Forms!OrderF!OrderTotal.
This should be easy to look up: =Forms!OrderF!OrderTotal.
OK, close this. Close this. Now, it is not going to work when this opens up because it cannot find it, but once we have that order form open, now I can close the main menu and reopen it and there it gets the value.
What if this is in the subform? How do we read it then? Let's put it back.
Let's put this guy back in here. Let me just cut this out and paste it in here. Then we have to change it back to what it was before, which is =Sum([ExtendedPrice]). That is an easy one.
Let's make sure it is working. Yes, it is working; it is in the subform now.
So now, how do I refer to that from the main menu?
Design view, open it up, go in here.
=Forms!OrderF!OrderDetailF.Form!OrderTotal
Now, look at this. See this IntelliSense? This spoils you because when I was learning Access, we did not have this. I wanted to show it to you first with the zoom so you could see it better, but you do not get the IntelliSense in the zoom window. I wish they would add that, but they do not have that. This makes it easier for you to figure out where you are going: Forms!OrderF!OrderDetailF.Form!OrderTotal. Then I will come up here and Shift+F2 so you can see it.
In this instance, since we do not use spaces in our form names or our field names, we do not need brackets around these things, but Access adds them for us anyway.
Save it, close it, open this back up again, and there it goes; it reads that value out of the subform.
One more trick I want to show you: how does the subform get a value from its parent? This is a neat trick, too. Let's move this over here. Let's say, hypothetically, for some reason you wanted to get the description down here in the subform. I will just drop a text box down here, chop off that label.
Now right here, there are two ways you can do it. You can use the form's full name in the control source: =Forms!OrderF!Description.
OK, close it, save it, open it back up again, and you can see it is working.
That is fine. But if you want to use this subform on a couple of different forms, and I sometimes do, you want to make it so that this guy pulls off whatever its parent form is without having to specify OrderF. You can do that by saying, instead of Forms!OrderF, you just say Parent!Description.
Then whatever the parent form is, it goes up one level and finds it. Save that, close it, open it, and there you go.
Any changes up here will reflect down there, but not until you leave that record and come back to it. If you want that to refresh, you have to use something like an After Update event. I will put a link to that down below in the link section. That requires a little bit of VBA programming, but it is not hard. You just say, in the After Update event for this guy, "Requery this subform."
If you want to learn more about this cool subform stuff with the naming conventions and all that, I cover a lot more in Access Expert Level 2. We talk a lot more about that form field name notation. I also do a lot more with subforms in Access Developer Level 7. Lots of cool stuff in these lessons.
In the full courses, I take the time and go over stuff in the order you should learn it, so you are not jumping around between different videos. Fast Tips have to be quick because they are supposed to be Fast Tips, but in the full course, I take my time. This one is an hour and 12 minutes.
So, there you go, Andrew. There is your Fast Tip for today. I hope this helps you out. If you have any questions, post them down below in the comments section. I will see you next time.
Quiz
Q1. What is the primary goal of the video tutorial?
A. To create subforms in Access
B. To sum a value from a subform and display it on the parent form
C. To build a report from scratch in Access
D. To add new records to a table in Access
Q2. Why might someone want to display a sum from a subform on the parent form instead of the subform footer?
A. To prevent errors
B. To make it easier for other forms to access the value
C. To reduce file size
D. To improve sorting
Q3. What is the correct syntax to reference a control on a subform from the parent form?
A. Forms!ParentForm!Subform!Control
B. Subform.Form![Control]
C. SubformName.Form!ControlName
D. SubformControl.Control
Q4. Why did the text box initially display the "#Error" message when copied from the subform to the parent form?
A. The calculation was missing
B. The parent form did not have the referenced field
C. The sum function was incorrect
D. The text box property was set to invisible
Q5. What must be added at the beginning of a control source expression to reference another control or calculation?
A. A dash (-)
B. The word "SUM"
C. An equals sign (=)
D. Curly brackets {}
Q6. Which aggregate function is recommended for summing values from a table or query based on criteria?
A. DLookup
B. DCount
C. DSum
D. DAverage
Q7. When using DSum to look up a calculated field, which object should you reference if the field does not exist in the table?
A. The table with a similar name
B. The subform control itself
C. The query where the calculation exists
D. The main menu
Q8. What does the NZ function accomplish when used with DSum?
A. It notifies if the value is negative
B. It converts null sums to zero
C. It removes formatting from text boxes
D. It sorts the results
Q9. What is a noted disadvantage of using lookup and DSum functions in Access forms?
A. They are prone to errors
B. They do not work with subforms
C. They can slow down performance
D. They require VBA programming
Q10. How do you reference a control on another open form from a different form?
A. =Forms!FormName!ControlName
B. =Parent!FormName!ControlName
C. =Lookup(FormName, ControlName)
D. =Forms[FormName].Controls[ControlName]
Q11. What is the benefit of using Parent!FieldName in a subform control source?
A. It always references the OrderF form
B. It dynamically references the parent form, whatever it is
C. It disables data entry
D. It makes the control invisible
Q12. What must you do if you want changes in the parent form to be immediately visible in the subform?
A. Add a macro to the main form
B. Reopen the parent form each time
C. Use the After Update event to requery the subform
D. Rename both forms
Answers: 1-B; 2-B; 3-C; 4-B; 5-C; 6-C; 7-C; 8-B; 9-C; 10-A; 11-B; 12-C
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 retrieve a value from a subform in Microsoft Access and display it on the parent form, rather than in the subform footer. Andrew from Rochester had a question about summing up a value in a subform and showing that total on the main form, and the techniques shown here will help you with exactly that scenario.
To set the background, think about a typical order entry form. In this example, you might have individual line items, each with an extended price that is calculated by multiplying quantity by unit price. The total for the entire order is usually calculated and displayed in the footer of the subform that lists the line items. However, sometimes you may want to show that total on the main, or parent, form instead. Not only can this make the value more visible, but it can also make it easier for other forms to utilize it.
Before getting started, make sure you understand how these forms are structured. If you have not seen my invoicing video, go watch that first to learn how the order entry form is built. I also recommend checking out my video on getting values from other open forms, where I explain using the Forms!FormName!FieldName syntax. Subform references are a little more complex, and that is what we'll focus on here.
If you are using my TechHelp Free Template, you can follow along with the sample forms as well.
The first method involves copying the control that sums up the extended prices from the subform and placing it somewhere on the parent form. When you do this, you will notice that you cannot just reference the field name directly, because the parent form does not have access to that field by default. You need to tell the text box on the main form to get its value from the correct control in the subform.
The syntax for referencing a control in a subform from the parent form is: SubformControlName.Form!ControlName. For example, if your subform control is named OrderDetailF and your control that sums extended price is called SumExtendedPrice, you would write: OrderDetailF.Form!SumExtendedPrice. One important detail to remember is that the expression must start with an equal sign. It is a common mistake to leave it out, and Access will not recognize the source otherwise.
Once you have this set up, the total from the subform will appear on the parent form. If you want to avoid showing the total in both places, you can hide the control in the subform. I often make these hidden controls stand out in design view by changing their color, so I can keep track of which fields are visible and which are not.
There is another technique you can use. Rather than pulling the total from the subform, you can sum up the records directly on the parent form using a domain aggregate function like DSum. DSum allows you to sum a field across a set of records in a table or query that match certain criteria. In this case, you would sum the ExtendedPrice field from the correct query, making sure to use the right source (usually a query rather than the table itself, especially if your field is calculated). You also want to wrap the DSum function with NZ to ensure that you get a zero if there are no matching records, rather than a null or error.
Personally, I prefer the first option, where you calculate the sum on the subform and then reference it on the parent. Domain aggregate functions like DSum and DLookup can slow things down, especially as your database grows larger, so minimizing their use is a good idea when possible.
If you ever want to retrieve a value from another form entirely, such as showing the order total from the order form on a main menu, you can use the standard Forms!OrderF!OrderTotal syntax. Just remember, the source form must be open when you reference its value. To access a value sitting inside a subform, for example, you would use Forms!OrderF!OrderDetailF.Form!OrderTotal.
A related trick is getting data from the parent form into the subform. There are two ways to do this. You can use the complete form reference, such as =Forms!OrderF!Description, but if you want your subform to be portable and reusable across different parent forms, you can use Parent!Description instead. This syntax automatically pulls the description value from whatever parent form the subform is sitting in.
Keep in mind that certain updates between parent and subform may not automatically refresh unless you leave the record and return to it. If you want to force the subform to refresh in response to a change on the parent form, you can use a little bit of VBA code, like a Requery command in the After Update event.
If you want a more in-depth look at how subforms work, the naming conventions, and how to use these referencing techniques in your projects, I provide a thorough explanation in Access Expert Level 2 and even more advanced subform work in Access Developer Level 7.
The complete video tutorial with step-by-step instructions for everything discussed here is available on my website at the link below. Live long and prosper, my friends.
Topic List
Referencing a subform control from a parent form
Copying a calculated total from a subform to the parent form
Understanding subform control naming and referencing
Fixing invalid control source errors in Access forms
Correct syntax for referencing subform fields
Hiding subform fields on the parent form
Using DSum to sum values from a related table or query
Using the NZ function to handle null values in calculations
Displaying calculated values from a subform on another form
Reading values from a parent form into a subform
Using the Parent keyword to reference parent form fields
Updating subform data when parent values change
|
