Reminder Popup
By Richard Rost
5 years ago
Timer Event for Reminders. Display a Popup.
In this video, I'll show you how to create a popup form that will show you a reminder at regular intervals whenever you have customers in your table that need a followup. This same technique of using a form timer event can be used for all kinds of things, like sending emails, pulling down data from a web site, processing orders, you name it.
Melody from Berlin, Germany (a Gold Member) asks: I would like my database to notify me once an hour if there are any new sales leads in the system that I need to respond to. Is there any way to have Access pop up a message for me?
Members
I'll show you how to make a button to open up just customers that need a followup, add a countdown timer to your form so you can see it counting down 5... 4... 3... 2... 1... with a PAUSE option. And I'll show you how to make it so that the notification doesn't interrupt your work flow. You can still keep working in the form you were on without the popup disturbing your typing.
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!
 Links
Reminder Popup on Date: https://599cd.com/ReminderPopupDate
Intro to VBA: https://599cd.com/IntroVBA
DLookup Function: https://599cd.com/DLookup
NZ Function: https://599cd.com/NZ
Timer Get Web Data: https://599cd.com/ACX24
Enhanced Timer Form: https://599cd.com/ACD6
Quiz Timer: https://599cd.com/ACD11
Subscribe to Reminder Popup
Get notifications when this page is updated
Intro
In this video, I will show you how to create a reminder pop-up in Microsoft Access using a timer event on a main menu form. We will cover how to set up the timer interval, use simple VBA code to trigger a pop-up form, and apply conditions using the DLookup function so the reminder only appears when needed, such as for new sales leads or follow-ups. I will also discuss form design tips for the pop-up and explain how to make the reminder relevant to your database needs.
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I'm going to show you how to make a reminder pop-up. We are going to use a timer event to display a reminder on a pop-up form.
Today's question comes from Melody in Berlin, Germany, one of my Gold members. Melody says, "I would like my database to notify me once an hour if there are any new sales leads in the system that I need to respond to. Is there a way to have Access pop up a message for me?"
Yes, of course, Melody, I personally use this all the time. If you have any kind of event that you want your system to respond to and pop a notice up, if you have an appointment coming up, or if you have sales leads you need to respond to, or even just something to remind you to get up and walk around, you can have Access run any kind of timer event you want. I'm going to show you how to do that in this video.
One simple prerequisite is my Intro to VBA. Watch it - it's free - I'll put a link down below in the link section under the video. You can do this without any programming, but with just one line of programming - and I'll show you how in this video - you can make it so much easier than having to deal with macros. I don't really like macros, so go watch Intro to VBA if you've never done any programming; again, it's a free video.
I'm going to start off with a copy of my TechHelp Free Template. You can download a free copy of this off my website. I'll put a link down below in the template section. You don't have to use my template; you can start with your own database if you want to.
In all of my databases I like to have a main menu, that's a form that starts up when the database starts. If you don't know how to do that, I cover that in the video where I build the TechHelp Free Template, so go watch that video.
This main menu form is the perfect place to put a timer event because it's always open and it's always sitting in the background and you don't want your user closing this. Once they close this, then I put in my databases an event to shut the whole database down if they close the main menu, but that's a topic for a different lesson.
How do you find that timer event? Right-click, go to Design View, bring up the form's properties. You can double-click right here if you don't have the property sheet open. On the Events tab, we're going to scroll down until we find On Timer and Timer Interval.
Now, On Timer is an event that runs when the Timer Interval hits whatever you set it to. Notice down here it says, "Specify the Timer Interval in milliseconds." So if you want one second, you have to put 1000 in there. If you want five seconds, you put 5000 in there. So if you want this to run once a minute, it's going to be 1000 milliseconds times 60. That's once a minute, 60000. If you want it once an hour, multiply that by another 60. So it's going to be 3,600,000 is what you're going to put in there to run this event once an hour. You can put pretty big numbers in here, so you can go 3,600,000. Alright, so that will make the timer event run once an hour.
Now, for the purposes of class, I'm going to have this event run once every five seconds, so 5000 for me, just so you can see the event run and we don't have to sit around here waiting for an hour.
Now, what's going to happen when that hits five seconds? That's what the On Timer event is for. You can throw a macro in here if you want to if you want to open a form. I hate macros. I like using VBA. So again, if you haven't watched my Intro to VBA, it's real simple, it's not hard to do. Go watch that lesson now.
For those of you that are with me and know a little VBA, click on this dot dot dot button right over here. You might get a window up that says what kind of builder do you want. Pick the code builder. I explained that in the Intro to VBA lesson. That's going to put us in the form timer event. This is what's going to happen every time that timer runs, every time it clicks down to zero. For me, five seconds - it's going to come right down to zero and something's going to happen.
In here, let's just pop up a message for now. Message box "timer" like that. Let's see the word "timer" appear on the screen.
Let's save it. Let's go back over here, close our form, and then open it up again. Now we'll sit around here and wait. Here we go... blah blah blah... Oh, there it is after five seconds - "timer." That's a simple timer event.
Now the problem with just using a message box is that's going to sit there. You can't do anything else. If you're in the middle of work and you have to stop and hit OK on that, yeah, it gave you the warning. There it is again after five more seconds. It's going to keep running every five seconds. The code is stopped right now because that message box is sitting there. In order to stop that timer event, you have to close that form.
Now, instead of popping up a message box, how about we pop up a little form? It might sit off to the corner and that way we can leave it there and still keep working.
Let's just make a simple little form that's got a notice in it. Take the single blank I've got here, copy, paste. Let's call this my notice NoticeF. We'll pop a little notice up - nothing fancy. Design View. We don't need any fields in it. Let's make the background something like yellow so you see the notice. Come in here, pick yellow but not quite that bright yellow. Let's go more colors. Let's go a little bit like washed out yellow. There we go.
Just throw a couple of labels on it. I'll put a label up here that says "Notice." We can make that red and maybe bold it and make it a little bit bigger like that, center it.
Let's drop another one on there and what are we going to have this guy do? We're going to have this say "There are customers to follow up with." Let's say you've got a multi-user database, for example, and other people are entering sales leads or "Hey, you've got to follow this guy" or service reports or anything, whatever you want this to do. Maybe even your own stuff -- you open up the database in the morning, you want to know, "Hey, there are customers you've got to deal with." Do a little format on this guy, maybe make it dark blue or black. Resize that.
Let's save that, close it, take a peek at it. We don't need any data in it, so we can remove the navigation buttons and the record selectors. Design View. I've got lots of videos on form design and making your forms look pretty. I start with my free Access Beginner Level 1 class - it's three hours long - we cover a little bit of form stuff in there. Level 2 we go over even more. We can go to the Format tab here, turn off the record selectors, turn off the navigation buttons, turn off the scroll bars.
Let's save it and take a peek. That's a little form there, just to put a notice in your face.
Now, let's go back to my code window. I always leave it open, I don't usually close this while I'm working on designing my database, and it should be down in your task bar down below so you can just flip between it. But if you close that, you can go back into your main menu design, and then go back to that event tab, find On Timer, click right here - or there's a little button right there you can use to go to this form's code. There are always five different ways to do everything in Access.
Now, we don't want a message box "timer" anymore. I want to open up that form, so again, one line of code: DoCmd.OpenForm "NoticeF" just like that. Save it. Then open up your menu, wait five seconds, and there's my notice.
Now the nice thing is, I can be working with something else, and it just popped up. I can be over here typing in some data or doing this and that and it just popped up. I can just leave it sit there, move it over here, save it over there, and it'll just keep popping up. I can do some stuff over here, do some stuff over there, and my notice is sitting there. If by some chance something does cover it, every five seconds it'll come back to the foreground. See, and it will steal your focus though. In the Members extended cut, I'll show you how to make it not steal your focus so even though it's popping up, it'll still stay here so you can keep typing.
You can actually make that guy a pop-up form if you want to so it stays on top of other windows. I don't particularly like pop-up forms; there's a format setting called Pop Up. The reason why is, a lot of us use multi-monitor setups now and if you've got more than one monitor, that pop-up window could appear on any of them depending on where you had your Access database last and where you saved that form, so it might not show up on your main monitor where you've got your database. I don't like pop-up forms but usually this little guy showing itself is enough. You can be like "OK, my notice is there," and you can go back to work, and then if you don't do anything in another couple of minutes, it'll pop up again.
Now, what criteria can we use to decide whether or not to show this? That's really up to you. You can use anything from inside your database to determine whether or not you've got to show that guy. Now, I've got lots of other videos on how to do things like DLookups or reading values from forms, or whatever criteria you want to use to say "Hey, show me a pop up form." You can do that, or even what that form does when it opens up - I'm just showing you a simple message. You could have that form do all kinds of stuff - you can send out an email, you can sound an alarm, you can back up your database. You can pretty much do whatever you can think of. You can have that form run and that's just a matter of adding the programming to it.
But let's pick some condition in the database to where that window won't show up unless that condition is met. For example, I've got an IsActive field here in my database. Let's say I only want to show that pop up message if a customer is marked active. Now normally, I use that IsActive to mean whether or not the customer is an actual active customer. Like, if they retire and they're no longer buying from you, you can mark them inactive. But for the purposes of this lesson, let's say "active" means I've got to do something for them. Maybe it's a follow up thing. I'm going to turn everybody off as far as active goes.
Now, back to my code. How can I determine whether or not to show that? I'm going to use the DLookup function to see whether or not there are any active customers in my database. Again, if you've never used the DLookup function, I've got free videos on that - go watch the DLookup video first, understand that, then come back here.
We need a little bit more programming in here now. I'm going to look up a CustomerID so I need to store that in some variable, so I'll Dim ID as a Long. Then I'll say ID = NZ(DLookup("CustomerID", "CustomerT", "IsActive=True"), 0). What that does is it wraps the DLookup function in another function so that in case DLookup returns a Null (in other words, there are no customers that meet this criteria), it will return 0 instead of Null, which is easier to deal with. That's all NZ does. I've got videos on using DLookup with NZ together - go watch the NZ function video, it's free, it's down in the link section; go down there, there's lots of good stuff in that link section.
I'm looking up a CustomerID from the Customer table where IsActive equals true, and I want to wrap that inside of an NZ to return 0 if the customer doesn't exist. So, go out to the Customer table, find me any CustomerID, anyone, doesn't matter. I just want to know if someone meets this criteria where IsActive is true. You can use anything you want in there; you can look up in your FollowUp table if a follow up date is now or later, whatever you want to do. You can use the Max function to find the largest value of something. There's all kinds of stuff you can do here - I'm just scratching the surface.
Now, I'm going to say here: If ID is not 0, then we have a customer, go ahead and open up that form. That will run once every five seconds. If there are no customers that meet this criteria, it just won't do anything.
Let's save that, come back over here. I'm going to close my form. I like to close forms and reopen them every time I make a change. Open it up and now we'll sit here - one, two, three, four, five, six, seven, eight - nothing's happening. My five second timer isn't going off. That's because there are no customers that are active in my database.
Let's go make one active. Let's say someone else on a different computer - if you've got your database set up multi-user, for example, someone else is working with customers and they mark one active and then close that. Now, let's see - within five seconds you should see that notice "There are customers to follow up with."
You can put a button on here to open up that customer record, or open up all the active customers. I'll put that in the extended video. I'll put that in the extended video where you just click the button, it'll open up whatever customers are active. I am also going to add a countdown timer. See this little thing right here? That's a little timer on your main menu form that will actually show you it's counting down five, four, three, two, and give you the option to pause it if you want to click on that little box if you want to stop that timer, if you don't want to be bothered for a while. That will be in the extended cut for members.
If you want to learn more about timer events, I actually cover them in a couple of my different classes. In my Access Expert 24 class, I show you how to make a timer event that goes out to the web and checks a weather service at whatever interval you specify - one minute, one hour, whatever. It will get the current temperature and humidity and the status (whether it's clear or not) and save that in a table for you, so you get the weather information once an hour.
In Access Developer 6, I create an enhanced timer form that you can use to perform pretty much any function you want, like sending out an employee email once every five seconds or whatever. I also show you how to set up a nightly backup event that runs at, for example, 3 a.m. that will back up the critical tables from your database.
In Access Developer 11, I make a quiz form where it pops a question up and you have so many seconds to answer, and if you don't answer in that time, it closes the form and moves on to the next question. That's a pretty cool lesson.
But for right now, if you want to continue learning with the pop up form, check out the extended cut for members. Silver Members and up get access to all of my extended cut videos.
How do you become a member? Click the Join button below the video. After you click the Join button, you'll see a list of all the different types of membership levels that are available. Silver Members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more.
Gold Members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use.
Platinum Members get all the previous perks plus access to my full beginner courses and some of my expert courses. These are the full length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more.
But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more.
If you like this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select "All" to receive notifications when new videos are posted.
Click on the Show More link below the video to find additional resources and links. You'll see a list of other videos, additional information related to the current topic, free lessons, and lots more.
YouTube no longer sends out email notifications when new videos are posted. If you'd like to get an email every time I post a new video, click on the link to join my mailing list.
If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It's over three hours long. You can find it on my website or my YouTube channel. If you like Level 1, Level 2 is just one dollar and it's also free for all members of my YouTube channel at any level.
Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there.
Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel.
Thanks for watching this video from AccessLearningZone.com.
Quiz
Q1. What is the primary purpose of the timer event demonstrated in this video?
A. To periodically display a reminder or notification based on database conditions
B. To make the database backup every hour
C. To optimize form loading speed
D. To update database records automatically
Q2. Where does Richard recommend placing the timer event in an Access database?
A. In the customer data entry form
B. In the main menu form that is always open
C. In the report object
D. In a macro file
Q3. What unit is used for the Timer Interval property in Access forms?
A. Seconds
B. Minutes
C. Milliseconds
D. Hours
Q4. If you want to trigger the timer event once every hour, what value should you set for the Timer Interval property?
A. 60000
B. 1000
C. 3600000
D. 1000000
Q5. What is the recommended method in this video for populating the On Timer event code?
A. Use a macro builder
B. Use VBA code in the code builder
C. Use a query builder
D. Type code in a text file
Q6. Why does Richard suggest replacing the message box with opening a form for notifications?
A. Because message boxes require user interaction and halt code execution
B. Because message boxes do not support color formatting
C. Because forms are easier to close
D. Because forms consume no memory
Q7. Which function does Richard use to check whether any records meet a certain condition before showing the pop-up form?
A. DSum
B. DLookup
C. DCount
D. DMax
Q8. What does the 'NZ' function do in the context of using DLookup in this video?
A. Returns a Null value if the lookup fails
B. Converts all values to string
C. Returns 0 instead of Null if nothing is found
D. Counts all the records in the table
Q9. What is the purpose of making the pop-up notice form have no navigation buttons or record selectors?
A. To make the form more secure
B. To prevent users from moving between records or seeing table details
C. To allow multiple forms to open simultaneously
D. To enable multi-user editing
Q10. Why does Richard not prefer to use pop-up forms with the 'Pop Up' property set to Yes for reminders?
A. Because they always stay hidden behind other windows
B. Because they can appear on any monitor in multi-monitor setups
C. Because they consume more resources
D. Because they cannot be designed
Q11. What does the code 'DoCmd.OpenForm "NoticeF"' accomplish?
A. Opens a query called "NoticeF"
B. Opens the form named "NoticeF"
C. Sends an email using data from NoticeF
D. Runs a macro stored in NoticeF
Q12. How can you control when the pop-up reminder appears?
A. By editing the table relationships
B. By adjusting the form width and height
C. By changing the criteria in the timer event code, such as checking active customers
D. By changing the font color inside the form
Q13. What feature will be added in the extended cut for members, as mentioned in the video?
A. A countdown timer and a pause option for the reminder
B. The ability to print the notice directly
C. Synchronization with Outlook calendar
D. Export to Excel functionality
Q14. According to the video, what happens if there are no customers matching the IsActive=True criteria when the timer event runs?
A. A warning message is shown
B. The form is closed
C. No notice is shown
D. All customers are deleted
Q15. What suggestion is made for users unfamiliar with programming who want to implement the timer event?
A. Use only macros, not VBA
B. Complete the free Intro to VBA lesson first
C. Skip the timer event feature entirely
D. Use Excel instead of Access
Answers: 1-A; 2-B; 3-C; 4-C; 5-B; 6-A; 7-B; 8-C; 9-B; 10-B; 11-B; 12-C; 13-A; 14-C; 15-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 video from Access Learning Zone demonstrates how to create a reminder pop-up in Microsoft Access using a timer event. This type of notification can be incredibly useful for situations where you want your database to alert you at set intervals, for example, to remind you of new sales leads, upcoming appointments, or simply to take a break and walk around.
The scenario for this lesson came from a request to have Access notify the user once every hour if there are new sales leads in need of attention. Fortunately, Access gives you the flexibility to trigger actions at regular intervals through timer events, which are ideal for displaying reminders or running background checks in your database.
A good foundation for this lesson is a basic familiarity with VBA. If you're new to programming, I recommend watching my free Intro to VBA video. While you can accomplish some tasks without programming by using macros, with just a single line of VBA you can make your setup both easier and more reliable.
For this demonstration, I start with my TechHelp Free Template, but you are welcome to follow along using your own database. In every one of my databases, I use a main menu form that opens when the database starts. This persistent form is the perfect place for a timer event because it stays open in the background and the user isn't expected to close it during normal operation. In many of my databases, if the main menu is closed, the entire database will close, but that's a lesson for another time.
To set up the timer event, open your main menu form in design view and access its properties. On the Events tab, you'll find the On Timer event and Timer Interval property. The timer interval determines how often the event triggers, and it is set in milliseconds. For example, to have the event run every hour, you would enter 3,600,000. For testing purposes, I set the interval to 5,000 milliseconds, or every five seconds, so we can quickly see results.
When the timer event fires, you decide what action to take. While macros are an option, I vastly prefer using VBA for flexibility and control. The timer event code can be as simple as displaying a message box, but the downside there is that it halts all other activity until the message is dismissed.
A better approach is to have the timer event open a pop-up form as a notification. I create a simple form, "NoticeF," styled with a yellow background and a label announcing, for example, "There are customers to follow up with." This form does not need any data fields or navigation controls, and removing record selectors and scroll bars keeps it clean and focused on just delivering your message.
Instead of a message box, the timer event opens this pop-up form. The advantage here is that the notification form doesn't block your workflow; it can appear off to the side and be left open while you continue your work. However, Access will always bring the pop-up to the front whenever it's triggered. In the Members Extended Cut, I show how to adjust the form's behavior so it doesn't interrupt your typing or steal focus from your current work.
There is an option to use the form's Pop Up setting so it always stays on top, but due to multi-monitor setups, I usually avoid this, as the form might not appear on the screen you're currently using.
Now, you may want this reminder to show only under certain conditions, such as when there are active sales leads or pending follow-ups in your database. You can use criteria from any part of your database to determine whether the timer should trigger the notice. For this demonstration, I use an "IsActive" field in the Customer table to determine if any customers need follow-up. Using the DLookup function, the timer event checks if there are any active customers and only displays the form if necessary. If none are found, the reminder form doesn't appear.
To illustrate, I deactivate all customers and observe that no reminders appear. Once I activate a customer, within five seconds the reminder shoots up as expected.
You can build on this by adding a button to the notice form to open the relevant customer records or to display a list of active items. Additional enhancements, such as a countdown timer on your main menu form (showing the time remaining until the next check, with options to pause), are covered in the Extended Cut for members.
Timer events have a range of applications. For example, in my Access Expert 24 class, I demonstrate automatically retrieving and storing current weather information at set intervals. In Access Developer 6, I walk through creating advanced timers, such as those used for scheduled backups or sending emails. In Access Developer 11, there's an example of building a timed quiz form.
If you're interested in these more advanced solutions or would like more features for your reminder pop-up system, consider viewing the Extended Cut. Silver Members and above have access to these additional lessons, including downloads and sample code.
To become a member, simply join through my website, where you'll see the various membership levels and their benefits. Silver Members and up get access to all extended TechHelp videos and live sessions. Gold Members can access all sample databases and my Code Vault. Platinum level unlocks full access to my entire library of courses on Access and other Office software.
Remember, free TechHelp videos will always be available on my channel, so there's plenty of content for you to learn from regardless of membership status. If you found this video helpful, I appreciate your feedback and invite you to subscribe for future updates. Be sure to check the additional resources and links found below the video for related lessons, including my free Access Level 1 course.
If you have a question that you'd like addressed in a future video, visit my TechHelp page to submit your query.
A complete video tutorial with step-by-step instructions on everything discussed here can be found on my website at the link below.
Live long and prosper, my friends.
Topic List
Creating a reminder pop-up using a timer event
Setting the Timer Interval property in milliseconds
Using the On Timer event for scheduled actions
Adding VBA code to the On Timer event
Displaying a message box using VBA
Creating a custom notice form for reminders
Formatting and designing the notice pop-up form
Opening the notice form from VBA code
Using the DLookup function to check database conditions
Applying the NZ function to handle Null results in DLookup
Displaying the notice form based on specific criteria
Testing the timer functionality with and without active records
Demonstrating multi-user considerations for reminders
Adjusting reminder criteria using database fields
|
