Fitness 48
By Richard Rost
34 days ago
Add Default Value Box for New Records in Form Header
In this Microsoft Access tutorial I will show you how to add a text box in your form header to set the default time for new entries, making it easier to log past meals without manually updating each entry. You will learn how to use the form header to control default values, set up and format unbound text boxes, handle user changes, and update time values for new records in your fitness tracking database. This is part 48.
Members
There 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!
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, create text box in form header, set default value form header, food log default time, unbound text box, format time value, before insert event, after update event, typecasting, validate time input, food date time, log date function, debug compile, record sorting, portion control meals
Subscribe to Fitness 48
Get notifications when this page is updated
Transcript
Today we're going to create a text box in the form header of our food log so that if we're going to add stuff, let's say we want to add a meal that we had earlier and it's now 8 o'clock at night and we had the meal at 4. I don't want to have to change the time on every one of those entries. So we'll put 4 o'clock and a text box in the header and then every new entry that we put on the form will have 4 o'clock on it.
That's how you can use the form header to set the default value. This is part 48 of my fitness database series but whether or not you care about fitness, this is a video series about building cool databases with lots of tips and tricks like this one.
If you haven't watched parts 1 through 47 yet, do yourself a favor and watch them. It's about building databases not necessarily about fitness.
So are you ready? Here we go.
So that's going to do it for part 48 folks. Let's see. Today is Thursday, October 9th. I record these way ahead of time and publish them later. If you've watched the previous videos, you already know this, so tomorrow is going to be Quick Queries Friday for the 10th and then Monday the 13th is Columbus Day. So no video that day except maybe I'll do a Columbus Day video or Indigenous Peoples video, whatever. So the next fitness video will be on Tuesday the 14th of October 2025.
But that's it for your TechHelp video for today. I hope you learned something. Live long and prosper my friends. I will see you next time.
Next up is a little annoyance that I want to fix. Sometimes you want to add a meal that you had earlier. But when you add stuff, like if I come down here and add this, it puts in right now, which is 10:57. So let's stick it right there. And that's okay. But then I have to go back in here and change this and blah, blah, blah.
It would be nice if we had a way to put the time up here. When the form opens up, have it default to whatever the current time is, but you could change it. Let's say I forgot I had a snack at 2 p.m. and it's going to involve two or three items. I can change this to 2 p.m. and then everything that I add will go in at 2 p.m. It will obviously follow our other rules too.
But let's do that. So I want to take this guy and put it up here. There. Copy. Paste. There it goes. Look at it right there. Let's give it a little color. Let's make it that guy. Yeah. Let's call this guy default food time text. We're going to make this unbound because we want to be able to change it, but it's not bound to anything in the table. I'm going to set the default value equal to format food log time of right now.
So when the form opens, it's going to get right now, and it's going to format the food log time, which is that function we made to format the text properly. I really care about the time portion because the date's coming out of this guy.
Save that, and let's see what we get. Close that, close it. There we go. All right. 11 p.m. And it just so happens to be exactly 11 p.m., so that's good.
Now this guy. This guy doesn't have a default value. Double checking. Yep.
You have to remember folks, it's been many weeks since we started this thing. Sometimes even I don't remember all the stuff that we did in part four. I have to go and double check and review sometimes.
So we put that value there if memory serves, in the before insert event. We set the food date time. Yeah. So food date time equals proper log date time. Let's go there. If the date value of now is the date value of log date, so if we're on the same day, entry for today, proper log date time equals...
Now instead of now, we're going to take today's date and add that value up top, the default food time text. So we're going to say date plus time value of default food time text, because that should be a valid time value.
Oh, we also need to update that if the user changes it manually. But let's test what we have here so far first. Debug compile. I'll make sure to compile once in a while. I've got to say it. It is those stupid catchy things that I repeat and you remember it. Oh, that's so dumb when Richard Rost says Debug compile once. Yeah, but you remember it, or if you forget to say it, you will forget it. I make them silly so that they stick in your brain.
Let's go check. Let's test this part first and we'll deal with that after update event. Change is yes, open it up.
So now if I add a value, so this is 11:03, I'm going to change this now to 2 p.m. We have to fix that so it formats it properly. I get that. But now if I add a value down here, because we're on the same day, it should go to 2 p.m. And there it is right there. Perfect. That's exactly what I wanted.
Delete that because I didn't need that. Trying to make sure I don't. This is my actual food log for today. I'm doing pretty well. 1800 calories and I ate a lot of food today. You don't have to starve yourself, folks. I think I eat more now that I'm actually watching my calories and trying to eat healthy. And I'm eating stuff like meatloaf.
Today I had pork tenderloin with potatoes. It's these metabolic meals. I love them, but it's all portion controlled. So this meal was only 362 calories. Then I added a whole can of mixed vegetables, a tangy, and a banana. That whole thing was about 600 calories. You watch what you eat. I think I eat more food volume now than I did before when I didn't care. Two pieces of pizza is 800 calories right there.
I promised myself during this series, I'd give you little tips from my diet and exercise stuff, but I wouldn't preach. I'm not preaching. I'm just saying you can eat healthy and eat a lot of food and you don't have to starve yourself to lose weight. That's a problem. It's the mistake I made the first time I did this, I starved myself.
We need to do the after update event to update food date time. We basically need to set it so that it equals the formatted value from this guy. So if they update this in the after update event, it's going to be that, but we're going to use this instead.
So the default food time text equals format food log time default. See, that's why it's nice to make these things functions because we don't have to rewrite any code. We just call the same guy that we wrote earlier that does the same thing. We're just taking the time and making sure it's formatted properly.
Debug compile. Oh, variable not defined default food date time. What did I do? Oh, yep. This is actually sending food date time, not food time text. So this guy is expecting an actual date value. That's not a problem because we can just send this as a date value.
Now we might get away with doing this because Access is pretty good about converting strings directly into text. Let's test it and see. If not, we'll just convert this over to a time before we send it.
Debug compile that. It should work. Let's test it and make sure. Sometimes I'm a little leery of doing this though. All right. So if I come up here and I type in 2PM, it did work. It formatted it nicely because basically we're sending a string value, because that's a string. We're sending a string value into here and then it's formatting what it's expecting as a date. So Access is doing an automatic typecasting.
Typecasting is when you convert from one type to another, like an integer to a string or a Boolean to a long, whatever. I try not to rely on that if I can. In fact, it's probably a good idea when they update that to check and make sure it's valid.
Again, if this is a database that you're just building for your own purposes, great. The point of this video series is I want to teach you good tricks and best practices. So at this point here, we should probably say if not is date default food time text, then let's just set it equal to right now. You can chop off the date part so you can say now minus date like that. Then default food time text equals that.
So if it's not a valid date, then it'll just put the current time in there. Now minus date gives you like today at 9 p.m. minus today gives you just 9 p.m.
Save it. Debug compile once in a while. Already compiled it. Close it. Close it. Open it.
Let's try putting in here 3 p.m. That looks good. Let's try putting in here null. Perfect. Null is not a valid date and it just put today's time in there.
There we go. So let's say I had tea at 4 p.m., I can come in here and put 4 p.m. in there and now anything that I add down here, do I have tea? I have tea and I do, I'll pick something else. I'll put an apple in there. Boom. 4 p.m. right there. If I add a protein shake or a Clif bar, boom.
Now just to double check, let's take a look at our log and make sure that those seconds are still working. Uh huh. Yeah. Uh huh. See, so they'll stay sorted properly now too.
Now I get to get rid of this stuff because I didn't eat it. That's throwing my numbers off. Another cool little trick added. That's how you can get the default value.
This works in regular forms too. You don't have to have all this VBA trickery going on. I cover it in my default value video. Technically, I cover it in the extended cut though. You could put a value in the header or the footer and then use that as the default value for new records as they come into your form.
This one is worth becoming a member, folks. If you're not a member, go ahead and sign up and you can watch this guy.
TOPICS:
Adding a text box in the form header for default time
Making the text box unbound for user input
Setting the default value of the text box to current time
Using a custom function to format time values
Copying and pasting controls in the form header
Modifying Before Insert event to use header time value
Combining today's date with user entered time
Handling manual user updates in After Update event
Formatting and validating the user entered time
Converting string input to valid time values
Using automatic typecasting in Access
Adding error handling for invalid time values
Auto-resetting to current time if input is invalid
Maintaining correct time values in new records
Using calculated default times for all new entries
Ensuring sorting by time works with new entries
COMMERCIAL:
In today's video, we are learning how to add a text box to the header of your food log form in Microsoft Access so you can set a default meal time for new entries. This is part 48 of the fitness database series, but the tips shown apply to any Access project. You'll see how to set up an unbound text box, have it default to the current time, and make sure all new entries use the chosen time, even if you add items from earlier in the day. We will also discuss how to validate the time input, use simple VBA code in the form events, and ensure everything updates properly. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the main benefit of adding a text box in the form header for entering meal times?
A. It allows setting a time once so all new entries use that time until changed.
B. It makes the form look more organized and colorful for users.
C. It automatically fills in food descriptions based on time of day.
D. It prevents users from entering times in the future.
Q2. Why is the text box for the default food time made unbound in the form?
A. Because it should not be saved directly to the table.
B. Because unbound controls are required in the form header.
C. Because only numeric values can be bound to a table.
D. Because an unbound control automatically syncs with the table.
Q3. How is the current time initially set as the default value in the new text box?
A. By using a function that formats the current time when the form opens.
B. By hard-coding the value into the control source.
C. By basing it on the value from the last entry in the table.
D. By calculating the average time of all meals for the day.
Q4. What happens when the user changes the value in the default food time text box?
A. All subsequent new entries are assigned the new time until it is changed again.
B. Only the last record in the form is updated.
C. The database deletes all previous times.
D. The form automatically closes.
Q5. Where in the code is the value from the default food time text box used when creating a new entry?
A. In the before insert event.
B. In the form load event.
C. In the table design view.
D. In the after update event only.
Q6. What is typecasting as discussed in the video?
A. Automatically converting one data type to another like a string to a date.
B. Setting default values for all fields in a form.
C. Assigning string values only to text boxes.
D. Formatting colors in a form header.
Q7. Why is it recommended to check if the value entered into the default food time text box is a valid date or time?
A. To prevent invalid data from causing errors in the form.
B. To allow users to enter any kind of information they want.
C. To enable features for automatic calorie calculation.
D. To minimize memory usage in the database.
Q8. What should the system do if the value in the default food time text box is not a valid date or time?
A. Set the value to the current time.
B. Delete the entire record.
C. Prompt the user to restart Access.
D. Save the value as a string only.
Q9. What is the purpose of using the after update event for the default food time text box?
A. To update records when the time value changes.
B. To delete blank entries.
C. To send email notifications.
D. To change the color of the header.
Q10. How does separating the date and time values improve data entry for the food log?
A. It allows multiple items for the same meal to share the same meal time easily.
B. It enforces a strict one entry per meal rule.
C. It disables the use of drop-down menus for time selection.
D. It lets the user change the date for all previous meals automatically.
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-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 TechHelp tutorial from Access Learning Zone will show you how to set up a text box in the form header of a food log database to conveniently control the default time value when entering new meals.
There are plenty of situations where you might want to add a meal to your log that you actually ate earlier in the day, not at the current time. For instance, it could be 8 PM and you want to log a meal you ate at 4 PM without having to manually update the time for every single entry. By adding a text box in the header of the form, you can set the default time for any new items you add, making the process much easier.
This lesson is part 48 of my fitness database series on building sophisticated databases with Access, packed with practical design strategies and clever VBA solutions. Even if you're not specifically interested in tracking fitness, the tips in this series are valuable for anyone looking to improve their Access database skills. If you are new to the series, I highly recommend starting from the beginning with part 1, as each lesson builds on previous concepts.
Now, onto the technique. The problem at hand is that, by default, every new entry on the food log instantly sets the Date/Time field to the current moment. So if you add food items now, they'll be timestamped with the present time. That isn't ideal if you're recording meals you consumed hours ago, particularly if you have multiple items to log at the same time.
The solution is to move, copy, and paste a text box into the form's header section. This box will represent the "default food time." It's set as unbound, so it is not tied to a specific field in your database, allowing you to adjust it freely. The default value for this box is set to the current time, formatted by a helper function you may have created previously in this series.
The entry-adding logic checks the text box for the default value and applies it as the time for all newly added food items. When you open the form, the box displays the present time, but you can simply type in another value, such as "2 PM" if you forgot to log a snack from earlier. Any new item you add after setting the time will automatically log with that time, as long as you're still working with the same day's entries.
Behind the scenes, this is handled in the "BeforeInsert" event, which sets the Date/Time stamp based on the contents of the default food time box instead of always using Now. If you change the value in the time box, the "AfterUpdate" event makes sure that all future entries pick up your new default until you change it again or close and reopen the form.
One important point is the issue of typecasting in VBA. Access often converts strings to date/time values automatically, such as when you take a string like "2 PM" and use it where a Date is expected. However, best practice suggests that you should check whether user-entered values are valid dates before trying to use them. If the provided value is not a valid time, the code falls back to the current system time. This helps to avoid bugs and ensures your database always works smoothly.
There is also some discussion on healthy eating habits and weight management, a recurring theme in this series, where I like to give practical advice based on personal experience. The takeaway here is that you can eat well, feel full, and be healthy without starving yourself, which is a common misconception about dieting.
To address usability and data integrity, whenever the default time text box is updated, the code checks if the new value is actually a date. If not, it resets to the current time, showing you exactly how robust error handling can make your database more reliable.
Also, this method is not exclusive to food log databases. Using a header text box to control the default value for new records can be applied to any Access form. It is especially effective if you often need to create batches of entries that share a common piece of information, like a time or category. For a simpler overview on this topic, you might look into my default values lesson, which covers how to prepopulate new records in regular forms too.
Finally, if you want the step-by-step walk-through on how to set this up, including all the code and property settings discussed here, you can find the full video tutorial on my website at the link below.
Live long and prosper, my friends.
Topic List
Adding a text box in the form header for default time
Making the text box unbound for user input
Setting the default value of the text box to current time
Using a custom function to format time values
Copying and pasting controls in the form header
Modifying Before Insert event to use header time value
Combining today's date with user entered time
Handling manual user updates in After Update event
Formatting and validating the user entered time
Converting string input to valid time values
Using automatic typecasting in Access
Adding error handling for invalid time values
Auto-resetting to current time if input is invalid
Maintaining correct time values in new records
Using calculated default times for all new entries
Ensuring sorting by time works with new entries
Article
If you want to make data entry faster in your Microsoft Access food log (or any data entry form), you can use a text box in the form header to set a default time for new entries. This is especially useful when you want to log something you ate earlier in the day, such as at 4:00 PM, but now it is 8:00 PM. Instead of manually adjusting the time for each entry, you can set your desired time in one place, and all new items will use it until you change it again.
Let's walk through how you can set this up. When the form opens, a new unbound text box in the header will default to the current time, but you will be able to change it as needed. Any new record you add will automatically use the time from this box, unless you override it.
First, open your form in design view. Add a new, unbound text box to the form header. You can copy an existing control if you like, but make sure to set it up as unbound (not linked to any table field). Give it a meaningful name, for example, DefaultFoodTimeText. You might want to format it with a background color or border to make it noticeable.
Next, set the default value for this control so it displays the current time when the form loads. You can use an expression like this for the default value:
=Format(Now(), "h:nn AM/PM")
If you have a custom function, such as FormatFoodLogTime, that formats the time for your food log, you can use that instead.
Now you need to adjust your form's code so that when a new entry is created, its time is set to this default. In the form's BeforeInsert event, add code to set your record's time field based on the value of DefaultFoodTimeText. Suppose your record has a field called FoodDateTime, and you already have logic to set its value. Update it to use the user's selection from DefaultFoodTimeText. For example, your VBA code could look like this:
If DateValue(Now) = DateValue(Me.LogDate) Then
Me.FoodDateTime = Date + TimeValue(Me.DefaultFoodTimeText)
End If
This means that, for entries dated today, the time portion will come from that text box in the header, and the date portion will be today. If your log supports entries for other days, adjust the code as needed.
It is important to check that users actually enter a valid time into the text box. Add an AfterUpdate event to DefaultFoodTimeText that validates the input. For example:
Private Sub DefaultFoodTimeText_AfterUpdate()
If Not IsDate(Me.DefaultFoodTimeText) Then
Me.DefaultFoodTimeText = Format(Now - Date, "h:nn AM/PM")
End If
End Sub
This code checks if the input is a valid time. If not, it resets the box to the current time. Now - Date gives you just the current time portion.
With this setup, if you want to log a meal you had at 2 PM, simply type "2 PM" into the DefaultFoodTimeText box in the form header. Any new entries you add to your food log will default to that time until you change it again. You can always reset it back to the current time by entering a new value.
If you ever want to use this approach in other types of forms, the key is to place a control in the form header or footer and use its value as a default for new records you insert into the detail area. This can save a lot of time and reduce errors, especially when entering batches of related records.
As a final tip, it is always good practice to validate user input, as done above, to prevent data entry errors and make your database behave more predictably. With this method, you can quickly set and reuse a default value for new entries, making your data entry process much more efficient.
|
