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 (although you wouldn't see all the lists open at the same time!).
So, we have a form with three combo boxes:
Access doesn't know that these combo boxes represent the parts of a date, so there are several tasks to perform:
If you are new to writing code, follow these simple instructions for creating code procedures.
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:
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
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.
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
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.
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.
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.
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.
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
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.
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!
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.
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
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...
©2003 Martin Green - www.fontstuff.com - martin@fontstuff.com - All rights reserved