Fitness 60
By Richard Rost
23 days ago
How to Save Template Data for Records in MS Access
In this Microsoft Access tutorial, I will show you how to save template data for records in your database, using the example of a fitness tracking database. We'll cover how to create a new template table by copying an existing table structure, set up a save template button, and write VBA code to copy a day's records to your template table using SQL. This technique can be applied to various scenarios such as meal tracking, order management, scheduling, and more. This is part 60.
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, load and save template data, fitness database, food log, meal planning, recurring records, template table, copy records, insert into SQL, workout day, non-workout day, daily schedule template, budget template, employee scheduling, auto number primary key, delete records SQL
Subscribe to Fitness 60
Get notifications when this page is updated
Transcript
In today's video, we are going to learn how to load and save template data for records in your Microsoft Access database. Now, what does that mean? Well, if you have the same thing that you do over and over again, I consider that to be like template data.
For example, I am going to be putting this in my fitness database. Most days, I know I eat the same breakfast, mostly a similar lunch, and the same dinner. So I could set up a template for my workout days where I eat a little more and I have a workout in there, or my non-workout days where I eat a little less and I do not have a workout.
But the same technique can apply regardless of whether you are doing, let's say, orders. If you have the same starter kit or a bundle or a holiday special that you have and multiple people order it, you just pick the template and it loads all the items onto the order.
The same thing for employee scheduling. Most weekly schedules repeat. You have a Monday template, a Tuesday template, and so on. Save those as a template.
Budget templates, monthly budgets, again, same thing. Rent, utilities, insurance, whatever. Put it in a template. So it does not matter what kind of data you are dealing with. You can use the same technique, and we are going to learn how to do that in today's video.
Now, this is part 60 of my fitness database series. Do you have to watch all 59 previous parts? No, you do not really have to. You will get the idea. But this is a database series about cool database tips and tricks. So whether you care about fitness or not, it does not matter.
So here is my fitness database. In here, we track food and we track meals, but that stuff does not really matter. What we have basically here is a daily food log where every day I log what I ate. It records how many calories, protein, and so on. These are all individual records inside of the food log.
What I want to do is I want to say, for me personally, I have usually two different types of days: a workout day and a non-workout day. On my workout days, you will see here, for example, I have my weight lifting, my power shake, I eat a little bit bigger lunch, a little bit bigger dinner, because I can. My non-workout days usually I will just have a walk in here. I try to walk a mile, mile and a half every night regardless.
What I want to do is I want to be able to save this as my template. So when I go to today, or let's say I go to tomorrow to plan tomorrow's meal, I can just hit load the template and it loads it all in there.
Right now, I have a copy item button here. We can go back here and I can say, copy this, copy this, copy this, but now that I have been using this for a while myself, I am seeing that most of this stuff, like the details will change for my lunch or my dinner. That is why I have these here with all these extras in there because I have not done that yet. I am going to do this part of my day for today. So I put in here, 700 calorie lunch, 800 calorie dinner. And so this I could say was a template because I loaded it into tomorrow and now I just have to fill in the details for lunch and dinner. Everything else is pretty much the same.
So first, we need to have a spot to save the template data. Where do you save something? You make a table for it.
Now I have the food log T, which is where the actual data is. Since the template is essentially going to be copying all of this stuff, but for one day, let's just copy this table and make a few modifications to it. That way we do not have to recreate all these fields.
So I am going to take the food log T, copy it with control C, and then hit control V to paste. We are going to call this the food log template T. I am only going to copy the structure. I do not need all the data that is in there. Hit OK, and now you have a nice blank brand new table with all this stuff.
In this table, I do not want the primary key to be the food log ID. I want a special template ID in here, but I am going to leave this field in here and I will explain why in just a little bit. I am going to make this not the primary key and I am going to change it to just a regular number and make sure it is indexed duplicates OK instead of indexed no duplicates. So this is no longer the primary key, but I am leaving the field in the table and I will explain why in a few minutes.
But I do want to add a primary key. So I am going to right-click in here and insert a row and call this the food log template ID, and that will be the auto number. All the other fields are identical except we are adding that one.
Save it. This is interesting. This comes up from time to time. You cannot change one field from an auto number data type and add another field as an auto number at the same time. It is a quirk with Access. I forgot all about that. You do not do it that often.
Basically, put this back to a number. Save the table. Now you can switch it to an auto number and save the table. It is just one of those little Access quirks that you cannot do both steps at the same time. I do not know why, but I think if I was not doing this as a teaching class, I have had it happen maybe once or twice in my entire career.
Now we have this blank template ready to accept data. Let's go back to our food log table and make a button down here. I am going to collapse this back up so I have more room to work. Let's add a button down here. I am going to just copy this button, copy and paste, and we are going to make this the save button. I am going to put a label next to it. Just copy any old label, copy and paste, and bring it down here because we are going to make a template save and load area.
Create template like that. This way, I do not have to put on the button save template and load template. We just have save and load buttons next to the template. We want to try and conserve space.
Now let's give this button a good name. Open it up. Go to all. Change this to save template button BTN.
Right-click, build event, and let's go into our code builder.
First, we are going to ask the user if they are sure they want to do this because it is going to overwrite whatever is in the table. Make sure they want to do this. We are going to say if not, are you sure. What is are you sure? For those who have not watched the whole series, it is just a function that I made that pops up a message box with the question yes or no, but I changed it a little bit to make it easier.
This will overwrite existing template data. Are you sure? The title is going to be save template. If they say no, then exit. That is it.
See how my are you sure function makes the logic a little easier than saying if not, if the message box is not equal VB no, but anyway, next we are going to delete existing template data. This one is really easy. Just delete all the records from the table. CurrentDB.Execute delete from food log template, delete from the food log, that is all your stuff.
Now we have a clean blank empty table ready to save the data. Here we are just going to use an insert into command to copy all the records from the food log for this day.
This date is our log date, so the date of the food item has to be on this log date, between this date at midnight and less than tomorrow at midnight.
We can use a simple SQL statement to do this.
Current day's records to template.
Here we go. CurrentDB.Execute insert into the food log template T (spelling is important).
Next line, select star from food log T.
Normally when we do an insert into, like we had in this entire course and for most insert into queries, you do a field list. You can actually do a select statement in here too. It is a little rarer, but you can do it. This prevents us from having to list all of the fields in our select statement. I can just select all with the star.
That is why I had you leave that field in the template table, that food log ID, because it is going to be part of this set of data. If that field was not in there, this would generate an error. Even though we do not need the field, I am leaving it in that destination table so that it avoids the insert into error. That is perfectly fine. You can have some unused fields in there that make your coding easier. Otherwise, you would have to sit here and loop through all the fields in the table.
Select all the fields from the food log table. Where (and do not forget your spaces, by the way. Notice I have a space here, I have a space here. You have to have spaces, otherwise you have to put a space in front of these.)
So, where the food date time (that is the field in the table) is greater than or equal to the log date. Put it inside the pound signs since it is a date. Then you need to have here, and the food date time is less than log date plus one. That makes it a date. It is the log date, whatever date we are on, so it has to be greater than or equal to that date at midnight and less than tomorrow at midnight.
That will copy all those records.
Now, status done, beep, let them know they are done. Status is my own function that also puts the text in the status box.
Debug, compile once in a while, close it, close it, open it.
Let me make this a little bit bigger so we can see here. Let's go to tomorrow. Actually, we have nothing in our table. We have to save our table. We will go into tomorrow in the next step.
Here is the data. I am going to save it in my template. Ready, hit save. This will overwrite existing template data. Are you sure? Yes. Let's take a look at the template. There are all our records. There is all the data that we just copied from the log into the template.
Now we have saved the data. The next step is to go to another day, hit the load button, and it loads the template table back into the log and updates all the dates and times to this day. A little bit of a different strategy we have to take, and we will do that in tomorrow's video.
So tune in tomorrow, same bat time, same bat channel. Members, you can watch it right now because I am going to record it in just a few minutes, and that is one of the benefits of being a member: you get to watch my videos as soon as I post them, not as soon as they go public.
While I am at it, I am going to change this slide. This one is just how to save. Yes, this is PowerPoint. How to save template data for records, and we will make part 61 how to load the template data.
There we go.
That is going to do it. That is your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I will see you tomorrow for part 61.
Quiz
Q1. What is the primary purpose of a "template" in the context of a Microsoft Access database as described in the video?
A. To create a backup of the database
B. To automatically optimize queries
C. To save and reuse a set of similar records for recurring situations
D. To enforce referential integrity between tables
Q2. Which of the following is NOT mentioned as a scenario where template data could be helpful?
A. Recurring employee schedules
B. Monthly budgeting with standard items
C. Saving database forms for future use
D. Predefined order bundles or starter kits
Q3. What is the first step described to enable saving template data in the database?
A. Adding a new query
B. Copying the structure of an existing table to make a template table
C. Creating a new form
D. Exporting records to Excel
Q4. Why does the video suggest copying the structure of the "food log" table when creating a template table?
A. It eliminates the need to create all fields from scratch
B. It copies all existing data into the template automatically
C. It prevents accidental data loss during the process
D. It improves database performance
Q5. When creating the template table, what change is recommended regarding primary keys?
A. Keep the same primary key as the original table without any changes
B. Splitting the primary key into two fields for better tracking
C. Adding a new primary key specific to the template records
D. Removing all keys for simplicity
Q6. In Microsoft Access, what is a notable limitation or "quirk" experienced when working with auto number fields in table design?
A. You cannot delete any auto number field once created
B. You cannot change one field from auto number and add another as auto number simultaneously
C. You must always have at least one auto number field in each table
D. The auto number fields are not compatible with queries
Q7. Before saving a new template, what precaution does the video recommend?
A. Running a backup of the entire database
B. Asking the user for confirmation, warning that this will overwrite previous template data
C. Locking the template table for exclusive access
D. Logging out all other users from the database
Q8. What method is used to copy the day's records into the template table?
A. Using an Excel import wizard
B. Manually entering each new record
C. An SQL "INSERT INTO...SELECT * FROM..." statement
D. Exporting and re-importing data from a CSV file
Q9. Why does the video suggest keeping a field like "food log ID" in the template table, even if it's not strictly required?
A. It is required by Access system functions
B. It helps avoid errors when copying records with "INSERT INTO...SELECT *"
C. It simplifies user interface design
D. It improves database search speed
Q10. What is the purpose of checking if the "food date time" is greater than or equal to the log date and less than the next day when copying records?
A. To ensure the template contains data for multiple days
B. To filter and copy only records for the specific selected day
C. To include all historical data in the template
D. To randomize the date fields for privacy
Q11. What immediate feedback does the user receive after the template is successfully saved?
A. The database restarts automatically
B. A status message and a beep
C. The application closes
D. A printout is generated
Q12. What is the next logical step after saving the template, as described at the end of the video?
A. emailing the template to a colleague
B. Going to another day, loading the template data, and updating records
C. Rebuilding the index on the table
D. Running a compact and repair utility
Answers: 1-C; 2-C; 3-B; 4-A; 5-C; 6-B; 7-B; 8-C; 9-B; 10-B; 11-B; 12-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 TechHelp tutorial from Access Learning Zone is all about how to load and save template data for records in your Microsoft Access database. The idea behind this is quite simple: if you frequently enter the same data repeatedly, you can set up a template to make this process much faster and more efficient.
For instance, in my own fitness database, I have noticed that my meals tend to stay pretty consistent. I usually eat the same breakfast every day, and my lunches and dinners are fairly predictable as well. So, I want to create templates for my workout days and non-workout days. On days when I exercise, I tend to eat a bit more and add workouts into the log. Non-workout days call for a lighter meal plan and just a walk, but the concept remains the same. With a template in place, I can simply load my typical day's data with just a click, and then make any necessary adjustments.
The principle behind this is universal and can be applied to any kind of data entry situation. Think about processing orders: if you sell a starter kit, a bundle, or maybe a holiday special that several customers order, you can save time by using a template. Similarly, for employee scheduling, you might create templates for each day of the week if your staff schedule is relatively consistent. This approach also works for things like monthly budgeting, where the same recurring costs appear each month—just set up a template for that as well.
In this lesson, I am building on my ongoing fitness database series—specifically, this is part 60. You do not need to have seen the first 59 parts to grasp today's concept, as it stands alone as a valuable tip for anyone working with repeated data entry tasks in Access.
Let me start by explaining the structure of my fitness database. I have a daily food log where each entry tracks what I consumed, the nutritional breakdown, and so on. Each of these records is tied to a specific date. Since my days mainly fall into two categories—workout days and non-workout days—I want to make it easy to load a standard set of records for each type. For example, on workout days, my log includes weight lifting, a protein shake, and larger meals. Non-workout days focus more on walking and lighter meals.
Rather than copying each individual record from a previous day one at a time, I want the ability to store the entire day's entries as a template. Then, when I want to plan for tomorrow, I can simply load the template for that kind of day and adjust the parts that actually change, like the details of lunch or dinner.
To accomplish this, first, we need a place to store our template data. In Access, that means creating a new table. My original table, food log T, contains all actual entry data. Since a template consists largely of the same structure as actual data—just saved for reuse—I took the approach of copying the existing table. I used copy and paste in Access to create a new table, which I named food log template T, and I chose to copy only the table structure, not the data.
The original table contains a primary key called food log ID. For the template table, I need a new primary key (food log template ID) specific to the template itself. I left the food log ID field in the template as a regular number field and removed its primary key status, changing the indexing to allow duplicates. This step is important for reasons I will explain shortly.
Access has a little quirk to be aware of here: you cannot change a field type from auto number and simultaneously assign auto number to a new field. The workaround is to first change the old field to a regular number, save the table, then add the new auto number primary key, and save again.
Now, with a blank template table structure in place, we can build our interface for saving templates. I added a button to my form to handle this function. Name your button appropriately—in my case, I called it save template button BTN for clarity.
The next step is to write the VBA code for that button. The first thing I do in the code is ask the user for confirmation before saving, since saving a template will overwrite any existing template data. I use a custom function called are you sure, which shows a confirmation dialog.
If the user confirms, all records from the existing template table are deleted using a straightforward SQL delete statement. This ensures we always have a clean slate each time a template is saved.
Once the template table is empty, I use another SQL command, an insert into statement, to copy all current records from the food log table for the relevant date into the template table. This is done by specifying the date range (from the start of the day to just before midnight the next day) so that only records for that particular log date are included.
It is important that the field structure of both tables matches, which is why I kept the food log ID field in the template table. If you were to omit that field, the insert statement would throw an error. Leaving unnecessary fields in place can sometimes make the process much easier.
After the template data is saved, I display a status message and play a beep sound to notify the user of successful completion.
At this point, the template is saved and ready to be used. The next step—which I will cover in the following lesson—is to create a way to load this template back into the food log for a new day, updating all necessary date and time information in the process.
So, that sums up our lesson for today. This is how you can save template data for records in your Access database, a technique that is versatile and can be applied to many types of data entry tasks.
If you would like to watch a complete video tutorial with step-by-step instructions for everything I discussed here, you can find it on my website at the link below.
Live long and prosper, my friends.
Topic List
Creating a template table for recurring data
Copying table structure in Microsoft Access
Modifying primary keys and indexes in template tables
Handling Access quirks when changing data types
Adding an AutoNumber primary key to a new table
Setting up Save Template and Load Template buttons
Writing VBA code for Save Template functionality
Prompting the user for template overwrite confirmation
Using VBA to delete existing template records
Using SQL INSERT INTO with SELECT to copy records
Handling date filtering in SQL for current day's records
Preserving field structure to avoid SQL errors during copy
Copying records from one table to another using VBA
Updating button names and labels for template actions
Displaying process status using custom VBA functions
Article
If you find yourself entering the same records in Microsoft Access over and over, it is worth learning how to use template data. Template data is just a set of records you frequently need that you can quickly load again later. For example, if you have a fitness database and your meal and activity routines are often repeated, you can save a workout day or non-workout day as a template and then apply it to a new date without having to enter all the details manually again. This concept applies to many different scenarios, not just fitness data. Maybe you have standard product bundles in order management, repeating weekly employee schedules, or recurring expenses in a budget tracker. Anything you do on a regular basis can be turned into a template and reused to save time.
Let me show you how you can set this up in Access using a food log example, but you can apply this idea to any set of repeating records.
Suppose you have a table named "food log T" where each line represents a meal or activity entry for a particular day. You might want a template for your workout day, which will include records for your breakfast, regular lunch, pre-scheduled workout, and other repeating activities. On a non-workout day, you might record a lighter meal plan and a simple walk. If you want to reuse these patterns, you need a place to store the template data. The easiest way is to create a new table with the same structure as your food log, so you can easily copy and retrieve records.
To set this up, go into your database window, locate your food log table, highlight it, and copy it (CTRL+C), then paste it (CTRL+V). When prompted, name this new table "food log template T" and choose to copy only the table structure, not the data. Now you have a blank template table ready to store your typical day.
Next, to distinguish template records from regular log entries, you should add a new primary key to the template table. Open "food log template T" in design view. You will see the original primary key (for example, "food log ID") carried over from the source table. Change that field from an AutoNumber to a Number type, and set its index property to allow duplicates (Indexed: Duplicates OK). Then insert a new field at the top of the table called "food log template ID," set its type to AutoNumber, and make it the primary key. Save the table. If you run into any issues—such as Access not letting you change more than one AutoNumber at the same time—finish one change, save, then make the second change and save again. It is just one of those quirks in Access.
Now that you have the template table, you need a procedure to save your current day into it. On your food log form, add a button to handle saving the template. You can bring in a label next to the button for clarity, calling it "Create template." Name the button meaningfully in the property sheet, for example, "save template BTN."
In the click event for the button, use VBA code to prompt the user before overwriting the existing template, delete all records from the template table, and copy the current day's records into the template. Here is how you could do it:
First, ask the user for confirmation to prevent accidental overwrites. You can use a function like "AreYouSure" that returns True if the user confirms.
Next, remove the old template records:
CurrentDb.Execute "DELETE FROM [food log template T]"
Then, to insert the current day's records from the food log into the template table, use an SQL Insert Into statement. Suppose "food date time" is your date field and "log date" is the day you want to template. You want to copy all records where "food date time" is greater than or equal to "log date" at midnight, and less than the following day at midnight.
Here is the key VBA statement:
CurrentDb.Execute "INSERT INTO [food log template T] SELECT * FROM [food log T] WHERE [food date time] >= #" & Format(logDate, "yyyy-mm-dd") & "# AND [food date time] < #" & Format(logDate + 1, "yyyy-mm-dd") & "#"
This statement copies all records for the specified date from the main food log into your template table. Note that it is important to make sure the template table has the same fields as your original log—including the original ID field—so the Insert Into works with SELECT *. If the fields do not match exactly, you will get an error. By carrying over unused fields, you keep things simple and avoid having to write out all the field names.
Finally, you may want to provide feedback in your form that the process is complete, such as calling a status function or using a simple beep to alert the user.
With this setup, whenever you want to save your current day's records as a template, you just hit the save template button and confirm. Your template table will be updated with the latest version of your daily setup.
The next step is to create a load template button that copies the template back into your log for a new date, adjusting the date fields as needed. But with the above work, you now have the key ingredients to easily save any set of records as a reusable template in Microsoft Access, saving you time and reducing repetitive data entry. This pattern works for fitness, orders, weekly schedules, standard invoices, or any scenario where you frequently use the same set of records.
|
