Access 2007-2013
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
 
 

Microsoft Access Calendar Seminar
Use Access to Create Monthly Calendars

 
If you've ever wanted to print monthly calendars from your Access databases, then this seminar is perfect for you.

Access Calendar 

This seminar covers creating an appointment database in Microsoft Access. You will create a form that looks like an actual monthly calendar, that you can review your appointments on. Then, when you're ready to print, just pick a month, and the report is generated. Click here for a video showing what's covered in this seminar:


 

 
Seminars - Access Calendars
Description: Create monthly calendar forms and reports completely within Microsoft Access
Versions: I will use Access 2007, however the lessons are valid for all versions of Access back to AC2000. I will show any differences between 2007 and 2003. The seminar works fine with Access 2010 and 2013, however the Calendar Control object was removed from this version by Microsoft. This is not a problem, since there is now a built-in Date Picker. See notes in the comments section below.
Pre-Requisites: Access 101-104 basic knowledge of Access strongly recommended
Access 201-207 relational database concepts recommended
Access 301-313 helpful but not necessary
Running Time: 1 hour, 56 minutes  (1:56)
Cost: $49.99

 

This seminar has two main goals. You will learn how to:

1. Build a calendar form to edit appointments
2. Create a printable monthly calendar report
 

You will begin by creating a basic table to store our appointment data. We'll also make a simple form to edit and add new records.

 

Next we'll build an appointment list form, where we can see all of our appointments. Double-click on an appointment to open and edit it. We'll also make a checkbox to allow us to see open vs. closed appointments (close it when you're done with it). You'll also learn about triple-state checkboxes where you can see open, closed, and ALL records.

 

In the next lessons we'll build a form to pick a date using a built-in Microsoft Access ActiveX Calendar control. Using this date, we'll generate our own form that looks like a full-sized monthly calendar, complete with all of our appointments on it.

 

One of the things we're going to learn how to do is make the first day on our calendar form figure out what the first Sunday on or before the start of the month is, then build the rest of the calendar accordingly, greying out any days that are before or after the selected month. We'll tackle that with a little bit of VBA programming.

 

Of course, you'll be able to double-click on any appointment on the calendar to open up a popup form to edit that appointment.

 

Then, once the form is completed, we'll create the printable monthly calendar report. We'll actually make two versions - I'll show you how to lay it out portrait and landscape.


 

 

This seminar is perfect for anyone who wants to do any kind of scheduling in Microsoft Access. You can store your appointments, edit them easily, and print out professional-looking monthly calendar reports - all from within Access.

This seminar is long (almost two hours) but it's broken up into easily managed lessons of about 10 minutes each. You can sit down, watch a lesson, review the material, test the code out yourself, and experiment. Do a little bit each day. It's long, but it's comprehensive - you won't miss a single step as I've recorded everything from start to finish.

All of the sample database files are available on my Web site (instructions on where to download them are in the course videos). They are available in Access 2007 and 2000 formats.

This seminar is available to view online in the Amicron Theater. You can click here to watch the first two lessons absolutely free.

NEW: If you would like to download a copy of the database we build in this class so you can look it over and see if it will meet your needs, then click on one of these links:

- Access 2007
- Access 2010/2013

The sample databases are only available for Access 2007 and 2010/13, however we do cover Access 2000 and 2003 in the class.

Of course, if you have any questions about whether or not this seminar is for you, please contact me.
 

 

Access Calendar Seminar Outline

00. Intro (7:35)
Topics Covered
Pre-Requisites

01. Create Database (7:02)
Turn on Overlapping Windows
Create Database File
Create Calendar Table
Create Calendar Form

02. Appointment List 1 (13:56)
Form to List Appointments
ApptListF
Double-Click to Open Appt
OnDblClick Event
Show Closed Items Checkbox

03. Appointment List 2 (7:54)
Triple State Checkbox
Show Closed, Open, All Appts
Create Dynamic SQL Rowsource
Refresh Button
List.Requery
Change Form Caption in VBA

04. Monthly View Form 1 (11:39)
CalendarQ With Short Time
DatePickerF Enter a Date
Start Monthly Form

05. Monthly View Form 2 (10:23)
Day 2 of our Calendar Form
Text Boxes to Show Dates
Make 7 Boxes for the Week

06. Monthly View Form 3 (11:30)
Calendar Control
Calculate First Day of Month
First Sunday on or Before 1st

07. Monthly View Form 4 (10:51)
Grey Out Days of Diff Months
Forms!Form("FieldName") Notation
&HFFFFFF Color Notation

08. Monthly View Form 5 (10:26)
Shrink Text
Get Rid of Horizontal Scrollbars
DblClick Event to Open Appointments
Use Excel to Generate VBA Code

09. Calendar Report 1 (9:40)
Design Printable Monthly Report

10. Calendar Report 2 (12:14)
VBA Code Rewrite for Reports
Using the Detail Build Event

11. Review (3:39)

 


 

 
 
 

Student Interaction: Access Calendar Seminar

Richard on 1/1/2009:  Manage your appointments, and create printable monthly reports from your Access databases.
Richard Rost on 1/2/2009: IMPORTANT ADDENDUM:

If you're using Access 2010 you will NOT have the Calendar Control object. That's OK. There is an addendum video in the TIPS section that will show you how to use a simple text box and the Date Picker to get around this.

There is also a SECOND ADDENDUM video and database file available to teach you how to add more control buttons and the name of the month to the top of the calendar form. Don't miss this one!

There is also now a THIRD ADDENDUM to show you how to create events that span multiple days. Watch this one after you've finished the whole seminar.

I've also put together a separate ACCESS CALENDAR TEMPLATE database that you can purchase separately that mimics the old Access Calendar Control.

 Andy on 9/18/2009: At approx 10:48 in Access Calendar session 4, where your adding he DateTime< StartDate+1, when i save i rec'v the error "The expression is typed incorrectly, or it is too complex to be evaluated..." my SQL looks like this:
SELECT CalendarID, ApptTime, Description FROM CalendarQ WHERE DateTime>=Forms!DatePickerF!StartDate AND DateTime
the error occurs whenever the +1 is in my sql line, in both Access 2007 & Access 2003, havnt tried this on Access 2000yet. what am i doing wrong ?

 andy on 9/18/2009: after my earlier comment, i did get this to work 2 different ways, one was to add a 2nd "EndDate" to DatePickerF that had an event on update from StartDate that used SQL to adddate("d",1,StartDate) into Enddate. then changed the SQL in MonthlyCalanderF to include AND
when that worked, i decided it was easier to just go back to the way everything was set by you, and just change the original SQL to read:
SELECT CalendarID, ApptTime, Description FROM CalendarQ WHERE DateTime>=Forms!DatePickerF!StartDate AND DateTime<=dateadd("d",1,Forms!DatePickerF!StartDate) ORDER BY ApptTime;

i don't know why i can't just +1 to a date. but this was on two different computers, two different versions of access.

Richard Rost on 9/18/2009: Andy, you didn't type the whole thing in. It should be:

SELECT CalendarID, ApptTime, Description FROM CalendarQ WHERE DateTime>=Forms!DatePickerF!StartDate AND DateTime
Richard Rost on 9/18/2009: That's very strange. I also used two computers as well, and two different versions of Access (2007 and 2003). I had no problems with this at all. If anyone else has the same issue, please let me know. Earlier it looked like you just didn't type in the full SQL statement.
David Leech on 9/19/2009: Hi
First of all excelent tutorial, really easy to understand and fun to watch.

With regard to changing the caption of the form (about 7.20), I was wondering how one might change a larger heading in the form of a label. I ask because I know alot of users dont particularly notice the captions.
I usually like to put a good sized heading on all forms just so its easy to see what they're all about. Just curious.
Thanks.
once again I must compliment you on your videos theyre the best i've come across. very good work.
Look forward to more Thanks.
David

Richard Rost on 9/19/2009: David, thanks for the compliment. If you want to make the message more noticeable, just drop a big old label on the top of the form (or anywhere) and say:

MyLabel.caption = "HEY! CHECK THIS OUT!"
MyLabel.BackColor = vbRed

Or whatever. You can even hide and unhide it with:

MyLabel.visible = TRUE 'or FALSE

If you really want to get crazy, you can make it FLASH with a little Timer event. Just set your TIMER INTERVAL property for the form to 1000 (for one second) and then in the TIMER EVENT for the form, say:

If MyLabel.visible = TRUE then
MyLabel.Visible = FALSE
else
MyLabel.Visible = TRUE
end if

Now sit back and watch your label start flashing. I love this trick.

Hope that answers your question. :)

