Followups 2
By Richard Rost
4 years ago
Customer Followups in Microsoft Access, Part 2
In this Microsoft Access tutorial, we're continuing work on our Followup database. We will add a Followup Date to the table, show/hide the date field based on whether or not the user has selected this item to get a followup, and sort our query by date so that our followups show up on the form in order.
Pre-Requisites
Links
Up Next
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, How To Create A Customer Follow-Up System, organize your database followups, Database Follow up, follow-up template, Sales Follow-up System, customer follow-up system, Creating an Effective Follow Up System, show only followups, button from main menu, customer combo, double-click to open, contact management, crm, Add followup date, show/hide date, sort by date in query, show sorted on form
Subscribe to Followups 2
Get notifications when this page is updated
Intro In this video, you will learn how to add a follow-up date field to your contact form in Microsoft Access, control its visibility with a checkbox, automatically set default dates using simple VBA code, and update your follow-ups form to display dates in chronological order. I will also show you how to update your queries so follow-up records are sorted by date, making it easier to see which contacts you need to follow up with next. This is part 2.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.
Today is part two of my follow-ups series. If you haven't watched part one yet, go watch that first. I'll put a link down below you can click on to watch it.
In part two, we're going to add a follow-up date to the contact form, and then on our follow-ups form, we'll make it so when you click this little button here, the date will either display or hide.
Next, we'll sort the dates so that our follow-ups show up in order chronologically and do some other cool stuff.
So here we go.
We're back to the follow-ups series. Welcome to part two.
If you haven't watched part one yet, go get over there and watch it right now.
The next thing we're going to do is set up follow-up dates for each of these because if I talk to, let's say, Deanna Troy today, I might send her over a proposal. I want to put a date in there where that follow-up will show up on my list. Before that date, I don't want to see it. I don't want a big, gigantic, long list of everything here. Then you get decision paralysis. I just want to see the ones I have to call today.
This date/time field that we have in here already is the contact date/time. That's the date and time that this was put in the system. That's really not that important for your follow-up list. So I'm going to get rid of this one and replace it with the follow-up date.
To start doing that, let's go back to the contact table's design view and add a follow-up date. That will be a date/time.
I'm not going to put a default value in here because not every follow-up has to have a date. You might just check the box Yes and just leave it. You don't necessarily need to put a date in there, but if you want to push it toward the future, like if this guy is going on vacation and you don't have to call him back for a week, then you can mark a date and you won't see it.
So let's save our contact table.
First, we're going to start off with the customer form because this is where you're generally going to put in your contacts - talked about whatever follow-up in a week.
Now, for this guy I might mark it a follow-up and then we'll put the little box down here. I'm going to wall or add it, make this a little bit bigger. We got a wee bit more, like so. It doesn't matter if it's in the footer or the header. I'm going to slide it up here out of the way. That's a little bit too big.
Okay, so let's go to add existing fields, take our follow-up date, and drag it and drop it right there.
Perfect. Maybe I want it over here on the side. Let's do it. Let's put the date there. We got more room for activities down here.
Now let's open this guy's properties up. Let's go to All. I'm going to set the format equal to short date. If you want to have times on yours - if you're the kind of person who likes to log the time of your follow-up calls to the minute - that's fine. Put whatever time format you want in there. If you want to work with times, great. I'm just going to keep it to dates. In my long history of doing sales, I almost never had to have them that exact. Some people do. I would just put in the notes over here, call between two and four or whatever.
In case I didn't mention it before and you haven't seen this in my other videos, I use the ISO date standard, which is year-month-day. That way I've got clients all over the world, and it prevents my students from having problems with different regional date formats. So I keep all my databases in this format so we're all on the same page.
I promised in the last video that I'm going to sprinkle in a little bit of VBA here and there just to make the database cool. Just so you can see how, with a little teeny tiny bit of VBA, I can atisia.
What we're going to do is make this guy not visible. Let me move this over here so it's not in the way. Open up the properties, go to format, and set Visible to No. I don't want to see it - that follow-up date field unless this is checked on. If this is checked on, then make that visible. If it's checked off, then make it not visible.
To do that, we need to program in two events to do the same thing: an After Update event for this checkbox. When I click on that, it updates this property to visible or not visible, and I also need an On Current event. That will run when you move from record to record, so this will be displayed based on the value of that.
If you want to learn more about these things go to After Update - watch that video - and the On Current event - watch that video too.
I'll start with the After Update event for the checkbox here, so I'll go to Events, After Update, dot dot dot. That will bring up my Code Builder.
I'll say in here: ShowHideFollowUpDate.
What does that mean? We're going to make our own subroutine called ShowHideFollowUpDate. Copy that, come down here, Private Sub ShowHideFollowUpDate. Now whatever happens in here is what's going to run when the After Update goes. It's going to run this.
Why did I do that? Well, I'm going to make its own subroutine out of it because I'm going to call it from two different places. I'm also going to call it from the form's On Current event.
So in here, I'm just going to put FollowUpDate.Visible = FollowUp.
Now FollowUp is a true or false value. So if it's checked on and FollowUp is true, I want the FollowUpDate to be visible. If it's not, it'll make it false.
Save it.
Let's close this. Let's open it back up again.
Now just check the box. Look at that, see? That data appears. Same.
We also have to call the same thing from the On Current event so that same code runs in the On Current event. I'll go back to your code window. We can do it this way. Let me show you the easiest way to get to it. Go in here, design view, double click here for the form properties, events, and then On Current.
You have to dot dot dot here. The other way, I'm more used to doing it this way: you drop this box down, pick form.
Now it's going to put you in the form load event. Just ignore that and go in here and go to current.
Now you're in the form current event. We copy this, we paste it in there, and we get rid of this.
It's that simple.
Now we can close this and close this. Let's reopen it. Save changes? Yes.
There we go. We click on that one, click on this one. It hides it. No follow-up. Click here, no follow-up. Click there, and you can see this follow-up in.
Now, you might want to have it so that when you click this box, tomorrow's date goes in there as a default. If you turn this off, maybe delete it. We can do that by adding some additional code into the After Update event. You can say right here, with a little If Then statement:
If FollowUp Then
FollowUpDate = Date + 1
Else
FollowUpDate = Null
End If
Let's see what happens.
Check it on, it sets it to tomorrow. Today is December 21st. Turn it off, and it sets it to null. You can verify that by looking in the table.
Let's do this. Talked about whatever follow-up in a week. Where are you? Talked about whatever follow-up in a week and it is set to null.
It never hurts to throw a Me.Refresh at the end of that because if you look here - let me run this out real quick and show you what's going on - if you look, whenever I click this button, now notice that record is dirty. It means it hasn't been saved at the table yet, so a Me.Refresh on the end of that forces Access to save it to the table immediately as soon as you click on that box.
Now let's take this follow-up date and let's put this over on the follow-ups form on here, which is a little more useful. I am going to change this to FollowUpDate. We'll change from Contact Date to FollowUpDate. I'm going to change this to short date. We don't need times in here and don't forget to change the name of the box as well.
It doesn't need to be that big. We can shrink it down just a little bit, and let's put this on the right side.
That's my hourly chime.
So there's follow-up, and let's just make this date and we would follow up in it.
That looks better.
Save it, close it, open it, and there's our follow-ups and follow-up dates. Not all of them have dates and that's okay. We have to follow up at some point.
In fact, let's do this. I think that there was a little training. The user will understand what we'll do by that there. Bring that in, then we got more information.
Don't forget you can use that whole notes. I like the way that looks better. Save that, close it, open it up. Yes, that's good.
Let's edit our underlying query so these guys are sorted by date. Let's go to that FollowUpQ, design view.
We're going to add in our FollowUpDate, hide it so we don't get duplicates, and then we're going to sort this. Let's sort it ascending so the oldest ones show up on top. Save it, close it, open it back up again, and there you go.
Yes, there is a way to have it so that null values sort to the bottom of the list. It's a little more tricky. I do cover that in Access Developer 26.
But now if you do come in here and give these guys dates - let's say this one is going to be 12-23 and this one is going to be January 4th of next year - when you close this and reopen it, there you go. They're in order.
In part three, I'm going to show you how to set it so that you don't see any follow-ups that are in the future, and we'll make a little button you can click on so you can or cannot see them. Sometimes you might want to go and say, What are my follow-ups for tomorrow? We'll do that in part three.
There you go. There's your part two. Hope you enjoyed it. Hope you learned something.
See you next time for part three.Quiz Q1. What is the primary reason for adding a follow-up date to the contact form?
A. To record the date and time each contact was created
B. To ensure contacts are always sorted alphabetically
C. To display only the contacts that need to be followed up on each day
D. To limit the number of contacts stored in the database
Q2. What should be done to the existing contact date time field on the form to effectively manage follow-ups?
A. Leave it as the default follow-up date
B. Remove or hide it and replace it with the follow-up date
C. Rename it as the follow-up date
D. Set its default value to today
Q3. Why does Richard recommend not setting a default value for the follow-up date field?
A. Because every follow-up must always have a date
B. So users are forced to enter a date every time
C. Because not all follow-ups require a specific date
D. To auto-fill the date with yesterday's value
Q4. Which field property does Richard modify to hide or show the follow-up date field based on the checkbox value?
A. Row Source
B. Default Value
C. Visible
D. Read-Only
Q5. When should the subroutine ShowHideFollowUpDate be called?
A. Only when the database starts
B. Only when saving the form
C. From both the After Update and the On Current events
D. Only during initial form design
Q6. What is the purpose of the On Current event in this context?
A. To trigger validation rules
B. To display or hide the follow-up date when moving between records
C. To automatically close the form
D. To sort records as the form loads
Q7. How is the follow-up date set to default to tomorrow's date using VBA?
A. Setting FollowUpDate = Now + 1
B. Setting FollowUpDate = Date
C. Setting FollowUpDate = Date + 1 when the checkbox is checked
D. Setting FollowUpDate = Null whenever the checkbox is checked
Q8. What does adding Me.Refresh at the end of the event code accomplish?
A. It closes the form automatically
B. It makes Access save the current record to the table immediately
C. It recalculates all calculated fields in the database
D. It undoes the last action
Q9. What sorting is applied to follow-up records in the FollowUpQ query to make the list more useful?
A. Sorting by contact name ascending
B. Sorting by follow-up date descending
C. Sorting by follow-up date ascending
D. No sorting is applied
Q10. Why is the ISO date standard (year-month-day) recommended by Richard?
A. It is required by Access for all date fields
B. It helps prevent issues with different regional date formats
C. It is the only way to input dates using VBA
D. It always defaults to today's date
Q11. What does Richard plan to show in part three of the series?
A. How to create a summary report for follow-ups
B. How to make a form that accepts only past dates
C. How to filter out future follow-ups and add a button to toggle them
D. How to export follow-up records to Excel
Answers: 1-C; 2-B; 3-C; 4-C; 5-C; 6-B; 7-C; 8-B; 9-C; 10-B; 11-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 video from Access Learning Zone continues our series on follow-ups in Microsoft Access with part two. If you have not watched part one yet, I recommend checking that out first so you are up to speed on what we have covered so far.
In part two, we focus on adding a follow-up date field to the contact form. This enhancement allows you to track when you need to follow up with a contact. The idea is to avoid being overwhelmed by a long list of follow-ups. By setting a date for each follow-up, only those that are due today will appear, which helps you stay organized and prevents decision paralysis.
Up until now, the form included a date time field, but that simply recorded when the contact was entered into the system. For our purposes, we do not need that field for managing follow-ups, so we remove it and introduce a new field specifically for the follow-up date.
We start by opening the contacts table in design view and adding a new field for the follow-up date. It is set as a date time field. I intentionally do not provide a default value since not every follow-up needs to have a date assigned. In many cases, you will just check the follow-up box and leave the date empty, but if you want to set a future date, say if the contact will be unavailable for a week, you can fill that in.
Moving over to the customer form, this is where you generally enter new contacts and their follow-up information. After making room on the form, I add the follow-up date field using the add existing fields option and arrange it where it makes sense visually. For most users, just the date is sufficient, so I set the format to short date, but you are welcome to include the time if that is helpful to you.
I prefer using the ISO date format of year-month-day. This avoids confusion with different regional date formats, especially if your users or clients are spread across various countries.
Next, I promised a touch of VBA. In this case, I want the follow-up date field on the form to only be visible if the follow-up checkbox is checked. This requires a little bit of coding. I set the visible property of the follow-up date field to 'No' so that it starts out hidden. When the follow-up checkbox is ticked, the date field becomes visible, and when the box is unticked, it hides again.
To achieve this, I write a small subroutine called ShowHideFollowUpDate. This routine is called in two places: first, in the after update event of the checkbox so the visibility updates when you check or uncheck it; and second, in the form's on current event so that as you move between records, the appropriate fields show or hide based on the checkbox value.
This way, if the checkbox is checked, the date field becomes visible for that record. If it is unchecked, the field stays hidden.
You might want the follow-up date to default to tomorrow's date automatically when you check the box, and clear it when you uncheck the box. This can be done by adding an If Then statement in the after update event. When checked, it enters the next day's date. When unchecked, the date is cleared to null. I also recommend adding a Me.Refresh statement to ensure the record is saved right away after clicking the box. This forces Access to write the change to the table immediately.
Next, we update the follow-ups form to use this new field. I modify the label and textbox to refer to FollowUpDate, and set the format to short date to match our earlier changes. After rearranging the controls to improve layout, everything looks better and cleaner.
Now, we want our follow-ups to appear in chronological order. To accomplish this, I go into the underlying query, add the FollowUpDate field, and sort the records in ascending order by date. This way, the oldest follow-ups appear at the top of the list. Not all contacts need follow-up dates, and having them sorted this way keeps things manageable.
You might wonder about sorting records so that those with no follow-up date appear at the end. That is a little more advanced and covered in my Access Developer 26 course if you are interested.
Finally, I show how changing or setting dates on the form will affect their sorting once the form is reopened. Giving records dates in the future moves them into their proper chronological place.
Next time, in part three, I will show you how to filter out any follow-ups that are set in the future, so you only see the ones that are due now. I will also demonstrate how to create a button to toggle whether or not you see future follow-ups, which can be useful if you want to get a sense of what is coming up soon.
That wraps up part two of the follow-ups series. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Adding a follow-up date field to the contact table
Inserting the follow-up date field onto the contact form
Formatting the follow-up date as a short date
Using ISO date format to avoid regional issues
Hiding and showing the follow-up date field with a checkbox
Writing a VBA subroutine to toggle field visibility
Using After Update and On Current events to control visibility
Defaulting the follow-up date to tomorrow using VBA
Clearing the follow-up date when unchecked using VBA
Using Me.Refresh to save form data to the table
Adding the follow-up date field to the follow-ups form
Renaming and formatting the follow-up date control
Editing the query to sort follow-ups by date
Sorting follow-up records in ascending date order |
