Fitness 55
By Richard Rost
19 days ago
How to Save and Load User Settings in MS Access
In this Microsoft Access tutorial, I will show you how to save and load user settings in your database by creating a settings table to store values like daily calorie and protein goals. Using a step-by-step approach, you'll learn how to design the table, add new settings, and set up functions to read and write these values to the table. We'll also talk about considerations for future features such as tracking historical goals or supporting multiple users. This is part 55.
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, save and load user settings, settings table, daily calorie goal, daily protein goal, tracking calories, protein tracking, user preferences, settings form, put setting function, get setting function, DLookup example, BMR, auto number field, global module, value field, recordset, index setting name, historical goal tracking
Subscribe to Fitness 55
Get notifications when this page is updated
Transcript
In today's video, I'm going to show you how to save and load user settings in your Microsoft Access database. We'll set up a settings table and you can save and load any settings you want.
For example, this is going to be part 55 of my fitness database. Now, whether or not you care about fitness and tracking calories and all that, that's not the point. The fitness database series is something I'm building for myself so I can teach you guys all kinds of cool Microsoft Access tips and tricks.
One of the things I need to save is what is my daily calorie goal or my protein goal. So I'm going to save those as numbers in a settings table, and I can look them up other places in the database, and that's what the user settings table is for. You can use it for all kinds of stuff: the name of the computer, the username, the window position, you name it.
Should you watch the rest of the series, parts 1 through 54 first? Yeah, absolutely. There's tons of great tips and tricks in there. Do you have to? No, not really. This one kind of stands alone.
So here we go. In today's video, we're going to start tracking our daily goals. Now for me, I track calories and protein. Obviously, for whatever you're tracking, if you're tracking sodium or cholesterol or fat or whatever, that's up to you. I'm going to do like we've always been doing with this database series, my protein and my calories.
The first thing is we need a place to store our goal values. For me, it's 2,000 calories a day, and my protein is 200 grams of protein. So I need someplace to store that stuff.
Now for the members in the extended cuts, we have this thing called the settings table where we've got all kinds of other settings in here. For example, here's the new pop-up clock we did in the extended cuts. If you're coming in here and want to change this time, double click. Boom, you got a pop-up clock. You can make it 8:55 pm, whatever. So I'm going to put that back to 9 o'clock. All those settings for that clock are saved in the settings table.
We also have our window positions so when we open up the database, it goes to this position on our screen, and things like that. So we're going to use this table.
I'm going to instruct you on how to set this up in a few minutes. I've covered this for the members and I also did a TechHelp on this a few years ago. Here's the video. Now, I've made some modifications. This video is three years old. I've made some changes to the code over the last three years, but this video will explain to you generally what we're doing.
We're creating a settings T. In this one, I called it default T, but now I call it setting T. The ID is really meaningless, but you have a name of the setting and then the value. So you've got the computer name as Bacar, your daily protein as 200, that kind of thing. Then you can look them up throughout your database. Go watch this video if you want to see roughly what's covered.
Members, of course, Gold members, it's also in the code vault. The newest code is in here, but you've seen this already for the previous extended cut video. So this is nothing new to you. In fact, the members version has some additional code that can tell you if the table exists when the database starts, and if not, it will create it for you. I'm not going to show you all that code. That's stuff for the members. So this way, when you start your database, if the settings table doesn't exist, it will create it for you. Then you can even put your initial settings into it. That's members only stuff.
But you guys, non-members, you'll have to set this up yourself. It's not that big of a deal. Create a setting T. I have a setting ID, which is an auto number, which we really don't use, but it's just a good idea to have it. The setting name, which is short text, is the name of the setting itself, like first name, last name, that kind of stuff, daily protein goal. The setting value is long text. I set it as long text, even though you're saving numbers or whatever, so it can store any type of value. It can store a date, a memo, a number, whatever. You just store it in a long text field.
Is that super efficient? No, but this is a table that's not expected to get a lot of traffic. You're not going to be storing 100,000 records in here. You're going to be storing maybe a total of 100 items, just database settings and preferences. So you're not going to be doing a ton of reading and writing to this.
In fact, one thing I thought of for members is that the setting name, we don't by default index it. We don't have to index it because again, you're not going to be constantly looking up stuff from this table. But it wouldn't hurt to index it in case you do have a couple thousand settings in here maybe, but it's not necessary.
This is what you need to have to save your settings. In here, we're going to add two new settings. We're going to put in daily calorie goal, and for me, that's 2000. You can set in whatever your number is. Then daily protein goal, and again, for me, that's 200 grams of protein. Those are the two settings that I want. You can set yours up.
These figures are based on a whole ton of different statistics: your current height, weight, age, daily activity. I have a smart scale that tells me what my BMR is, my basal metabolic rate. We've talked about this before. Mine is about 2450. So I need 2,450 calories every day just to exist. My organs, my brain - my brain probably burns a few thousand calories. Just kidding.
Any exercise you do on top of that is just cake. Not really cake. You don't want to add cake when you're on a diet. That's my goal number, is to hit 2000.
Save that and close it. Now we need functions to be able to read and write these values. In the global module for the members at least - yeah, I put them in the global module instead of the member module, the member module too - but I put it in the global module because I knew we'd eventually have to make these public.
Where are they? Here they are. They're coming up. There they are: put setting and get setting. I'm going to go over them just briefly in a moment. I strongly suggest you type these in instead of just screen capturing it in OCR, because you'll learn more by typing this stuff in and trying to understand it yourself.
I remember when I was a kid, I used to have my Tandy Color Computer, my CoCo, and I'd type in programs that were in the book. I learned a lot by that. When I was learning computers, we didn't have an internet where we could just copy and paste code. I got magazines or bought books from the store and typed in the code myself. By typing it in, your brain tends to analyze it a little better than just if you've copied and pasted it off a website. Even though you could screen capture and OCR, I strongly recommend you type it in. You'll get more out of it.
Now, put setting. This is for when you're putting data to the table. You send it the setting name and the setting value. I'm sending in a variant because this way I can accept null values. If I want to null something, you can't send it in if it's a string. ByVal means that these cannot change the originals. It's sending in just a copy of the data.
If you want to add an "on error resume next" - I know I don't always recommend it, but for functions like these, if it encounters an error, you just want it to exit out. You can check on the other end to make sure that something was actually read or not by looking at the value. We're trimming whatever comes in. SV is just a holder variable for the setting value. I'm casting it into a string value inside here. That's another reason for the On Error Resume Next. If it comes in with a null, it just skips over it. Rather than try to look up to see whether or not the value exists and then either adding it or editing it, I just delete it either way. So we just delete the setting. If it's in the table, it deletes it. If it's not, it doesn't, so it just executes that line either way. Then we're going to add it new with a recordset. Then that's it. Let it exit out.
The other way, get setting, reads the value with a simple DLookup, that's the setting name. So there's the code - type it in. Now you're ready to get and put values.
Now, yes, I realize at this point that some of you are going to say there are a couple of issues with doing it this way. First of all, I did mention early on in the series that we might make this multiuser, depending. That's one of my goals. If we do make it multiuser, we'll obviously need to have multiple users' daily goals, because not everybody's going to be the same. We might move this to a separate table. For now, I'm not worried about that. I just want to get this functioning as it is.
Another consideration is that as you lose weight, these numbers will change. As I drop from 295 to say, 250, my BMR might come down to 2100. So I need fewer calories every day to survive. This data will change as your body composition changes. Likewise, the number of grams of protein that I need will change. It'll also change once I get down to my goal weight; I plan to add some muscle, so I'll need to increase that value.
Right now, I'm just concerned about this for daily planning. I really just want to see, like today, how many calories do I have left, how many more grams of protein do I have to eat? I'm not too worried about last month. But you might, in the future, decide you want to track all that. If that's the case, then we'll have to store those daily calorie goals and protein goals in a table so you can have historical information.
It's kind of like with order entry systems. One of the things I teach in my Developer course is storing historical data when it comes to product prices. You know what this particular computer costs today, but what did it cost a year ago when you sold it to customer Z? You have to store that pricing information in their order. It would be the same thing here.
Every day, we need to actually store this goal data in either this table or some other table. I don't really want to store it in this table, but it's a possibility. You could do that when you create a new day: have it pop in the goal values here. But that's kind of wonky. I'd probably set up a separate daily goal log table. We might add that in the future, but not for now.
All I care about is just seeing down here, hey, how about today, how did I do yesterday, maybe the day before. But I'm not really at this point too much concerned about what I did last month.
Now that we've got all that set, we are ready to put the data here on the form and we'll get to that in tomorrow's video. So tune in at the same time, same channel. Members can watch it right now because I'm going to record it in just a few minutes.
So that's going to do it for your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part 56.
TOPICS:
Saving and loading user settings in Access
Creating a settings table for user preferences
Defining fields in the settings table
Using long text to store setting values
Adding daily calorie and protein goal settings
Writing a function to save settings (put setting)
Writing a function to retrieve settings (get setting)
Using DLookup to read settings values
Handling null values in setting functions
Replacing existing settings by deleting and adding
Considerations for changing user goals over time
Discussing single user vs multiuser settings storage
COMMERCIAL:
In today's video, we're going to learn how to save and load user settings in your Microsoft Access database by creating a settings table to store things like daily calorie and protein goals. You'll see how to set up the table, understand how to store various values, and I'll show you simple VBA functions to read and write your settings whenever you need. I'll also talk about when you might want more advanced features, like handling multi-user settings or historical goal tracking in the future. 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 primary purpose of creating a settings table in a Microsoft Access database as described in the video?
A. To store user-specific and application-wide settings for easy retrieval and update
B. To keep track of all database transactions
C. To manage all user login credentials
D. To maintain a backup of the database at regular intervals
Q2. What type of information does the settings table typically store in the context of the fitness database example?
A. Only user passwords and security tokens
B. User goals such as daily calories and protein, computer name, and window positions
C. All previous user data entries
D. Historical workout logs
Q3. Why is the "setting value" field in the settings table defined as a long text data type?
A. Because long text is required to enforce data validation rules
B. To store any type of value, including numbers, dates, and text
C. Because it allows for faster searching and indexing
D. To restrict the input to only numeric values
Q4. How does the "put setting" function handle saving a setting to the table?
A. It only adds new settings if they do not exist
B. It deletes any existing setting first, then adds the new value
C. It edits the existing record in place without any additional actions
D. It prevents duplicate setting names by generating errors
Q5. What does the "get setting" function use to retrieve a setting value from the table?
A. It filters the table directly within a subform
B. It uses a DLookup operation with the setting name
C. It retrieves all records and filters them manually
D. It only returns values containing numeric characters
Q6. In what situation might indexing the setting name field be beneficial?
A. When storing only one or two settings in the table
B. When the table is expected to hold a large number of settings
C. When only date values are saved in the table
D. Indexing is never recommended for a settings table
Q7. Why is it suggested that you type in the code manually instead of copying and pasting it?
A. Typing the code ensures that you receive the latest version
B. Manual typing helps you better learn and understand the code
C. Copying code always introduces syntax errors
D. Code copied from books does not work in Microsoft Access
Q8. Which of the following is a potential limitation of using a single settings table for multiuser environments, as discussed in the video?
A. The settings table cannot store more than 10 settings
B. All users would share the same setting values, which may be inappropriate
C. The table requires daily maintenance by an administrator
D. Only text values can be saved, not numbers
Q9. What is a suggested solution for tracking historical changes to user goals over time?
A. Overwrite the settings table entries daily
B. Export the settings table to Excel at the end of each week
C. Create a separate daily goal log table to record changes
D. Add a timestamp field to the existing settings table and append new rows
Q10. Why is performance not a primary concern when defining the settings table structure?
A. The table is being used for transaction processing
B. The settings table only stores a small number of records and is rarely written to or read from heavily
C. The long text data type guarantees high speed performance
D. Microsoft Access automatically optimizes all small tables
Answers: 1-A; 2-B; 3-B; 4-B; 5-B; 6-B; 7-B; 8-B; 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 TechHelp tutorial from Access Learning Zone will focus on how to save and load user settings in a Microsoft Access database. This process involves setting up a settings table where you can store and retrieve any preferences or configuration data that you wish to save.
As a practical example, this tutorial is part of my ongoing fitness database series, but even if health tracking is not your primary interest, the key takeaway here is how to manage persistent user settings in Access. I use this database to illustrate Access features and methods, and the concepts we cover today are widely applicable.
One reason you might want to store user settings is to keep track of goals, such as daily calorie or protein targets. But the use of a settings table is much broader. You can store anything from user names and computer names to window positions or other preferences.
While it is beneficial to watch the earlier parts of the series, this lesson works as a stand-alone module. For this demonstration, I am tracking daily calorie and protein goals since these are relevant for my own needs, but you are free to customize the settings table to track whatever you find important, such as sodium or fat intake.
To start, you need to create somewhere to store your goal values. In my case, I aim for 2,000 calories per day and 200 grams of protein. Creating a dedicated settings table for this is the most flexible solution. In previous parts of the series and in my extended cut lessons for members, we built a settings table that also contains things like pop-up clock preferences and saved window positions. All of these are stored in the same table for easy management.
If you want to see a detailed walk-through of setting up the table, I recommend viewing an earlier TechHelp video I recorded several years ago. The table structure has not changed much since then, although I have made minor updates to the code over time. Even though there have been some refinements, the underlying approach remains the same.
The table, which I now call setting T, contains a meaningless auto-numbered primary key, a name field to identify the setting, and a value field to store the setting's data. For example, setting names might include "ComputerName" or "DailyProtein," and their associated values could be "Bacar" and "200," respectively. Whenever you need to retrieve a setting, you can look it up by its name.
The latest code for this series is available in the code vault for members. Members also have access to advanced features, such as code that checks if the settings table exists at startup and creates it if necessary. Furthermore, member-only code allows you to populate the table with initial settings values when needed. For those not enrolled, you will need to set up these tables on your own, but it is a straightforward process.
To build your settings table, define an auto-number primary key for uniqueness, although it is not used directly. Add a short text field for the setting name, such as "DailyCalorieGoal" or "DailyProteinGoal." The setting value should be stored as long text so it can accommodate any type of data, whether that is a number, date, or string. This approach is not optimized for massive data, but since you are storing relatively few preferences, performance is not an issue.
Indexing the setting name is not required, given the light load, but if you anticipate a large number of records, you could add an index. However, for most users, this is unnecessary.
Now, for the settings at hand, just add two records to the table: one for the daily calorie goal, and one for the daily protein goal. Input your own values based on your personal health objectives. For instance, your calorie goal will depend on factors such as height, weight, age, activity level, and basal metabolic rate (BMR). Mine is roughly 2,450 calories just to maintain basic bodily functions, so I set my goal slightly lower to promote weight loss.
After saving these records, you will need the ability to read from and write to these settings. I provide two utility functions for this purpose: one to put a setting into the table and one to retrieve a setting. In my database, these are located in the global module, to make them public and reusable across your application.
When updating a setting, the function accepts the setting name and value. Using a variant type allows flexibility, including the ability to set values to null. Inside the function, the input is converted to a string, and any existing entry with the same name is deleted before the new value is inserted. This ensures only one record exists for each setting. Error handling is minimal, since this is not critical data, and you can always verify that a setting exists or handle missing values as needed when reading.
The function for retrieving settings simply looks up the value using DLookup based on the setting name. I strongly advise you to type out the code yourself instead of copying or using OCR tools. This practical exercise improves your understanding of the logic, which was how I learned when computers were less user friendly and everything had to be entered by hand from books or magazines.
There are important considerations to keep in mind. If you intend your database to support multiple users, you will eventually need to support separate user-specific settings for goals or preferences. For now, I am storing them globally, but in a multiuser environment, you would need to introduce a user identifier or a separate table for user settings.
Additionally, as personal data changes—such as weight loss reducing your calorie needs—your goal values should be updated. The current implementation only records the latest values, but if historical tracking becomes important, such as seeing what your goals were a month ago, you will need to develop a log table that captures changes over time. This is similar to how order entry systems store the price of a product at the time of sale, not just the current price.
For this lesson, my priority is having a daily reference for calorie and protein goals. If you need to track changes over time in the future, setting up an additional history table will be necessary.
With the structure and logic in place, you will be ready to display these settings on your forms, which will be covered in the following lesson. For now, you have all the tools you need to establish a robust user settings system in Access.
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
Saving and loading user settings in Access
Creating a settings table for user preferences
Defining fields in the settings table
Using long text to store setting values
Adding daily calorie and protein goal settings
Writing a function to save settings (put setting)
Writing a function to retrieve settings (get setting)
Using DLookup to read settings values
Handling null values in setting functions
Replacing existing settings by deleting and adding
Considerations for changing user goals over time
Discussing single user vs multiuser settings storage
Article
Saving and loading user settings in a Microsoft Access database is a powerful technique that you can use to make your applications more flexible, user-friendly, and personalized. Imagine you want to keep track of some preferences or configuration options, like daily nutrition goals, window positions, computer names, or any kind of persistent setting the user might need. A great way to handle this is by setting up a simple settings table in your database, which you can read from and write to as needed.
To get started, create a table in your Access database called settingT. This table should have at least three fields. First, a SettingID which is an AutoNumber field; this serves as the primary key, though you will rarely need to refer to it directly. The next field is SettingName, which is a Short Text field and will store the name of each setting, like DailyCalorieGoal or DailyProteinGoal. The third is SettingValue, which should be a Long Text field. By storing the value as Long Text, you make it flexible enough to handle numbers, dates, strings, or even memo fields. Efficiency is not a big concern here since you will only keep a limited number of settings—maybe a dozen or so, certainly not tens of thousands—so performance will not be impacted.
For example, if you are tracking fitness goals, you might insert into settingT two records: one named DailyCalorieGoal with a value like 2000, and another named DailyProteinGoal with a value like 200. These values will, of course, depend on your own targets and can be changed anytime, either by editing the table directly or through forms and code as your application evolves.
The next step is to make code that lets you easily read and write these values. To do this, add two functions to a standard module: PutSetting and GetSetting. When you want to store a setting, use PutSetting; when you want to retrieve it, use GetSetting. Each function takes a SettingName as input, with GetSetting returning the SettingValue, and PutSetting updating (or creating) the value.
Here is how you can set up these two functions in VBA:
For saving a value, use this:
Public Sub PutSetting(ByVal SettingName As String, ByVal SettingValue As Variant)
On Error Resume Next
Dim SV As String
SV = Trim(CStr(SettingValue))
CurrentDb.Execute "DELETE FROM settingT WHERE SettingName='" & Replace(SettingName,"'","''") & "';", dbFailOnError
If Len(SV) > 0 Then
CurrentDb.Execute "INSERT INTO settingT (SettingName, SettingValue) VALUES ('" & Replace(SettingName,"'","''") & "', '" & Replace(SV,"'","''") & "');", dbFailOnError
End If
End Sub
This code first deletes any existing record with that SettingName and then, if there is a value to store, inserts a new record. This process avoids having to check if the record exists ahead of time. If you pass a blank or Null value, the function just deletes any previous value for that setting.
For reading a value, use this:
Public Function GetSetting(ByVal SettingName As String) As Variant
GetSetting = DLookup("SettingValue", "settingT", "SettingName = '" & Replace(SettingName,"'","''") & "'")
End Function
This function simply looks up the requested setting name in the table and returns its value. If the value does not exist, it will return Null.
It is important to note that while this approach works very well for single-user databases or settings that apply globally, you will need to reconsider the design if you plan to build a multiuser system. In a multiuser setup where each user might have their own settings, you would likely want to expand your settingT table to include a UserID field, for example, so that each user's preferences are stored separately. Another consideration is historical data: If a user's preferences are likely to change over time and you need to track old values (for example, to revisit what a calorie goal was in the past), you may need a separate log table to store dated entries. For now, most users just need to read and write the latest value.
Once your table and functions are in place, you can use them anywhere in your Access database—on forms, in reports, or from queries and VBA routines. For instance, your startup code could read the user's preferred window position and size by calling GetSetting and then adjust the Access application accordingly. Or, from a form, you could let the user change their daily goals, saving the new value with PutSetting.
Storing settings in this way makes your application much more adaptable to changes and preferences without hard coding values, and without needing to change form properties or rewrite code each time a setting changes.
Remember to secure your code against SQL injection and error conditions, especially if any input comes from users. The samples above do basic safeguarding by escaping single quotes, but you can always improve upon this by using parameterized queries or additional error handling.
With these simple steps, you can easily save and load any user or application setting you like in your Microsoft Access database, giving you powerful tools for building flexible, user-friendly applications.
|
