Browse All Articles > Printing a Calendar with Access
Introduction
Nobody prints calendars any more. This used to be a frequent request, but today, one consults calendars only on screen: linked to email software, on-line collaborative solutions, gadgets on portable devices. Why write an article about something obsolete?
One reason is that the few times it makes sense to print a calendar, nobody remembers how to do it. In those cases, the calendar often ends up being produced and formatted using a text processor or a spreadsheet. With a simple template available, this won't be necessary.
The second reason is that printing calendars provides a very good introduction to Visual Basic. Even complete beginners can learn the concepts and techniques needed to print a calendar within hours, and several major topics can be introduced naturally: event programming, controls, variables, handling dates, loops, etc. The problem at hand is simple to explain and the result can be validated by anyone; it doesn't look or feel like a programming assignment.
This isn't a class. However, if you have only a little experience using Visual Basic -- and a little more with reports, you might want to try following the instructions in the first section before looking at the solution. Even if the code looks simple, it isn't entirely trivial either; if you don't understand something, you can of course ask questions in the Access Reports area. On the other hand, if you are an experienced user, you can simply download the attached database, and return here only for clarifications about the techniques used, if needed.
The Yearly Calendar
If we wanted to print 365 (or 366) little boxes using a text processor, we would need to create them, and then manage them. In an Access report, we need only one box, which will be printed any number of times. The first report of the exercise is quite simple.
Previewing this report should show a tiny box in the upper left corner of the page. That's all. Before it is formatted, an event occurs, and we can use that event to write something into the box, and also to print it 31 times. There is an option called "build event" on the context menu of the detail section. The code we want to add is this:
The "solution" is the Report1 in the attached database. It looks like a generic calendar, but we haven't addressed two problems yet. Not all months have 31 days, and we will want all weeks to start on the same day. The leftmost column should always be Sundays or Mondays, depending on one's preferences. In order to address them, we need real dates, not just day numbers.
The main report will show twelve months, as twelve monthly calendars, much like the one we just created. This time, we will need three controls instead of one.
The detail section needs a control to display the month name: txtMonth, formatted as 'Long Date', so that the weekday is displayed. We will write an actual date into that control, not just the month number.
Finally, as place holder, we can add a rectangle measuring 2.1" by 1.8", the size needed to hold six rows of seven days, measuring 0.3" by 0.3".
The first preview shows only the rectangle. Similar code to that for the first report can be used to display twelve rectangles and organise the columns (three columns with some spacing in this case).
First we need to know the year. The simple solution is to use "=Year(Date())" as control source for the control txtYear, and adjust the detail format event handler to show real dates:
The header's format event is used to write the year into the control txtYear.
The "solution" is Report2 in the attached database, including code comments. It shows twelve boxes, with the full date of the first of each month above it.
The final operation is to replace the box by a subreport. Let's rename Report1 to rsubMonth and Report2 to rptYearCalendar, and place the first as subreport on the second. The subreport can now use the parent's report control called txtMonth to manage dates instead of day numbers. There are two adjustments to make.
When a detail section is printed, "next record" is set to False (line 14) unless the next day would fall into the next month (unless the month of the date plus one is different from the current month).
The closing steps are easy:
Final advise: don't forget to comment the code (the "solutions" are commented).
The yearly calendar consists of only four controls: txtYear, txtMonth, subMonth (the subform control), and txtDay. With less than half a dozen event handlers, these controls are repeated on the page to produce a yearly calendar. I find this simplicity very pleasing.
» highlighting of week-ends (for example in bold)
» underlining school days (a new project in itself)
» signaling events (birthdays, holidays, yet another project)
The first idea is very simple to implement. Since txtDay contains an actual date and not just a day number, the WeekDay() function can be used directly to find Saturday and Sundays. Note however that conditional formatting doesn't work: it can be used only with controls having a control source, not with controls that get their value through code.
The second is implemented in the attached database as a function SchoolDay(). For weekdays, it looks up the date in a table Holidays(From, To, Why) containing the school holidays of the State of Geneva between autumn 2009 and summer 2012.
The final idea isn't implemented, and is reserved for the next section. A yearly calendar has just enough space for two lines per day, so additional information should be very compact (symbols or perhaps initials).
The final yearly calendar looks like this (using American regional settings):
![2010]()
Technical Background
The calendar is built formally by a loop within a loop. Presented as a program, the pseudo-code could look like this:
iYear = AskUser()
For iMonth = 1 To 12
Print MonthName(iMonth)
dFirst = CreateDate(iYear, iMonth, 1)
For dDay = PreviousSunday(dFirst) To dFirst - 1
Print Null ' place holder
Next
dLast = CreateDate(iYear, iMonth + 1, 0)
For dDay = dFirst To dLast
Prind DayNumber(dDay)
Next
Next
This disregards the columns within columns, but highlights the intrinsic logic. It is unusual to see reporting as a loop structure, but it works quite well for that purpose. A For loop had three parts: an initialisation, an increment, and an exit test. In the example of the yearly calendar, they took the following forms:
Three properties combine to determine exactly what happens after a section is formatted. They are all set to True before the format event. The help pages (for version 2003) are quite disapointing in that they don't offer any example of a meaningul usage.
PrintSection determines whether printing will occur. Setting this property to False is different from cancelling the format event, in that the next property is not affected.
MoveLayout determines the next printing point. If set to False, the next section will be printed at the current location, not at the next logical position -- the next column in our case. (This property isn't used for calendar printing, the next column is used every time.)
NextRecord determines whether the recordset cursor will advance to the next record after printing the section. By forcing the cursor to remain on the same single virtual record, it is possible to print the same section as many times as needed.
The Monthly Calendar
Note: This section isn't written in steps, and probably doesn't contain enough details to build the reports from scratch. The completed reports in the demo database should be studied in parallel.
We already have monthly calendars, twelve of them, on the yearly calendar. However, each day is too small to display any meaningful information, like appointments. Furthermore, there is a serious technical limitation in using a record-less report as we did above.
Conditional formatting didn't work, because the controls had no control source. Likewise, we cannot add a sub-report if the "link master field" is a control set through code. This works for forms, but not for reports. If we want to display a sub-report, we need to construct a record source.
Jet SQL doesn't provide a syntax to create virtual tables, for example the numbers 1--100, so we need to start with an actual table. It wouldn't be efficient to create a table of all dates, but we need at least 31 records to generate the 31 days of a month. In practice, it is easier to build a full grid of six weeks, 42 days, and decide later what to do with days outside of the current month.
There are twelve ways 31 days can fall on a six-week grid (and fifteen for 28 days, but let's look for a generic solution)
![twelve calendar layouts]()
In the yearly calendar, the layouts in the first row of the figure were used. On a monthly calendar, it might make sense to show a few days of the previous month, with contrasted formatting. In this case, I will choose three as minimum, which means a full week of the previous month will be made visible when the first of the month falls too early in the week. The offsets between the first day in the grid and the first day of the month will be between 3 and 9. In rare cases, only two days of the next month will be visible at the bottom right of the grid.
Building the monthly calendar starts in a familiar way. The report header will display the month selected during the "report open" event -- not reproduced here -- and store the first day of the grid in an invisible control, txtStart, calculated as follows:
The detail section contains a text box and a rectangle, and doesn't need code, because there is a record source:
SELECT N FROM Numbers WHERE N Between 0 And 41;
Many of my databases have a table of Numbers. The WHERE condition is only needed if the table contains more numbers, for another usage. The date for each detail section is simply txtStart+N, for example in a control called txtDay.
Since this control has a record source (even if it is a calculated one), it can be used as "link master field" for any subreport included in the detail section. It also supports conditional formatting, in this case using an expression to determine if the day belongs to the selected month.
The table used in the subreport, Events(Start, Notes), contains some dummy data for January 2010, inspired by my daughters activities. The rectangle over the subreport is needed because, when there is no data, the border of the subreport will not be printed either.
![January 2010]()
Note: The space is limited (about five lines), and both the subreport object and the detail section should have "can grow: no". I left these settings at "yes", so that you can see the effect of too many events for the same day or long notes. Reporting with limited space, as is the case with calendars, often requires a good understanding of concatenation. In the subreport, the fields Start and Notes are combined into a single field in a very compact way. A companion article, Concatenation in Reports (and Forms) explores these techniques in detail.
The Demo Database
This database in Access 2000 format contains the three tables mentioned in the article and all the reports, including two intermediate "solutions" called simply 'Report1' and 'Report2'. The main reports are 'rptYearCalendar' and 'rptMonthCalendar'. The Visual Basic code is documented almost line-by-line. If you are an experienced Access user, you will not even need to read the article.
Calendar.mdb
Conclusion
There are only a few things one can do on a report but not on a form. Printing calendars is one of them. It would be great if a single subform object could be displayed forty-two times on screen, in columns, ready to accept user input. On the other hand, I wouldn't want to be the programmer having to implement that sort of feature!
To obtain the same thing on a form, well, you actually need to add the same subform forty-two times, each below a text box displaying the date and used as "link master field". It isn't hard, but it's just as much work as building a calendar in a text processor.
If you read this article to find a template, I hope you like those provided. If you wanted to learn some something new, I hope you did that as well. Finally, if you are teaching Access, please try the calendar exercise: it has a lot of potential and is quite rewarding.
Happy New Year!
Markus G Fischer
(°v°)
about unrestricted Access
__
Nobody prints calendars any more. This used to be a frequent request, but today, one consults calendars only on screen: linked to email software, on-line collaborative solutions, gadgets on portable devices. Why write an article about something obsolete?
One reason is that the few times it makes sense to print a calendar, nobody remembers how to do it. In those cases, the calendar often ends up being produced and formatted using a text processor or a spreadsheet. With a simple template available, this won't be necessary.
The second reason is that printing calendars provides a very good introduction to Visual Basic. Even complete beginners can learn the concepts and techniques needed to print a calendar within hours, and several major topics can be introduced naturally: event programming, controls, variables, handling dates, loops, etc. The problem at hand is simple to explain and the result can be validated by anyone; it doesn't look or feel like a programming assignment.
This isn't a class. However, if you have only a little experience using Visual Basic -- and a little more with reports, you might want to try following the instructions in the first section before looking at the solution. Even if the code looks simple, it isn't entirely trivial either; if you don't understand something, you can of course ask questions in the Access Reports area. On the other hand, if you are an experienced user, you can simply download the attached database, and return here only for clarifications about the techniques used, if needed.
The Yearly Calendar
If we wanted to print 365 (or 366) little boxes using a text processor, we would need to create them, and then manage them. In an Access report, we need only one box, which will be printed any number of times. The first report of the exercise is quite simple.
1
Create a new report, remove all headers. Create a text box, remove the label, call it txtDay. Give it a border. Make it exactly 0.3" by 0.3" in size, and the detail section likewise. Rename the section to secDetail (for good practice: we are going to program for these objects, applying a consistent naming convention).Previewing this report should show a tiny box in the upper left corner of the page. That's all. Before it is formatted, an event occurs, and we can use that event to write something into the box, and also to print it 31 times. There is an option called "build event" on the context menu of the detail section. The code we want to add is this:
2
Private Sub secDetail_Format(Cancel As Integer, FormatCount As Integer)
txtDay = FormatCount
If FormatCount < 31 Then Me.NextRecord = False
End Sub3
The "page setup" menu has a [Columns] tab. Choose seven columns, without any spacing, arranged "across, then down". While you are at it, try other options, this is a good occasion to understand how columns work in Access reports.The "solution" is the Report1 in the attached database. It looks like a generic calendar, but we haven't addressed two problems yet. Not all months have 31 days, and we will want all weeks to start on the same day. The leftmost column should always be Sundays or Mondays, depending on one's preferences. In order to address them, we need real dates, not just day numbers.
The main report will show twelve months, as twelve monthly calendars, much like the one we just created. This time, we will need three controls instead of one.
4
The new report should have a report header (a page header works as well), with a control to display the year: txtYear. The header section is renamed "secHeader".The detail section needs a control to display the month name: txtMonth, formatted as 'Long Date', so that the weekday is displayed. We will write an actual date into that control, not just the month number.
Finally, as place holder, we can add a rectangle measuring 2.1" by 1.8", the size needed to hold six rows of seven days, measuring 0.3" by 0.3".
The first preview shows only the rectangle. Similar code to that for the first report can be used to display twelve rectangles and organise the columns (three columns with some spacing in this case).
First we need to know the year. The simple solution is to use "=Year(Date())" as control source for the control txtYear, and adjust the detail format event handler to show real dates:
5
Private Sub secDetail_Format(Cancel As Integer, FormatCount As Integer)
txtMonth = DateSerial(txtYear, FormatCount, 1)
Me.NextRecord = (Me.FormatCount >= 12)
End Sub6
Dim mintYear As Integer
Private Sub Report_Open(Cancel As Integer)
Dim strAnswer As String
Do Until 100 <= mintYear And mintYear <= 9999
strAnswer = InputBox("Enter year:", "Calendar", Year(Date))
If strAnswer = vbNullString Then Cancel = True: Exit Sub
If IsNumeric(strAnswer) Then mintYear = strAnswer
Loop
End Sub
Private Sub secHeader_Format(Cancel As Integer, FormatCount As Integer)
txtYear = mintYear
End SubThe header's format event is used to write the year into the control txtYear.
The "solution" is Report2 in the attached database, including code comments. It shows twelve boxes, with the full date of the first of each month above it.
The final operation is to replace the box by a subreport. Let's rename Report1 to rsubMonth and Report2 to rptYearCalendar, and place the first as subreport on the second. The subreport can now use the parent's report control called txtMonth to manage dates instead of day numbers. There are two adjustments to make.
7
The format of txtDay is changed to "d". We will write a date into that text box, but we want to see only the day number. For testing, it's also good to try the format "ddd d", showing the weekday abbreviation and the day number.Private Sub secDetail_Format(Cancel As Integer, FormatCount As Integer)
If FormatCount = 1 Then
txtDay = Parent.txtMonth - Weekday(Parent.txtMonth, vbUseSystem) + 1
Else
txtDay = txtDay + 1
End If
If txtDay < Parent.txtMonth Then
Me.PrintSection = False
Me.NextRecord = False
End If
End Sub
Private Sub secDetail_Print(Cancel As Integer, PrintCount As Integer)
If Month(txtDay + 1) = Month(Parent.txtMonth) Then Me.NextRecord = False
End SubWhen a detail section is printed, "next record" is set to False (line 14) unless the next day would fall into the next month (unless the month of the date plus one is different from the current month).
The closing steps are easy:
8
Set the format of txtDay back to "d" once the report has been tested. Likewise, txtMonth should use the format "mmmm", the full day of the month only.Final advise: don't forget to comment the code (the "solutions" are commented).
The yearly calendar consists of only four controls: txtYear, txtMonth, subMonth (the subform control), and txtDay. With less than half a dozen event handlers, these controls are repeated on the page to produce a yearly calendar. I find this simplicity very pleasing.
9
Naturally, several enhancements can be made:» highlighting of week-ends (for example in bold)
» underlining school days (a new project in itself)
» signaling events (birthdays, holidays, yet another project)
The first idea is very simple to implement. Since txtDay contains an actual date and not just a day number, the WeekDay() function can be used directly to find Saturday and Sundays. Note however that conditional formatting doesn't work: it can be used only with controls having a control source, not with controls that get their value through code.
The second is implemented in the attached database as a function SchoolDay(). For weekdays, it looks up the date in a table Holidays(From, To, Why) containing the school holidays of the State of Geneva between autumn 2009 and summer 2012.
The final idea isn't implemented, and is reserved for the next section. A yearly calendar has just enough space for two lines per day, so additional information should be very compact (symbols or perhaps initials).
The final yearly calendar looks like this (using American regional settings):
Technical Background
The calendar is built formally by a loop within a loop. Presented as a program, the pseudo-code could look like this:
iYear = AskUser()
For iMonth = 1 To 12
Print MonthName(iMonth)
dFirst = CreateDate(iYear, iMonth, 1)
For dDay = PreviousSunday(dFirst) To dFirst - 1
Print Null ' place holder
Next
dLast = CreateDate(iYear, iMonth + 1, 0)
For dDay = dFirst To dLast
Prind DayNumber(dDay)
Next
Next
This disregards the columns within columns, but highlights the intrinsic logic. It is unusual to see reporting as a loop structure, but it works quite well for that purpose. A For loop had three parts: an initialisation, an increment, and an exit test. In the example of the yearly calendar, they took the following forms:
Initialisation is performed during the first formatting (when "format count" is one). In other cases, it would be performed in a header.
Increment occurs during every subsequent formatting of the section (when "format count" is higher than one). In other cases (not here), it is better to use the "print" event instead.
The Exit Test can often be performed in either the "format" or the "print" event. In reports, one must specifically instruct the loop not to terminate by setting "next record" to false. The loop continues until the default advancement to the next record is allowed, in which case printing stops (there is only one record).
Reports have been designed primarily to print records from tables and queries. The idea to print something "out of thin air", so to speak, doesn't occur naturally. But once it has, many things can be printed like that, by using a section as a loop structure: arrays, collections, lists, etc.
Three properties combine to determine exactly what happens after a section is formatted. They are all set to True before the format event. The help pages (for version 2003) are quite disapointing in that they don't offer any example of a meaningul usage.
PrintSection determines whether printing will occur. Setting this property to False is different from cancelling the format event, in that the next property is not affected.
MoveLayout determines the next printing point. If set to False, the next section will be printed at the current location, not at the next logical position -- the next column in our case. (This property isn't used for calendar printing, the next column is used every time.)
NextRecord determines whether the recordset cursor will advance to the next record after printing the section. By forcing the cursor to remain on the same single virtual record, it is possible to print the same section as many times as needed.
The Monthly Calendar
Note: This section isn't written in steps, and probably doesn't contain enough details to build the reports from scratch. The completed reports in the demo database should be studied in parallel.
We already have monthly calendars, twelve of them, on the yearly calendar. However, each day is too small to display any meaningful information, like appointments. Furthermore, there is a serious technical limitation in using a record-less report as we did above.
Conditional formatting didn't work, because the controls had no control source. Likewise, we cannot add a sub-report if the "link master field" is a control set through code. This works for forms, but not for reports. If we want to display a sub-report, we need to construct a record source.
Jet SQL doesn't provide a syntax to create virtual tables, for example the numbers 1--100, so we need to start with an actual table. It wouldn't be efficient to create a table of all dates, but we need at least 31 records to generate the 31 days of a month. In practice, it is easier to build a full grid of six weeks, 42 days, and decide later what to do with days outside of the current month.
There are twelve ways 31 days can fall on a six-week grid (and fifteen for 28 days, but let's look for a generic solution)
In the yearly calendar, the layouts in the first row of the figure were used. On a monthly calendar, it might make sense to show a few days of the previous month, with contrasted formatting. In this case, I will choose three as minimum, which means a full week of the previous month will be made visible when the first of the month falls too early in the week. The offsets between the first day in the grid and the first day of the month will be between 3 and 9. In rare cases, only two days of the next month will be visible at the bottom right of the grid.
Building the monthly calendar starts in a familiar way. The report header will display the month selected during the "report open" event -- not reproduced here -- and store the first day of the grid in an invisible control, txtStart, calculated as follows:
Const OFFSET = 3
Const FIRSTDAY = vbUseSystemDayOfWeek
Dim mdatMonth As Date
Private Sub secHeader_Format(Cancel As Integer, FormatCount As Integer)
txtMonth = mdatMonth
txtStart = mdatMonth - Weekday(mdatMonth, FIRSTDAY) + 1
If Weekday(txtMonth, FIRSTDAY) < OFFSET + 1 Then txtStart = txtStart - 7
End SubThe detail section contains a text box and a rectangle, and doesn't need code, because there is a record source:
SELECT N FROM Numbers WHERE N Between 0 And 41;
Many of my databases have a table of Numbers. The WHERE condition is only needed if the table contains more numbers, for another usage. The date for each detail section is simply txtStart+N, for example in a control called txtDay.
Since this control has a record source (even if it is a calculated one), it can be used as "link master field" for any subreport included in the detail section. It also supports conditional formatting, in this case using an expression to determine if the day belongs to the selected month.
The table used in the subreport, Events(Start, Notes), contains some dummy data for January 2010, inspired by my daughters activities. The rectangle over the subreport is needed because, when there is no data, the border of the subreport will not be printed either.
Note: The space is limited (about five lines), and both the subreport object and the detail section should have "can grow: no". I left these settings at "yes", so that you can see the effect of too many events for the same day or long notes. Reporting with limited space, as is the case with calendars, often requires a good understanding of concatenation. In the subreport, the fields Start and Notes are combined into a single field in a very compact way. A companion article, Concatenation in Reports (and Forms) explores these techniques in detail.
The Demo Database
This database in Access 2000 format contains the three tables mentioned in the article and all the reports, including two intermediate "solutions" called simply 'Report1' and 'Report2'. The main reports are 'rptYearCalendar' and 'rptMonthCalendar'. The Visual Basic code is documented almost line-by-line. If you are an experienced Access user, you will not even need to read the article.
Calendar.mdb
Conclusion
There are only a few things one can do on a report but not on a form. Printing calendars is one of them. It would be great if a single subform object could be displayed forty-two times on screen, in columns, ready to accept user input. On the other hand, I wouldn't want to be the programmer having to implement that sort of feature!
To obtain the same thing on a form, well, you actually need to add the same subform forty-two times, each below a text box displaying the date and used as "link master field". It isn't hard, but it's just as much work as building a calendar in a text processor.
If you read this article to find a template, I hope you like those provided. If you wanted to learn some something new, I hope you did that as well. Finally, if you are teaching Access, please try the calendar exercise: it has a lot of potential and is quite rewarding.
Happy New Year!
Markus G Fischer
(°v°)
about unrestricted Access
__
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Commented: