App Settings
By Richard Rost
31 days ago
Create Configurable System Settings for MS Access
In this video, you will learn how to create configurable system settings for your Microsoft Access database using a simple system settings table. I will show you how to set up a table to store name-value pairs, use DLookup to retrieve settings, and then make the process easier by building custom GetSetting and PutSetting functions for managing application defaults, toggling features, and handling scenarios like temporarily disabling order entry during inventory. This video is aimed at developer-level users and covers essential VBA topics and best practices for making your Access databases more flexible and maintainable.
Graham from Fort Worth, Texas (a Platinum Member) asks: I want parts of my database to be configurable without having to change VBA code and push out an update every time. For example, I want to be able to change default values, toggle certain features on or off, or temporarily disable part of the system. When we do inventory on Sunday afternoons, I want to disable the order entry system so no one can place an order, and instead they get a message saying the order entry system is down during inventory. What's the easiest way to store and retrieve system-wide settings like this in Microsoft Access?
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
Recommended Courses
Up Next
Keywords
TechHelp Access, app settings, GetSetting, PutSetting, configurable system settings, system settings table, DLookup, system defaults table, type conversion functions, CStr, CLng, CCur, CDate, Recordset, Nz function, error handling, SQL, ByVal, BeforeUpdate event, global module, design view, long text field, SettingT, SettingID, SettingName, SettingValue, setting default value, restrict order entry, message popup, network drive, update default values, toggle features
Subscribe to App Settings
Get notifications when this page is updated
Intro
In this video, you will learn how to create configurable system settings for your Microsoft Access database using a simple system settings table. I will show you how to set up a table to store name-value pairs, use DLookup to retrieve settings, and then make the process easier by building custom GetSetting and PutSetting functions for managing application defaults, toggling features, and handling scenarios like temporarily disabling order entry during inventory. This video is aimed at developer-level users and covers essential VBA topics and best practices for making your Access databases more flexible and maintainable.
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, we are going to talk about app settings, specifically how to create configurable system settings using two functions we are going to create called GetSetting and PutSetting. For your Microsoft Access database, this will allow you to change defaults and the behavior of your application without actually rewriting code and pushing an update.
Today's question comes from Graham in Fort Worth, Texas, one of my Platinum members. Graham says, I want parts of my database to be configurable without having to change VBA code and push out an update every time. For example, I want to be able to change default values, toggle certain features on or off, or temporarily disable part of the system. When we do inventory on Sunday afternoons, I want to disable the order entry system so no one can place an order, and instead they get a message saying the order entry system is down during inventory. What is the easiest way to store and retrieve system-wide settings like this in Microsoft Access?
Well, Graham, in a nutshell, the easiest way to do this is to simply make a system settings table, which we are going to do today. Then you can read and write values to that table, and if it is shared on a network drive on your backend, everybody on the network can read the same set of system defaults.
About three years ago, I did a video where I showed you how to set up a system defaults table, but we just literally set up the table, and then we used DLookup everywhere in the database to read from that table. That is okay; that works. But today we are going to take it a step further. We are going to make it so the users can read and write values, and we are going to wrap all of that in a function to make it much, much easier to use throughout your database instead of having to use DLookup calls everywhere.
It could not hurt to go watch that video first to give you a little leg up on what we are doing today.
Now, this will be a developer-level video. There are lots of things you need to know before this one. So let me give you a list of prerequisites.
Intro to VBA is first. If you have never done any VBA programming before, go watch this. It is about 20 minutes long. It will teach you everything you need to know to get started. Do not worry. VBA is not scary.
You are going to have to know variables. We are going to use the Trim function and a bunch of functions today. We are going to be using type conversion functions, especially CStr (convert to a string). You are also going to want to learn CLng, CCur, and CDate. Where are you, CDate? It is in there somewhere.
We are going to use a Recordset. We are going to use DLookup, the Nz function, and make sure you know how to use those nasty double-double quotes. They confuse a lot of people. Some basic error handling. A little bit of SQL. We are going to create our own functions. See, I told you I was not messing around with this one. Lots of prerequisites.
This is one of the benefits to taking my full course: I cover this stuff in order. You are going to need to know how to use ByVal and what that means. We are going to use the BeforeUpdate event.
Did you get all that? Go watch all that stuff first if you do not know what any of that is. If not, you have a whole afternoon of videos to watch. Then come on back and we will get started.
Alrighty, so here I am in my TechHelp free template. This is a free database. You can grab a copy off my website if you want to. You will find a link to this down below. All those videos I gave you earlier you will find links to in the description text below the video window.
The first thing we have to do is set up a table to store our settings in. So let's create a table in design view. Very simple table: it is just going to be SettingID (our autonumber), SettingName (that will be short text), and then SettingValue. I am going to save that as long text. The reason why is because that lets us store everything in the table: you can store text, numbers, dates, whatever you want. They are going to be stored as long text, so that field can accommodate everything.
It is going to be your job, however, when you read that value back to convert it to whatever you want. We will talk about this a little bit later. So if you are storing a date in there, when you read it, it is going to be a text value. You are going to have to use CDate or those type conversion functions I talked about earlier and convert it to a date.
We are going to go over some examples a little bit later. Let's save this as SettingT. Set the primary key, and there we go. Let's put some sample values in here.
Real quick, notice that in the Extended Cut for the members, we are going to write a separate function that, when you start the database, is going to check to see if this table exists, and if it does not, it will create it for you. It is going to be really cool. That will be in the Extended Cut.
Let's put in some sample names. It is basically name-value pairs. So let's say you want to, like Graham mentioned earlier, have OrderSystem, and then I will put "Closed" in here. You put whatever you want, "Open" and "Closed," "True" and "False," whatever.
We are going to read that back later, and if that value is closed, we will give the user a message that says we are doing inventory, so you cannot get in here.
What are some other things you can do? How about the default state? Let's say this database is used by several different offices, and you just set the default state to whatever state that office is in. Florida for this one.
Let's do a numeric value. How about MaxFamilySize? On my customer form, I have a family size field just to test a number. Let's set that to 20, so if the user types in a family size larger than 20, they get an error message.
How about MainMenuNotes? This will be a notes field you can have on the main menu, just like I did in the other system values video. I will show you how it becomes much easier to use now that we have it wrapped in a function. We will just put in here, "Hi there." This can be long text.
Now that we have some values in here, let's test this "OrderSystem Closed." Let's do it first without the functions, and then you will see how making functions for this can be a whole lot easier.
We are going to assume that the only way your users can add an order to this system is by going through the customer form and clicking on the Orders button. I like doing that in my databases. I like to have so there is only one way for users to get to certain things. Obviously, we have the database locked down so they cannot get to this. You do not want your end users playing with this. I have many videos to show you how to lock this stuff down and hide this stuff.
So to create an order, they have to open the customer up first and then click the order button. If not, then you have to put the code to check this in the form itself. But if this is the only way they can make an order, we can just simply put the code in this button.
Right-click, Build Event.
In this button code, we are going to look up that value. So we will say
Dim s As String
s = Nz(DLookup("SettingValue", "SettingT", "SettingName = 'OrderSystem'"), "")
So it is going to go to the setting table, look for SettingName = "OrderSystem," and then bring back the SettingValue, which is "Closed." That is what DLookup does.
Now,
If s = "Closed" Then
MsgBox "We are doing inventory, come back later."
Exit Sub
End If
You can do an Else here if you want to, but either Exit Sub or do an Else. I like to exit the sub instead of doing an Else because if you do an Else block later on in the future and you do more stuff down here, you might not realize it might not have passed this point. So that is why I like to do an Exit Sub and then get out of there because everything that comes beyond that point is now moot. If it is anything but closed, then it opens up the form.
Save that, debug compile once in a while, close this, reopen it, and try to place an order. "We are doing inventory, come back later." Now it is Monday or you are done with inventory. You, the manager, can come back in the setting table, and you can make yourself a nice interface for this that only you as manager can get to. You just come in here and put in "Open" like that.
You can also put notes over here for yourself in another field. I sometimes do that. I will put a notes field in here so I can put notes just for myself, like "put Closed if you want to close the inventory system," so you remember what to do. Or you could make it a combo box on a form. You could do all kinds of stuff with this.
Now that the order system is open, we can get back in. That is basically how that works.
This is all fine and dandy and it works and this is basically what we did in the last video. But wrapping all of this and remembering to do this as a DLookup everywhere you want it gets to be a pain. It would be nice if I could just say
If GetSetting("OrderSystem") = "Closed" Then
' do whatever
End If
instead of having to do all this DLookup stuff everywhere.
That is why I want to wrap this in a function. So let's go to our global module, and if you do not have one, create one. It is up here under Create, then Module (not Class Module). In this database, I already have one right here, the global module.
I will put it right up top here. We are going to make our own function that is going to basically do the same thing, just return a value. It is public so everybody can use it. It is a function that is going to return a value as opposed to a sub that does not. GetSetting is the name of it. What are we going to ask for? We are going to send in the setting name as a string and it is going to return a string. That is what that means.
Now, I do not usually put a ton of checking in here like making sure this is not an empty string because this is a function that you, the developer, are going to use. So you do not usually have to worry about end users breaking stuff if you are the end user, but it is up to you. If you want to future-proof against yourself, you can put in here:
GetSetting = ""
If SettingName = "" Then Exit Function
In other words, you are setting the return value as an empty string, and if you somehow send yourself an empty string, just exit out and return the empty string.
This is stuff that you usually do not have to do if you are just protecting against yourself because you are not going to use GetSetting wrong and send it an empty string. But you never know, you might. Usually you are smarter, but sometimes it goes the other way.
Assuming your code is good and you use it properly, we are going to say in here
GetSetting = Trim(Nz(DLookup("SettingValue", "SettingT", "SettingName = '" & SettingName & "'"), ""))
That will return the value that the user asks for. If you really want to be sure that this is safe, you can put an On Error Resume Next in here too. Just in case this throws any kind of an error, it is not going to crash the database. If this thing errors out, you are going to get returned an empty string.
Debug compile once in a while. Save it, close it.
Now in this button, instead of doing all of this, we can get rid of that stuff right there. All you have to say here instead is:
If GetSetting("OrderSystem") = "Closed" Then
MsgBox "We are doing inventory, come back later."
Exit Sub
End If
See how much easier this gets now? You do not have to remember DLookups and field names and all that stuff anywhere. You just have to remember GetSetting and the name of the setting that you want.
Save it, debug compile again, close it, open it, and it worked.
Now, let's close the order entry system. It is Sunday. We are doing inventory. Come in here and mark this Closed, or open your manager form and check the box, whatever you want to do. Now when you try to open it, "We are doing inventory, come back later."
Now, what is the benefit here? We wrapped that in a function, and this function is easy to use everywhere. You can use it in more places than just your VB code.
So remember that other setting that we did, DefaultState? Watch this. Let's go into our state field. Let's go to the default value here. I am going to zoom in (Shift+F2) so you can see what I am doing. I am going to put in here:
=GetSetting("DefaultState")
Now the default value for the state field is =GetSetting("DefaultState"). Make sure you put that equal sign in the front. Save it, close it, open it, go to a new record, and there is your "Florida." Let's make sure it is not in the table. Let me check CustomerT.State. I did not put a default value in here, so that proves that it is working.
If you want to change that, if you give this database to your New York office, all you have to do is go into the setting table and change this to "New York," and then give them a copy of the database. Now when they go to add a customer, it is "New York."
See how easy that is now to change settings everywhere in your database?
We can do the same thing with checking the family size. You could put your notes field on the main menu. More importantly, we can write a function to also put settings back to that table.
We will talk about all of that in tomorrow's class. So come back tomorrow, same Bat-time, same Bat-channel. I used to love that show.
For members, you can watch it right now because I am going to keep recording today and keep doing this, and we are going to see what happens.
That is going to be your TechHelp video for today. I hope you learned something.
Live long and prosper, my friends. I will see you tomorrow for part two.
Quiz
Q1. What is the main benefit of creating configurable system settings in a Microsoft Access database?
A. It allows you to change application behaviors without modifying code and distributing updates
B. It lets users bypass security settings
C. It improves query speed for reports
D. It automatically optimizes your code
Q2. What is the simplest way to store and retrieve system-wide settings in Microsoft Access, according to the video?
A. Hard-coding values in VBA
B. Storing settings in a dedicated table
C. Using environment variables
D. Storing settings in the registry
Q3. Why is "SettingValue" stored as Long Text in the SettingT table?
A. So you can store large amounts of text only
B. So you can store text, numbers, dates, or anything as text
C. Because Short Text fields are incompatible with VBA
D. To improve network performance
Q4. When retrieving a value stored in SettingValue, such as a date, what must you do before using it in its intended context?
A. Convert to the appropriate type using type conversion functions like CDate
B. Always keep it as text and never convert
C. Use DLookup again to find the type
D. Delete the record after retrieving
Q5. What is the limitation of using DLookup everywhere to retrieve settings?
A. DLookup cannot be used in VBA
B. DLookup is only available on reports
C. Using DLookup everywhere is repetitive and error-prone
D. DLookup automatically converts values to numbers
Q6. What does the GetSetting function do?
A. It wraps the DLookup call to retrieve a setting value by name and returns it as a string
B. It deletes settings from the table
C. It disables error handling
D. It creates a new settings table
Q7. Why might you want to use error handling like "On Error Resume Next" in the GetSetting function?
A. To prevent the application from crashing if there is an error retrieving a setting
B. To slow down performance
C. To pause the code for debugging
D. To automatically clear the settings
Q8. Once you create the GetSetting function, what becomes easier in your application?
A. Referencing settings anywhere by calling the function with a setting name
B. Adding new tables for every setting
C. Writing out long DLookup statements everywhere
D. Disabling form events globally
Q9. How can you set a default value for a field (such as "State") using your new settings system?
A. Enter =GetSetting("DefaultState") as the field's Default Value property
B. Use DLookup in the table's property sheet
C. Hard-code "Florida" as the field value for all records
D. Add a macro to the form
Q10. After implementing the settings system, how could you change the default state for a different office?
A. Change the SettingValue for DefaultState in SettingT
B. Update all customer records individually
C. Recompile all VBA code
D. Delete and recreate the table
Q11. What is a suggested way to restrict users from accessing or editing the settings table directly?
A. Lock and hide the relevant forms and tables from general users
B. Give everyone full access to all tables
C. Store the settings in user-specific tables
D. Remove primary keys from the table
Q12. Which VBA concept is NOT a prerequisite for this video?
A. Understanding of Recordsets
B. Using the Trim and Nz functions
C. Advanced .NET programming
D. Basic error handling
Q13. What is the purpose of the SettingID field in SettingT?
A. It is an autonumber primary key for unique identification
B. It stores the setting value
C. It holds the name of the setting
D. It is used for storing notes only
Q14. What would you use the type conversion functions CStr, CLng, CCur, and CDate for in the context of this settings system?
A. To convert the retrieved setting value from text to the appropriate type as needed
B. To format dates in table design
C. To encrypt the settings table
D. To import data from Excel
Q15. Why does wrapping the settings retrieval logic in a function help long-term maintenance?
A. It centralizes logic and reduces duplicated code
B. It slows down the database intentionally
C. It makes adding new tables easier
D. It removes the need for VBA completely
Answers: 1-A; 2-B; 3-B; 4-A; 5-C; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-C; 13-A; 14-A; 15-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 video from Access Learning Zone focuses on how to create configurable system settings for your Microsoft Access database. The main goal here is to allow you to change default values and adjust features in your application without needing to rewrite any VBA code or push out an update for every small change.
Let's look at a specific example. Suppose you want to turn certain features on or off, alter default values like the default state for new customers, or temporarily disable a section of your system, such as disabling the order entry process during scheduled inventory. Rather than editing VBA and distributing a new version each time, you need a centralized way to store and retrieve these system-wide settings that everyone connected to the backend database can access.
The easiest way to accomplish this is by creating a dedicated table in your database specifically for system settings. I will walk you through setting up this table, which will serve as a simple place to read and write configuration values. By sharing this table in your networked backend, all users will work from the same set of configuration data.
A few years ago, I showed how to set up a defaults table and retrieve values using DLookup, but that older approach required you to spread DLookup calls throughout your database code. While functional, it's not ideal. Today, I want to refine that by wrapping all the code for reading and writing settings into two functions: GetSetting and PutSetting. This encapsulation will drastically simplify maintenance and usage going forward.
Before jumping in, there are some prerequisite skills you need. You should be familiar with the basics of VBA, including working with variables and common functions like Trim, Nz, and type conversion functions such as CStr, CLng, CCur, and CDate. You should also know how to create and use Recordsets, as well as some basic SQL and error handling. We will work with custom functions and understand the use of ByVal and the BeforeUpdate event. If any of these are new to you, I recommend reviewing some of my earlier material on these topics first.
With those basics out of the way, let's build the foundation. The system settings table should be simple: an autonumber field for the key (SettingID), a short text field for the name (SettingName), and a long text field for the value (SettingValue). Each setting is stored by name, paired with a value. The long text type allows maximum flexibility, storing text, numbers, dates, or whatever you need, though you'll be responsible for converting the value back to its correct data type at runtime, using conversion functions like CDate if needed.
Once the table is set up, you can begin to add configuration items. For example, you might store whether the order entry system is open or closed, the default state abbreviation for a location, numeric settings such as a maximum family size, or a main menu note for users. All these can be adjusted directly in the table by a manager or using a secure interface you create for yourself.
As a quick example, let's say the only way users in your database can place an order is by clicking a button on the customer form. In the button's code, you'll want to check whether the order system is open or closed. Previously, this would involve a DLookup statement looking up the 'OrderSystem' setting in the table. If it's set to 'Closed,' a message pops up and the subroutine exits. Otherwise, the code continues with order entry.
While this DLookup approach works, it becomes tedious and redundant as your application grows. That's why wrapping all of this into a GetSetting function is such a benefit. Now, whenever you need to read a configuration value, you simply call GetSetting with the setting name. Internally, the function performs the DLookup and handles cases where a value might not be found, reducing repetitive code and avoiding common errors like typos in field names.
By using this function, you also make it easier to call settings from places other than your VBA code. For instance, you can set the default value of a field, such as the state field in your customer form, to =GetSetting("DefaultState"). This way, if the database is deployed to another office, you simply change the setting in the table and new records will use the updated default automatically.
This pattern of using a settings table with GetSetting illustrates how you can configure your application on the fly, without code changes. If you want to allow managers to open or close the order system, swap out a default state, or alter any other global setting, you just update the table. The rest of your application simply responds based on the value returned by the function.
In addition, for members, the Extended Cut covers how to have your application check for the existence of the settings table at startup, automatically creating it if it doesn't exist. This adds robustness to your deployment, allowing your settings framework to initialize itself seamlessly.
Tomorrow, I'll cover the companion PutSetting function, which will let you programmatically write settings back to the table. We will continue building on this framework to make your Access development more flexible and efficient.
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 system settings table in Access
Storing configuration values using name-value pairs
Examples of configurable settings like order system status
Manually retrieving settings with DLookup and Nz
Using type conversion functions to interpret setting values
Locking down access to the settings table for end users
Checking a setting in VBA to control form behavior
Creating a global GetSetting function in a module
Using GetSetting in VBA and form default values
Applying settings to form controls at runtime
Making configuration changes available without code updates
Article
In this tutorial, you will learn how to make your Microsoft Access application more flexible by adding system-wide settings that you can change at any time without touching your VBA code or redeploying your database. This approach is perfect for situations where you need to tweak the default values of fields, turn features on or off, or temporarily disable parts of your system, such as turning off order entry during inventory.
To achieve this, we will build a simple but very effective method: storing configurable settings in a table, then using custom VBA functions to get and set those values. This method is much easier and cleaner than sprinkling DLookup expressions all over your code or forms.
Let us start by creating the settings table. In Access, switch to table design view and build a table called SettingT. Give it three fields: SettingID (an AutoNumber primary key), SettingName (Short Text), and SettingValue (Long Text). The idea is that SettingName identifies what your setting does, and SettingValue stores the value. We use Long Text for SettingValue so you can put anything in there: numbers, dates, text, or even long notes. When you read the value back, it will always come back as text, and you will convert it as needed in your code.
Here is a sample of what you might have in SettingT:
- SettingName: OrderSystem, SettingValue: Closed
- SettingName: DefaultState, SettingValue: Florida
- SettingName: MaxFamilySize, SettingValue: 20
- SettingName: MainMenuNotes, SettingValue: Hi there.
For the OrderSystem setting, for instance, you can toggle it between "Open" and "Closed" as necessary. When OrderSystem is set to "Closed," you want to prevent users from placing orders and display a message explaining why.
Without wrapping this in a function, you might use DLookup directly in your button code, for example:
Dim s As String
s = Nz(DLookup("SettingValue", "SettingT", "SettingName = 'OrderSystem'"), "")
If s = "Closed" Then
MsgBox "We are doing inventory, come back later."
Exit Sub
End If
With this, clicking the button to place an order during inventory simply shows a message and stops the process. As a convenience, you or another manager can open the SettingT table (or a dedicated admin form) and change "OrderSystem" back to "Open" when orders can be processed again.
While DLookup works, having to repeat that logic throughout your app is tedious and error-prone. To make life easier, let's wrap the entire process in a reusable VBA function we will call GetSetting.
Open the VBA editor and create a standard module (not a class module). In that module, add the following function:
Public Function GetSetting(SettingName As String) As String
GetSetting = ""
If SettingName = "" Then Exit Function
On Error Resume Next
GetSetting = Trim(Nz(DLookup("SettingValue", "SettingT", "SettingName = '" & SettingName & "'"), ""))
End Function
This function takes the setting name you want, looks up its value in SettingT, and returns it as a string. The On Error Resume Next is there to prevent any accidental errors from crashing your code, and the function returns an empty string if anything goes wrong or the setting is not found.
Now, instead of DLookup every time, all you need to do is write:
If GetSetting("OrderSystem") = "Closed" Then
MsgBox "We are doing inventory, come back later."
Exit Sub
End If
This approach is much cleaner, easier to read, and avoids copy-pasting DLookup statements with field names everywhere.
There is another bonus to this method: you can use GetSetting in a variety of contexts. For example, suppose you want the default value of your State field on the Customer form to reflect whatever is stored in your settings. Go to the table or form in design view, select the State field, and for its Default Value property, enter:
=GetSetting("DefaultState")
Now, whenever you add a new customer, the state field will pre-fill with whatever value is in SettingT under DefaultState. If you need to change it for a different office, just update the SettingValue in SettingT, and you are done.
This technique works for numeric settings too. For example, you can create a MaxFamilySize setting and check it when users try to enter a new family size, converting the value to a number in your code as needed:
If CLng(GetSetting("MaxFamilySize")) < NewFamilySize Then
MsgBox "That family size is too large. The maximum allowed is " & GetSetting("MaxFamilySize")
Cancel = True
End If
Remember, since SettingValue is stored as text, when you need a number, date, or currency, simply convert it using the appropriate VBA function: CLng for numbers, CCur for currency, CDate for dates, and so on.
With this system in place, changing your default values or toggling features on or off becomes as simple as editing a value in the SettingT table. You could even build an admin-only form to manage these settings securely, so non-technical staff can tweak things without your intervention.
In summary, setting up a system defaults table with a reusable GetSetting function is a powerful way to add flexibility and configurability to your Microsoft Access applications. No more hard-coding defaults or redeploying code for simple changes. Read, update, and use settings wherever you need them in your application with just a single line of code, making both development and maintenance far easier.
|
