BeforeUpdate & AfterUpdate
By Richard Rost
7 months ago
BeforeUpdate & AfterUpdate in Access: Conflict Check In this Microsoft Access tutorial I will show you how to use the BeforeUpdate and AfterUpdate events together to prevent double-booking appointments, validate user input for valid dates and times, and prompt for sending a confirmation email after an appointment is added. We will build a simple appointment form, check for date conflicts, and add useful validation to make sure appointments are entered correctly. Bryce from Mesa, Arizona (a Platinum Member) asks: I have a form where I schedule appointments, and I only book them on the hour, like 10am or 3pm. Is there an easy way to check if I already have something scheduled at that time so I don't double-book? Also, after I enter the appointment, can Access prompt me to send a confirmation email? I keep forgetting to do that, and it would really help if the system reminded me automatically. MembersThere 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!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, Before Update and After Update together, appointment conflict resolution, DLookup conflict check, validate date input, confirmation email prompt, NZ function usage, VBA message box Yes No, developer-level VBA, appointment form design, hour-only scheduling, appointment validation, date and year validation, TechHelp template, continuous form setup, VBA event handling, send email with CDO, prevent double booking
Transcript
Today we are going to learn about using Before Update and After Update together. I have separate videos on Before Update and on After Update, but this example is going to show you a good combination of how they work together.
Today's question comes from Bryce in Mesa, Arizona, one of my Platinum members. Bryce says: I have a form where I schedule appointments, and I only book them on the hour like 10 AM or 3 PM. Is there an easy way to check if I already have something scheduled at that time so I do not double book? Also, after I enter the appointment, can Access prompt me to send a confirmation email? I keep forgetting to do that, and it would really help if the system reminded me automatically.
First of all, it is good that you only have appointments on the hour, because if you had a bunch of different times, it would be more difficult to check for something like 10:05. Then you would have to put in durations or appointment ending times. As long as they are all hour-long appointments on the hour, this will be easy to do. If not, I have other alternatives. We will talk about that later. And sending a confirmation email is, again, not that hard to do.
This is a perfect example of a Before Update situation and an After Update situation. Before the field is updated, you have to verify that it is a valid date. Check to make sure it is a correct date. Make sure the date range is valid. They do not enter something from 1990, for example. Then you want to check to see if that same date exists in the table. You can give them an error and not allow them to save the update in the Before Update event.
Once you have done all that and verified that it is a valid date, then you can do the After Update stuff. For example: ask if you want to send an email confirmation. We are going to do that in today's video.
First off, this is a developer-level video. What does that mean? That means if you do not know VBA and want to learn VBA (and I strongly recommend you do because it is awesome), go watch this video first. It is about 20 minutes long and will teach you everything you need to know to get started.
I recommend you go watch my separate videos on Before Update and After Update so you understand the differences between them. Also, you should know the Lookup. We are going to use this to see if there is already an appointment with the specified date in the table. We are going to use the NZ function in case there is not. It will return a null, so we have to handle that. You should know how If Then blocks work, and of course, you should know how to use a message box to get a Yes or No response.
These are all free videos. They are on my website. They are on my YouTube channel. Go watch all of those, and then come on back.
Here I am in my TechHelp free template. This is a free database. You can grab it up from my website if you want to, but this will work with pretty much any database you have.
Alright, so let us create a real quick appointment table. Table Design. We have got AppointmentID - that is our AutoNumber. The AppointmentDateTime - that will be a Date/Time field. A Description - Short Text. And Notes - Long Text. If you want to put other stuff in here, like a CustomerID or who it is for, whatever you want to put in here, that is fine. But that is not the point of this data or this video. We are just going to keep it simple.
Alright, save this as my AppointmentT, and yes, I tend to abbreviate really long words like appointment. Private primary key, yes, there we go, save it. Alright, let us close it and we will make a real quick form for it.
You can make a single or a continuous form. I will use a continuous form for this. I have got some basic ones in here. Copy and paste that one. We will call this my AppointmentF. I am going to open that up and right click Design View. We are going to bind this form to the appointment table, so go to Data, Record Source, drop that down, pick AppointmentT.
I am just going to get rid of these fields and bring in the ones right from here. All we need is DateTime and Description. Actually, I like to bring them in with Description first. I put the Description there, get rid of the label. I like to type in the Description first. Maybe that is just the way I work. I like to put in here, okay, we are going. We have got an appointment for whatever. Then we will put the Date and Time. That is just how my brain works.
If I am putting it on a calendar, then you need to find the date on the calendar first. But when I am usually setting up appointments with people, I am like, "Okay, got to meet with Captain Picard, and then okay, what time are we doing that?"
Of course, our labels go up top. I like to just use one label sometimes, especially if it is simple like this. So we have got the Appointment, and then the DateTime. Make sure your tab order is right. Tab Order: Description, AppointmentDateTime. Make sure everybody is left aligned. Good, because remember, date/times usually go to the right. I hate that.
Here is my brand new appointment form that points to my appointment table. Real quick and easy to set up.
Alright, we have got a phaser inspection, and that is going to be... I am going to hit Control+Semicolon - that puts today's date in there. You can easily change it to tomorrow if you want to. Then a space and then 9AM.
Then we have got a photon torpedo inspection. Those of us in Starfleet just call it PTs (that is physical training), and that is going to be at 10AM.
Then the doc wants to see me down in Sickbay, so he wants to see me at 10AM. But wait a minute, I have got a conflict. I have got a photon torpedo inspection, so I have to tell the doctor I cannot do it.
This is where we need that Before Update event to check for those conflicts.
Right click Design View, go to your AppointmentDateTime, bring up its properties, double click on it. Go to the Events tab, then to Before Update. Click ... to bring up the code builder.
We are going to need an ID because we are going to look up an appointment.
Dim ID As Long. We are going to look up an appointment, but whenever I do temporary lookups like this, sometimes I just call it ID. You can call it whatever you want.
We are going to check for conflicts, and it works out great that we are only having appointments on the hour. If you had to look up like 10:05, you would need more detailed appointment conflict resolution with the duration of the appointment or the end time.
If you do want to get that complicated, I do have a more complicated series of videos where we build a full-fledged appointment database with a start time and an end time and conflict resolution and all that. Check out that video if you want more. In today's video, we are keeping it simple.
Here, we are just going to say:
ID = NZ(DLookup("AppointmentID", "AppointmentT", "AppointmentDateTime = #" & AppointmentDateTime & "#"), 0)
NZ is in case DLookup throws us a null. What are we looking up? An AppointmentID from AppointmentT where AppointmentDateTime equals whatever the current DateTime is that the user just entered. That has to be inside pound signs because it is a DateTime. The field is AppointmentDateTime like that.
Close the DLookup. Return a zero if it is null. That is what NZ does for us.
Now, if it finds another appointment in the table at that same DateTime, it is going to return an ID - like a 6 or a 5 or a 32.
So if we get it, I am going to say: If ID <> 0 Then we have a conflict.
Show a message box: "There is a conflict." Then set Cancel = True. That is going to keep the user in that box; they cannot leave unless they either hit Esc and back out, or they type in a different date. Then Exit Sub. End If. And that is it.
Save it. Debug - Compile once in a while, and yes, I am putting that on a t-shirt.
Close it and then open it down here. We do not have a button for it yet.
Let us put in that Sickbay appointment and put in 10AM. There is a conflict. Cannot do it. Cannot leave the field until I fix that conflict.
Alright, fine. Tell the doctor 11AM. We have got a transporter inspection day. Tomorrow at 9AM. Conflict! See? There is one right above it. So that has to be Noon. See?
That is how you do basic Before Update conflict resolution if your appointments are on the hour, every hour.
Of course, there are some other basic things you can throw in here too before you even get that far. You could say (I do this one all the time): If it is only a time, tell them they need a date.
Here is a cool trick: If the AppointmentDateTime that they enter is less than one. Remember, one is equal to one day. So if they type in just 9AM, that means they did not put a date portion in, right? Then the value will be less than one.
I am just going to copy all the code above and put it there. Now we have time only. Show a message: "You only entered a time. Please add a date too." Or whatever else you want to say.
So we will do that before you even get to the conflict resolution.
Save it. Debug Compile once in a while, come back out here. Now if I come in here and type "Tribble Roast" and put in 10AM, "You only entered a time. Please add a date too." Oh, okay, so that is tomorrow, and see, I left it at midnight at 6PM.
I do not mean we are actually roasting tribbles. They are gross. You have to shave all that hair off. I mean roast like a comedy roast. What do you think? I am not some kind of Klingon. Do only Klingons eat tribbles? They have to because they were all over the engine room.
Another one that I like to throw in there: Let us again copy all this. Let us make sure it is a valid year. I do this a lot too.
Check for a valid year. If the Year of AppointmentDateTime is less than 2000 or the Year of AppointmentDateTime is greater than 2100, then invalid year. Show a message like "Year must be between 2000 and 2100."
Why those dates? I do not necessarily want to say before today's date, because sometimes I backdate appointments. If I had an appointment last week Wednesday with somebody, maybe I will put in last week's date and then go back and fill this way. I have a full contact history and appointment history with all my clients.
So you might want to allow them in the past, but not back too far. If you started the company in 2002, you would not want to allow dates before that. So that is why go back to 2000.
Why 2100? Well, it is far enough in the future where I will not be here. So if I accidentally type in 2250, then oh, okay. You could change whatever range of dates you want.
Now if I come in here and put in "Going back to see mom on November 5, 1955," 1955 of course, you get the point.
That is all the Before Update part of it.
What about the After Update? Once we have a valid date in here, now we can do stuff that you might want to - reminders are perfect for this.
After you have a valid date, you are going to save the record. Everything is good.Now we're going to come up here and change Before Update to After Update. That's a fast way to get to the other properties for the same field.
Point and date times are over here; After Update is over here. There is also a Before Update for the entire record too. I have whole separate videos on that.
I usually put something like this in the After Update event for the record, so when you're done editing the record. But this works fine here too.
Here we can just say, if MessageBox: "Would you like to send a confirmation email?" I like vbYesNoCancel. I like Cancel too because sometimes people get decision paralysis. If they get Yes or No and they're not sure, they do not know what to click. It gives them a quick and easy way to back out of it.
Plus vbQuestion. Plus vbDefaultButton2. I want the default to be 2, which is No (the second button). If that is anything but vbYes, then exit the sub, so if they enter No or Cancel it will just exit.
I'm going to put right here: send email here. Am I going to do it right now? No, because I have several other videos on how to send emails.
This is the one I recommend. I'll put a link down below: Sending Email with CDO. It sends it direct to their mail server. It does not require a SendObject or Microsoft Outlook.
Because in the coming future, they're going to be removing Outlook's ability to be automated with VBA. So this is what I recommend you do.
But if you want the older, simpler version that uses Send Outlook Email, you can watch this one too. Both work. This one's just going to be outdated soon.
But this one is easier. If you're currently using Outlook, you have a couple more years left still, so if you want to do it this way, that's fine too.
But there you go. That's how you build a real quick, simple appointment form with some basic conflict resolution.
You could do it on the half hour or the hour or whatever. It doesn't matter. Even if you have 15 minute appointments, as long as it's not the exact same start time, that's what matters.
But if you do 20 minute appointments and you want to start one at 11:05, this won't catch that. But if you do 15 minute appointments, and they're always on a quarter hour, like 11:00, 11:15, 11:30, 11:45, then this will work just fine.
All we're checking is that exact date and time. In my Appointment series, I show you how to do a little more robust conflict management.
But that's going to do it, folks. That is your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Combining Before Update and After Update events
Creating an appointment table in Access
Designing a simple appointment form
Binding a form to a specific table
Checking for duplicate appointments using DLookup
Using NZ function to handle null values
Conflict detection for overlapping appointments
Displaying a message box for appointment conflicts
Preventing form field updates with Cancel = True
Validating date entry includes both date and time
Ensuring date entry is within a valid year range
Displaying custom error messages for invalid date input
Prompting to send confirmation email after valid entry
Using MsgBox with Yes No Cancel options
Suggesting methods for sending email from Access
COMMERCIAL:
In today's video, we're learning about using the Before Update and After Update events together in Microsoft Access. You'll see how to create an appointment form that checks for scheduling conflicts before saving the record, making sure no double bookings slip by. We'll cover verifying the date, making sure a valid year is entered, and confirming that both a date and time are provided. Then, after a valid appointment is entered, you'll learn how to prompt the user to send a confirmation email automatically. If you're new to VBA, there are helpful recommendations for what you need to know beforehand, plus tips for working with DLookup, the NZ function, and message boxes. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the primary purpose of using the Before Update event in the appointment scheduling form?
A. To verify the validity of the date and check for appointment conflicts before saving
B. To prompt the user to send a confirmation email
C. To automatically add customer notes to the appointment
D. To sort the appointments in chronological order
Q2. Why is it easier to check for conflicts when appointments are scheduled only on the hour?
A. Because all times are unique and need no validation
B. Because it simplifies the lookup since overlapping times are not an issue
C. Because users are less likely to make mistakes
D. Because Access automatically prevents duplicate times
Q3. What does the NZ function do in the context of checking for appointment conflicts?
A. It formats a date so it displays in the correct format
B. It handles null values returned by DLookup and provides a default
C. It notifies the user of conflicts by displaying a message
D. It prevents users from entering duplicate appointments
Q4. When using DLookup to find a conflicting appointment, what is being compared?
A. Description fields in the table
B. AppointmentID numbers only
C. AppointmentDateTime fields for an exact match
D. Customer names for conflicts
Q5. What action should be taken if only a time is entered without a date for an appointment?
A. Allow the entry since any time is valid
B. Reject the entry and ask the user to include a date
C. Change the time automatically to today's date
D. Ignore and save the record anyway
Q6. Why does the code check that the appointment year is between 2000 and 2100?
A. To prevent appointments that have already passed
B. To ensure that no future appointments are ever scheduled
C. To keep the appointment history within a reasonable business range
D. To match the format required by Access
Q7. What happens in the Before Update event if there is a conflict with an existing appointment?
A. The conflicting record is overwritten
B. The user is notified and cannot leave the field until the conflict is resolved
C. The appointment is rescheduled for the next available hour
D. A confirmation email is automatically sent
Q8. What is the purpose of the After Update event in the tutorial's example?
A. To check for duplicate records after saving
B. To prompt the user to send a confirmation email
C. To reset the form for a new entry
D. To recheck for appointment conflicts
Q9. Why is it not sufficient to use only the Before Update event in this scenario?
A. Because only After Update can validate user input
B. Because After Update is required to prompt for additional actions after validation
C. Because Before Update cannot prevent conflicts
D. Because After Update runs before changes are saved
Q10. What are some limitations of this simple conflict checking method?
A. It cannot check for overlapping times unless appointments are always at the exact same time slots
B. It cannot handle customer cancellations
C. It cannot handle appointments on weekends
D. It cannot save appointments at all
Q11. Why does the video recommend learning VBA for these kinds of solutions?
A. Because it's necessary for basic table design
B. Because VBA allows for automation and custom logic like validation and prompts
C. Because macros are unreliable in Access
D. Because tables cannot store appointment data without VBA
Q12. If the user chooses "No" when prompted to send a confirmation email in the After Update event, what happens next?
A. The email is sent anyway
B. The code exits the event without sending an email
C. A new appointment record is created
D. The appointment is deleted
Q13. What method does the video recommend for sending confirmation emails due to upcoming changes in Outlook automation?
A. Using CDO to send emails directly via mail server
B. Only using SendObject with Outlook
C. Sending emails manually outside Access
D. Creating a text file with appointment details
Q14. If you wanted to allow appointments with variable durations that might overlap, what would you need to add?
A. Nothing, the current method will handle overlaps automatically
B. Start and end times, and more advanced conflict checking logic
C. Only a Description field
D. A separate notes form
Q15. What is one benefit of using message boxes with options like Yes, No, and Cancel when prompting users?
A. It allows users to save time by always choosing Yes
B. It prevents users from making any mistakes
C. It gives users clear and flexible choices, including backing out if unsure
D. It requires users to review appointment history first
Answers: 1-A; 2-B; 3-B; 4-C; 5-B; 6-C; 7-B; 8-B; 9-B; 10-A; 11-B; 12-B; 13-A; 14-B; 15-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone focuses on how to use the Before Update and After Update events together in Microsoft Access, specifically in the context of scheduling appointments. In this lesson, I'll walk you through a solid example of when it makes sense to combine these events for practical form validation and automation.
The scenario for today's lesson comes from a common request: managing hourly appointment bookings and ensuring there are no double bookings. For example, suppose you have a form where you regularly schedule appointments only on the hour, such as 10 AM or 3 PM. You want a system that prevents double bookings by checking if something is already on the schedule at that time. Additionally, you want Access to prompt you to send a confirmation email after entering an appointment so that important follow-ups are not forgotten.
Scheduling all appointments on the hour simplifies the conflict-checking process. If you allowed for arbitrary appointment times, like 10:05, then you'd need to manage appointment durations and check for partial overlaps. Having standardized, hourly slots makes this example straightforward, but I'll mention some alternatives later for those with more complex needs. The task of prompting for emails is also straightforward in Access VBA.
This situation is a great demonstration of where Before Update and After Update work well together. Here's how they fit:
- Before updating the data, you validate the field. This means checking for a correct and complete date, ensuring the date falls in a reasonable range, and verifying that there is not already an appointment at that specific time slot. If there's a conflict, you stop the update.
- Once the appointment passes all validation and is saved, After Update is a good place to prompt for an additional action, such as sending a confirmation email.
Now, a quick note: this video is intended for developers. If you're new to VBA or want to strengthen your skills, I highly recommend watching my introductory tutorial on VBA, which will help you get up to speed.
You should also review my separate tutorials on the Before Update and After Update events, as well as on using the DLookup function, the NZ function to handle nulls, If Then logic blocks, and how to display message boxes with Yes/No responses. All these resources are available on my website and my YouTube channel.
For this example, I'm using my free TechHelp template database, which you can also download from my website, but feel free to use any existing database you have.
I start by creating a simple appointment table with fields for AppointmentID (AutoNumber), AppointmentDateTime (DateTime), Description (Short Text), and Notes (Long Text). Of course, you can expand on this and add fields like CustomerID or other information specific to your needs, but for this demonstration, we'll keep it straightforward.
Next, I create a continuous form based on this appointment table. I like to arrange the form with the Description field first, followed by the appointment date and time, as that's the order I tend to use when entering appointments. If you prefer another field order, adapt as you wish.
With the form set up, I enter a few sample appointments. For example, a phaser inspection at 9 AM, a photon torpedo inspection at 10 AM, and so forth. When I attempt to add an appointment at a time that already has one scheduled, such as 10 AM, I need a way to prevent this overlap.
To accomplish this, I use the Before Update event for the AppointmentDateTime control in the form. In the event's code, I look up the appointment table to see if another appointment already exists at the same date and time. If DLookup finds a record, I notify the user of the conflict and prevent the update.
It's essential to return a zero if DLookup yields no results, so the NZ function is helpful here. If the returned ID is not zero, I display a message stating there is a conflict, cancel the update, and keep the user in the field until a valid, available time is provided.
You can also validate other things before checking for conflicts. For example, you can check if the user has entered only a time without a date. In Access, if the value is less than one, it indicates a time without a date, and in that case, you can prompt the user to include a full date.
similarly, you can add a check for a valid year. If the entered year is outside a specified range (for example, before 2000 or after 2100), display a message and prevent the record from being saved. This flexibility is useful for cases where backdating is allowed for recent years but not for improbable years far in the past or future.
All of these checks happen in the Before Update event, which is where you guard against bad or conflicting data.
Once the data is validated and accepted, you can take additional actions in the After Update event. For this example, the After Update event is used to prompt the user with a message box: "Would you like to send a confirmation email?" Give the user options for Yes, No, or Cancel, with No as the default to avoid accidental sends.
If the user chooses Yes, you could proceed to send an email. While I do not cover all of the email-sending code here, I have separate tutorials to show you how to send emails using CDO, which does not require Outlook, and is more future-proof. There are also older tutorials for sending emails through Outlook if your setup still supports it.
With this approach, you have a straightforward appointment form that checks for conflicts, ensures full and valid date/time entries, validates the year, and prompts for a confirmation email after each appointment is entered. This method works well for hourly, half-hour, or quarter-hour appointments, as long as appointment start times do not overlap. For more advanced scenarios, such as appointments at arbitrary times or those that span various durations, I recommend checking out my in-depth Appointment series, where I cover more sophisticated conflict resolution.
If you found this lesson helpful, a complete video tutorial walks you through every step discussed here and is available on my website at the link below.
Live long and prosper, my friends.
Topic List
Combining Before Update and After Update events
Creating an appointment table in Access
Designing a simple appointment form
Binding a form to a specific table
Checking for duplicate appointments using DLookup
Using NZ function to handle null values
Conflict detection for overlapping appointments
Displaying a message box for appointment conflicts
Preventing form field updates with Cancel = True
Validating date entry includes both date and time
Ensuring date entry is within a valid year range
Displaying custom error messages for invalid date input
Prompting to send confirmation email after valid entry
Using MsgBox with Yes No Cancel options
Suggesting methods for sending email from Access
|