David Leech on 9/19/2009: Thanks for the quick reply in regard to changing labels on the triple click checkbox.
What I've done (Which I think is pretty cool)
is to have 3 labels and enter the code:

If ShowClosedItems = True Then
ApptListBox.RowSource = "SELECT CalendarID, DateTime, Description FROM CalendarT WHERE Closed=True ORDER BY DateTime;"
ClosedItems.Visible = True
openitems.Visible = False
AllItems.Visible = False
ElseIf ShowClosedItems = False Then
ApptListBox.RowSource = "SELECT CalendarID, DateTime, Description FROM CalendarT WHERE Closed=False ORDER BY DateTime;"
ClosedItems.Visible = False
openitems.Visible = True
AllItems.Visible = False
Else
ApptListBox.RowSource = "SELECT CalendarID, DateTime, Description FROM CalendarT ORDER BY DateTime;"
ClosedItems.Visible = False
openitems.Visible = False
AllItems.Visible = True
End If

I think its a little easier for users to see whats going on. I dont know if anyone else might find that helpful.

Thanks for the idea :)

David Leech on 9/20/2009: If you were to theoretically wanting to set the "date" field of the current date to another color. say if the date is the 2nd of sept then when you open the calendar the current date is shown in say red where as all others stay the same. I ask because people often want to be able to see the current date easily

Thanks

David Leech on 9/20/2009: Hi,
I've completed all of the forms etc both in a blank database aswell as integrated in a project i'm working on.
As I understand it, the canceled appointments remain in the calendar. How would one go about making the text for these apointments red (therefore showing that the appointment is a canceled one). Also within an application I am building there are two appointment types (maintenance and open house) how would I go about making text either say green or blue to show the two different types of appointment.
If any of these things are covered in other videos of yours let me know.

Thanks for the video though its added alot of functionality to my application(s)

Richard Rost on 9/20/2009: David, excellent code.
Richard Rost on 9/20/2009: David, very good question. I didn't think to do this in the video. It would just be a simple matter of checking to see if the current date textbox is equal to today's date. In your FOR loop that goes through the days on the calendar to colorize it, add this:

If Forms!MonthlyCalendarF(S1)=Date() Then
Forms!MonthlyCalendarF(S2).BackColor = vbRed
End if

Put this right before or after the other IF/THEN clause that colorizes the days from other months.

Richard Rost on 9/21/2009: David, you cannot change the text of a single record in a LIST BOX the whole box has to be the same color. The only way to do this would be with using a SUBREPORT instead of a list box. It requires a lot more work, but it can be done. When I get some time, I'll try to add this as another lesson to the seminar. Is anyone else interested in seeing this?
David Leech on 9/23/2009: Lol consider that a brain fart on my part, I was aware that you can not change the color of just one item in a list box. It completely escaped my mind that the list box is what we were using here. I'm actually going through alot of your older lessons to polish my skills a little bit I'm sure i'll get some ideas from there to add a few tricks to my project. I must say this is the easiest and most fun callendar tutorial i've found. I've added this to a current project and it works a treat. Cheers :)
Richard Rost on 9/23/2009: Thanks, David. There are third-party listbox controls you can purchase that DO offer that functionality, but I try to stay away from those in my classes. Like I said, you COULD get colors with a subreport, it would just involve a lot more work.
Cathy on 10/21/2009: The date picker doesn't seem to be commuticating with the active x calendar on the DatePickerF form. The 'set first day of month did not show 10/1/2009 in datepicker and cannot find the 1st sunday. What am I doing wrong? This is the 3rd Calendar database that I have started from scratch.
Richard Rost on 10/21/2009: Cathy, what version of Access are you using?
Cathy on 10/21/2009: Access 2007. I continued with some more of the vba code and I got DatePicker to find the 1st Sunday before 10/1 - but if I exit out of MonthlyCalendarF and DatePicker, select DatePicker again and enter 8/30/2009 MonthlyCalendarF stays the same with 9/27/2009.
Kim Boren on 10/22/2009: Your mention of making us pay our dues is so true. I messed up doing the rowsource code. I had to go back through the whole calendar to verify that I had done the rest of it correct. I did learn more than if I had copied it from your website.
Richard Rost on 10/22/2009: RE: Paying Your Dues... it's so true. When I was first learning how to program in C (a long, long time ago) I used to just copy and paste source code from the sample CDs they include with books. Didn't learn a thing. Then, I started typing in the code from the book itself... that extra little step is so crucial. Don't JUST copy the code though, EXAMINE it as you're typing it. Try to figure out WHAT is going on, and WHY.
 Alan Hill on 10/28/2009: I have just got back from a three weeks holiday in Thailand. I needed to unwind a bit so I have just run through your Calendar Seminar. I loved it. The use of Excel to generate code was fantastic. Lots of tricks and tips and really helpful code snippits.
You really know your stuff. Thankyou.
Regards Alan Hill

rae davis-craig on 1/16/2010: Hi Richard,
Great seminar! I am trying to build a vacation calander to manage employee vacations. How do you incorporate an enddate on the input form where the calander will look different for a range of dates where there are vacations? For example, if an employee is on vacation for a week, I want to put in the start date and end date so that I see the same employee on the calander for the five days and those days are red or whatever. Can you help me with that or tell me if that is covered in any of your training?

Richard Rost on 1/16/2010: Rae, I'm not sure I understand your question. Could you give me some more details, please?
rae davis-craig on 1/16/2010: Thanks Richard for attempting to help me with this. I am trying to builed a calander for managers to manage employee vacation time. So instead of using DateTime category, I would like to use StartDate and EndDate so that the manager can use the CalendarF form to input the StartDate and EndDate for each employee's planned vacation. After doing so I would like the calendar to use the start date and end date information and put the employee's name in for those dates on the calendar. For example if employee X has vacation from 1/18/10 through 1/22/10, I want the calendar to display the employee's name on the calendar on 1/18, 1/19, 1/20, 1/21, & 1/22. In addition, I want to make it look different than any other dates, so that when the calendar is printed the mangager can quickly see the weeks employees are on vacation versus weeks when there are no vacations. I hope you can help me with this. To sum it up I need to know how to incorporate an end date into what you have taught us, how to make the calendar represent a range of dates (example from x to x) and how to make those dates appear different from dates where there are no vacations.
Richard Rost on 1/23/2010: Rae, these are very good ideas. It's a lot more than I can answer here quickly, so I will try to include these in a follow-up video lesson soon. If you don't see anything from me in a couple of weeks, remind me again! :)
Richard Rost on 2/24/2010: UPDATE: If you want to SET the date of the Calendar Control Object, here's some code you can use. This will set the Calendar to the current date:

Calendar.Year = Year(Date)
Calendar.Month = Month(Date)
Calendar.Day = Day(Date)

If you want it to show the current date when you first open the form, put that code in the FORM_LOAD event. It will NOT work in the FORM_OPEN event.

 Dana on 5/21/2010: 1) Have I missed something - No matter what date I click on the calendar control it opens to the same place in the month.

2) because dblclick event on the list box only opens existing data, I have used the date text box double click event to open the CalendarF to add mode. I have set the CalendarF to requery on close, but I have to close the MonthlyCalendarF to have the new data show up. Can I requery the MonthlyCalendarF from the CalendarF? Also I used a macro to open the form and I am concerned that creating 42 macros in the Date Text Boxes will make my database sluggish.


Reply from Richard Rost:

Dana, (1) if you open the calendar control and its based on a date that has no value, it will always appear to open to the same date. To change that, assign it a date value. I believe I covered this in one of the lessons.

(2) You can requery the data in another form like this:

Forms!MonthlyCalendarF.Requery

(3) I wouldn't worry about 42 macros making your database sluggish. That shouldn't be a problem, but you might want to ask yourself WHY you need 42 macros? Can you make one bit of VBA code and just send it the control name? When in doubt, VBA is always better than a macro, IMHO.

 Alan Hill on 5/31/2010: Hi Richard

I have a problem with extending the Calendar Seminar

1) I typed in the code and got it running a treat works well (once I fixed a typo)

2) The next project was to colour each listbox according to criteria - Important.Red/1, School Holidays.Yellow/2 PublicHolidays.Green/3 ect.
I added a column [Holiday] to the table CalendarT and added a combobox to the form. Then added the column to the Listboxes rowsource.

3) Next I tried to get the value out of the Listbox on MonthlyCalendarF after it was populated by the DatPickerF form
But I need to click the box to get the value. So that's no good.

4) I added a ShortDate to the CalendarT table then I tried DLookup with the StartDate+x but I have not got the DLookup Where clause right.

