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.

�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

�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 =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.

Add Your Questions or Comments