Microsoft Access Calendar Seminar

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!

�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. :)

Add Your Questions or Comments