Assigning a Follow-up Date

You've made your contact and they have suggested, or you feel it appropriate, to make a second call when? Tomorrow, if you got voice-mail and didn't leave a message, next week if they are away on business this week, next month if they are away on vacation, and need a week to clear their backlog when they return, or perhaps just a call in six months time. Perhaps you want to rotate your multiple contacts within one form and want to make a follow-up call at some random time in the future.

In this lesson we are going to create a set of buttons, each similar in nature, but with slightly different effects. You may modify our choice of date intervals to suit your own preferences.

By now you are becoming familiar with the addition of command buttons to your form. We will use each new button to explore another part of Access/VBA.

Make six more Command Buttons and place them next to the Follow-Up date field. Give them Captions

"1Day",

"1Week",

"1Month",

"6Months",

"Random" and

"Never"

Their names will correspond: "cmd_1Day", "cmd_1Week", "cmd_1Month", "cmd_6Months", "cmd_Random" and "cmd_Never".

You can get help with the alignment of the buttons by selecting the entire set of Command Buttons (Click on the first Command Button, then hold down the Shift key while you click on each of the others), and from the menu choose Format, Align, Left (or Top); Format, Align, Size, To Widest; Format Vertical Spacing, Make Equal or Format Vertical Spacing, Decrease.

Here is the Click event procedure for the One-day button:

Private Sub cmd_1Day_Click()
Me.FOLLOWUP = Format(Now() + 1, "YYYYMMDD")
End Sub

The reserved word "Me" always refers to the form on/in which we are working.

The "Followup" is the name we had given, long before we met you, to the data field that holds a follow-up date - the date on which we should make a follow-up call to our client.

The Format function formats, or converts a numeric value to a string form.

The expression inside the parentheses has a component "Now", which in most systems is today's date (and time!). Adding "1" to "Now" gives us "tomorrow", or "1 day more than today.

The string "YYYYMMDD" is a special message to the Format command, telling it to lay out tomorrow's date as 4 characters for the Year, 2 characters for the Month, and 2 characters for the Day. We treat our FollowUp field as an eight-character string.

Load your form, take a note of the Followup date, then click on your 1Day button. You should see the Followup date has been set to tomorrow's date.

Here are the code procedures for the remaining events that modify the FollowUp date:

Private Sub cmd_1Day_Click()
Me.FOLLOWUP = Format(Now() + 1, "YYYYMMDD")
End Sub

Private Sub cmd_1Week_Click()
Me.FOLLOWUP = Format(Now() + 7, "YYYYMMDD")
End Sub

Private Sub cmd_1Month_Click()
Me.FOLLOWUP = Format(Now() + 31, "YYYYMMDD")
End Sub

Private Sub cmd_6Months_Click()
Me.FOLLOWUP = Format(Now() + 180, "YYYYMMDD")
End Sub

Private Sub cmd_Random_Click()
Randomize
Dim i1 As Integer
i1 = (Int(93 * Rnd + 60))
Me.FOLLOWUP = Format(Date + i1, "YYYYMMDD")
End Sub

Private Sub cmd_Never_Click()
Me.FOLLOWUP = "99999999"
End Sub

Most of them are self-evident once you remember that "Now" means today, so "Now + 180" must be 180 days from now, that is, 6 months from now (roughly speaking).

"Never Again" is for those prospects who have indicated that they may never use my services, perhaps because they are already well supplied. I want, therefore, never to see them pop up on my follow-up screen. However, maybe I will want to use their services, and for that reason I do not want to delete them from my list. It will be a long, long time before we reach the year 9999.

I use the Random Followup date when I have established contact with several individuals on the same day. I don't want to give them all a follow-up call exactly so-many months from now, and the Random date allows me to scatter opportunities. The little expression calculates a date as 93 days (3 months) into the future plus a random number of days from 1 to 60. The Followup date will therefore be set anything from 3 to 5 months into the future.

As you play with these six buttons, note that the random button always gives a different date, whereas the other buttons give a date fixed relative to today's date.

If you want to be very adventurous, have the Command Buttons change their own captions when you click them, so that today they will reflect the dates that will be set if you click them again.

We have chosen to embrace the random dates in double-quotes to indicate that it is a supposed date (refer to TV shows where the actors hold up two fingers of each hands to indicate rabbit-ears!). We had to indicate a double-quote in a string by typing it twice, so as not to confuse it (them!) with the double-quotes that delimit the string itself. You'll get used to it.

Search