App Settings 3
By Richard Rost
26 days ago
Create Configurable System Settings for Access Part 3 In part 3, we continue building the app settings feature in Microsoft Access by adding the ability to write user changes back to the settings table. I will show you how to create a reusable PutSetting subroutine that accepts different data types and safely writes the setting value using a recordset. We will discuss validating inputs, deleting and re-adding records, and how to trigger these updates from form events, allowing changes like main menu notes or default state settings to be stored in your database. MembersIn the extended cut, we will cover how to check if the settings table exists when the database loads and create it automatically if missing, work with system colors for defaults on form backgrounds, and make settings user specific so each user can have their own custom settings. I will show you how to implement these features step by step. 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!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, app settings, settings table, write value to table, DLOOKUP, manual SQL, function module, put value function, put setting function, ByVal, ByRef, data type Variant, recordset, error handling, delete value SQL, CurrentDb.Execute, OpenRecordset, AfterUpdate event, GetSetting, user specific settings, default state, system colors, code vault
Subscribe to App Settings 3
Get notifications when this page is updated
Intro
In part 3, we continue building the app settings feature in Microsoft Access by adding the ability to write user changes back to the settings table. I will show you how to create a reusable PutSetting subroutine that accepts different data types and safely writes the setting value using a recordset. We will discuss validating inputs, deleting and re-adding records, and how to trigger these updates from form events, allowing changes like main menu notes or default state settings to be stored in your database.
Transcript
Today is part three of my app settings video series. If you have not watched parts one and two, what are you doing here? Go watch one and two and then come on back.
Alright, so we are up to the point now where we have got this text box here that reads values from the main menu notes in the settings table. Now we want to be able to write them back. Writing them back is a little more difficult than reading them, but it is something we can handle. In fact, I did something very similar in this fast tip video I did a while back called "Notes Anywhere" where I show you how you can literally do the same thing. Drop a notes box anywhere you want.
Oh, someone is beaming in. But again, this involves DLOOKUPs and then manual SQL. We are going to wrap this in a function. If you want to get a leg up, go watch this "Notes Anywhere" video first. It is basically what we are doing, but we are wrapping that in a function so you can do it anywhere in your database.
So let us go right to the global module and we are going to write our put value function or put setting function. I always change the names in my brain.
Now, this can be a subroutine because it does not have to return a value. You could optionally make it so that it returns a true or false status if it was able, and if it fails, then you can put up a message or abort it somehow. But we are going to keep it simple for now.
So, Public Sub, it is a subroutine. PutSetting is the name. Now, we are going to send into it two values: the setting name and the setting value. In other words, which setting are you trying to change, like the main menu notes or the default state? And then what is the value you want assigned to that in the table?
I am going to specify for these ByVal. Remember the ByVal versus ByRef. The default is ByRef, so if anything changes the value, it could modify the original. We do not want to allow that just in case in the future you make some changes.
So we have setting name as a string and then ByVal setting value. Now here is the key. We are going to make this a Variant. What is a Variant? A Variant basically can be any data type, including nulls. You want to allow nulls to come in here because you might want to blank that value in the table. If the user blanks the value in that setting notes, it might send a null value in here. If you declare that as a string, it will force an error. So we are going to declare that as a Variant. It can accept any value type: dates, booleans, numbers, and null values. I have got a whole separate video on Variants.
Now, we are going to use a recordset to write this. You could use an SQL statement, but then you have to worry about escaping quotes and all that stuff. A recordset is just easier and cleaner.
So, Dim rs As Recordset. If you did not watch my recordset video and you are wondering what a recordset is, go watch it. That is why I give you prerequisites.
On Error Resume Next, just a real simple error handling in case of an error. Ideally, you should add some more robust error handling here so you can pop up error messages if something is unsuccessful, but for class, we are keeping it simple.
Now, if IsNull(settingName) or the settingName equals an empty string, then pop up an error message or whatever you want. I am just exiting the sub. Ideally, again, you should not have that problem because settingName is something you, the developer, specify. The value is what the user is sending in.
Now we are going to trim both of those and make sure they are string values. So, settingName = Trim(CStr(settingName)). It should already be a string, we are just double checking to make sure. We are going to do the same thing with the setting value. We are going to convert it to a string and trim it. So if they send in a date value, it is going to get converted to a string and then trimmed to make sure there are no extra spaces at the beginning or the end. So now we have two valid strings that we can confidently save to our table.
Now, just in case the value is already in the table, we are going to delete it and then add it again. This is easier than trying to look up the value to see if it is already there, and if it is there, then we edit it, if not, then we add it. That is a lot of work. So we are just going to delete the value if it is already in the table and then add it new.
To delete the value, that is simple. We can just use a little SQL statement for that. So, CurrentDb.Execute "DELETE FROM SettingT WHERE SettingName = '" & settingName & "'". CurrentDb.Execute allows you to execute an SQL statement. It is very similar to DoCmd.RunSQL. I have got videos for both of those things. There is a video you can watch that explains the two of them and the difference between them.
So we deleted that value if it is in the table already. Now it is a very simple matter to just simply add. So, Set rs = CurrentDb.OpenRecordset("SettingT"). Just open the SettingT, and then rs.AddNew or adding a new record. rs!SettingName = the setting name that I sent in, rs!SettingValue = the setting value that I sent. And that is now a valid string because we converted it to a string. rs.Update saves the value to the table. rs.Close. And then if you set it, you got to forget it. That is on a mousepad. Set rs = Nothing.
That is it. I prefer the recordset method because again, setting value could be anything. If you use an SQL string like this, if the user puts quotes in there or anything that can break the string, then you have to worry about it. This is so much better and easier. But for the delete, it is simple because you only have to worry about this one value. And setting name is something you control. The end user cannot type in a setting name. You set the setting name. So you are not going to make it a bad name when you develop the database.
Debug compile once in a while. Now we have got put setting available. So where do we put it? Well, we put it wherever we want to save that value, like for example, when the user changes this text box. So you come into here, you open up this guy, you go into his AfterUpdate event. I have got a bunch of blank lines in there for some reason. I will get rid of those.
Now after this is updated, we are going to put setting "MainMenuNotes", MainMenuNotes. Set it to the value of whatever is in that field. If this is confusing, you can always say .Value, but that is understood because it is the default property. You do not need that.
Save it, again debug compile. Close it, close it, open it, watch my database move again. Now we just got more laptops and good to go, sales team. Now when you close it, it saves it because it was in the AfterUpdate event. As soon as you leave that field or close the form, it saves it. Open it back up again. There you go, now it is saved in the table. I have got to keep my database around, fixed that in the last video.
If you want to test it with something else, let us test it with that default state value for example. Let us come up here. Let us pretend that this is a manager menu. It is no longer the main menu, it is a manager menu. We will put the default state in here for the manager to change.
In here, let us change this to default state. We are going to set the control source equal to nothing, get rid of the format. Now for the default value in here, I am going to bring in what the default value is now. So, GetSetting "DefaultState". Save that. Let us close it, open it back up again.
Alright, so we got New York in there. Let us move this over. Now we got New York in there, that is what we had. Now let us put an AfterUpdate event in here to change that. So event AfterUpdate, right there.
PutSetting "DefaultState", DefaultState text box value. So again, pretending that is a manager only field. Now if I come in here and make this California, hit Tab, leave that field so that event runs. Now when you go to add a new customer, California pops in there because you changed it in the table.
That is the value of having that GetSetting and PutSetting. Only the manager can do that, not everybody can do that, or you might want to allow the users to change that. That is up to you. If you are going to enter in a whole bunch of new records from Texas, let us say you got 15 orders from Texas, just come in here, you can change it to Texas and now when you open up this, it will be Texas, but it will be Texas for everybody.
Now, if you like learning this kind of stuff and you like my style, you enjoy learning from me, come check out my developer lessons. I teach this stuff in the order that it is supposed to be learned. So we start with the basics and then we build upon it. Each lesson builds on the one before it, so you are not jumping around watching all these different videos that I have to do here in the TechHelp series.
Check out my developer lessons. There is a link right there.
In the extended cut for the members, we are going to do a couple of different things. First, when the database loads, we are going to check to see if that settings table exists. If it does not, we are going to create it automatically. That is good if you want to drop this code into a brand new database. You just copy in that module code that I gave you earlier, and then when the database starts up, it says, oh, I do not see a settings table, let me create it for you. Then all you have to do is go in there and put whatever values you want in there. You do not have to build the table every time or copy it from another database.
I will show you how to work with system colors, so you can set defaults for the different colors on the page, like the form background and all that kind of stuff. That is kind of neat. We are going to make these settings user specific, so each user can have their own settings. Joe might have a default state that is Florida. Tammy might have Texas as her area that she works with. So her default is Texas. Every user can have their own user specific system settings, like the main menu notes. They can be set for each user so they can type in their own notes there.
That is all covered in the extended cut for the members. Silver members and up get access to all of my extended cut videos. Gold members can download these databases that I build and you get access to my code vault, where all of this code (well, not all of it, but 99 percent of the code that I make in these TechHelp videos and even in some of my lessons) goes in the code vault. It is all in there. You can just say, "I want the 'GetSetting' code." Search for it on my website, it will take you right to it.
Plus everybody gets some free training. If you are a platinum member, you get a free developer level course every month after you finish the beginner and expert series. So you get lots and lots of training. Everybody gets some stuff. Check it out.
There is the link right there. You will find a link down below. If you have any questions, post them in the comments down below.
That is going to do it for your TechHelp video for today. I hope you learned something. I hope you enjoyed this little three-part series. Let me know how I did. Post a comment down below. Live long and prosper, my friends. I will see you next time.
Quiz
Q1. What is the primary purpose of the PutSetting function introduced in this video?
A. To retrieve values from the settings table
B. To write or update values in the settings table
C. To format values before displaying them
D. To delete the settings table
Q2. Why is the settingValue parameter declared as a Variant in the PutSetting subroutine?
A. It only accepts string values
B. It allows any data type, including null
C. Variants are faster than other data types
D. To prevent SQL injection
Q3. What is the advantage of using a recordset to write values to the settings table instead of SQL statements?
A. It is required in all Access databases
B. Recordsets automatically encrypt data
C. No need to manually escape quotes or handle input formatting
D. It prevents accidental deletions
Q4. Why does the PutSetting subroutine delete an existing setting before adding a new value for the same setting?
A. To prevent duplicates and simplify the update process
B. To generate a backup before updating
C. To test the delete SQL command
D. To reset the auto-number field
Q5. Where is the call to PutSetting placed for saving changes to a setting in the user interface?
A. In the Form Load event
B. In the AfterUpdate event of the relevant text box
C. In the Form Current event
D. In a macro attached to the table
Q6. What is the effect of placing PutSetting in the AfterUpdate event of a text box?
A. The value is saved to the table as soon as you close the form
B. The value is only saved if you restart Access
C. The value is not saved until you close Access
D. The value is written to a log file
Q7. What happens if the settingName parameter is null or an empty string in PutSetting?
A. An error message is always displayed
B. The subroutine exits early without making changes
C. The database crashes
D. A default value is assigned
Q8. What is an advantage of making settings user-specific in the settings table?
A. All users must share the same preferences
B. Each user can have customized settings, like default state or notes
C. Settings become read-only
D. The database will use less memory
Q9. What is a suggested safety check before converting inputs to string and trimming them in PutSetting?
A. Ensure inputs are integers
B. Make sure inputs are not objects
C. Convert with CStr and Trim to avoid errors or input inconsistencies
D. Skip the conversion for performance
Q10. Why did the presenter mention the "GetSetting" and "Notes Anywhere" videos?
A. They show unrelated techniques
B. They introduce different kinds of databases
C. They cover similar concepts or prerequisites to the current topic
D. They discuss Access security topics
Answers: 1-B; 2-B; 3-C; 4-A; 5-B; 6-A; 7-B; 8-B; 9-C; 10-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's video from Access Learning Zone is part three of my ongoing series about storing application settings in an Access database. If you have not already watched parts one and two, I highly recommend pausing here and reviewing those first, since each part builds on the previous one.
In the earlier videos, I showed how to display a setting's value from a settings table using a text box. Now, we are ready to take the next step and write changes back to the table. This process is a bit more complex than simply reading values, but it is well within our abilities. In fact, this technique is very similar to something I demonstrated in my previous "Notes Anywhere" video, where I showed how to embed a notes box anywhere in your database. If you are interested in more background or want a head start, that video covers comparable ground, except for the fact that today we will be wrapping our code in a reusable function.
To accomplish this, we move over to our global module and start by creating a new procedure, which I call PutSetting. While you could craft this function as one that returns a status (like True or False to indicate success or failure), I am keeping it simple for demonstration purposes and making it a Sub since it does not need to return a value for now.
This subroutine accepts two parameters: the name of the setting you want to update (for example, 'MainMenuNotes' or 'DefaultState') and the value you want to save. For maximum flexibility, I declare the setting value as a Variant. This ensures it can accept any type of data, including Nulls, which is important because a user clearing out a setting should not cause errors. If you force it to be a String, passing a Null would trigger a problem, so using Variant gives us that flexibility. I cover Variants in more depth in another lesson if you are interested.
The best way to write to the table is by using a recordset. While an SQL statement could do the job, you often run into complications with string handling, escaping quotes, and related issues. A recordset is a cleaner, simpler approach.
I start with a bit of basic error handling using 'On Error Resume Next' to ensure minor problems do not crash the program. Ideally, you would enhance this with more robust checks that provide user feedback. I also validate the parameters, making sure the setting name is not empty or Null, and trim both the name and value to ensure they are clean strings with no leading or trailing spaces.
To avoid duplicate entries, instead of checking if the entry already exists and updating or inserting as needed, I opt to delete any existing value and then insert the new one. This approach is straightforward and avoids the need for multiple checks. Deleting existing values is a simple SQL statement, which works well here because the setting name is controlled by you, the developer.
Once the old value is removed, I open the settings table as a recordset, add a new record, and assign the setting name and value. After that, I update and close the recordset to save the changes, and perform some housekeeping by setting the object to Nothing.
This recordset method is much more reliable for handling a wide range of data types and values, because you are not as likely to encounter string parsing errors compared to building a dynamic SQL statement. The only exception is the deletion step, where a simple SQL statement is sufficient, since the setting name is always safe and not user-entered.
After compiling and saving the module, you can call this PutSetting subroutine wherever you need to store a user's change. For example, after a user updates a text box, you simply fire PutSetting with the appropriate setting name and value in that control's AfterUpdate event. This ensures the data is immediately saved when the user makes changes.
I demonstrate this by adjusting the logic for both the notes field on the main menu and another field representing something like the default state for new records. By applying the same process to different controls, you now have a flexible way to store and retrieve these values for your application, and only specific users (such as managers) can update certain settings if you design it that way.
If you are interested in structuring your learning more formally, consider my Access developer lessons. These courses guide you through concepts in an orderly progression, building on previous lessons rather than jumping between unrelated topics. There is a link provided for more details.
In today's Extended Cut video for members, I go several steps further. I'll show how you can automatically detect whether the settings table exists when the database loads, and create it if it does not. This is extremely useful if you want to copy this code into a new database, as the system will handle the table creation for you. I also cover storing and applying system color preferences, so you can set form color defaults. Most importantly, I demonstrate how to make these settings user specific. That way, each user can have personalized defaults, such as their own main menu notes or default state value, instead of sharing the same settings across all users.
Silver members and above have access to all extended cut videos, and Gold members get to download the databases, plus access my growing code vault which contains almost all of the code examples I use in TechHelp videos and lessons. Platinum members get additional free courses as they complete certain series, so there is always new content to help you improve your skills.
You will find links to all of these resources below. If you have questions, feel free to post them in the comments.
That wraps up this installment of Access TechHelp. I hope you enjoyed this three-part series and found it useful. 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
Creating a PutSetting subroutine to update app settings
Using ByVal and Variant data types for flexibility
Error handling with On Error Resume Next
Trimming and validating input values
Deleting existing setting records with SQL
Adding new setting records with recordsets
Implementing PutSetting in a form's AfterUpdate event
Saving textbox values to the settings table
Updating and testing settings like DefaultState
Using GetSetting and PutSetting functions together for dynamic defaults
Article
In this article, we will build on the concept of storing application settings in an Access database and learn how to write values back to your settings table using VBA. Specifically, we are going to create a subroutine called PutSetting that will allow you to easily update settings such as notes, default values, or any other configuration stored in your settings table. This method is flexible and works for strings, numbers, dates, and even nulls, making it practical for almost any application.
Suppose you have a text box on your main menu form that displays notes, and those notes are loaded from a field in your settings table. We want users to be able to change those notes, and save their edits back to the table. Reading data from a settings table is usually straightforward, but writing data back can be a bit trickier, especially if you need to support various data types or handle blank values.
To solve this, we will place our code in a global module so it is easy to call from anywhere in your database. The subroutine we are about to create will take two arguments: the name of the setting you want to write, and the value you want assigned to that setting. We will call our subroutine PutSetting.
Here is the code for the PutSetting subroutine:
Public Sub PutSetting(ByVal settingName As String, ByVal settingValue As Variant)
Dim rs As Recordset
On Error Resume Next
If IsNull(settingName) Or settingName = "" Then Exit Sub
settingName = Trim(CStr(settingName))
settingValue = Trim(CStr(settingValue))
CurrentDb.Execute "DELETE FROM SettingT WHERE SettingName = '" & settingName & "'"
Set rs = CurrentDb.OpenRecordset("SettingT")
rs.AddNew
rs!SettingName = settingName
rs!SettingValue = settingValue
rs.Update
rs.Close
Set rs = Nothing
End Sub
Let us walk through what is happening here. We declare a recordset variable, and use a basic error handler with 'On Error Resume Next' for simplicity. If the setting name is missing or empty, we exit right away as a safeguard. We then trim both the setting name and setting value, coercing them to strings just in case any extra spaces sneak in, though the setting value is actually declared as a Variant. That allows it to accept any data type, including null values, which is important if a user leaves a field blank and you want to store that as an empty or null setting.
Next, before writing the new value, we delete any existing record for this setting name from the settings table. We do this with a simple SQL statement using CurrentDb.Execute. This is safe because the setting name is always controlled by you, the developer, so there is no risk of user input breaking the SQL syntax.
After ensuring there is no existing record for that setting, we open the settings table as a recordset and add the new record. We assign the setting name and value, then call .Update to save the record, and finally clean up by closing the recordset and releasing the variable.
To use this functionality in your form, you simply call PutSetting in the AfterUpdate event of the relevant control. For example, suppose you have a text box named MainMenuNotes which you want to save back to your settings table whenever the user edits it. In the AfterUpdate event for that text box, write the following code:
PutSetting "MainMenuNotes", MainMenuNotes
This call passes the setting name ("MainMenuNotes") and the current value in the control to the PutSetting subroutine, which then saves it in the SettingT table. Now, whenever the user changes the contents of the MainMenuNotes box and moves away from it, the new value is immediately written to the table.
You can use this same pattern for other settings. For instance, suppose you have another text box for a "DefaultState" setting. In its AfterUpdate event, write:
PutSetting "DefaultState", DefaultState
Now, when a manager or user changes the default state in that field, it is saved back as the new default value for the application. This becomes especially useful if, for example, you want to pre-fill new records with a different state depending on recent input or user preference.
This PutSetting approach, paired with your existing GetSetting function to read values, gives you a flexible system for managing application settings of all types from any part of your database. All you need to do is add PutSetting calls wherever you allow users or managers to update settings.
A few extra notes for best practices: while this code snippet uses basic error handling, in production you might want to expand the error handling to capture and report unexpected errors, especially if saving settings is a critical part of your workflow. Additionally, deleting and re-inserting records for updated settings is simpler than testing for existence and updating, but if performance is a concern or your table is very large, you may consider using an SQL UPDATE statement with proper value escaping.
You may also want to extend this pattern for user-specific settings or other advanced features. For now, however, this solution gets you up and running with robust application settings management.
Try this approach in your forms, and you will have a reliable way to let users and managers change application settings at runtime, with all changes safely stored in your backend settings table. With PutSetting in your toolbox, updating app settings becomes both simple and highly flexible.
|
