App Settings 2
By Richard Rost
29 days ago
Create Configurable System Settings for Access Part 2
In this video, we continue working with configurable system settings in Microsoft Access by expanding on our get setting function to handle different data types, including numbers and text. I'll show you how to validate numeric values, add helpful error checking, and use the Before Update event to ensure your settings are applied correctly. You'll also learn how to display and retrieve a notes field on an unbound form, and see tips on UI presentation and useful code commenting practices. This is part 2.
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, configurable system settings, get setting function, put setting function, IsNumeric, before update event, Max Family Size, data type conversion, string to number, CLNG, numeric validation, message box warning, Boolean handling, IsDate, CDate, main menu notes, unbound form, On Load event, system bulletin
Subscribe to App Settings 2
Get notifications when this page is updated
Intro
In this video, we continue working with configurable system settings in Microsoft Access by expanding on our get setting function to handle different data types, including numbers and text. I'll show you how to validate numeric values, add helpful error checking, and use the Before Update event to ensure your settings are applied correctly. You'll also learn how to display and retrieve a notes field on an unbound form, and see tips on UI presentation and useful code commenting practices. This is part 2.
Transcript
Today's part two of my app settings video, where we're learning how to make configurable system settings and our own get setting and put setting functions in Microsoft Access.
Today's part two, so what does that mean? If you haven't watched part one, go watch part one first. You'll find a link down below in the description.
For the rest of you, here we go. One more prerequisite today that I forgot to mention in part one: go watch my IsNumeric video. You can check a string value to see if it's got a numeric value in it.
Yesterday we created our setting table where we got our system settings in here. We've got our get setting function that we wrote right there to get a setting from the table so we don't have to DLookup it every time. We set the get setting default for the state and for our order entry system so we can lock it.
Today let's talk about using other data types, like numbers for example. We put a setting in here: Max Family Size is 20. Now, when this value gets returned by get setting, it's being returned as a string value. So we are in charge of converting that to the right data type.
Let's say, for example, we want to use our family size, our max family size setting. Now, in order to check to see if this value is correct or not, we are going to put it in the before update event.
The before update event runs after the user types in the value but before it gets committed to the table. Of course, I told you last time to watch my before update video so you understand all that.
We are going to go to Events and then Before Update... that opens it up. Now, we are going to retrieve the value, convert it to a long integer because that's what the family size value is, and then we can compare the two. So we are going to need S as a string and L as a long.
Now, S equals get setting max family size. That's the setting in the table. But now it's stored in a string value. So now we have to convert it over to a long.
Now, if you want to check first to make sure it's valid, because again, if you want to future proof against yourself, you could check to see if it's numeric. You could say, if not is numeric S then, and give yourself a warning here: message box, warning max family size is not numeric. Cancel equals true and then end if and exit the sub here. Exit sub.
The user is kind of stuck at this point. They really can't do anything because you messed up - the value that you put in the table isn't numeric or it doesn't exist. If it returns an empty string, that's not numeric either.
But now, assuming you've got a valid value in that max family size, now we can convert it. We can say L equals CLNG (convert to long) S. So now L has the number in there. So if it's 20, S is going to have in it the number 20, but as a string (picture it in quotes). Now we've converted it to the numeric value, 20.
And now we can say, if family size is greater than L, then this is the error for the user: message box: family size is too large. Max is, and then you can tell them what the max is, L. And here again, we'll go cancel equals true, exit sub, end if. Otherwise, it'll save the value.
Let's put a little commenting in here:
- Get the value from the table
- Check to make sure it's numeric
- Convert to number
- Check value against what was entered
Comments are always good. It might seem rudimentary for you now, but trust me, three years from now, ten years from now... I never thought I'd be using the same database for 20 years running in my business, but I am. I'm still finding old code that I wrote 20 years ago and thinking, what was I thinking? So these comments, even if you're a solo developer, are for future you. These comments are for you ten years from now, so you can look at this quickly and go, okay, check them out, I got it.
Trust me. I don't always comment everything in my videos or my classes, but I try to emphasize, for actual production stuff, put comments in.
Debug, compile once in a while. Let's close it. Close it, save it, open it. If I put a five in here, I'm good. If I put a 50 in here, I'm not good: family size is too large, max is 20. So I can come back in here and put in 19.
Now, let's check that other condition. I like to always check this kind of stuff. Let's say you were goofing around and you put in here an X, which is not numeric. So now, if we try to put something in here, even if it's valid, family size is not numeric.
That's a you-fault, but the user now is stuck. They can't put something in there. Double-check and make sure. You could also code in, like, if it's not numeric, assume it's whatever. That's another thing you could do. You could say, if not numeric, then... or if this value is missing or whatever.
If not numeric, then we could say in here, instead of dropping the user out, we could say S equals 20. You could hard code that, like that. Check to make sure it's numeric, if not, default to 20, which is hard coded. Or, if S equals blank or not is numeric. This way, if you forgot to put that value in, you're at least getting a hard-coded 20 and the user isn't getting that error message.
Whatever you want to do, this is your database. I'm just showing you all the Lego pieces. You put them together however you want to. I'm just giving you options.
Now, this will be the same with Boolean values. This will be the same with date values. If you're returning a date value from your table, make sure you convert it to date. There's an IsDate function. Check to see if it's a date first, and if it's a valid date, then use CDate to convert it to a date. Same thing.
I'm not going to run through an example right now, but if you want to see an example, post a comment down below. Let me know and I'll run through an example for you if enough people are interested. Squeaky wheel gets the grease.
Now, let's talk about that notes field. We've got the main menu notes. I want to put a notes field block right down here on the bottom of my main menu that people can type in notes.
Now, the main menu form is just a generic menu form. It's not bound to a table. So there's no data in it. There's no records behind this form because there's no control or a record source. No source for controls, record sources for forms and reports.
You could bind this to a table, but I don't like doing that with menu forms for lots of different reasons. What I'm going to do is just stick a notes field down here and everybody can just type in whatever notes down here. For example, if you want Sally to see the reminder for something or you want to just put in here that we're 86ing mashed potatoes, whatever you want to put down here.
So, the status box, copy, paste, and I'm just going to make this guy a little bit different in size. Let's do maybe like that. We'll slide it over here. We'll do something like this. Let's make it blue. There. Bottom back up.
I'm all about presentation. A pretty database with a nice interface is easier to use than something that's a cluster with lots of weird colors. Presentation is a lot.
Let's make it blue and we'll make the foreground color white and we'll make it a little bit bigger text so it's easy to read. And let's give it a name. Let's call it... Oh, not totally, we'll call slide up to the top. There we go. Let's call it main menu notes.
Save it. Close it. Close it. Open it. Now, I'm not going to move it. Right now, you could type stuff in here but it's not going anywhere. And if you close it and reopen it, it's back to blank. I get it because I got code in my database that slides it over to a specific spot.
So what we're going to do is first, we're going to look up the main menu notes that are in my main menu notes setting for the table. We're going to do that when this form opens up.
So, design view. Come into here. Go to your Events. Now, I've already got an On Load event in here. You can use On Load or On Open, it doesn't matter. I've got the "load app window position." This is for me. It snaps it to a certain size and location on my screen for recording videos, which right now just happens to be slightly off-center from the bottom I'm actually recording in.
But in here, all we have to put is: main menu notes = get setting main menu notes. That's the setting in the table. It's called main menu notes. We'll check that and make sure. Main menu notes.
So when the form loads, it's going to get setting main menu notes and stick that into the main menu notes text box that we just created.
Save it. Debug, compile. Close it. Close it. You're ready for my main menu to snap over again. And there we go. We got "hi there" in there. In fact, let me move things around real quick. I'm going to move this over, that's my recording window. So now it won't happen again.
Now this is all fine and dandy. As it is right now, you can use this just as a billboard. You can use this as a manager billboard. You can come in here and say, you know, we're out of the Christmas special laptops, or whatever. So it's an easy spot for you to put something in there so that when you open it up, the users will all see that system bulletin, basically. That alone is pretty functional.
But if you want to allow people to be able to edit this, then all you need to do is allow them to save that value back to the table. To do that, we're going to write a put setting function, and we're going to do that in part three.
Now, I know the slide says "to an intomaro," but today, if you're watching these as they're being published, this is being published Thursday, the 18th of December, 2025. Tomorrow is Friday, so it's going to be Quick Queries Friday. Monday, the 22nd, will have part three.
So, tune in Monday, or if you're watching this at some future point, if you're a future boy, you can watch this right now because it'll be online. But if not, if you're watching it live, come back Monday. And members, you can watch it right now because that's part of the benefit of being a member, as you can watch videos as soon as I make them and post them. Then they're yours. For everybody else, I'll see you on Monday.
That's going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends.
I'll see you next time.
Quiz
Q1. Why is it necessary to convert the value returned by get setting to the appropriate data type when using app settings in Access?
A. Because get setting always returns a string
B. Because get setting returns an integer
C. Because get setting cannot retrieve values
D. Because get setting returns a Boolean
Q2. What function can you use to check if a string contains a numeric value in VBA?
A. IsDate
B. IsString
C. IsNumeric
D. IsValue
Q3. Where should you typically check constraints like max family size to ensure user input is valid before saving the data?
A. In the form's Open event
B. In the Before Update event
C. In the After Update event
D. In the Click event
Q4. What would be the default action if the max family size setting is found to not be numeric, according to the video?
A. Show an error and allow the user to continue
B. Ignore the issue and proceed
C. Show a message box error and cancel the update
D. Automatically correct it without informing the user
Q5. Which VBA function should you use to convert a string to a long integer?
A. CINT
B. CLONG
C. CLNG
D. CVAR
Q6. If the max family size is set to a non-numeric value in the settings table, what is a possible way to handle this other than showing an error?
A. Set the value to zero
B. Default to a hard-coded value such as 20
C. Remove the setting
D. Ignore it and let user input anything
Q7. What should you do before converting a string from a setting to a date in VBA?
A. Use the IsNumeric function
B. Use the IsDate function to verify it is a valid date
C. Use the IsString function
D. Use CINT directly
Q8. Why are comments in code recommended, especially in the context of system settings and validation?
A. They make the database run faster
B. They help future developers (or yourself) understand your logic years later
C. They prevent runtime errors
D. They are required for Access to run code
Q9. What is a characteristic of the main menu form described in the video?
A. It is bound to the settings table
B. It has no control source or record source
C. It can only display settings, not edit them
D. It is used only for reports
Q10. What should you do in the form's On Load (or On Open) event to show the current notes in the main menu notes field?
A. Use DLookup to pull the notes from the table each time
B. Assign main menu notes = get setting main menu notes
C. Manually type in the notes each time
D. Leave the field blank
Q11. What is the purpose of writing a put setting function, as previewed for part three?
A. To allow users to save settings back to the table
B. To stop users from editing settings
C. To delete old settings automatically
D. To convert settings to numbers
Answers: 1-A; 2-C; 3-B; 4-C; 5-C; 6-B; 7-B; 8-B; 9-B; 10-B; 11-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 continues our lesson on creating configurable system settings in Microsoft Access by building our own get setting and put setting functions. This is part two of the series, so if you haven't watched part one yet, please start with that first. You can find the link to part one below.
Before we proceed, there's one more prerequisite I neglected to mention earlier: I recommend watching my IsNumeric video. That way, you'll know how to check if a string contains a numeric value, which is important for what we'll cover today.
Previously, we built our settings table to manage system options and wrote a get setting function to retrieve values without resorting to repetitive Dlookup calls. We set up some default values for our settings, such as the state and the order entry lock.
Today, we're going to focus on working with settings that are not just text, such as numeric values. For example, let's say we add a "Max Family Size" setting and set its value to 20 in the table. When we retrieve this setting with our get setting function, it's returned as a string, so we need to convert it to the correct data type before using it in our logic.
Let's imagine a scenario where you want to enforce the maximum allowed family size on a data entry form. The appropriate spot to check this is in the form's Before Update event. Remember, the Before Update event fires after the user enters a value but before it's saved to the table. If you need a refresher on how Before Update works, I recommend my video on that topic.
Within the Before Update event, we retrieve the setting from our table as a string. Next, we need to ensure this string contains a valid number. We do this with the IsNumeric function. If it's not numeric, we display a warning message to the user indicating that the Max Family Size setting isn't valid. We then cancel the update to prevent incorrect data from being saved. If the value is valid, we convert it from a string to a long integer using the CLng function, and then compare the entered family size to the allowed maximum. If the entered size is too large, another warning message appears and the update is cancelled.
It's important to include comments in your code to explain each step, even if it feels unnecessary now. Code that seems obvious today may be confusing when you look at it again years later. Adding comments helps both you and anyone else who may work with your code in the future.
Remember to debug and compile your code periodically to catch any issues early. After closing and reopening your form, try entering values to see that your validation works as expected. Entering a number under the maximum should succeed, while entering a number over the limit triggers your warning. If you enter a non-numeric value for the setting, you'll get an error message as well, preventing incorrect configuration.
For flexibility, you can also decide how you want your code to handle invalid or missing settings. Instead of stopping the user with an error when the setting isn't numeric, you might choose to assign a default value, such as 20, to keep things running smoothly. This approach ensures there's always a reasonable fallback if the setting in your table is incorrect or missing.
The same idea applies for Boolean or date settings. When retrieving a value that should be a date, you can use the IsDate function to verify it's valid, and CDate to convert it. The process is similar: check the type, convert, and then use the value as you need. If you'd like to see an example of handling other data types, let me know in the comments and I can provide one if enough people are interested.
Switching gears, let's talk about adding a notes section to the main menu form. I want to provide a space at the bottom of my main menu where anyone can leave shared notes. My main menu form is just a standard menu form in Access. It's unbound, meaning it doesn't have a record source or table behind it. While you could bind a menu to a table, it's often better not to for various reasons.
For convenience, I'll add a notes text box at the bottom of the form where users can type in notes, such as reminders or announcements. After designing the control to make it visually appealing, I give it a recognizable name like "main menu notes." Since the form isn't bound to a table, the notes box will be blank each time the form is opened, unless we write code to load existing notes.
To display stored notes, we retrieve the saved notes value from our settings table in the form's On Load event. Using the get setting function, we pull the "main menu notes" value from the settings table and assign it to the notes text box when the form opens. This simple step turns the notes field into a kind of manager billboard where important messages appear every time the main menu loads.
At this stage, the notes field functions as a read-only announcement board. To let users edit and save their own messages, we need to update the settings table with the new value from the notes box. For this, we'll create a put setting function in the next lesson, part three.
Keep in mind, due to the video release schedule, the third part will be available on Monday, December 22nd. If you're watching later, you may already have access to it. Members can watch all parts as soon as they're posted, while everyone else can follow along with the scheduled releases.
That wraps up this TechHelp video for today. If you're interested in step-by-step instructions and a full video walkthrough of everything covered here, you'll find it on my website at the link below.
Live long and prosper, my friends.
Topic List
Handling numeric settings values in the settings table
Converting string values to numeric types in VBA
Validating numeric input using IsNumeric
Using Before Update event for data validation
Displaying error messages for invalid input
Hardcoding default values for missing or invalid settings
Handling empty or non-numeric setting values
Applying the same logic to boolean and date settings
Using IsDate to validate and convert date settings
Creating an unbound notes field on a menu form
Populating a notes textbox from a settings table
Formatting and styling the notes textbox
Loading settings into the form on open or load
Using the notes field as a system bulletin for users
Article
In this tutorial, we are building on a previous lesson about creating configurable system settings in Microsoft Access. The goal is to store application settings in a table, retrieve those settings with a custom function, and use them throughout your database. I will also teach you how to handle settings that are not just plain text, such as numeric and date values, and how to validate them in your forms. Finally, we will integrate a notes field into an unbound form, showing how to display and later update settings data from your settings table.
Let's start by recalling that we have a table for app settings, and a function called getSetting that retrieves values from this table. For example, you might have settings like "OrderLock," "StateDefault," "MaxFamilySize," and "MainMenuNotes." The getSetting function gives you the value for any named setting without repeatedly using DLookup, improving performance and code readability.
Now, storing numbers in a settings table usually means they're saved as text since settings tables generally use text fields. Let's use "MaxFamilySize" as an example, which might be stored as "20". When you use getSetting("MaxFamilySize"), you get back the string "20", not the number 20. That means if you want to use it for comparison or arithmetic, you must convert it to a numeric type.
Suppose you want to enforce a maximum family size in a data entry form. You should check whether the value entered does not exceed "MaxFamilySize". The best place to do this is the form's BeforeUpdate event. This event fires after the user types their value but before it's saved, giving you a place to validate the entry.
Here is a sample of how you might implement this validation. Open your form in Design View, find the relevant control (for example, FamilySize), and set up the BeforeUpdate event. You will use VBA code like this:
Dim s As String
Dim l As Long
' Get the value from the settings table
s = getSetting("MaxFamilySize")
' Check to make sure it's numeric
If Not IsNumeric(s) Then
MsgBox "Warning: Max Family Size setting is not numeric."
Cancel = True
Exit Sub
End If
' Convert to number
l = CLng(s)
' Check value against what was entered
If Me.FamilySize > l Then
MsgBox "Family size is too large. Max allowed is " & l & "."
Cancel = True
Exit Sub
End If
Adding clear comments like in this example is always a good practice. Even if you understand the code now, it's invaluable to add comments for your future self or other developers who may maintain your database in years to come.
If you want to make things more robust, you could provide a default value in the event the setting is missing or invalid. For example, instead of stopping the user with a warning, you could default to a hard-coded value:
If Not IsNumeric(s) Then
s = "20" ' Default to 20 if not numeric
End If
l = CLng(s)
Now, no matter what, l will have some reasonable value, and the code continues smoothly. You can adapt this pattern to settings that store Boolean or Date values as well. For dates, use the IsDate function and convert using CDate after validation.
Next, let's create a Notes field on an unbound main menu form that displays an editable notice to all users. Assume you have a setting called "MainMenuNotes" in your settings table. Your main menu is not linked to any table; it is purely for navigation and does not have a RecordSource. To show the notes, add a text box to the form (e.g., called txtMainMenuNotes).
To display the stored notes, set the text box value when the form loads. Open the form in Design View, go to the On Load event, and enter:
Me.txtMainMenuNotes = getSetting("MainMenuNotes")
Now, every time the form opens, it retrieves the "MainMenuNotes" setting from your table and displays it to users. This is a great way to implement a flexible message board or system-wide notification area without making the main menu form bound to a table.
For now, this only allows you to display the notes. If you want to let users change the note and save it back to the table, you will need to implement a function to update a setting in your table (often called putSetting). That part will be covered in a future lesson.
To summarize, storing settings as text in a table makes your application flexible, but you must handle type conversion and validation yourself. For numeric settings, validate with IsNumeric and use CLng or other conversion functions as needed. For date settings, use IsDate and CDate. You can display settings in any control, even unbound forms, by using getSetting in the On Load event. With these techniques, you can build a maintainable, configurable application in Microsoft Access that adapts to your needs.
|
