• Printer Friendly Version
• Part 1: The Form
• Access Index
• VBA Index
• View the Code
• Download the File
• More About Leap Years

Build a Combo Box Date Chooser
Part 2: Add the VBA Code

The Story So Far...

In Part 1 of the tutorial I built a form containing three combo boxes, one each for Day, Month and Year. These combo boxes will work as a date chooser, the user's selections from the boxes being converted to a date which will be placed into a text box on the form (which is bound to a date field in the form's underlying table). The form looks like this [point at the combo boxes to see a simulation of their working appearance]:

If your browser does not have JavaScript enabled my mouseover effects for the image above won't work. You can see a composite image of the form by clicking the thumbnail below (click the Back button on your browser to return to the tutorial):

So, we have a form with three combo boxes:

• cboDay returns a number between 1 and 31 representing the day of the month.
• cboMonth returns a number between 1 and 12 representing the month but displays the month name (if you skipped Part 1 of the tutorial you might want to follow this link to see how that was done).
• cboYear returns a 4-digit number representing the year.

What the VBA Has to Do

Access doesn't know that these combo boxes represent the parts of a date, so there are several tasks to perform:

• When the user selects a value from any one of the combo boxes, a date must be created using that value and the values in the other combo boxes.
• The value that the user selects must be compared with the values in the other combo boxes to ensure that a valid date has been chosen (such as September 31, or February 29 if it is not a leap-year).
• If the user creates an invalid date it should be automatically corrected to the nearest valid date and the user notified of their error.
• If there is already a date in the destination text box, the combo boxes should show the same date.

Writing The Code

Note for Beginners

If you are new to writing code, follow these simple instructions for creating code procedures.

• Open the form in Design View and choose View > Code to open the VBA editor.
• Look at the top of the code window where you will see two combo boxes:
  
  
• On the left is the General List which displays a list of all the objects on the form, including the form itself.
• On the right is the Declarations List which displays a list of all the events that apply to whatever object is chosen from the General List. Nothing is shown on the Declarations list until you have made a choice from the General list.
• To start creating an Event Procedure (the code that runs when a particular event occurs) choose the object in question from the General List then choose the required event from the Declarations list.
• As soon as you make a choice from the General list Access tries to help and immediately writes the beginning and end lines of the default event into the code window. Sometimes this is the one you want, sometimes it isn't. Don't worry if that happens. Just choose the one you want then delete the unwanted lines.
• Enter the code by typing it directly into the code window, between the beginning and end lines of the procedure, as described below. You can copy the code directly from the browser window and paste it into your Access code window.

Setting the Default Date

You can decide whether or not you want to have the combo boxes displaying a date when the form opens or when the user moves from record to record. I prefer to do this because it acts as a prompt to the user.

I have chosen to have the combo boxes match the date in the date field text box or, if the text box is empty, to display the current date [click the thumbnail below to see a full-sized image]:

To achieve this I need to attach a procedure to the On Current event of the form itself. A form's Current event happens whenever a record is displayed, i.e. when the form opens, when the user moves from record to record, or to a new empty record. Here's the code:

Private Sub Form_Current()
   If IsNull(Me.txtHireDate) Then
      Me.cboDay.Value = Day(Date)
      Me.cboMonth.Value = Month(Date)
      Me.cboYear.Value = Year(Date)
   Else
      Me.cboDay = Day(Me.txtHireDate.Value)
      Me.cboMonth = Month(Me.txtHireDate.Value)
      Me.cboYear = Year(Me.txtHireDate.Value)
   End If
End Sub

How It Works:

An If Statement uses the IsNull function to determine whether the date field text box (txtHireDate) contains a date. Note that I have used the Me keyword to refer to the form itself.

Because each combo box is a separate object, the value of each has to be specified separately and I have used the Day(), Month() and Year() functions to calculate a value to be displayed based on the current date (as determined by the Date function) or the value in the txtHireDate text box.

