Custom Calendar Form in MS Access - Part 1
There are many questions on the newsgroups about using a calendar for date picking. There is an ActiveX Calendar Control available, but some people have trouble with ActiveX controls. For those who prefer, here is a way of creating a date picker that uses just plain Access.
Part 1 of the calendar article will get us this far: (image should appear below this paragraph)
(article continues after sponsor spot)
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 an unbound form.
- On this form I created a command button with a width of 0.3 inches and a height of 0.3 inches.
- I changed the caption font size to 10 and made it bold.
- I changed the caption to 33. (no particular reason! but the default caption was too big)
- I made 6 copies of this command button and lined all 7 of them beside each other in a single row.
- I then made 5 copies of this row (select all 7 command buttons and do copy/paste) and lined them up one row under the previous.
- I now have a grid of command buttons, 7 wide and 6 tall, which will cover all possible layouts of days in a month.
- I selected all 42 buttons and in the Property Box set their Visible property to No.
- Now the hardest part. Each of them has to be given a unique name. I named them in this style, left to right, then the next row: cmd01, cmd02, cmd03, ..., cmd42 (you will see why later)
- One solution I saw that was similar to this then proceeded to create an OnClick event for all 42 command buttons. Too much work for me. I selected all 42 buttons, brought up the Property Box, and in the OnClick event I typed =ChooseDate(). ChooseDate will be a function that I will create later. (make sure you include the equal sign)
- Now we will work on the logic that creates the correct numbers for the calendar. I will start with a simple version that only works for the current month.
- I created an OnOpen event at the form level and put the following code in it.
Private Sub Form_Open(Cancel As Integer)
Dim intFD As Integer
Dim intLD As Integer
Dim i As Integer
'--- determine day of week of first day of the month
intFD = Weekday(DateSerial(Year(Date), Month(Date), 1))
'--- which command button will be the last day of the month
intLD = Day(DateSerial(Year(Date), Month(Date) + 1, 0)) + intFD - 1
For i = intFD To intLD
Me("cmd" & Format(i, "00")).Visible = True
Me("cmd" & Format(i, "00")).Caption = i - intFD + 1
- That code will make the appropriate buttons visible, and place the number of the appropriate day of the month on each visible button. Let's discuss in detail.
- intFD is a declared integer that will hold the number of the button that will represent the first day of the month.
- intLD is a declared integer that will hold the number of the button that will represent the last day of the month.
- i is a declared integer used in a For loop.
- DateSerial(Year(Date), Month(Date), 1) determines the date of the first day of the current month. As I type this, it is Apr 30, 2004. Today's date would result in that expression passing back Apr 1, 2004.
- Weekday(DateSerial(Year(Date), Month(Date), 1)) determines which day of the week the first day of the month is on. This also becomes the number of the button that will represent the first day of the month, since we used a convenient numbering scheme that matches Microsoft's numbering of the days of a week (Sunday is weekday # 1). For Apr 1, 2004, that would give a 5, which represents Thursday.
- DateSerial(Year(Date), Month(Date) + 1, 0) determines the date of the last day of the current month. Apr 30, 2004 as I type this document.
- Day(DateSerial(Year(Date), Month(Date) + 1, 0)) gives the day portion. Example. Day(#Apr 30, 2004#) would result in the number 30.
- Now the neat part based on our naming of the command buttons.
- The For loop starts with the first day of the month, by referring to the appropriate command button, which we know is button # intFD (and intFD holds the number 5).
- The code Me("cmd" & Format(i, "00")) is a neat way of referring to an object that we do not know the exact name of until the program is running. Take the button number we want (i, which would be 5 for the first day of April 2004) and format it as a 2 digit, zero prefixed, number. In the case of Apr 1, 2004 it would be button number 05.
- Next, the VBA code appends that "05" to the end of a string "cmd" and you get "cmd05" and the Me() construct allows Access to refer to a command button with that name.
- The code then makes button "cmd05" visible, and changes its caption to 5-5+1 which is "1". Makes sense. After all, it is the first day of the month.
- Continue through the loop until the end of the month, which in April's case would put the number 30 (intLD which is 34-5+1=30) on the button named "cmd34".
- We now have a cute little calendar for April 2004. Pretty the form up by putting titles on the columns (Sun, Mon, Tue, ...).
Part 2 of this series on Creating a Custom Calendar Form will look at how to allow the user to change the month being displayed.
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.