Warn Just Once
By Richard Rost
2 months ago
Show a Warning Message Only Once in Access Forms
In this Microsoft Access tutorial, I will show you how to display a warning message just once per customer when they are over their credit limit, so you are not repeatedly bothered by the same alert with each new order. I'll walk you through creating a hidden checkbox to track if the user has already been warned, updating it as needed, and resetting the warning when you move to another customer or close and reopen the form. We'll also talk about an alternative using TempVars for system-wide or daily warnings that only appear once per session.
Cassandra from Naples, Florida (a Platinum Member) asks: Is there a way to make Access only show a warning about a customer's credit limit once, instead of displaying it every time I enter a new order for that customer? I just want one alert per customer until I move to another customer or close the database, so I do not keep getting the same message over and over.
Members
In the extended cut, we will save the warning data per customer, track the user who saw it, and record the time they were warned. I will show you how to make the warning interval user-specific and time-based, so each user gets separate reminders per customer, with the option to reset the warning after a set amount of time.
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
Keywords
TechHelp Access, credit limit warning, vba message box, warn once per customer, checkbox warned flag, oncurrent event, tempvars usage, static variable warning, per user warning tracking, daily import check, status messages, management decision alerts, log warning by user
Subscribe to Warn Just Once
Get notifications when this page is updated
Intro In this video, we'll talk about how to display a warning message just once for each customer in Microsoft Access, so you aren't repeatedly alerted every time you add multiple orders for the same customer. We'll use a checkbox on your form to track whether the warning has already been shown and discuss how to reset it when changing customer records or closing the form. I'll also show you how to use TempVars for system-wide, one-time alerts during your database session.Transcript Ever get that warning message that you see over and over and over again? Well, in today's video, I'm going to show you how to just pop that message up once and then you won't see it again for that customer unless you leave the customer record, go back to it, or close the form or whatever. This way you don't keep seeing it over and over and over again. You get the point. They get annoying. So today we'll see how you can just get it once and then it won't bother you again.
Today's question comes from Cassandra in Naples, Florida, one of my Platinum members, and hey, that's kind of in my backyard. Cassandra says, is there a way to make Access only show a warning about a customer's credit limit once instead of displaying it every time I enter a new order for that customer? I just want one alert per customer until I move to another customer or close the database so I don't keep getting the same message over and over again.
And full disclosure, yes, Cassandra's question was slightly different from this, but I changed it a little bit so it fits the database that we're working with. So, let me show you how to do this.
All right. So, two videos ago, I showed you how to show how much credit they have left or whether they're over the credit limit. We're going to use this database. Yesterday, I showed you how to recalculate this when the user updates the order.
These don't have to be watched as prerequisites, but I just wanted to mention this because I'm using the same database. I saw all these questions come in and they're kind of similar. So, I figured I'd put them all together.
But this is a developer-level video. So at the minimum, make sure you watch my Intro to VBA video if you're not sure how this stuff works. Then watch my Are You Sure video, which is what we're going to use to ask the user if they're sure when we prompt them. Go watch this one; two of these are free videos. They're on my YouTube channel. They're on my website. Go watch them and then come on back.
All right. Now, how about a warning? You go to this guy's record. Let's say, let's make me over my credit limit here. Let's go and say that this is 1000. Oh, still not over my credit limit. Let's make it 1500. There. That's much better.
You might get some colorblind employee who doesn't catch that. So, when you hit the order button, you might want to say, wait a minute, you're over your credit limit. How do we do that?
Well, that's not that hard to do. We just come in here in our order button, right-click, build event.
Now, before we open that form, we just give them a little warning message. We say if their credit left, that's that box, is less than zero, then we just throw up a message box. If message box, this user is over their credit limit, continue anyways.
See, this is a management question. Do you want to allow them to put the order in or do you want to stop it? If you want to stop them, just say, exit sub right here. But if you still want to just give them a warning and say, hey, hold on, this guy's over his credit limit, are you sure you want to continue? That's a management decision. I'm showing you how to just give them a warning here.
Let's get vbYesNoCancel. So, I always like to have a cancel option plus vbDefaultButton2; I like No to be the default for things like this. And then, plus one more. We're going to move this over so you can see it. Let's do a vbQuestion.
And then we're running out of lines here. Come on. No, we're going to close parentheses. If this whole thing, let's make this two lines because it's really big.
If that whole thing is not vbYes, then exit sub.
I've covered this in several different videos. Essentially, it's going to say, hey, this user is over their credit limit. Do you want to continue? It's going to give them the Yes, No, Cancel buttons. If the user clicks Yes, then it doesn't exit the sub. If they click No or Cancel, they exit out.
I like to include Cancel always because sometimes users don't know what they're doing. They'll go, I have no idea, and their instinct is to hit Cancel. That's what you want, just to cancel out. They'll ask someone else.
And then End If.
Okay. Save it. Close it. Close it. Open it.
Now, if they're on someone who's not over their credit limit, it just opens. If they're on someone who is over their credit limit, like this guy, it yells. Do I continue anyways? Continue anyways, I'm not sure. Continue anyways. Yeah, okay. Then they can still add it. So that's up to you, that's a management choice.
What if you're going to be adding a bunch of orders and you don't want to be pestered every time? You're going to be adding like three, four, five orders. You don't want this bugging you every time. Well, you could put a temporary warning or a WarnedOnce box on here.
Here's what I would do. I'd come in here, Design View. I'd put a hidden checkbox on here. So, form design, find a checkbox. There you are. Stick it there. I'm just going to delete the label.
This guy here, we're going to name it Warned, W-A-R-N-E-D. Normally, you'd make its Visible property No, because you don't want to see it, but I'm going to leave it Yes just so we can see it working. Let's set the Default Value equal to No.
Now, in here, at this point, we'll say Warned equals True, because we're popping up the message. But I don't want to see it again. So, what we're going to say in here is we're going to say If Not Warned, then do all that exit sub. So, if Warned is Yes, it won't even bother with that block. It'll just open up the block or open up the form.
Save it. Close it. Close it. Open it.
Right, so first, whoop, whoop, whoop. Oh, I put exit sub there. That's my bad. Raise your hands in the back row if I mess up, guys. End If, not exit sub, End If. Sometimes I'm talking to you and I'm not paying attention.
All right, let's try it again. Ready? Click. Are you sure? See how it got marked Yes? Are you sure? Yeah. Put an order in. Blah, blah, blah. Put another order in. See, it's not bothering me. Put another order in.
Okay, if I close it, come back to him later, it's going to give you the warning again. That's fine.
Now, what if I move to a different customer? You want that box to reset. So, what you'll do then is put that in the OnCurrent event. Event OnCurrent right in here. You'll just say Warned equals False. That'll make sure that box resets every time you move from record to record so you don't get bothered again.
All right, let's see. There you go. Warned me. Then go to somebody else and give me a warning. Oh, wait, what happened here? Didn't give me the warning. Oh, because we're not over the credit limit, that's why.
This guy's almost over his credit limit. Let's see. Make this 5,400. That'll put him over his credit limit. Now I'll get the warning. There we go.
Now, if you're not just doing something that's customer-related, let's say you've got a system-wide event, something that you want to run every time, like every day when you log on to the database, you want to, like me for example, I import my new YouTube members every morning and my PayPal information, sales and stuff from PayPal. I do that every morning. I can't automate it just yet, I'm working on it. I know, it's complicated. But I don't want it to, I want it to warn me if I try to do it a second time.
Let's pretend this button is my import. If I go to hit the import button and it says, hey, you already imported the data for today. You could do the same thing. You could use a static variable. You could use a checkbox on here. I like to use TempVars because TempVars will survive a VBA error.
So, let's pretend this is my daily import button. Build Event in here. Status "Hello World." You just say in here, for example, if IsNull(TempVars!Warning), then TempVars!Warning = False.
Always check a TempVar to see if it's Null. If it's Null, that means it hasn't been initiated yet. If you try to see if it is True or False, it'll always come back with Null. Remember, Null isn't equal to anything, even Null itself. So, always check to see if it's Null first.
Okay, I'm going to copy this to my clipboard so we don't have to keep typing that in.
In here, I'm going to say, if TempVars!Warning, you could put = True in here, but you don't need to. Sometimes it makes it easier to read. So, if the warning is True, which means you've been given the warning before, then status "You've already done this, dummy." Exit sub.
Otherwise, you haven't done it yet. So now we're going to say this = True. Now it should only warn you once. Same kind of concept.
Close it. Open it. First time I do it: "Hello, World." Later on, "You've already done this, dummy." "You've already done this, dummy." Even if I close the main menu and come back to it, "You've already done this, dummy."
The only thing that's going to reset that is closing the database and reopening it.
That brings me to today's Extended Cut.
Now in today's Extended Cut for the members, we're going to take this to another step. We're going to save the warning data per customer. So, every customer will have their own warning level. If you've already done it with ABC Corp. and then you go to XYZ, and they also need to be warned, you'll see a separate warning. It doesn't matter that you already got warned for ABC Corp. And we'll store it per user. So, if you're entering an order for ABC Corp and your buddy Joe in the next office goes to add an order, he will also see the reminder. He'll get his own reminder.
We'll track what user saw it, and we'll store the time that they were warned. You could set whatever interval you want, like once an hour. So, if you're going to add two or three orders right now, you might forget three hours from now. So, we'll reset that warning.
Now, I know this is a silly example, warnings for a customer being over their credit limit, but the techniques I'm going to show you are valid for all kinds of stuff. You can use it for that daily import or whatever you've got going on in your database. If you need warnings but you don't want to see too many warnings, we're going to log it. We're going to log it by user and all kinds of cool stuff, all covered in the Extended Cut for the members.
As a reminder, Silver members and up get access to all of my Extended Cut videos, not just this one, all of them, the whole library. There are tons of them. There's hundreds of them, so check it out. Click that blue Join button down below.
Gold members can download these databases that I make in the TechHelp videos. You get access to my Code Vault and everybody gets free classes every month. I don't know why you're not all joining. Join right now.
And of course, if you like this stuff, if you like learning with me, check out my developer lessons. I've got hundreds of hours of developer training on my website. I'll put a link right there; you can click on it down below.
That is going to do it for your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the main issue being addressed in the video?
A. How to create new customer records in Access
B. How to display a warning message only once per customer session
C. How to prevent all warning messages in Access forms
D. How to delete warning messages from previous records
Q2. Why might constant warning messages become a problem for users?
A. Users may miss important data fields
B. Users may be unable to add new orders
C. The warning can become annoying and repetitive when entering multiple orders
D. The warning prevents switching between customer records
Q3. What is the original scenario Cassandra wanted to solve?
A. Warning users when a customer's address is missing
B. Automatically approving all orders
C. Displaying a credit limit warning only once per customer until leaving their record
D. Showing warning messages every time a form is opened
Q4. What approach is suggested for allowing the user to proceed after seeing a warning?
A. Automatically denying their order
B. Giving the user a Yes, No, Cancel message box to choose whether to continue
C. Deleting their customer record automatically
D. Hiding the warning completely
Q5. In the video, what is the purpose of the hidden checkbox named "Warned" on the form?
A. To store the customer's account status
B. To store the date their last order was placed
C. To track whether the warning message has already been shown for the current customer
D. To mark customers as inactive
Q6. What property should be set to No for the Warned checkbox in a live database?
A. Visible
B. Enabled
C. Default Value
D. Tab Stop
Q7. How is the warning message logic changed to ensure it only shows once per customer?
A. The message always displays when entering an order
B. The code checks if Warned is False before displaying the message, then sets Warned to True
C. The message is only shown the first time the application opens
D. The warning is sent in an email instead
Q8. How is the warning reset when moving between different customer records?
A. By closing and reopening the database
B. By using the OnCurrent event to set Warned to False when switching records
C. By clearing all form data after every order
D. By clicking a reset warning button
Q9. What is mentioned as an alternative to the checkbox method for system-wide events, like daily imports?
A. A temporary query
B. Using static variables or TempVars
C. Adding a new table to the database
D. Sending notifications by email
Q10. Why is it important to check if a TempVar is Null before working with its value?
A. Because Null means the TempVar has not been initialized
B. Because Null is the same as False
C. Because Null values always convert to True
D. Because Null cannot be used in VBA
Q11. What causes the TempVar-based warning to reset?
A. Closing and reopening the database
B. Switching to a new form
C. Refreshing the record source
D. Modifying the customer table structure
Q12. What additional features are discussed as part of the extended member version of the lesson?
A. Automatic order approval
B. Logging warnings per user and customer, and tracking warning timestamps
C. Deleting repeated warnings permanently
D. Sending the warning message by SMS
Answers: 1-B; 2-C; 3-C; 4-B; 5-C; 6-A; 7-B; 8-B; 9-B; 10-A; 11-A; 12-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone covers how to display a warning message only once per customer when they exceed their credit limit, instead of having the alert appear repeatedly every time a new order is entered for the same customer. This approach keeps your workflow smooth and avoids those annoying, repetitive popups.
A question came in from a member asking whether it was possible to have Access show a credit limit warning just once for each customer, so that after acknowledging the message, you won't be bothered by it again unless you move to another customer or close and reopen the form. I thought this was a great topic to explore, so let me walk you through the process.
I'll be using the same database that I set up in previous lessons, where we've already built a system to monitor customers' available credit and warn users when they are about to go over their credit limit. If you're not familiar with the basics of VBA or how to prompt users with a message box, I highly recommend watching my introductory VBA videos and the Are You Sure video before proceeding. Both are available for free and provide essential background information you'll find useful.
Suppose you have a customer whose orders push them over their limit. Usually, we might display a warning every time you open a new order for that customer, but that quickly becomes annoying, especially if you're entering several orders in a row. The solution is to provide a warning just once per customer per session.
To set this up, you can add a hidden checkbox control to your form, called something like "Warned." By default, this checkbox is set to No. Each time a warning would normally be shown, you check whether that box is already checked. If it is not, display the warning and set the box to Yes. After that, as long as you stay on the same customer record, you will not see the warning again.
If you move to a different customer, you want the warning mechanism to reset. This can be accomplished by setting the Warned checkbox back to No in the form's OnCurrent event, which triggers every time the user navigates to a new record. That way, each customer gets one warning per session, and you avoid being pestered with repeated alerts for the same customer.
Let me also mention a variation of this technique for situations where you need to issue warnings for a system-wide action, not just per customer. For example, maybe you have some kind of batch import routine that you run once daily, and you want to notify users if they're trying to run it again the same day. In these cases, TempVars are your friend, because TempVars persist as long as the session stays open, even after VBA errors. You can use a TempVar to track whether the warning has already been shown, check its value before displaying the message, and update it accordingly. This prevents the same notice from popping up repeatedly for the rest of the session.
Now, in today's Extended Cut, we'll take these concepts even further. We'll store the warning data per customer, so every customer has their own warning state. This means that if you've already seen the warning for ABC Corp, and you move to XYZ, you'll get a separate warning for each. We'll also track it per user, so each person entering orders gets their own reminder. We'll log the exact time the warning was issued and even allow you to set a specific interval before the warning can be shown again, such as limiting the alert to once per hour.
While our example focuses on credit limit warnings, these techniques are broadly useful and can be adapted for many different scenarios, including periodic imports or any event where you want to avoid bombarding users with repeated messages.
If you are interested in a deep-dive with step-by-step walkthroughs and extra materials, you can find the complete video tutorial, including all of the methods discussed here and the Extended Cut for members, on my website at the link below.
Live long and prosper, my friends.Topic List Displaying a customer credit limit warning only once
Creating a Yes No Cancel message box in VBA
Adding a hidden checkbox control to a form
Setting and using a Warned field to track warning status
Updating the Warned checkbox when displaying a warning
Resetting the Warned checkbox in the OnCurrent event
Preventing repeated warnings within the same customer record
Using TempVars for session-based warnings
Checking and setting TempVars in VBA
Providing warnings for daily import operations
Ensuring warnings reset when closing or switching records or databaseArticle If you use Microsoft Access databases, you probably know how annoying it can be to see the same warning pop up repeatedly, especially when working with customer records. For example, if a customer is over their credit limit and you have a warning message set up, you might find yourself having to dismiss that alert every single time you create a new order for the same customer—even if you just acknowledged it a second ago. This can really slow you down if you are entering several orders in a row for one customer.
Fortunately, you can modify your Access forms and VBA code so the warning appears only once per customer session. You will only see the warning again if you leave that customer's record and come back, or if you close and reopen the form. Let me walk you through a practical way to set this up, as well as similar techniques you can use for other scenarios, such as daily scripts or system-wide warnings.
Imagine you have a customer form where you track credit limits. There is a button that opens a new order entry form, and before opening it, you want to warn the user only once if the customer is over their limit. To do this, you can add a hidden checkbox control to the form—let's call it 'Warned.' This checkbox tracks whether the warning has already been shown for the current customer.
Start in Design View of your customer form. Add a checkbox control anywhere on the form. Delete the label, since you do not want this to be visible to users. Name the checkbox Warned and set its Default Value property to No. You can set the Visible property to No as well, so it stays hidden in production, but you might leave it visible while testing.
Next, open the VBA code editor for the button's Click event—the one that launches the New Order form. You want to modify the event so it only shows the warning if the Warned checkbox is not yet checked. Here is how your code can look:
If Not Warned Then
If CreditLeft < 0 Then
If MsgBox("This user is over their credit limit. Continue anyway?", vbYesNoCancel + vbDefaultButton2 + vbQuestion) <> vbYes Then
Exit Sub
End If
Warned = True
End If
End If
This code first checks if Warned is false. If so, and if the credit left is below zero, it prompts the user with a message box. If the user clicks No or Cancel, the subroutine exits and the order form does not open. If the user clicks Yes, Warned is set to True, and the warning will not appear again until Warned gets reset. Now, the user can create several orders for the same customer without seeing the same warning repeatedly.
Of course, the next concern is resetting the Warned flag when you move to a different customer. You want the warning mechanism to rearm for each new customer you view. To do this, go to the form's OnCurrent event (this event runs whenever the current record changes). Add this line of code:
Warned = False
This ensures that as soon as you navigate to a different customer, the Warned checkbox unchecks, and the next time you try to place an order (if that customer is over their limit), the warning will appear again. If you close the form and reopen it, Warned also resets for all records.
This same approach works for other forms of temporary or session-based warnings, not just per-customer alerts. But what if you want to display a system-wide warning message only once per database session (for example, letting a user know that a daily import has already been run)? In this case, you can use VBA's TempVars collection to track whether the warning has occurred.
For instance, suppose you have a button that starts an import process. You could enter this code in the Click event for the button:
If IsNull(TempVars!Warning) Then
TempVars!Warning = False
End If
If TempVars!Warning Then
MsgBox "You have already completed this import today."
Exit Sub
End If
' Place your import code here
TempVars!Warning = True
In this example, TempVars!Warning is created and checked. If it is True, the warning appears and the import is skipped. If not, the import is permitted and TempVars!Warning is set to True so you will not be warned again in the same session. TempVars persist until you close and reopen the database, so this flag is perfect for session-based warnings.
Whether you use a hidden form control like a checkbox or a TempVar, these methods give you fine control to avoid bombarding users with repeat alerts. You can warn them once per customer per session, once per user, or once per day as needed, making your application less nagging and much more usable. As a bonus, you can extend these techniques, for example by storing warning timestamps in a log table, or associating them with particular users for even more customization.
With these techniques, you will keep warnings meaningful and unobtrusive, so users will appreciate the heads-up without getting frustrated by constant interruptions. Give this a try in your database and it will make working with repeated processes much smoother. |
