Custom Calendar Form in MS Access

As suggested at the end of Part 1, I created a default sized Label.
I made the label the same width as the buttons.
I made its Back Style "Normal".
I changed its Caption to "Sun".
I made 6 copies of this label and changed their captions to the other days of the week.
I made the Fore Color of the "Sun" and "Sat" ones to 255 (red).
I lined the labels up just above all the buttons, in the obvious pattern.
I changed the properties of the form as follows; Scroll Bars to Neither, Record Selectors to No, Navigation Buttons to No, Dividing Lines to No, and Auto Center to Yes.

Now for the method of changing the year and month. (NOTE: I am going to do this using 2 text boxes, as that is the obvious simplest structure in Access, and then in part 3 or 4 of this series I will show you how it is actually simpler to change the month using a combo box.)

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

I created 2 text boxes (and deleted their labels), put them above the calendar, calling one "txtYear" and the other "txtMonth".
To the left of each of them, I put a small command button with a "-" symbol on them. One is called "cmdYearMinus" and the other is "cmdMonthMinus".
Repeat on the right with the "Plus" versions.
Change the VBA code in the Form Open event by adding these two lines.

Me.txtYear = Year(Date)
Me.txtMonth = Format(Date, "mmmm")

Those two lines result in the year and month of the current date showing up in the new text boxes. Example for today (I am writing this on June 6, 2004) would be "2004" and "June".
Note that the "mmmm" in the Format command causes the full version of the month's name to be used. "mmm" would cause the 3 letter short forms.

Next, we need the code for the little "+" and "-" buttons.

To make this work nicely, we need to introduce a new twist here. I want to track the month "number" (ie. 1 throuigh 12) so that I can use simple math in the "+" and "-" buttons. I'll start with the VBA code behind the buttons, and fill in some other details after.

Private Sub cmdMonthMinus_Click()

   '--- subtract 1 from current month #
   intMonth = intMonth - 1
   '--- if January was the old month, answer will be "0",
   '--- change to December of previous year
   If intMonth < 1 Then
       Me.txtYear = Me.txtYear - 1
       intMonth = 12
   End If

   '--- strMonth is an array of 12 month names (see below)
   Me.txtMonth = strMonth(intMonth)

   '--- this call recreates the calendar based on the new month
   Call basCreateCalendar

End Sub

I'll leave the "+" version to you; watch for what happens after December. Next is the year "+". No comments as I think you will see the reasoning right away.

Private Sub cmdYearPlus_Click()

Me.txtYear = Me.txtYear + 1

Call basCreateCalendar

End Sub

Okay. Where does that strMonth come from, not to mention the intMonth from the MonthMinus code?

I added this code to the Form Open event.

strMonth(1) = "January"
strMonth(2) = "February"
strMonth(3) = "March"
strMonth(4) = "April"
strMonth(5) = "May"
strMonth(6) = "June"
strMonth(7) = "July"
strMonth(8) = "August"
strMonth(9) = "September"
strMonth(10) = "October"
strMonth(11) = "November"
strMonth(12) = "December"

strMonth and intMonth are global variables (at least global to all the code in this form) so we need this at the top of the VBA code, before any of the functions or subroutines. This makes those variables available in any of the form's subroutines or functions. (please take note of the "Option Explicit" code, which should be in ALL your VBA modules)

Option Compare Database
Option Explicit

Dim strMonth(12) As String
Dim intMonth As Integer

Now, the basCreateCalendar code. You should recognize most of it, as it was in our original version of the Form Open event VBA code.

Private Sub basCreateCalendar()

   Dim intFirstDay As Integer
   Dim intLastDay As Integer
   Dim i As Integer

   '--- this will make all the date buttons invisible
   For i = 1 To 42
      Me("cmd" & Format(i, "00")).Visible = False
   Next i

   '--- determine day of week of first day of the selected month
   intFirstDay = Weekday(DateSerial(Me.txtYear, intMonth, 1))
   '--- which command button will be the last day of the month
   intLastDay = Day(DateSerial(Me.txtYear, intMonth + 1, 0)) + intFirstDay - 1

   '--- this will make the appropriate date buttons visible
   For i = intFirstDay To intLastDay
      Me("cmd" & Format(i, "00")).Visible = True
      Me("cmd" & Format(i, "00")).Tag = i - intFirstDay + 1
      Me("cmd" & Format(i, "00")).Caption = i - intFirstDay + 1
   Next i

End Sub

Finally, the complete new version of the Form Open event.

Private Sub Form_Open(Cancel As Integer)

    strMonth(1) = "January"
    strMonth(2) = "February"
    strMonth(3) = "March"
    strMonth(4) = "April"
    strMonth(5) = "May"
    strMonth(6) = "June"
    strMonth(7) = "July"
    strMonth(8) = "August"
    strMonth(9) = "September"
    strMonth(10) = "October"
    strMonth(11) = "November"
    strMonth(12) = "December"

    Me.txtYear = Year(Date)
    intMonth = Month(Date)
    Me.txtMonth = strMonth(intMonth)

    Call basCreateCalendar

End Sub

Phew! More stuff than I had planned on for this article. Here is an up-to-date screen shot of our Date Picker calendar.

Part 3 of this series on Creating a Custom Calendar will examine a method of getting the chosen date back to the calling form.

Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.