At the moment I am thinking of using a recordset so I can get more control and set the background colour accordingly.
Sometimes after a break a simple solution comes to mind but It has not happened yet.

If you can think of an easier way I would love to know. But a simple solution eludes me. Am I making a mountain out of a molehill?

Regards Alan Hill

Alan Hill on 5/31/2010: Hi Richard Hope you can point me in the right direction please. This is where I am at.
Private Sub OpenCalendarForm_Click()
Dim x As Integer
Dim S1 As String, S2 As String, IsHoliday As Integer, GetID As Integer
Dim MySql As String

CalculateStartDate

DoCmd.OpenForm "MonthlyCalendarF"
For x = 1 To 42
S1 = "Date" & x
S2 = "Day" & x
If Month(Forms!MonthlyCalendarF(S1)) <> Month(Calendar) Then
Forms!MonthlyCalendarF(S2).BackColor = &HD8D8D8
Else
MySql = "'DateTime>=Forms!DatePickerF!StartDate +" & x
MySql = MySql & " AND DateTime
' tried Dlookup("Holiday","DateQ",mysql) that did not work
' so now try to get the ID then use "CalID=" & Getid
' that does not work either. Dlookup is not reading the correct values

GetID = DLookup("CalID", "DateQ", MySql)
IsHoliday = Nz(DLookup("Holiday", "DateQ", "CalID=" & GetID))

'MsgBox GetID & " - " & IsHoliday
' result is always 1

If IsHoliday = 1 Then Forms!MonthlyCalendarF(S2).BackColor = vbYellow ' School Holiday
If IsHoliday = 2 Then Forms!MonthlyCalendarF(S2).BackColor = vbBlue ' Public Holiday
If IsHoliday = 3 Then Forms!MonthlyCalendarF(S2).BackColor = vbGreen ' Student Free Day

End If
Next x

End Sub

 Ugene on 6/8/2010: Is there any way to set the Calendar to start on Monday and end on Sun.? I tried different combitions with the DatePickerF button, but didn't come up with a solution. Thanks for the help.
Randy Montalvo on 6/24/2010: Hello Richard,
I have done exactly as you are saying and quadruple checked it to make sure I didn't screw something up. At around 9:30 in this video you put in an sql statement that I put into my 2003 database and everytime I add it all the data on my appt list box disappears when I go back to form veiw. Any ideas how to fix this?

Selby Halfpenny on 7/8/2010: Richard - When I type code in the Visual Basic Editor, the cursor will not move forward and jumps back to the last word typed. Those letters typed turn red - intellisense does not work properly. Do I have reference problems Richard. Many thanks in Advance for your help.

Reply from Richard Rost:

Selby, I've never heard of this problem before. It sounds like an issue on your computer. I would try (a) rebooting, (b) reinstalling Office, (c) looking for other programs that might be conflicting with Access... in that order. If it's still happening, let me know and maybe we can figure it out.

Jon on 9/9/2010: Richard,

I'm using Access 2010 for my calendar, and as you are probably aware,Calendar Control is not included in Access 2010. I have managed to get around that by manipulating the StartDate to bring up the Calendar form for the correct month and starting date. But I cannot get the DatePickerF StartDate to show the pop up calendar which emulates the Calendar Control. Probably because it is just a text box and not bound to anything. Do you have any suggestions on what I can do? Thanks a lot!

 Alan Hill on 9/19/2010: Hi richard
I got your calendar program working OK, but that was my starting point.
I decided to use VB and sql 's to populate the listboxes. Easy,done it heap of times, but I keep getting odd results. It appears I have to convert dates to US dates to use in SQL's. What a pain. So I converted StartDate to mmm ddd yyyy which is a USDate Format. Ran my loop 1 to 42 but I am getting a mismatch error trying to add 1 to StartDate. As you have international customers, can you please look at SQL's and Dates on non US systems.
Best wishes Alan Hill

Reply from Richard Rost:

Alan, the date format shouldn't matter, as long as you're storing the value internally in a date/time field. The regional settings are something that you set in your Windows Control Panel. All of the calculations should still work OK.

 Alan Hill on 9/19/2010: Hi Richard
I finally cracked it. Anyone who does not live in the US needs to know this. Assigning a .rowsource to a listbox will NOT work properly if you are using dates as criteria with a SQL.
The first part of your expression is fine but after the "SELECT * FROM CalenderT WHERE " & "CDate=" - the next part has to be USDate format.
USDate = "#" & Month(TD) & "/" & Day(TD) & "/" & Year(TD) & "#"
Some of the listboxes were OK some were not. I had to requery each listbox or click the refresh all to see the results.
Once I used the USDate format all my problems were fixed. As you live in the US. I think it would be difficult for you to view my problem. Access is not entirely International we have to find ways to fix what the Access Team have missed. They do know about it. Pity they have not fixed it. But I am very happy my calender now works as well as yours.

Regards Alan Hill


Reply from Richard Rost:

Thanks for posting that, Alan.

Kenneth Lange on 10/31/2010: The Seminar video for access Calendar Video 5 (Lesson 5) Plays showing only the beginning video image in amicron video player. It seems to work for me when viewing the seminar thru IE.

Reply from Richard Rost:

Kenneth, it sounds like that particular video might not have downloaded completely. Try downloading the course again. Several hundred people have purchased this seminar, and so far yours is the only complaint in over a year, so it's probably on your end.

Kenneth Lange on 10/31/2010: Using Access 2010, I do not have the ActiveX Data Picker tool you refer to in Access Calendar Seminar, 6. Monthly View Form 3 Seminar near time stamp 4:48.
Kenneth Lange on 11/2/2010: Hello Richard, I am using Access 2010, but it does not use the Active-X Calendar Control. Is there a way to Make the Access Calendar work without the Active-X Calendar Control, or is there a third-party control that you reccommmend for Access 2010?

Reply from Richard Rost:

Apparently, Microsoft isn't shipping the good old Calendar Control with Access 2010. That control has been around since Access 2.0, and I'm sad to see it go. However, there are several different things you can do in Access 2010 to show a calendar.

Here is a link to MSDN that discusses some alternatives for the calendar control.

I plan on addressing this in more detail in a forthcoming lesson that I will add on to the Calendar Seminar.

Paul Civitarese on 11/14/2010: On lesson 4 At the end it skips the last short cut button out.The Button Is Open Calendar. Can anyone help me?

Reply from Richard Rost:

Paul, at what time index in the video are you seeing a skip?

Richard on 11/18/2010: Microsoft listed the Calendar OCX Control as one of the things that were removed from Access 2010. So if you're using 2010, you'll get an error message when you first open the sample class database.

The GOOD news, however, is that YOU DON'T NEED IT. The only reason I used the OCX control in class was to give Access 2003 (and earlier) users a form to pick a date with. Now, Access 2007 and 2010 come with a Date Picker automatically built in. Just click on ANY date field, and you can pick a date. So, Access 2010 users, just ignore the lesson where I build the Date Picker form.

Alex Hedley on 11/27/2010: Hi Richard,
As the ActiveX Calendar Control isn't available in Access 2010 I was just using the StartDate textbox to get the date value a user had entered to produce the Monthly Calendar but as the CalculateStartDate function can change this date to show the first Sunday (which can lead to the month changing) this caused a problem when colouring the none months differently.
To get past this I placed an invisible textbox on the DatePickerF which got the month from the initial date, stored it in a variable and then ran the CalculateStartDate, Now i can loop through and colour using the correct month.
Alex

Reply from Richard Rost:

Very elegant solution. Thanks for sharing. Honestly, I haven't run completely through this seminar using Access 2010 yet - as I recorded it before 2010 was available. I appreciate you noting any other problems you might find.

  on 12/30/2010: Hi - will this work with a multi-table format? for example listing each meeting participant from a "Customers" table?

Reply from Richard Rost:

I'm not sure I understand the question. Could you clarify, please?

Brent Rinehart on 1/2/2011: Great tutorial I would like to know if you can steer me in the right direction I am wanting to convert this calendar database to a calendar where there would be a form to generate employee shifts and would display person1 9-5p, person2 11a-6p, etc. in the form there would be a dropdown box with the employees and the different shifts they could possiably work. you would select the day, person, and shift and all that would display in the calendar to be handed out to the employee's, would this siminar be hard to change to accomidate this?

Reply from Richard Rost:

If you know that you have distinct shifts, then you could construct a form to just select people for those shifts, sure. Let's say you have three shifts (1st, 2nd, 3rd) and you want to set it up for the week (7 days). Just make a total of 21 unbound combo boxes on a form. Select an employee for each shift, then make a command button insert all of those people into your schedule table using a recordset. If you want to be able to have multiple people for each shift, then make multiple combo boxes for each shift.

