Calendar Control:Stephen Lebans Example

Sunday, December 22, 2002 22:59:33

Requires intermediate-advanced knowledge base level. End user-types will likely want to pass this off to a database designer or administrator. Requires knowledge of modules and Visual Basic.

Other Calendar Links

One commonly requested function for Access is a popup Calendar Control. The user wants something that, like in Quicken or Microsoft Money, gives them a popup calendar which they can then click on the date and plug it in. It would look something like this:

There are many different ways of doing this, some of which have the user use the built-in "ActiveX" calendar control that Access comes with. However, this one which I found from Stephen Lebans' web site comes highly recommended because there are no ActiveX licensing issues. That is, Access can be installed with different options included or not included, and if certain options are omitted in the installation the built-in calendar control won't work. The Stephen Lebans solution works around this. Note: it is somewhat more complex to do it this way than using the built-in Active X components, but the universal compatibility makes it worth it.

Here are the steps to installing this feature:

Go to Stephen Lebans' Calendar Control Web Page, and download the database--either for 97 or 2000/XP (those are download links)

Import the modules from his database into yours. The names of the modules are:

clsMonthCal

modCalendar

modAddrOf

modColorPicker

modFontPicker

In each form which will utilize the function, the following code will need to be inserted:

1st, with the form open in DESIGN view, click "View....Code." Near the top of the code shown, enter the code as follows:

Option Compare Database
Private strCaption1 As String
Private strCaption2 As String
' Our MonthCalendar Class
Private mc As clsMonthCal

Next, insert the following code into the form's Load event procedure:

Private Sub Form_Load()
' Calendar control code courtesy of Stephan Lebans.
' http://www.lebans.com
Set mc = New clsMonthCal
DoCmd.MoveSize 1, 2
End Sub

(Note: I tend to comment-out--that is, not use--the DoCmd.MoveSize 1,2 bit of code, which prevents the form from being centered.)

Lastly (almost), enter the following code for the form's Unload event procedure:

Private Sub Form_Unload(Cancel As Integer)
' This is required in case user Closes Form with the
' Calendar still open. It also handles when the
' user closes the application with the Calendar
' still open.
If Not mc Is Nothing Then
    If mc.IsCalendar Then
        Cancel = 1
        Exit Sub
    End If
'DoEvents
Set mc = Nothing
End If
End Sub

Lastly, for every date field which will utilize the popup calendar, enter the following code for the date's DOUBLE-CLICK event procedure:

txtEndDate = ShowMonthCalendar(mc, Nz(Me.txtEndDate, 1), , , , True)

In the above example, the name of the field was txtEndDate; simply substitute the date's true name in place of txtEndDate.

(Note: throughout my web pages, it is my tendency to color-code Visual Basic insomuch that code that varies depending on your situation--field names come to mind--is color-coded in red to make it stand out.)

Important note: you cannot cancel events in any form using this calendar control with the End command; it will disenable the calendar control for the duration the form remains open. I believe that Cancel=True is still safe, but I'm not sure. The way I handle such events that need to terminate, I relabel the "End Sub" statement with "99 End Sub" and rather than using"End" to terminate a procedure I use a "Goto 99" statement (which tells Access to go to the "99 End Sub" statement).

Here are the other links I have on this topic:

Microsoft Knowledge Base Article: 190194
Templates Offered By Other Access Designers
My Step-By Step Instructions using the ActiveX Method (and other related links)