Each of these functions returns a number which the appropriate combo box displays. Remember that the cboMonth combo box actually displays the month as text because the "bound" column (the one that contains the month numbers) is hidden.

Coding the "Day" Combo Box

The cboDay combo box offers a choice of day numbers from 1 to 31 but not all months have 31 days. When the user makes a choice from the combo box, or types a number that is on the list, my code must get the current values from the cboDay, cboMonth and cboYear combo boxes, turn them into a date, and place it into the txtHireDate text box.

The mechanics of this isn't a problem. I can use the values from the combo boxes and the VBA DateSerial function. This function returns a date for a specified year, month and day. You give it the numbers and it gives you the date serial (which is normally displayed as a date). But it isn't as simple as that...

I want this date chooser to be "intelligent" so it must not allow the user to select a date that doesn't exist (e.g. 31 February). The question only arises if the user chooses a day number greater than 28 because all months have at least 28 days. So what has it got to do?...

First it has to look at the cboMonth combo box to check the chosen month. If that month is OK then it can go ahead and create the date. If not, it has to notify the user that they can't have their chosen date. But I'm going to try to be helpful and change their selection to the highest correct date (i.e. if they choose 31 September it will be changed to 30 September).

The month of February is a bit more complicated because it can have 28 or 29 days depending on whether or not it is a "leap year" (which can be defined as one whose number is divisible by 4). So in the case of February the code has to check the cboYear combo box as well.

This all sounds very complicated but thanks to VBA Case Statements and If Statements it is quite easy to put into code. I'm using the AfterUpdate event of the combo box to run my procedure. Here's the code:

Private Sub cboDay_AfterUpdate()
  Select Case Me.cboDay.Value
    Case 29
      If Me.cboMonth.Value = 2 Then
        If Me.cboYear.Value / 4 <> Int(Me.cboYear.Value / 4) Then
          MsgBox "February has only 28 days in your chosen year. " _
          & "I have reset the day box to 28.", vbInformation, "Bad date!"
          Me.cboDay.Value = 28
        End If
      End If
    Case Is > 29
      Select Case Me.cboMonth.Value
        Case 2
          If Me.cboYear.Value / 4 = Int(Me.cboYear.Value / 4) Then
            MsgBox "February has only 29 days in your chosen year. " _
            & "I have reset the day box to 29.", vbInformation, "Bad date!"
            Me.cboDay.Value = 29
          Else
            MsgBox "February has only 28 days in your chosen year. " _
            & "I have reset the day box to 28.", vbInformation, "Bad date!"
            Me.cboDay.Value = 28
          End If
        Case 4, 6, 9, 11
          If Me.cboDay.Value = 31 Then
            MsgBox MonthName(Me.cboMonth.Value) & " has only 30 days. " _
            & "I have reset the day box to 30.", vbInformation, "Bad date!"
            Me.cboDay.Value = 30
          End If
      End Select
  End Select
  Me.txtHireDate.Value = DateSerial(Me.cboYear, Me.cboMonth, Me.cboDay)
End Sub

How it Works - The User Selected Day 29:

