Access 2007-2016
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  
 
NEW Release: Access Developer Level 3   dismiss

News      User Comments     History     Notify Me

1/1/2009
Access Calendar Seminar
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

 
Manage your appointments, and create printable monthly reports from your Access databases.

Permanent Link
Course Link: Access Calendar Seminar
Keywords: access calendars appointments
Post Reply

Calendar Seminar Comment from Selby H @ 5/15/2016
Dear Richard and Alex,
I have figured out how to highlight the current day of the month on the Calendar following Alex.
How would I highlight the date that has been selected in the DatePicker Form?
All the Best
Regards Selby


Reply from Alex Hedley:

Have a variable for current date
Check if this equals the value of the datepicker


If (X = Day(Forms!MonthlyCalendarF(S1))) Then
    Forms!MonthlyCalendarF(S2).BackColor = vbRed
Else
    Forms!MonthlyCalendarF(S2).BackColor = &HFFFFFF
End If


I've not tested this but should work, just don't change the month buttons, or you'll need extra checks.
Show Just This Thread        Post Reply
Calendar Seminar Comment from Jeremy Heckman @ 5/7/2016
Alex, I understand the not showing as many days as in a monthly Calendar and have no problem with that aspect.  However I am am still struggling with only showing the 7 days of the week I pick.  It is still showing me the first Sunday of the month (in the case of May 2016) no matter what date I pick in the date picker.


Reply from Alex Hedley:

What code have you tried?
I'll need to take a look over it again, it's a loop checking for vbSunday if memory serves, you could add in an extra check to only loop x times.
Show Just This Thread        Post Reply
Calendar Seminar Comment from Jeremy Heckman @ 5/2/2016
I purchased the Calendar Seminar (among many other lessons) and have a question about it.  I am attempting to build a weekly "appointment book."  If your seminar you say you can just "chop off the weeks you don't need" to make it weekly or bi-weekly.  However how do you get it to show the first Sunday before the day you pick instead of the first Sunday before the first of the month.  I am using Access 2010.  Thanks for the help.


Reply from Alex Hedley:

You can use the principles from the lesson to go back to the first sunday from the week date you choose to open.
Just don't show as many days as the 42.
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from Alex Hedley @ 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
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from MICHAEL J @ 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.
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from Carlos Ramirez @ 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.

Show Just This Thread        Post Reply
Access Calendar Seminar Comment from Alex Hedley @ 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
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from Ronald Pero @ 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")

Show Just This Thread        Post Reply
Access Calendar Seminar Comment from Hilda S @ 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

Show Just This Thread        Post Reply
Calendar Seminar Questions Comment from DAWN @ 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.
Show Just This Thread        Post Reply
Calendar seminar Comment from Alex Hedley @ 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. :)
Show Just This Thread        Post Reply
Calendar seminar Comment from Jillette @ 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
Show Just This Thread        Post Reply
Calendar seminar Comment from Alex Hedley @ 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
Show Just This Thread        Post Reply
Calendar seminar Comment from Jillette @ 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.
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from stephen @ 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
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from Bert Bakker @ 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
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from Deon Riley @ 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
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from Alex Hedley @ 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
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from Deon Riley @ 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
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from Alex Hedley @ 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.
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from APhillips @ 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.
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from Aphillips @ 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.
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from Lindray @ 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.
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from Dana Michaels @ 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.
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from Alex Hedley @ 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
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from Dana Michaels @ 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.
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from Alex Hedley @ 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
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from Alex Hedley @ 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
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from David @ 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
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from Alex Hedley @ 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
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from David @ 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.
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from David @ 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.

Show Just This Thread        Post Reply
Access Calendar Seminar Comment from angela @ 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.
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from Richard @ 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."
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from ellesha @ 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. :/
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from ellesha @ 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?
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from ellesha @ 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.
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from ellesha @ 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.
Show Just This Thread        Post Reply
Access Calendar Seminar Comment from ellesha @ 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.



Show Just This Thread        Post Reply
Calendar Seminar Addendums Comment from Richard Rost @ 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.
Show Just This Thread        Post Reply

Add Your Comments or Subscribe
 
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
 
  Your Name:  Required
  Your Email:  NOT Public
  Subject:
  Comments:

 

Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 6+5:
  
  Notify me when the News is updated.
  Remember Me for my next comments
  
 
 
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard

 

 

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

7/19/2017Access Developer 2 and 3 Released
7/19/2017Microsoft Access Developer 3
7/19/2017Microsoft Access Developer 2
6/30/2017Access Developer 2 is Finished
6/7/2017Microsoft Access Developer 1
6/6/2017Access Developer 1 is Finished
5/18/2017Microsoft Access Advanced 6
5/17/2017Access Advanced 6 is Online
4/9/2017Microsoft Access Advanced 5
4/9/2017Microsoft Access Advanced 4
 

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