Custom Calendar Form in MS Access

In order to get the chosen date back to another form, normally for use in a text box, I chose to use a global variable. This is a variable that is available to every object in your Access database that can handle variables. I did this by defining it in a module.

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.

If you already have a common module for other purposes, just add the one required line to it. If you have no modules, create one, add the code, and save it, possibly with a name of "DateModule".

Option Compare Database
Option Explicit

Global gChosenDate As Date

Next, we have to get our calendar form to put a value in the variable. This is where the Choosedate function comes in. Remember it? We arranged to have the On Click event for all the little buttons on our calendar form set to =Choosedate().

The key to the ChooseDate function is its ability to know which button was clicked. For this, I used the following code segment.

Screen.ActiveForm.ActiveControl.Name

This will tell me the name of the active control on the form. Since we are clicking once on a button, then that button is the active control. When I inserted the following code in the module of the calendar form ...

Private Function ChooseDate()

MsgBox Screen.ActiveForm.ActiveControl.Name

End Function

... and clicked on one of the buttons, the message box showed me "cmd12". Now we just need to determine what date that is. We know how we calculated the captions when we created the form, so we just need to reverse that logic. Or do we?

If we can display the control's name, why not just display its caption property. So, I tested Screen.ActiveForm.ActiveControl.Caption and it worked! The following code determines the chosen date, places that date in the global variable "gChosenDate" and then closes the calendar form.

Private Function ChooseDate()

   Dim intDay as Integer

   '--- remember that the intMonth variable is storing the month number
   '--- whereas the txtMonth field has the text version of the month
   '--- I did not really need the intDay variable,
   '--- but it made the listing look neater on this page
   intDay = Screen.ActiveForm.ActiveControl.Caption
   gChosenDate = DateSerial(Me.txtYear, intMonth, intDay)

   DoCmd.Close '--- close the calendar form

End Function

Let's go back to the original form that needs to allow us to pick a date.

Open (in design mode) the form that needs a date picker on it.
Add a command button (do NOT use the button wizard).
In the button's properties, delete the caption.
In the button's properties, click once in the Picture field, and use the "..." at the end of that field to choose the value "Calendar".
The button now looks like a miniature calendar.
Create an On Click event for that button and put this code in it.

DoCmd.OpenForm "frmCalendar", , , , , acDialog
'--- you put code here that places the date value into a text box, etc.

The "acDialog" at the end forces the user to complete the date picking process before moving on. Once the date has been chosen, control comes back to the calling form and you can place the chosen date (held in variable gChosenDate) into a text box, or use it in a calculation, etc.

One little detail remains. What if the user clicks the date choosing button on the original form by mistake? How do they cancel the whole process?

Add a cancel button to the calendar form.
The VBA in the Cancel button's On Click would be two lines; "gChosenDate = Null" and "DoCmd.Close"
You will need VBA code in the calling form to deal with a null date (If IsNull(gChosenDate) Then ...)
In the Form Open event of the calendar form add the VBA code "gChosenDate = Null" to cover cases where the user manages to close the form without choosing (eg. they use the Ctrl-F4 combo)

In Part 4 of this series on Creating a Custom Calendar, I will look at converting the text box for the month to a combo box. It saves a lot of VBA code. Any other questions about this article?

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.