Alex Hedley on 1/9/2011: Hi Richard,

Brent's Idea for Employee Shifts ->
Could this be tied into the 'Tracking Attendance' Seminar you suggested a few weeks ago,
[http://www.599cd.com/blog/display-article.asp?ID=352&Thread=Tracking+Attendance]

I made some suggestions for the Employees application of this

So you make a rota for employees that you can print out and distribute then confirm that these hours were worked after each shift

Regards Alex

Reply from Richard Rost:

Sure. Anything can work. I'll add this to my TODO list for the Attendance Seminar.

haroon sattar on 1/25/2011: hi richard. i am using a database for clinical application. would this seminar help me with creating caleder in access for making clinic and investigation appointments and update this to my patient data.
br
haroon

Reply from Richard Rost:

Yep. That's what this seminar is all about... creating a scheduling database and then creating on-screen and printable forms that look like calendars. Watch the intro video on the calendar seminar page. It will tell you everything that it covers.

hector chapa on 1/30/2011: hey richard rost could you do a webinar were you could send messages thru access but not using outlook and forms like to do list to co-workers if you can do it i am totally buying it.

Reply from Richard Rost:

I've added a seminar called Sending Email with Access WITHOUT Using Outlook to the Waiting List. Make sure you vote for it.

Brent Rinehart on 2/19/2011: after keying in the code for the report to be generated I get "run time error type mismatch 13, I have checked the code and including below: Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim X As Integer
Dim S1 As String, S2 As String

For X = 1 To 42
S1 = "DayLabel" & X
S2 = "Day" & X
If Month(Me(S1)) <> Month(Forms!DataPickerF!Calendar) Then
Me(S2).BackColor = &HD8D8D8
End If
Next X

End Sub

why am i getting this error?

Reply from Richard Rost:

Well, I don't know if you changed your object names, but my code reads:

For X = 1 To 42
S1 = "Date" & X
S2 = "Day" & X
...

So check and make sure that you have Date1, Date2, Date3, etc. instead of DayLabel1, DayLabel2, etc. as your code indicates.

  on 2/23/2011: I have a problem with Video 2: When i try to build my own list box, DblCLick event
My code:
Private Sub Leave_DblClick(Cancel As Integer)

DoCmd.OpenForm "F_Employee List", , , "Clock ID=" & Leave

End Sub

when i try to run it, it show this -- syntax error (missing operator) in query expression Clock ID=7041231'

I am wonder what is not right here? Hope you could give me some advise. Thank you.


Reply from Richard Rost:

I see a space in your field name "Clock ID". If so, you violated one of my rules from Access 101... NO SPACES in your field names. If that's the case, you need to say:

DoCmd.OpenForm "F_Employee List", , , "[Clock ID]=" & Leave

ellesha on 3/4/2011: Hi Richard,

Sorry, to ask you the same question for two times. I did try the code you advised, I still can't get my Dbl click function work.

DoCmd.OpenForm "F_Employee List", , , "[Clock ID]=" & Day3

I try to change "[Clock ID]=" & Day3 to "[T.Employee List].[Clock ID]=" & Day3 , it is still not working.

-- I have a complicate Data RawSource: table/query
SELECT [T_Employee List].[Clock ID], [T_Employee List].Forename, [T_Employee List].Surname, [T_Holiday Notification].[Dates from], [T_Holiday Notification].[Dates to] FROM [T_Employee List] INNER JOIN [T_Holiday Notification] ON [T_Employee List].ID=[T_Holiday Notification].[Staff ID] WHERE [T_Holiday Notification].[Dates from]=forms!DatePickerF!ShiftDate+2;

I am wondering whether the Raw Source SQL make my Dbl click function fail.


(2nd Question)
I have a Staff Holidays monthly calendar, ie: StaffName: Scott put in Holiday from 1/3/11 till 3/3/11
Now I only have Scott show in 1/3/11 MonthlyCalendarF listbox. How could I make Scott to show in 2/3/11 and 3/3/11 of the MonthlyCalendarF too?

Thank you so much for your help.




ellesha on 3/4/2011: Hi Richard,

Another question, I am setting a staff holidays MonthlyCalendar to view who is on Holiday each day. Each day I would like to allow only 6 staff on Holiday, how could I put a Count Fuction in my listbox? If there is more than 6 staff put holiday on the same day, a PopUp msgBox appear "No Space Available."

Hope you guide me the right direction to get the issue solve. Thank you.

ellesha on 3/9/2011: Yea, Finally I get my double click function work ^^

Private Sub Day3_DblClick(Cancel As Integer)


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "SF_Employee List"

stLinkCriteria = "[T_Employee List].[Clock ID]=" & "'" & [Day3] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

Reply from Richard Rost:

Looks good.

ellesha on 3/9/2011: If I have an Event"A" happen Date from 1/3/2011 till 3/3/2011. How to make Event"A" to show in our calendarF 3times on 1/3, 2/3 and 3/3?

Reply from Richard Rost:

It's been a while since I recorded that seminar, but I don't believe I covered creating RECURRING appointments. I did cover that in the PAYABLES seminar, however... how to make recurring bills that come back month after month (or any set time period). The calendar seminar was more focused on the DESIGN of the calendar - not the actual scheduling. Should I perhaps make this an add-on?

ellesha on 3/10/2011: Of cos it will be very helpful that you could create an add-on tutorial covering RECURRING appointments.

Reply from Richard Rost:

OK. I'll add it to my TO DO list... which is quite long at this point. :/

Richard on 3/13/2011: From Microsoft's Web Site:

"The Microsoft Calendar control (mscal.ocx) is not available in Access 2010. An alternative is to use the date picker control in Access 2010. Opening an application from an earlier version of Access where the control was used will generate an error message in Access 2010 and the control will not appear."

angela on 3/21/2011: it looks like chapter 5 you can hear it, but you can not see the tutorial please advise.

Reply from Richard Rost:

Try reloading it (refresh the page in your browser) or if you're watching it offline, download the course again. There's nothing wrong with the video, but if for some reason it didn't download 100% completely to your system, that could happen.

David on 5/8/2011: Alex Hedley, on 27th November 2010, gave what Richard described as a very elegant solution to get past the fact that Access 2010 no longer has the Calendar Active X Control.
That solution is:
Comment from Alex Hedley @ 11/27/2010
Hi Richard,
As the ActiveX Calendar Control isn't available in Access 2010 I was just using the StartDate textbox to get the date value a user had entered to produce the Monthly Calendar but as the CalculateStartDate function can change this date to show the first Sunday (which can lead to the month changing) this caused a problem when colouring the none months differently.
To get past this I placed an invisible textbox on the DatePickerF which got the month from the initial date, stored it in a variable and then ran the CalculateStartDate, Now i can loop through and colour using the correct month.
Alex
Try as I might I cannot work out the code to get the date into a variable.
I have put a text box on the DatePickerF form called "CalendarDate" which I have set to =StartDate, but I cannot get it to update when I change the date picker's date but not to update when I run the CalculateStartDate Sub routine.
Can you please help.
Many thanks.


 Mark on 5/9/2011: Is there any particular issue with having the date picker on the same form as the calendar

Reply from Richard Rost:

Not that I'm aware of. As long as they both have unique names, you should be OK.

David on 5/10/2011: Further to my earlier post, I have solved my problem.
I put a text box called, in my case 'startday', (any name will do, but I did not use 'calendar' in case it is a reserved word) which has the Access calendar as an entry, and set 'startdate' to equal it with a me.requerry to make sure it does equal it before I use the routine to calculate the first Sunday. It works.

Alex Hedley on 5/10/2011: Hi David,
I've added a mini guide at
[http://599cd.alexhedley.com/access/Calendar%20Seminar.asp]

Basically you name a textbox on the form [DateMonth], make it invisible, then in code you assign the [DateMonth] the same value as the [StartDate] (DateMonth = StartDate)and now you can perform the calculations needed to colour the [CalendarF].

Let me know if it makes sense and hopefully you get it working.

Regards
Alex

David on 5/10/2011: Hi Alex,
Thanks ever so much. I had, in fact done something similar, but I make the visible text box 'startmonth' and 'startdate' invisible and equal to it. That way I do not have to reset it to nothing if using the datepickerF again, it gets reset by access' own date picker!
I do however ex abundante cautela add a me.requerry.
Again many thanks for your full and very helpful answer.
David

Alex Hedley on 5/10/2011: No problem David,
Glad to see you got it working,
Happy to help, even tho I was late :p,
Mine's probably not the best way to go about it but aut viam inveniam aut faciam,
Hopefully there is enough interest for a follow up Seminar to add extra functionality to make it,
Al

Dawn  on 6/22/2011: I am not able to open VB to to create the script to double click an item in list box and open up the item

Dawn  on 7/6/2011: 10:59

When I open the MonthlyCalendarF - I get a pop up box to Enter Parameter Value for Forms!DatePickerF!StartDate..rather then it showing up as today's date...

Below is myRow Source
SELECT CalendarID, ApptTime, Description FROM CalendarQ WHERE DateTime>=Forms!DatePickerF!StartDate AND DateTime
Can you help with what I am doing wrong...

Alex Hedley on 7/7/2011: Dawn which version of Access are you using?

There has been some problems with the new version as the calendar control has changed.

Another thing to check is the spelling.
Enter Parameter Value
usually pops up when it can't find the field because it is either not there or spelt incorrectly.

Is the textbox on the form named StartDate?

Al

 Dana on 7/10/2011: I have added two subforms to my form "CalendarF" one of the fields list the "Type" of course public or group. Both of these subforms are formed from linked data in separate Databases and list the participants attending these courses. I placed the "group" subform 'GroupQF' on top of the 'PublicRegF". 'GroupQF'visible is set to no. I have been trying to show the form only when public appears in the Type field.
The code is as follows:
Private Sub Form_Current()
If [Forms]![CalendarF]![Type]= "group" Then
SubformGroupQF.visible = True
End If
End Sub

nothing happens. Can you help?

Alex Hedley on 7/11/2011: Hi Dana,

I had a quick go recreating your problem with a few other controls instead, (just used two buttons) and it seems that when the control is made visible it's order isn't kept so even though the hidden button is above the other button even when it is made visible it doesn't appear over the top.

You try either try offsetting it and seeing if it appears or only have one subform on to test whether it appears or not, then try adding the other one.

I presume you don't need 'PublicRegF' if 'SubformGroupQF' is needed as you said they are on top of one another so one thing to do is you could make 'PublicRegF' hidden so


Private Sub Form_Current()
If [Forms]![CalendarF]![Type]= "group" Then
SubformGroupQF.Visible = True
PublicRegF.Visible = False
End If
End Sub

Dana Michaels on 7/25/2011: Thank you for your reply to my query regarding turning a form on or off in Calender. The code I used that finally achieved my needs was:

Private Sub Form_Current()
If Me.[Type] = "group" Then
Me.PublicRegFS.Visible = False
Else
End If
If Me.[Type] = "public" Then
Me.PublicRegFS.Visible = True
End If

End Sub

I have placed one form on top of the other so that when the [type] is Group only the group form will show - when the type is public only the public form will show. I have yet to have any issues with regard to what order the forms where placed on top.

My next goal (isn't there always something more!) I would like to have the [courses] text to change colour in the onscreen calendar to show [type] group show in vbblue. I read that the list box must all be the same text - can the change of colour be applied on the calendar form?

Alex Hedley on 7/26/2011: Hi Dana,

Glad you got it working :).

You could add something like this to make the background of the form change to a different colour

Private Sub Form_Current()
If Me.[Type] = "group" Then
Me.PublicRegFS.Visible = False
Me.Section(acDetail).BackColor = vbRed
ElseIf Me.[Type] = "public" Then
Me.PublicRegFS.Visible = True
Me.Section(acDetail).BackColor = vbBlue
End If
End Sub


If you didn't want something as major you could add a label that changes its text and colour and use it as title.

Al

Dana Michaels on 7/26/2011: thank you for your reply - but I think you misunderstood my next goal. I don't want to make the detail section of the Calendar Form change color on selection of Type "Group" or "Public". I would like to know if there is a way when I open the Calendar that all group course numbers will be vbblue the course number is coming from the ApptListBox and is the equivalent of your original description ie: text. I want to be able to quickly see which are group course or which are public without double clicking to open the CalendarF form.
Alex Hedley on 7/26/2011: Sorry,

As you have stated the list box can't be altered to have differing colours.

Another option is to use the type field you already have and add that another column in the list box of the AppListF, maybe make it one character G / P then you could have sorting options or filtering options that show just G or P with the current Open, Closed filtering, maybe change the background when just showing G for added clarity but other than getting a custom listbox I can't really think of anything else,

Anyone else have any ideas?

Al

Dana Michaels on 7/29/2011: If the data is drawn from the apptlistbox ([Type] is in that list row could you code the Calendar form itself (not CalendarF) to change on Load event to change the colouring?
Alex Hedley on 7/29/2011: I'm not sure what you mean.
Are you wanting the days colour to change on the MonthlyCalendarF?

Dana Michaels on 8/2/2011: yes, the MonthlyCalendarF is has two columns 1 showing the DATETIME and the other showing the course#. The source data includes (but doesn't show) the [Type] of course "Group" or "Public" I would like to change the course# from auto black to vbGreen when the course is Group.
Alex Hedley on 8/3/2011: Dana to get the value from the listbox and check if it is a certain value then change the colour use

If Forms!MonthlyCalendarF(CONTROL).Column(#, #) = "Group" Then
Forms!MonthlyCalendarF(CONTROL).BackColor = RGB(0, 100, 0)
End If

within the OpenMonthlyCalendarB_Click() code.

Lindray on 8/8/2011: I am making a calendar in access (2010) using as reference the tutorial, but when I set the day of the month with a week I can not, access 2010 does not have the calendar control to display and Access 2007. another code which I can use to perform the same function.
Alex Hedley on 8/12/2011: Hi Lindray,

There is a wealth of comments below discussing how to address the problem that has arisen since Access has removed the Calendar Control from it's new release.

A simple solution is to use a textbox which is formatted as a date, this will show a date picker so you can easily select a date, and you can grab the day, month, or year from that using Day(DATETEXTBOX) etc

Hope this helps

Al

Robert Fleming on 8/14/2011: In the DatePickerF if you select April 1st 2011 Why, in the MonthlyCalendarF, is April 28th,29th and 30th greyed out.
Alex Hedley on 8/17/2011: Hi Robert
Is this in your version or Rich's.
Is it just that month that isn't working?
Just that month in that year?

John Brodie on 9/3/2011: Hi Richard: Is it proper to say at 3:17? The triple state check box on the unbound form named ApptListF is controlling the row source property of the list box object named ApptListBox?
William  Ryan on 9/6/2011: I am about halfway through the course, which seems to be very well written and "almost" what I'm looking to do. My problem is that my database is a for a small service call center. I have three related tables, CustomerInfo, ProductInfo and ServiceInfo. I'd want the appointment calendar to list information from all three of these tables. At present I have a query that summarizes this, but I'd like to be able to double click the calendar event and open the ServiceInfo form. I've tried to create the form based on the list box from the first 3 lessons, but I haven't been able to make it work. Instead of populating the list box with items from the tables or query, I get #Name? errors.
Aphillips on 9/23/2011: Is there a way you send the appointments made in access to Outlook?

Reply from Richard Rost:

Yes, it's possible, but it requires some VBA programming. I'll try to cover this in a future lesson.

APhillips on 9/24/2011: Thank you. I look forward to the vb class. I have taken the first four of your vb classes the past couple of weeks and plan to use it to enhance my access database I created for work. We are required to put all of our appointments in an outlook calendar. It would save a considerable amount of time to be able to automatically transfer the appointments to outlook.

Reply from Richard Rost:

A one-time transfer TO Outlook, or even a one-time transfer FROM Outlook is no big deal. That's maybe a dozen lines of code. The problem is keeping them synchronized and being able to go back and forth. That creates some issues.

Mike on 9/27/2011: Has anyone successfully published this calendar to the web as part of an Access Services application? I ask as Access Services limits most automation to macros and I am wondering if this calendar will work.

Thank you,
Mike

 Deon on 10/14/2011: Hi Richard,

I have Access 2010 and as you've pointed out, the ActiveX Calendar has been removed in 2010; how do I get a calendar on the nominated form?

Cheers,

Deon

Reply from Richard Rost:

See the addendum.

Deon  Riley on 10/18/2011: Hi Richard,

I'm using Access 2010 and have found that although I've enabled the Triple state feature 'YES' in the All Tab in the A

Deon  Riley on 10/18/2011: Hi Richard,

I'm using Access 2010 and ccannot get the Triple State check box to work. I have followed your instruction and when I try to select the check box nothing happens. Can you advise?

Cheers,

Deon

Alex Hedley on 10/18/2011: Hi Deon,

I've just tried Rich's copy in Access2010 and it isn't working but I deleted the original checkbox, created a new one and named it ShowClosedItems and it works.

Did you create the whole database from scratch or are you using the sample one?

P.S.
Rich the original checkbox looks "old" as if it was created in a much early edition.

Reply from Richard Rost:

Thanks for the heads-up. I think it was created in Access 2003 and up-converted.

Deon Riley on 10/19/2011: Hi Alex,

Yes - I have created the dbase from scratch and followed Chris' example very carefully but it still does not work. I'll have to re-check everything from scratch as I'm pretty new at this Access thing!

Cheers,

Deon

Alex Hedley on 10/19/2011: Deon one suggestion is to add some message boxes after each case saying different things and then see if they fire to check you have the checkbox name correct in the code.
If they work then it might be your SQL string. Check for spaces after Order By etc.
Try printing the SQL to see if that is correct.
Most of the time it is a silly spelling mistake or something trivial.
Good luck

Vera Schafer on 10/21/2011: If I try to open the MonthlyCalendar2R from Sample Database on Access 2010, I get a error: Forms!DatePickerF!StartDate and Access doesn't allow me to close it. I have to restart Windows. I know it is because I forgot to open the Form, but is there a way to avoid having to restart Windows? Am I missing something?

Reply from Richard Rost:

You shouldn't have to restart Windows. That indicates to me there is a deeper problem with your machine. Access shouldn't take your whole system down like that... even if your form has a problem.

Vera Schafer on 10/22/2011: I'm trying to build my dbase from scratch with Access 2010. Everything works fine until I reach instruction on Lesson 4 @ 11:00 on your video and I get an error. Your previous response was that I should remember dates need to be enclosed in # signs. Even though the video doesn't show it did, I tried with and without # and still it doesn't work. Any hints?

Reply from Richard Rost:

What is the exact error message?

Vera Schafer on 10/22/2011: The exact error message is:
This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

Deon Riley on 10/24/2011: Hi Alex, Apologies for the delayed response, been away. Thanks for the suggestion and help and I think I will print it out to check the string and spelling etc. Cheers for now, Deon
mike baxley on 10/30/2011: Does anyone know how to expand the alz video player to full screen?
mike baxley on 10/30/2011: I am having the same problem with access Calendar Video 5 (Lesson 5)
Kelley Christian on 11/7/2011: Button Button …. Not as limited as you think
A simple way to get away from those bland buttons is to make the button background transparent then click on the rectangle button and make a rectangle the same size as the button. Set the background color to whatever you want and use whatever effect you want and send it on down to the back. Not as fancy as web buttons but….this is Access not Dreamweaver or AI. You can also create fancy button backgrounds with any half decent graphic program and use them as the background also. Alas maybe someday MS will wise up and give us what we want….. Unlimited creativity ;-)

Kelley Christian on 11/11/2011: Closed (Canceled) Events Showing On MonthlyCalendarF

After working the calendar build several times as a learning tool due to the fact that I am self-taught. The light bulb came on that the closed events were showing up in my MonthlyCalendarF form.

This should not be and is not acceptable since if the appointment was canceled it should be no longer showing in that fancy calendar. OutLook removes it, so why not my calendar?

Solution:
When you build your first list box (I use the query builder, safer with inch long nails) make sure to drag down the “Closed” field and in its criteria set it to “FALSE”.

SELECT CalendarQ.CalendarID, CalendarQ.Description, CalendarQ.ApptTime, CalendarQ.Canceled FROM CalendarQ WHERE (((CalendarQ.StartingDate)>=Forms!DatePickerF!StartDate And (CalendarQ.StartingDate)
Add a link to the ApptListF to see your canceled/closed Items.

Now if we could just do recurring appointments like Outlook (Hint Hint Rich) we would all be as happy as bugs in a rug.


Reply from Richard Rost:

It's on the list. :)

Kelley Christian on 11/11/2011: Well DUH.... no clue where I copied the code I pasted in my other comment.Sorry !!!!! the correct sql for my "Closed" showing on the MonthlyCalendarF is as follows

SELECT CalendarQ.CalendarID, CalendarQ.Description, CalendarQ.ApptTime, CalendarQ.Canceled FROM CalendarQ WHERE (((CalendarQ.StartingDate)>=Forms!DatePickerF!StartDate And (CalendarQ.StartingDate)
Would blame it on a blond moment but it's all gray at my age, not that I am ancient now!

sean leach on 1/2/2012: i also had the same problem, i can only see the beginning image, of the calendar with monthly calendar form, part 2 thought the video. i could hear everything, but not see anything.
i will try to redownload.

sean on 1/2/2012: i tried to redownload the file, still did not work. i when i deleted the folder (prior to redownload) i noticed was in windows media format, so i was able to watch it zune player. if you have any problems with vid, thats what i rec.
 Katie on 1/23/2012: Access 2010: turn on my wizards
Bert Bakker on 2/1/2012: Hi Richard,

I use your calendar form in an Access 2003 database and I have changed it a bit. I wanted the plus and minus buttons on the calendar form, as you have shown in the second calendar addendum video.
But ... It does not work properly. On 30 January 2012, I clicked the button one month ahead and jumped to date .... March 1, 2012. February has only 28 days and this year is a leap year, so this month has 29 days. Some months have 30 days and the rest of the month have 31 days. There are also problems when you click on the button -1 month if you are 30 or March 31. Do you have a nice solution to this problem?

Sincerely,
Bert

Stephen Davanzo on 2/12/2012: How do I modify this so that I start up with the calendar View. I can click in the date I want to enter information in and the CalendarF comes up so that I can enter my appointment. I am trying to simplify the keys strokes. I tired
Private Sub Day4_DblClick(Cancel As Integer)
'If Not IsNull(Day4) Then OpenDay (Day4)
'If IsNull(Day4) Then OpenDay (Day4)
OpenDay (4)
End Sub
How can I modifiy this? This way I have one view all access with report buttons on bottom. Thanks

stephen on 2/20/2012: The work around so far was to create two command buttons. One to open the EnterCalendarApptF, and one to Refresh after updating
Private Sub RefreshButton_Click()
Me.Refresh
End Sub

I was trying to be able to double click on any date to have the EnterCalendarApptF open to either edit or add new. This way I can contol everything from the calendar. Any thoughts on how to double click on a date and auto populate that dates information?? Maybe a third Calendar Addendum Thanks Stephen

Harold Patton on 2/22/2012: I can not get the report to show the grayed-out days. I'm using Access 2010 and noticed there are some changes. I selected the "MS MonthView Control 6.0 (SP4)". Is there anything else I need to modify and/or change for Access 2010? Otherwise I'll keep looking for an error I may have made.
Thanks Much....

R Ubilla on 3/16/2012: 11:39 - when I open the monthly calendar nothing shows up, I have redone this 3 times and I keep getting the same problem
This is SQL I have:
SELECT CalendarID, ApptTime, Description FROM CalendarQ WHERE DateTime>=Forms!DatePickerF!StartDate AND DateTime
Reply from Richard Rost:

Remember, you have to enclose DATES inside of # signs...

MySQL = "SELECT ... WHERE DateTime >= #" & DateField & "#"

R Ubilla on 3/16/2012: I have you the wrong time line of the video it should be 10:01
R Ubilla on 3/16/2012: Disregard my previous question. I just figured it out. On the datepickerF Properties "ALL" I had a space "Start Date" should be "StartDate". Now it works. Thanks anyway
Scott Adkins on 4/1/2012: Richard, I too cannot get this Lesson to play in the downloaded format. All the other lessons seem to work.

I have removed and re-installed twice with no help.

Reply from Richard Rost:

OK. I'll look into it.

Allamaar on 4/21/2012: I'm using Access 2010 and thank you to Comment from Alex Hedley @ 11/27/2010. it was necessary to add the invisible text box.
 Jillette on 4/24/2012: I have downloaded the sample database;but, similar to Vera Shafer (21/10/2011)cannot open the MonthlyCalendarF. I receive a request for a parameter value - Forms!DatePickerF!StartDate. If I type in a date I receive the message - This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. I have checked all the code with the online seminars (which rarely mention my version of Access) and handbook and watched the addendums (Ihave Access 2010)is this a problem with 2010? Please help as its driving me mad!!!
Jillette on 4/26/2012: Just an update on my previous post. I can now open the monthlyCalendarF, but have lost the month and year at the top!! If I put into the MonthName TextBox's Control source =Format([Forms]![DatePickerF]![Calendar],"mmmm yyyy") i get a message box asking for the parameter value of datepickerstart time (same as before)!!! it freezes my computer (perfectly working)and i have to close access down and restart. Please can anyone explain and help me with this. thank you.
Alex Hedley on 4/27/2012: Hi jillette

I see you have Access 2010 and watched the addendums.
Have you removed the reference to the incompatible calendar control?
I made a quick tip on getting it to work
http://599cd.alexhedley.com/tips/1
Give it a go and let me know how you get on.
Alex

Jillette on 4/29/2012: Thanks for your reply Alex. Yes I did remove the reference and i now can open the calendar from the datepicker (maybe I was using the datepicker wrongly)? don't really know why it seems to work now!!! I did read your tip but didnt understand what calendar B was? - so i left it alone. However I still cant get the month or year on my calendar without the same enter parameter box appearing. However, I also want to know how to convert this calendar to a weekly one which would be so much better for my needs. Hope someone out there can help. Thanks
Alex Hedley on 5/1/2012: Was it 'OpenMonthlyCalendarB' that you didn't understand - I'd put the names of the controls on the picture to show what they were called so you knew their names for the code in VBA.

Did you know there was an updated on version of the Calendar for 2010.
The video tells you which address to go to - try downloading the new version and see if that works.

How do you want the weekly one to work?
You could take the principles from the seminar and scale it down to just 7 days instead of 42.

Alex

Reply from Richard Rost:

Yes, a 7-day calendar is just taking the monthly calendar and deleting most of the text boxes/code so you only have 7 days. :)

 Steve on 7/18/2012: This is great for single day entries. It would be great to have one that you can have a start date and end date and the calendar would show the entry on each day between start and end dates.

Reply from Richard Rost:

Yes, that would be a great addition. Perhaps in a followup class when I get some extra time.

Dana on 9/1/2012: I need to know how to bring an appointment (course)across the week - it was mentioned before in 2011... have you created any VB programming to teach us how?

Reply from Richard Rost:

If by "across a week" you mean starting on Monday and ending on Friday then the answer is "no." I haven't added anything explaining how to make appointments that span multiple days. I would recommend for now just create a separate appointment for each day. An easy solution would be to have Access automatically add multiple daily appointments for you if it sees that your start and end dates span multiple days. This wouldn't be hard to do. I could throw it together as an addendum lesson if enough people are interested. If so... speak up, people. :)

DAWN on 12/4/2012: I have three questions for you. If these questions have already been addressed, please refer me to where I can find the answers.

1. When I click on "Open Main Calendar Report" and go to print a copy of that month's calendar, multiple pages of the same month are printing. What do I have to do to only have it print one page?

2. How can I program the dbase to allow me to print multiple months at the same time?

3. I would like to open a data entry only of CalendarF each time I click on a particular day of the month in the MonthlyCalendarF so I can add a new appointment.

Thank you in advance for your help.

Reply from Richard Rost:

1. Lessons 9 and 10 cover printing your calendar report. If memory serves, there's VBA code in the button that you click on to open the report that limits the results. It's been a few YEARS since I recorded this seminar, however, so if I'm mistaken please correct me... but I'm certain I gave you the means to just print a single month in one of the videos.

2. This would be best accomplished with a form with two fields on it: Start Date and End Date. Just have the query that the calendar report is based on use those two dates for their parameters.

3. Set up a form with the DATA ENTRY property set to YES.

Hilda S on 2/25/2013: Hello,

I'm using access 2010. I Placed the StartDate textfield on the same form as the calendar. Everything works fine, except that after I pick a date, I have to close and open the form for the calendar to update. How can I ovoid having to close the form and open it back up to get to my desired month?
This is the code I have:

Option Compare Database

Private Sub CalculateStartDate()

' set the first day of current month
StartDate = DateSerial(Year(StartDate), Month(StartDate), 1)

' find the first SUNDAY
While Weekday(StartDate) <> 1
StartDate = StartDate - 1
Wend
End Sub

Private Sub StartDate_AfterUpdate()
CalculateStartDate
End Sub


Shari Martell on 4/3/2013: In am using Access 2010, my form is a full page and doesn't view as a pop up size. I have changes margins already. How do I change?

Reply from Richard Rost:

I believe this is covered in the very first lesson: switching from tab view to overlapping windows. It works the same in 2007, 2010, and 2013.

01. Create Database (7:02)
Turn on Overlapping Windows
Create Database File
Create Calendar Table
Create Calendar Form

Ali Hasan on 4/23/2013: Hi Richard,

i was wandering if i can send the Monthly report automatically in the binning of each month by email to a particular email address ? how can i do that ....

Ali

Reply from Richard Rost:

Yes, it's possible. You would need a macro or some VBA code to process the report and email it. You could do it with either a button you click yourself once a month or an event that fires, perhaps in the OnLoad event of your startup form. This is much more in-depth than I can explain here, but if enough people want to see it, perhaps I can create an addendum video. Post here if you're interested.

jay collins on 5/25/2013: I can't get this double click to work. I am in version 2010. I keep getting an error with the "calendarid=" portion of the where statement

Reply from Richard Rost:

Well, what's your complete statement? Did you check to make sure everything is spelled correctly?

John C on 6/21/2013: I noticed others were having trouble with the following code not working:
SELECT CalendarID, ApptTime, Description FROM CalendarQ WHERE DateTime>=Forms!DatePickerF!StartDate AND DateTime
I too am having trouble with it giving a "This expression typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

I was curious if you had a work around because I am unable to build the days of the calendar because I'm unable to add the +2, +3, etc.

I am using Access 2010.

Don Thorpe on 6/30/2013: Richard, I am using Access 2010.
Currently I am tracking work groups who are coming to Atlantic City to help with the clean up from Super Storm Sandy. In my DB I capture arrival and departure dates.
Is there a way to display on the calendar form and report a contiguous string of dates for a group without entering each date separately?

Reply from Richard Rost:

Not the way the database is currently designed. Sorry. A few people have expressed interest in something like this. An easy solution would be to have a form where you enter in the start date and the end date, and Access could automatically put in the multiple appointments for you. That would be a relatively quick fix. Displaying a single continuous appointment across multiple dates would be a MAJOR revision.

Alex Hedley on 7/1/2013: There's the Addendum I recorded which might be of some use.
Ronald Pero on 8/12/2013: I cannot seem to get the triple state version of the listbox to work in Access 2010. I found an article online that said you needed to change the closed items checkbox data type to integer instead of yes/no. Doing this did seem to prevent me from getting an error but it still doesn't seem to work as a triple state. Does anyone have any suggestions?

Reply from Richard Rost:

They're a pain to work with. They're easiest as UNBOUND check boxes to select criteria on a form. If you want to use it with a BOUND field, then YES, you need to make the data type Integer or Long Integer (you can't use a Y/N value). Yes will be -1, No will be 0, NO SELECTION will be NULL.

Ronald Pero on 8/14/2013: After further playing around with the triple state of my checkbox is that at least in Access 2010 you cannot put anything in the default value for the checkbox. The video says to set that to False but maybe that worked in 2007, in 2010 at least it makes the checkbox act like its locked and so it won't change state.

Reply from Richard Rost:

I'm not sure what you mean. You can set the default value of a check box to YES, NO, or (in the case of a tri-state) NULL. I know for a fact that this works in Access 2007 and 2013. I don't see why it wouldn't work in 2010.

Ronald Pero on 8/15/2013: If the time in the CalendarQ query is formatted to show am/pm it seems to not sort times properly. It seems to want to put 1:00pm before 11:00am for example on a given date. This seems to work properly when the query is formatted as simple short time. Is it possible to apply the formatting to the listbox in the monthly calendar form so that am/pm can be shown? If so, how would you do that?

Reply from Richard Rost:

Try using the format function:

SomeValue = Format(MyDate,"hh:nn am/pm")


Ronald Pero on 8/15/2013: I did not realize you could type null in as a default value. That seems to be what is required for the checkbox to work as a triple state. If you have yes or no in the default field it behaves as if triple state is set to no. If you set default as False it locks the checkbox.

Reply from Richard Rost:

It shouldn't happen that way. NULL means "no value." You should be able to specify any of the three as default values.

Ronald Pero on 8/17/2013: For some reason when I select multiple rows of objects in order to change the format for example Access deselects all additional rows. It does allow me to select 1 row of items such as labels, listboxes or whatever but it seems to want to deselect all additional rows. I'm not sure why this is happening. I am using Access 2010. This is a minor issue but any help with this would be appreciated.

Reply from Richard Rost:

In Access, you can either select individual rows one at a time or you can select a block of CONTIGUOUS rows (next to each other) but you can't select multiple non-contiguous rows with the CTRL key like you can in Excel.

Ronald Pero on 8/19/2013: In the video for lesson 9 it seems like you select multiple non-contiguous list boxes which you are able to then modify all at once. How is that done?

Reply from Richard Rost:

Can you give me the time index, please?

Ronald Pero on 8/26/2013: Access calendar seminar, lesson 9, 3:08-3:12

Reply from Richard Rost:

I apologize. I didn't understand your question. When you're in DESIGN VIEW you can click on an item (or group of items) and then hold down the CONTROL key to select additional items (contiguous or not).

I thought you were referring to ROWS of data, such as in a continuous form. Sorry... I read through and answer 20 or 30 questions some days and I miss details from time to time.

Ronald Pero on 9/3/2013: Access Calendar Seminar, lesson 4 5:08-5:20

This is where you create the list boxes for the Calendar. They are associated with the CalendarQ query. Where is that
association stored? I would like to be able to create a new query and
somehow be able to associate the list boxes that I created in your sample
with the new query instead of having to recreate them from scratch. Is this possible?

Reply from Richard Rost:

If memory serves, it's the CONTROL SOURCE for the List Box. You could change it to whatever you want.

Ronald Pero on 9/4/2013: I am creating my own calendar database now that I have completed the seminar. I created my forms and I tried to remove/delete the custom layout that the form wizard creates as shown in the seminar by going to Design view, and clicking on the Arrange tab. I see a button for Remove Layout but it is grayed out and therefore cannot be selected. Can you tell me what I need to do so that I can remove the layout?

Reply from Richard Rost:

The 2007/2010/2013 Layout is AWFUL. I hate it. That's why in my courses I recommend you create your forms and reports from scratch using Form Design or Report Design. It takes more time and effort to remove that dumb layout than it does to just add the fields you want manually in Design View.

To remove it... go to Design View. Select ALL of the controls (CTRL-A). Right-click > Layout > Remove Layout.

Or save some time and just start over from scratch. :)

Ronald Pero on 9/4/2013: When designing forms is it possible to apply formatting to text fields so they will be formatted automatically for items such as phone numbers, in other words so that the area code will be displayed in parentheses and a dash will be there between the exchange and the last 4 numbers? It seems that the only options available are for time formats and currency.

Reply from Richard Rost:

Sure. You can use the FORMAT or INPUT MASK properties to set this up quite easily in your table so that all of the forms or reports you base on that data inherit it. I cover this in Access Beginner 3.

Ronald P on 9/17/2013: The Calendar database I created is for a mobile notary that closes real estate transactions so depending on whether the transaction is a purchase or refinance there can be 1 (borrower) or 2 (buyer and seller) customers. Can you point me in the direction of how I could make my form have 1 or 2 customer subforms depending on the transaction type selected in the master form. In other words can a subform be conditionally turned on or off in a master form?

Reply from Richard Rost:

Sure... you could just set up two subforms and set the VISIBLE property to YES or NO accordingly... OR change the source property of the subform to whatever actual FORM you want to use. There are a lot of ways to do it.

Sabina Compassi on 9/30/2013: I have exactly the same issue as Andy on 9/18/2009: At approx 10:48 in Access Calendar session 4, where your adding he DateTime< StartDate+1, when i save i rec'v the error "The expression is typed incorrectly, or it is Everything works, if I just have the first part of the clause, without the AND Statement.

Sabina Compassi on 9/30/2013: On 2:08 into the Video: Default value [Formulare]![DatePickerF]![StartDate]
works
[Formulare]![DatePickerF]![StartDate]+1 does not work.
I did try:
=DatAdd("\d";1;[Forms]![DatePickerF]![StartDate])
but this didn't work either

Mike Winkleman on 10/13/2013: What about reoccurring events on the calendar events? Is this possible and if this was already addressed could you please direct me to the solution

Reply from Richard Rost:

I don't think I covered this in the Calendar Seminar, but I know I covered adding recurring BILL PAYMENTS in the Payables Seminar.

R Isaac on 10/27/2013: I have completed my Access Calendar database and it works! Thank you! I have one problem: I added a combo box to the CalendarT that lists names of volunteers. I added SQL to change first/last names into volname. The names show on my CalendarF, but only their ID numbers show on my ApptListF and therefore also on my MonthlyCalendarF and MonthlyCalendarR. What code and where do I write to get names onto the Calendars?
Alex Hedley on 11/3/2013: It sounds like you need to show a different column in the Listbox.

Check the ColumnWidths property.
Match it to the order of your SQL, Query Fields.

Column # | 1 | 2
Field | ID | Name
Width | 0 | 2"

Alex

Joe L on 1/29/2014: Sabina, I had the same problem as well and did not see a reply to this. You have to change the format on the text box for "StartDate" in the Date Picker form to "short date". I think it was defaulted to text or something like that.
Richard Lanoue on 4/13/2014: For 2010 access where is the Calendar Control on active X

Reply from Richard Rost:

See THIS.

Carlos Ramirez on 5/22/2014: Hello! I was wondering, I wanna make a access calendar that will show all the tasks that are made or need to be done in the house every week. And can this seminar show me how to update a task x that has been done in the past week to a task x that need to be done in the next week automaticly?

Reply from Richard Rost:

The Calendar Seminar has basic scheduling in it, but the point of the seminar is to show you how to FORMAT a printed report and a form to look like a monthly calendar. If you're interested in tracking recurring tasks, I'd recommend my PAYABLES seminar. I show you how to set up recurring payments (monthly, bi-weekly, etc.) which is the same concept, just a different type of data.


Mike D on 5/24/2014: I keep receiving a "Enter Parameter Value" error when running the following sql code in lesson 4
SELECT CalendarID, ApptTime,Description FROM CalendarQ WHERE DateTime = Forms!DatePickerF!StartDate ORDER BY ApptTime;

Reply from Richard Rost:

You most likely have a field name spelled wrong.

MICHAEL J on 6/15/2014: I am half way into your Calendar Seminar using 2010, I also downloaded the sample Db for the 2010 Calendar just so i could see how it would all work. I got to the section on graying out the dates but noticed that it was not working as it should in the sample DB. I compared the SQL code from the tutorial and the Db, and there is a slight difference. Just curious to know if an error accured when I downloaded the DB or there is a mistake in the code.
Bob C. on 6/20/2014: I am using Access 2010 and all was going fine until Lesson 8.

After resizing for the scroll bar and the double click procedure ... my debug is stating that I no longer have my MonthlyCalendarF form and I receive a Run-time error'40036' Method 'Item' of object 'Forms' failed and I always receive a Parameter Box upon opening the Open Calendar Form which is Very Difficult to escape from.

Any assistance in this area would be Greatly Appreciated. The Private Sub Command has been gone over and all appears to perfect?

"Thank You Very Much" In Advance for any help you can be.

Alex Hedley on 6/23/2014: Have you watched the Addendums?


Calendar Control Addendum 2



If Month(Forms!MonthlyCalendarF(S1)) <> Month(Calendar) Then
Forms!MonthlyCalendarF(S2).BackColor = &HD8D8D8
Else
Forms!MonthlyCalendarF(S2).BackColor = &HFFFFFF
End If


Calendar Control Addendum

Calendar Control Addendum 3

My own Tip

Lynda Chase on 9/8/2014: Hi Richard.

I have the triple state box working, however, when i click on it, which should 'show open' it comes up blank.

This is the code I put in:
Private Sub ApptListBox_DblClick(Cancel As Integer)

DoCmd.OpenForm "CalendarF", , , "CalendarID=" & ApptListBox

End Sub

Private Sub Form_Open(Cancel As Integer)

ShowClosedItems = False
ShowClosedItems_AfterUpdate

End Sub

Private Sub ShowClosedItems_AfterUpdate()

If ShowClosedItems = True Then
ApptListBox.RowSource = "SELECT CalendarID, LocationID, WorkerID, CustomerID, DateTime, Description FROM CalendarT WHERE Closed=TRUE ORDER BY DateTime;"
ElseIf ShowClosedItems = False Then
ApptListBox.RowSource = "SELECT CalendarID, LocationID, WorkerID, CustomerID, DateTime, Description FROM CalendarT WHERE Closed=FALSE ShowClosedItems ORDER BY DateTime;"
Else
ApptListBox.RowSource = "SELECT CalendarID, LocationID, WorkerID, CustomerID, DateTime, Description FROM CalendarT ORDER BY DateTime;"
End If


End Sub

I can't seem to see where I've gone wrong.

Thanks
Lynda

Lynda Chase on 9/8/2014: Duh!!! Can't see the woods for the trees - I found my error! Soooo frustrated with myself lol.

Sorry for been a time waster.

Lynda :o)

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP