Internal Field List
By Richard Rost
3 years ago
Understanding a Form's Internal Field List in Access
In this Microsoft Access tutorial, I'm going to teach you how to update a form's internal field list so that when you add fields to an underlying table or query, the form can then recognize them.
Allan from Roseville, California (a Platinum Member) asks: I thought you said as long as a field was in a form's underlying table, you could Access the value of that field from VBA without having to put it as a control on the form. I added a couple of new fields to a table, and I'm trying to reference them in calculations in my code, but I'm getting "Variable Not Defined" errors. What's the deal?
Prerequisites
Recommended Courses
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, form internal field list, update form field list, add fields to form, variable not defined, form field variable, can't access form field variable from vb code
Intro In this video, we will talk about the internal field list that Microsoft Access forms use to track the fields in their underlying table or query. You will learn when you can reference these fields in VBA code without adding controls to the form, why errors may occur after adding new fields to the source table, and how to update the form so Access recognizes new fields. We will also discuss some quick solutions for handling these field reference issues when working with forms and VBA in Access.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, we are going to talk about the internal field list that forms keep so they know what fields are in the underlying recordset, the underlying table or query, when you can use them, when you cannot use them, and what you can do to fix a situation where you need to use one but you cannot. I will explain.
Today's video comes from Ellen in Roseville, California, one of my Platinum members. Ellen says, I thought you said as long as a field was in a form's underlying table, you could access the value of that field from VBA without having to put it as a control on the form. I added a couple of new fields to a table, and I am trying to reference them in the calculations in my code, but I am getting variable not defined errors. What's the deal?
Ellen, you are generally correct, and that is usually the case, but you identified the problem right here. You said, I added a couple of new fields to a table to the underlying table, and let me show you what happens in that case and how to fix it.
First, this is a developer-level video. What does that mean? If you do not know VBA and you want to learn it and you have not done any programming in VBA yet, go watch this video. It is about 20 minutes long. I teach you everything you need to know to get started. Go watch that and come on back.
Let's say you got a copy of my TechHelp free template. This is a free database. You can download it on my website, and I have a customer form right here. There are a bunch of fields on my customer form: first name, last name, email, whatever. Let's hijack this button here. Let's go into the build event, and let's say instead of opening that form there, I just want to message box first name.
The first thing to notice is that 'first name' camel cases. The N and the F capitalize automatically because Access sees that as a field on the form. Save it, close it, open it back up again, click the button, and there is the first name. Now, that is going to work even if first name is not on the form. Save it, close it, open it. There is no first name here, but it still works.
Why is that? That is because when you create this form or you update the recordset or a number of other times, Access actually keeps an internal field list inside this form's properties. So it knows what all the fields are in this form.
If you go back to the table and you add a field, let's call it 'new field'. Short text is fine. We can put some data in this field in the table right over here, 'stuff' and 'things'. We have 'things' and 'stuff' in a field called 'new field', right there, 'new field'.
Go back here. Now it is not on the form. If I say message box new field, the first thing that should tip you off is that it did not camel case. The N and the F did not capitalize like they should have. If I come back over here and hit the button, there you go, variable not defined.
Why is that? Because VBA does not know that field was added to the table. In fact, note the Access team, you might maybe add that. I do not know, we will see. But how do you get it to recognize it?
Let's stop this. All you have to do is come in here. If you want to have that field on the form, you can put it on the form. There you go, and it will work. Or you can put it on the form and have it hidden, make it invisible. But here is an easier thing to do.
Come in here, design view, go to the form's properties, see where it says CustomerT, drop this down and just pick CustomerT again. You really did not change anything. But when you do that, at that moment, Access updates its internal listing of all the fields that are in the table or query underneath that form.
Save it, close it, open it, click the button. Look at that, stuff and things. Because Access updated its internal record source, it now knows that that table field exists. If you come over here, look at that, it camel cased.
You can also update that by adding the field to the form. If you want to, you can delete it, whatever. Just updating the record source does that.
If for some reason you do have to do that programmatically, if you are constantly adding new fields to tables, in the form's open event, you can come up here, and in the form's load event, you can say Me.RecordSource = "CustomerT" or whatever. At that moment, it will reload the record source. That is overkill usually. If you only add a field once in a while, then it is not a big deal.
That is why that happens. I have had that happen to me a couple of times. Before I really understood this, when I figured this out a few years ago, I was always like, why can I not get that field to work in my calculations or in my VBA code? That is why. Sometimes it sees it and sometimes it does not. You have to update the form, that is all.
It is these little tricks like this that I cover a lot of in my developer lessons. I teach you how to be a professional developer from the ground up and cover all these weird situations and instances like this.
Believe me, we build a database, a customer order entry, invoicing database from scratch starting with lesson one. As we go through, we come up with a whole ton of these situations like this, and I teach you how to get around them all.
So if you are scratching your head, if you are trying to learn how to do something, my developer lessons, check them out. There is the link right there. You can scan this pretty little QR code.
There you go. There is your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I will see you next time.Quiz Q1. What is the main topic discussed in the video tutorial?
A. How to use queries within subforms in Access
B. How forms keep track of internal field lists and what happens when fields are added to the table
C. How to secure records in Access forms
D. How to design modern user interfaces in Access
Q2. Why did Ellen encounter "variable not defined" errors when referencing new fields in her VBA code?
A. She used the wrong data type for the new field
B. The new fields were not part of the form's internal field list yet
C. She deleted the fields accidentally
D. The fields had invalid names
Q3. After adding a new field to a table, why might Access VBA not immediately recognize that field on a related form?
A. VBA requires a reboot after table changes
B. The internal field list of the form does not automatically update for newly added fields
C. Access restricts the use of new fields in VBA for security reasons
D. A form must always display every field it uses
Q4. What is one sign that Access recognizes a field from the underlying table in VBA?
A. The field name turns red
B. The field name auto-capitalizes (camel cases) as you type it
C. The field appears in bold in the form's property sheet
D. The field is underlined in the VBA editor
Q5. What is one way to update the internal field list in a form after adding new fields to the table?
A. Change the form's view to Layout view
B. Re-select the record source in the form's properties
C. Restart the Access application
D. Compact and repair the database
Q6. How can you make a new field accessible in VBA without adding it as a visible control to the form?
A. You cannot
B. Make the field a primary key
C. Add the field to the form and set its visible property to False, or update the record source so the field list refreshes
D. Use a macro to insert the field
Q7. What happens if you programmatically set Me.RecordSource = "CustomerT" in the form's load event?
A. Access will permanently lock the record source
B. The form will update its internal field list each time it loads
C. The database will be compacted
D. The field will be deleted from the table
Q8. If you only occasionally add fields to your tables, what is the recommended way to make those fields appear in the form's internal field list?
A. Automatically set the record source every time the form loads
B. Manually refresh the record source in the form properties after making changes
C. Delete and recreate the form
D. Uninstall and reinstall Access
Q9. According to the video, what does Access do when you change and then re-select the record source in the form's properties?
A. It resets all form data to default values
B. It updates the internal field list so new fields are recognized
C. It deletes existing controls from the form
D. It hides all fields not on the form
Q10. What level of user is this video intended for?
A. Beginners with no database experience
B. Anyone with an interest in Access security
C. Developer-level users familiar with VBA
D. IT professionals specializing in cloud security
Answers: 1-B; 2-B; 3-B; 4-B; 5-B; 6-C; 7-B; 8-B; 9-B; 10-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 TechHelp tutorial from Access Learning Zone focuses on how Microsoft Access forms maintain an internal field list based on their underlying table or query. I want to help you understand when you can reference fields from your table or query in VBA code without explicitly placing them as controls on the form, why things sometimes do not work as expected, and what steps to take if you encounter problems accessing those fields.
This question comes from a student who recently added new fields to a table. After making these additions, she tried referencing the new fields in her VBA code and ran into "variable not defined" errors. She thought that if a field existed in the form's underlying table, she should be able to reference it directly in code, even if it does not have a corresponding control on the form.
Her understanding is generally correct, but there is a catch. When you modify the table by adding new fields, Access does not automatically update the form's internal field list. Let me break down how and why this happens.
First, let me remind you that this is a developer-level topic. If you are just getting started with VBA and are unfamiliar with programming in Access, I recommend you begin with a beginner tutorial on the basics of VBA before continuing with this topic.
Let's consider an example using my TechHelp free template, which is available for download on my website. Suppose you have a form displaying customer data, with several fields like first name, last name, and email. When you write VBA code referencing "FirstName," you might notice that Access automatically capitalizes the field name—this is a sign that Access recognizes "FirstName" as a valid field in the form's recordset. Even if you remove the FirstName control from the form, you can still reference it in your VBA code, and it will work correctly because the form recognizes the field from its record source.
The reason for this behavior is that Access keeps an internal list of field names associated with the form based on the record source—the table or query underlying the form. This allows you to reference any of those fields in your VBA code, regardless of whether there is a visible control for the field.
However, after you add a new field directly to your table, such as "NewField," and try to reference it in your VBA code right away, you may notice that Access does not automatically capitalize the name. When you try to use it, you get a "variable not defined" error. This is because Access does not yet realize that a new field has been added to the underlying table—the form's internal field list is outdated.
To fix this, you have a few options. The most direct way is to refresh the form's record source. Go into the form's design view, open the property sheet, and reselect the underlying table or query as the record source. Even though you are not changing the record source, simply reselecting it prompts Access to update its internal list of fields. After saving the form and reopening it, Access will now recognize the new field, and your VBA code referencing it should work as expected. You will also notice the field name now automatically capitalizes in your code, confirming Access has acknowledged its existence.
Another method is to add the new field as a control on the form, even if you set it to be invisible. This also causes Access to update the internal field list. Additionally, if you frequently add new fields to tables and want to ensure your forms are always up-to-date, you could set the form's record source programmatically in the form's load event, effectively forcing Access to refresh the field list every time the form opens. For most users, this is not necessary unless you are modifying table structures often.
Understanding this detail is important for anyone working extensively with form-based applications in Access. It can save you a lot of frustration, especially when troubleshooting why calculations or references in your VBA code are not working as intended. These behind-the-scenes mechanisms are just some of the topics I cover in depth in my developer-level courses, where I walk you through building advanced Access databases and solving issues like this as they arise.
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 Access form internal field list explained
Referencing underlying table fields in VBA
Adding new fields to a table and VBA access
Recognizing missing camel casing as a field indicator
Forcing Access to update form field list
Refreshing the form's RecordSource to include new fields
Adding hidden controls to forms for field accessibility
Programmatically reloading RecordSource in form events |