The procedure starts with a Case Statement looking at the value the user chose from the cboDay combo box. The first case is 29. The only month to have a problem with day 29 is February so next comes an If Statement looking at the value of the cboMonth combo box to see if it is 2 (i.e. February). If not then the date must be OK so the code skips to the last line and builds the date. But if it is then a second If Statement checks the value of the cboYear combo box to see if it is a leap year (I'll explain that in a moment!). If it is, then the date must be OK and the code skips to the last line and builds the date. But if not then a Message Box is displayed notifying the user of their mistake, the value of the cboDay combo box is corrected to create a date that is OK, and the code skips to the last line and builds the date.

How does it decide whether or not the chosen year is a leap year? The accepted method for determining if a year is a "leap year" (i.e. one in which February has 29 days instead of the usual 28) is that a leap year is exactly divisible by 4 (when you divide the year number by 4 the result is a whole number). Divide 2003 by 4 and you get 500.75 therefore 2003 is not a leap year. Divide 1996 by 4 and you get 499 therefore 1996 is a leap year. If you want to know more about calculating leap years follow this link.

The code checks for leap years by dividing the chosen year by 4 and comparing it with the integer of the same calculation. An integer is a whole number. The code uses the Int function which creates an integer of the "year/4" calculation by discarding any decimal places. If the two are the same then "year/4" must be a whole number and therefore is a leap year. If they are different then "year/4" must have decimals and therefore is not a leap year.

How It Works - The User Selected Days 30 or 31:

The next case of the Case Statement is >29, and deals with months that have more than 29 days. Here we have several months to deal with. February (2) has a problem with days 30 and 31, and April, June, September and November (4, 6, 9, 11) all have a problem with 31 days. So we have another (nested) Case Statement to distinguish between February and the rest.

As before, the procedure starts by looking at the month of February. It does not need to know whether the day is 30 or 31 because neither are acceptible. It just needs to know what to correct it to, 28 or 29.  Again it uses an If Statement to determine whether or not the chosen year is a leap year but this time it has to change the value whatever the result, hence the Else part of the If Statement. The value of cboDay is corrected accordingly and the user notified.

The second part of the nested Case Statement deals with the 30 day months. If the chosen day is 31 it resets cboDay to 30 and displays a message. If the chosen day is 30 then that's OK so the code skips to the last line and builds a date.

If the month is not 2, 4, 6, 9 or 11 there is nothing to fix so the code skips to the last line and builds a date.

Finally the nested Case Statement closes, followed by the outer Case Statement and a date is built from the now correct combination of values in the combo boxes, the date is placed into the txtHireDate text box, and the procedure closes.

Click the thumbnail image below to see a diagram illustrating the logic used when designing the date verification procedure:

Coding the "Month" Combo Box

You might think that only the cboDay combo box needs a procedure to verify the date, but that isn't the case. Supposing the date chooser already displays a perfectly legitimate date such as December 31. If the user changes the cboMonth combo box to, for example, November then an invalid date will result. So the whole date verification procedure has to take place whenever the value of cboMonth changes. Fortunately, the code used for the cboDay combo box can be used unchanged. Simply copy it (omitting the start and end lines of the procedure) and paste it into the cboMonth_AfterUpdate procedure.

Coding the "Year" Combo Box

The procedure for the cboYear combo box is the simplest of all, because a year can be either a leap year or not, and the only month on which this has an implication is February. Then you only need verify the date if the existing value in the cboDay combo box is 29. Here's the code:

Private Sub cboYear_AfterUpdate()
  If Me.cboMonth.Value = 2 Then
    If Me.cboDay.Value = 29 Then
      If Me.cboYear.Value / 4 <> Int(Me.cboYear.Value / 4) Then
        MsgBox "February has only 28 days in your chosen year. " _
        & "I have reset the day box to 28.", vbInformation, "Bad date!"
        Me.cboDay.Value = 28
      End If
    End If
  End If
  Me.txtHireDate.Value = DateSerial(Me.cboYear, Me.cboMonth, Me.cboDay)
End Sub

How It Works:

This procedure uses a number of nested If Statements. The first asks if the month in the cboMonth combo box is February. If not the code skips to the last line and builds a date, but if it is it asks if the day in the cboDay combo box is 29. If not the code skips to the last line and builds a date, but if it is it checks to see if the chosen year is a non-leap year (<> means "not equal to"). If it is a leap year then that's OK and the code skips to the last line and builds a date. If it is not a leap year then the value of cboDay is changed to 28 and the user is notified. After each of the If Statements close a date is built from the now correct combination of values in the combo boxes, the date is placed into the txtHireDate text box, and the procedure closes.

I could easily have combined all the conditions into a single If Statement like this:

If Me.cboMonth.Value = 2 And Me.cboDay.Value = 29 And _
     Me.cboYear.Value / 4 <> Int(Me.cboYear.Value / 4) Then ' etc.etc.

but I decided that nested If Statements were a bit easier (for me!) to read... an important consideration when going back to edit or re-use code long after originally writing it.

The "MonthName" Function

As I explained earlier, whilst the user sees the name of the month in the cboMonth combo box as text (e.g. September) the code is dealing with number (e.g. 9). When I need to make a change to the user's selection I want the message to be as friendly as possible, so I want to mention the month in question. If I were to use the cboMonth value like this:

MsgBox MonthName(Me.cboMonth.Value) & " has only 30 days. "

... the message would read something like: 9 has only 30 days ... and what the heck does that mean to anyone?! So I need a way to convert that number into the month's name as text.

In the code for the message box I have used a function called MonthName. This is not a built-in function but a custom made one (or "UDF" meaning User Defined Function). This function converts the number back to text for a friendlier, more readable message. Here's the code:

Function MonthName(MonthNumber As Integer) As String
   Dim varMonths As Variant
   varMonths = Array("January", "February", "March", "April", _
      "May", "June", "July", "August", "September", _
      "October", "November", "December")
   MonthName = varMonths(MonthNumber - 1)
End Function

This function can be placed in the same code module as the rest of the form's code but, if you want to make use of it elsewhere in your database you could create a new module and store it there (in which case the first line should start Public Function MonthName( ... etc.).

I can include this function in my message box code like this:

MsgBox MonthName(Me.cboMonth.Value) & " has only 30 days. "

... so that the message reads something like: September has only 30 days ... which makes a lot more sense!

How It Works

The function accepts a single argument which I have called MonthNumber which has to be an integer (a whole number) and it returns a string (a piece of text). It uses an Array which I called varMonths to hold the list of month names.

Put (very) simply, an array is just a variable that can hold more than one piece of data at a time. When data is held in an array like this you can refer to it by it's position in the array. VBA starts counting from zero so January is 0, February is 1, March is 2 and so on.

The last line of the function's code uses the supplied integer, which in this case is the value of the cboMonth combo box minus one (because of the counting from zero thing) to get the corresponding piece of text from the array.

More Help for the User

If you have chosen to prevent the user from typing directly into the date text box it's only fair to let them know!

In the first part of this tutorial I explained how to do this by locking the text box so the user can't type into it, and removing it from the tab order so that a user navigating through the form with their Tab key is taken straight to the date chooser.

When you create restrictions like this you should make an effort to let the user know what they can or can't do. In this case it's quite easy to alert them when they try to type in the text box. The only way the user can enter the text box is by clicking on it, so a suitable place to deal with this is its Click event. All it needs is a simple message box which will appear when the text box is clicked. Here's the code I used:

Private Sub txtHireDate_Click()
   MsgBox "Please select a date from the Date Chooser." _
      , vbInformation, "You can't type here."
End Sub

Follow this link to see the code for the completed procedure [new window].
 

Other Uses for the Date Chooser

In this tutorial I have used the date chooser to place a date into a text box that is bound to a table field, but its value isn't restricted to entering data. I have often used it where I didn't feel that an ActiveX calendar was appropriate, either because I couldn't guarantee the presence of the appropriate component on the user's machine, or simply because it didn't fit in with my form's design style.

It can be used whenever you need the user to give you a date. I have used it to ask for start dates for report creation, for query criteria and in many other circumstances. Here's a screenshot of a dialog box from one of my databases...

Download the File

You can download a fully working demo database illustrating the techniques covered in this tutorial. The files are provided in Access 97 and Access 2000 format, and also as Zip files for faster download (you will need a copy of WinZip or a similar program to extract the zipped files).

DateComboDemo97.mdb	DateComboDemo97.zip	Access 97 format.
DateComboDemo2000.mdb	DateComboDemo2000.zip	Access 2000 format.