Default Not Working
By Richard Rost
18 days ago
Fixing Default Values Not Working in Forms and Tables In this video, we'll talk about how to fix default values not working as expected in your Microsoft Access database. I'll show beginners common reasons why default values can behave differently in tables and forms, explain how setting defaults at both levels can cause confusion, and discuss best practices for staying consistent. We'll also cover how developers might set default values in VBA code and share a helpful trick for managing text defaults in your code. Miles from Wilmington, Delaware (a Platinum Member) asks: I am using Access at work and took over a database that someone else built. In one of the tables, there is a default value that works as expected on most forms, but there is one form where a different default value appears when I add a new record. I am not sure why this is happening, and I am wondering if I am missing something about how default values work in Access. MembersThere is no extended cut, but here is the file download: 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!
PrerequisitesLinks
Keywords TechHelp Access, fix default values not working, defaultFieldValue, form default value, table default value, vba default value, OnLoad event, form event procedures, override default value, system defaults table, double quotes default value, inherited database, customer form, state field default, control properties
Intro
In this video, we'll talk about how to fix default values not working as expected in your Microsoft Access database. I'll show beginners common reasons why default values can behave differently in tables and forms, explain how setting defaults at both levels can cause confusion, and discuss best practices for staying consistent. We'll also cover how developers might set default values in VBA code and share a helpful trick for managing text defaults in your code.
Transcript
The day we're going to talk about how to fix default values not working in your Microsoft Access database. Now, this video is mostly for beginners because I'm going to show you a feature that beginners often miss or did not know in the first place, and toward the end, I'm going to show you a developer reason why this might happen and a cool trick to get your default values to work in your code. So I have something for everybody.
Today's question comes from Miles in Wilmington, Delaware, one of my platinum members.
Miles says: I'm using Access at work and took over a database that someone else built. In one of the tables, there's a default value that works as expected on most forms, but there's one form where a different default value appears when I add a new record. I'm not sure why this is happening and I'm wondering if I'm missing something about how default values work in Access.
You probably are, Miles, and that's okay. This is something that a lot of beginners miss.
First off, if you're new to Microsoft Access, go watch my default value video. It explains what default values are and how you set them.
All right, here I am in my TechHelp free template. This is a free database you can grab off my website if you want to, and in here I've got a customer form, different customers and orders, and a whole bunch of other stuff, but we have the customer table right here.
Now, in the customer table we've got the state field. In here, we can set a default value for the state. Let's set it to Florida, FL. Save that, and if we were to just work in the table adding records, if I come down here to a blank new record, you can see there's the new record. That default value for Florida is right there. So for any new record that I create, you'll see Florida. It pops up on the next one as well.
Now, if I go to the customer form, which is where your end users should be working - you don't want to give end users access to your tables and stuff - but if you go to a blank new record right there, you'll see there's Florida. You can see I've also got USA set as the default for the country.
Here's what a lot of beginner Access developers miss. You can have a default value in the table and in the form.
I don't recommend doing this. I recommend you pick one and be consistent. I try not to have default values at the table level and at the form level. It gets confusing.
You can also come in here for the state, go to Data, go to Default Value, and put something different in here like New York. Save it, close it, and if I open up this form and go to a new record, look at that! The default value for this form is New York, and that will override the default value setting which is still in the table for Florida. If you come down here to the end, there's Florida still in there. If you have other forms, like your customer list form - it's based on the same table - if I go to a new record, look at that, it's still set to Florida. Every form can have a different default value. That is why it gets confusing. I like to stick to the tables most of the time, or do not put default values in your tables and just put them in your forms, but then you have to remember to copy it to all the different forms. There are lots of different ways to do it and lots of reasons, but that is why you might see different forms giving you different default values.
So, I do recommend picking one way and staying consistent if you can. Either use all table default values or use all form default values. If you have a lot of different forms that are based on the same table, then you can put the default value on the table, but if it is just one form, put it there.
Now, developers, this one is for you. You can also set default values in code. Let's say this credit limit here. I can come into here, design view, you can go into the form's events, like the On Load event, for example.
In your Form Load event, you can say creditlimit.DefaultValue = 1000. Save it, close it, and now when I open this form and go to a blank new record, you can see there's a default value in there.
Miles, pay attention, because if you've inherited a database from someone else before you, you might not know they did this. If you come in here to credit limit, you're not going to see a default value in here, so that is also where it could be hiding. Go to the form's properties, go to the events, see if there are any event procedures in here. If so, click on that little ... button and look for anything in here called DefaultValue.
Why do it this way? There are a million reasons why you might do it this way. I've done it this way in a lot of my databases. It could be based on some other criteria. For example, who is the logged in user, where is the location of the office. There are a million reasons why you might want to set the default value programmatically using VBA instead of just hard coding it in the form.
I have a whole other video on this, and you can set up default system value tables. So if you give your database to another office of yours, and they're in Texas and you're in California, you just change the system values in the system defaults table and it'll change all the default values around the entire database. There are a lot of reasons why you might load this in code, and that could also be the reason why you can't find it. If it's not in the form's design, go look in the VBA.
While I'm in here, I want to mention another trick for the developers, because I'm all about giving you the tips and tricks. That is the whole point of the TechHelp series. Check this out.
Let's say you want to set the state default value to Pennsylvania, right here. Let's try that. Save it, debug, compile, works just fine. Now come out here, open up the form, go to a new record and - what? I'm getting a #Name error here. Why is that? Pause the video, my advanced developer students, and see if you can figure that out. It looks fine, the credit limit worked just fine. What is going on here? This is why, even if it's a beginner video, and you're a developer student, you should still watch every video because I sneak all these cool little tips and tricks in here.
Pause it. Did you figure it out yet? You got it? The problem is, here is the problem: the default value in here, when you type in something like Florida, look what happens. Access automatically puts those quotes around Florida for you. I'll zoom in for you: Shift+F2. See, that is what is actually in that field. Since it is a text value, the default value has to be in quotes. That means if you're going to set this value in your code, you also have to include the quotes. What does that look like? That looks like double double quotes, there. Double double quotes there. Looks confusing, I know. I have a whole separate video on double double quotes. If you're a beginner, don't worry about this; this is a little hidden trick for the developer students. But now, save it, debug, compile, close it, open it, go to a new record, and there is your value. Those double quotes matter in that default value property. That used to throw me for a loop all the time too.
There you go. You have your table default value, each form can have its own set of default values, look in the properties for the controls, and if you still can't figure it out, check the VBA code. If you inherited this database from someone else, they might have done it that way. I do it that way a lot myself.
That 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.
If you enjoyed this video, hit that thumbs up button right now and give me a like. Also, be sure to subscribe to my channel, which is completely free, and make sure you click that bell icon and select All to receive notifications whenever I post a new video.
Do you need help with your Microsoft Access project? Whether you need a tutor, a consultant, or a developer to build something for you, check out my Access Developer Network. It is a directory I put together personally of Access experts who can help with your project. Visit my website to learn more.
Any links or other resources that I mention in the video can be found in the description text below the video. Just click on that Show More link right there. YouTube is pretty good about hiding that, but it's there, just look for it.
If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access, including building forms, queries, reports, tables, all that stuff. It is over four hours long. You can find it on my website or my YouTube channel. I will include a link below that you can click on. Did I mention it is completely free?
If you like Level 1, Level 2 is just one dollar. That is it, and it is free for members of my YouTube channel at any level.
Speaking of memberships, if you're interested in joining my channel, you get all kinds of awesome perks. Silver members get access to all of my extended cut TechHelp videos, and there are hundreds of them by now. They also get one free beginner class each month, and yes, those are from my full courses.
Gold members get the previous perks plus access to download all of the sample databases that I build in my TechHelp videos, plus you get access to my Code Vault, where I keep tons of different functions and all kinds of source code that I use, and gold members get one free expert class every month after completing the beginner series.
Platinum members get all of the previous perks plus they get all of my beginner courses - all of them, from every subject - and you get one free advanced or developer class every month after finishing the expert series. You can become a diamond sponsor and have your name listed on the sponsor page on my website.
That is it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by AccessLearningZone.com. I hope you enjoyed and I hope you learned something today. Live long and prosper, my friends. I'll see you next time.
Quiz
Q1. What is a common mistake beginners make regarding default values in Microsoft Access?
A. They do not set any default values at all
B. They think default values can only be set at the table level
C. They do not realize default values can be set at both the table and form levels
D. They always set the wrong default value
Q2. If a table has a default value set for a field and a form based on that table sets a different default value for the same field, which default value will show up in the form when adding a new record?
A. The table's default value
B. A combination of both default values
C. The form's default value
D. Neither default value
Q3. Why is it recommended to be consistent in where you set default values in Access?
A. It makes it easier to update existing records
B. It prevents confusion over which default applies
C. The database will not run if inconsistent
D. It helps enforce referential integrity
Q4. Where does the video recommend you do NOT allow end users to work directly?
A. In queries
B. In forms
C. In tables
D. In reports
Q5. In the context of the video, when might you want to use VBA code to set default values in a form?
A. When you need a different default depending on the user or location
B. Only when working with macros
C. When the table default is missing
D. Only for numeric fields
Q6. What should you check if you cannot find the default value set in the property sheet of a control on a form?
A. The table's field properties only
B. The form's Record Source property
C. Event procedures in the form's VBA code
D. The report's sorting and grouping settings
Q7. What is necessary when programmatically setting a default value for a text field in VBA?
A. Use single quotes around the value
B. Include double double quotes around the value
C. Separate multiple values with semicolons
D. Omit any quotes
Q8. Why might a developer set default values using a system defaults table?
A. It is the only way Access allows multiple defaults
B. It keeps the database smaller
C. Easily change default values for different offices or environments
D. Required for backwards compatibility
Q9. What error can occur if you set a text field's DefaultValue property in code without proper quotes?
A. #DIV/0! error
B. #VALUE! error
C. #Name? error
D. #REF! error
Q10. What is a key benefit of setting default values at the table level according to the video?
A. Every form must use the table default
B. All forms using the table will inherit the default value unless specifically overridden
C. It cannot be overridden
D. It forces users to always use that value
Answers: 1-C; 2-C; 3-B; 4-C; 5-A; 6-C; 7-B; 8-C; 9-C; 10-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's video from Access Learning Zone focuses on addressing the issue of default values not appearing as expected in your Microsoft Access database. This topic is especially useful for beginners since it involves a feature that is often overlooked or misunderstood, but I will also cover an important tip for developers on how to control default values with VBA.
The question comes from a user who has taken over a database built by someone else. In his situation, he noticed that most forms properly use the default value set in a table, yet there is one form where a different default value appears for new records. He is unsure why this happens and is asking for clarification on how Access manages default values.
This is a very common source of confusion, particularly for those who are new to Access. If you have not already learned about default values, I recommend watching my introductory video on the subject for a foundational understanding.
Inside my free TechHelp template database, which anyone can download from my website, I demonstrate the behavior. In the customer table, suppose you set the default value for the 'State' field to 'FL' for Florida. Whenever you add a new record directly in the table, you will see this value automatically filled in for you.
Switching to the customer form, which is what your users should be using instead of working in tables, you will notice the same default value of Florida appears for new records. I also have a default value of USA set for the country field.
Here's where many beginners encounter problems. Access allows default values to be set in both the table and the forms. However, if you enter a different default value for the same field at the form level, such as 'NY' for New York, the form's setting will override the table's default but only for that particular form. This means other forms based on the same table will continue to use the default set at the table level unless they also have a different form-level default. This behavior can quickly become confusing, so I recommend being consistent. Pick one location, either the table or all your forms, to assign default values.
If you primarily use one form with your table, set the default in the form. If multiple forms are built on the same table and should all share the same default, set it at the table level.
Now, let us talk to the developers. You can also control default values using VBA code. For instance, you might want the Credit Limit field to default to 1000. In the form's design view, under the relevant event (such as On Load), you can assign its DefaultValue property in code. When the form loads and you begin a new record, the value you specify in the code appears in the field.
Be aware that if you inherit a database, code like this might be responsible for a default value you cannot find in either the form design or the table. Always check the form's property sheet under Events for any procedures that might set the default in code.
There are good reasons for using VBA to assign default values. For instance, you might want defaults to depend on the logged-in user, the office location, or specific business rules. For more complex scenarios, you might even have a table of system default values, allowing you to easily update defaults throughout an entire database just by updating that central table. This method is particularly helpful if you distribute your database to multiple sites with different requirements, such as different states.
While I am on the topic, let me share a hidden pitfall that can happen to developers. If you try to set a default value for a text field through VBA, like setting state to Pennsylvania, you need to remember that Access requires a text default to be quoted. If you omit the quotes, you may see errors or unexpected results when you create a new record. In code, it means you have to use double quotes within the property to get the correct effect. This is a subtle but important detail to get right when assigning default values programmatically.
To summarize, Access allows you to set default values in tables, forms, and even code. Each form can have its own unique default, which may override the table's setting. If you cannot figure out why a particular form is behaving differently, check both the property sheet and the form's VBA code. If you are maintaining a database someone else built, you may need to dig into the code to find where defaults are set.
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
Setting default values in Access tables
Setting default values in forms
Interaction between table and form default values
Overriding table defaults with form-level defaults
How to check default value property in forms
Setting default values using VBA code
Locating default values set in VBA event procedures
Handling text default values with double quotes in code
Troubleshooting missing or overridden default values
Article
If you are working with Microsoft Access and find that your default values are not appearing as expected when you add new records, you are not alone. This can be particularly confusing if you have inherited a database from someone else or if you are just starting out with database design. In this tutorial, I will explain how default values work in Microsoft Access, why you might see different default values in different places, and how to manage and troubleshoot these settings. I will also cover an important tip for developers on setting default values using VBA code.
To start, it is important to understand that default values provide an initial value for a field whenever a new record is created. For example, you might want every new customer record to start with their State set to FL for Florida. To do this, you can set a default value for the State field in your table's design view. Once you set Florida as the default, every time you create a new record in that table, either directly in the table or using a form, the State will automatically be filled in as FL.
Problems can arise when forms are used, especially if you or another developer has set a default value for the same field at the form level. In Access, both the table and the forms can have their own default value settings for the same field. What many beginners do not realize is that if a form has a default value for a field, it overrides the default value set in the table for that particular form. So, if your table has State set to FL as a default, and your form has State set to NY, when you use that form to add a new record, NY will appear instead of FL. This only applies to that specific form. If you use a different form, or enter data directly into the table, you will still see FL as the default.
Because of this, it is best practice to choose one place to set default values—either in the table or in all your forms—to avoid confusion. If you use multiple forms for data entry into the same table, setting the default value in the table is usually the better option for consistency. If you only have one form for adding new records, you can set the default at the form level instead.
For developers, there is a third place where default values may be set: in VBA code. It is quite common, especially in more complex databases, to assign a default value to a control on a form during one of its events (such as the Form Load event) using VBA. Here's an example: Suppose you have a CreditLimit field on your form and you want every new record to start with a credit limit of 1000. You can use the following code in the form's On Load event:
creditlimit.DefaultValue = 1000
Save your changes, open the form, and you will see that a new record pre-fills 1000 in the CreditLimit field. However, if you look for this default in the property sheet or in the table definition, you will not see it. It is set via code, so unless you check the form's event handlers—and specifically, the VBA code—this default value can be hard to find. This may be exactly what is happening if you find that default values are not behaving as expected in a database that you did not create yourself.
There is a special trick you must be aware of when setting default values through VBA, especially for text fields. When you manually type a default value, like Florida, in the property sheet for a text field, Access automatically wraps the value in quotes behind the scenes, which you may not even notice unless you explicitly zoom in on the property. However, when you set the DefaultValue property for a text field in VBA, you need to provide those quotes yourself. For example, to set the State field's default value to Pennsylvania using VBA, you must write:
state.DefaultValue = """Pennsylvania"""
This may look confusing at first, but the double double quotes are essential. Each pair of double quotes represents a single quote in the resulting property value, so when Access uses that default value, it interprets it correctly as a string. Failing to include the quotes results in an error like #Name? appearing in your form when you try to open a new record. For number fields, you can set the default value directly without extra quotes, but for text values, these double quotes are mandatory when assigning defaults programmatically.
To summarize, if you ever find default values are not working as you expect, check in three places: first, check the table design to see if the field has a default value. Next, look in the form design for each control's default value property, since this will override the table's default in that form. Finally, especially if you inherited the database or if you suspect advanced functionality, check the form's VBA code for any assignments to DefaultValue. Understanding where the default is set will help you avoid conflicts and confusion.
Whenever you set default values, try to be consistent. Decide whether defaults should live in the table, in every form, or in VBA—do not mix and match unless you have a very good reason.
With this knowledge, you should find it much easier to manage and troubleshoot default values in your Access databases, whether you are a beginner creating your first forms or a developer handing more complex scenarios. Happy database building!
|
