MS Access Calendar

[MyWaterloo]

Admin Note

This thread has been closed as it has gotten just too big.

If you have any questions about the application discussed in this thread please start a new thread in MS Access by clicking on the Start a Discussion button and link to the this thread in your question.

Original Question

Just wondering...Am I the only one in the access universe that would like an Outlook like calendar option? Is there such a thing as a calendar that can be used for data entry? You know...double click the day and "POP" up comes the event screen to enter data into. I have spent countless hours searching for such a phantom. Any thoughts or sites with a template/example/download?

Admin Edit

For ease of usage I will include a link here to the post in this thread with the latest version of the solution database.

Currently this is at Post #298.

Alternatively, if the attachment is all you require, then you'll find it here (Calendar.zip).

A wide angled version (for wider screen real-estate) has also been posted now at Post #327. The attachment there is Wide Calendar with Switchboard.zip.

[Megalog]

I haven't seen one myself, and I've looked as well. I've thought about taking the events template that came with Acc 2007, and changing it to work with a big dynamic form using text boxes or memo fields worked up like a calendar... but I dont have that kind of time =/ We'll probably just sync up with a Sharepoint events calendar later this year.

If anyone else has one, I'd love to see it.

[MyWaterloo]

I haven't seen one myself, and I've looked as well. I've thought about taking the events template that came with Acc 2007, and changing it to work with a big dynamic form using text boxes or memo fields worked up like a calendar... but I dont have that kind of time =/ We'll probably just sync up with a Sharepoint events calendar later this year.

If anyone else has one, I'd love to see it.

*SIGH*
I have been trying for...well...over a year now to create/find a database that has a data entry calendar. The company I work for has been looking for some type of maintenance database with a calendar scheduler. I created a database to hold all the maintenance entries, but I can't find anyway to have a calendar be used to add/show tasks. The pathetic little popup calendar for date picking just doesn't cut it. =( No one in the access universe uses a calendar in access?!?!?!?!?!?!?!?!?

[Megalog]

There is this calendar form for 97/2000.... but it really needs a makeover.

http://www.fabalou.com/Access/Forms/calendar_entry.asp

[MyWaterloo]

There is this calendar form for 97/2000.... but it really needs a makeover.

http://www.fabalou.com/Access/Forms/calendar_entry.asp

Thanks Megalog, but I have that calendar already. Every once in awhile I drag it back out and beat on it some more; just hoping it will all of a sudden come to life. Alas, it continues to lay there like yesterdays jello...no jiggle...no wiggle...no flavor...
Was worth a try though.
"A calendar a calendar, my kingdom for a calendar..."

[ADezii]

Just wondering...Am I the only one in the access universe that would like an Outlook like calendar option? Is there such a thing as a calendar that can be used for data entry? You know...double click the day and "POP" up comes the event screen to enter data into. I have spent countless hours searching for such a phantom. Any thoughts or sites with a template/example/download?

See if something like this is what you are looking for. It can be easily modified:

[mshmyob]

In 2007 there is a built in date picker for date fields. When you set up a date field under the Format properties set the "Show Date Picker" to "For Dates".

That way when a user enters the date field a little Icon appears to the left and if they click on it a calendar popsup.

I don't remember if that is available before 2007 it has been awhile since I used an older version.

[Megalog]

In 2007 there is a built in date picker for date fields. When you set up a date field under the Format properties set the "Show Date Picker" to "For Dates".

That way when a user enters the date field a little Icon appears to the left and if they click on it a calendar popsup.

I don't remember if that is available before 2007 it has been awhile since I used an older version.

The popup date picker is nice, but he's asking for an actual calendar form where you see the events inside the dates.

[mshmyob]

To quote RoseAnn RoseAnna Dana ...... Nevermind ...... LOL

The popup date picker is nice, but he's asking for an actual calendar form where you see the events inside the dates.

[MyWaterloo]

See if something like this is what you are looking for. It can be easily modified:

Thanks for the calendar!! Looks very promising. Can't figure out what the letter sets of 3 are, i.e. BDF, CAE, DCF ???? What a whopping amount of code! =) Thanks again.

[MyWaterloo]

Thanks for the calendar!! Looks very promising. Can't figure out what the letter sets of 3 are, i.e. BDF, CAE, DCF ???? What a whopping amount of code! =) Thanks again.

Oops! LOL,

I found the solution to the letter sets. Ok, new question. (You may regret giving me the calendar because of a whole new set of questions it will produce.) =)
I have a maintenance table and form. I have placed a check box on the form to indicate whether or not the user wishes to send the info to the events/calendar. This procedure works. I would like to be able to go to a different month quicker and easier. It takes a good many clicks to get back to 2005. Seeing the calendar is based solely on VB and there is no record source for it...Is there a way to choose a specific month? Maybe a combobox that I can select a month that OnChange the VB would interpet my selection and show that month?
Well, maybe I'm grasping at straws. As always, Thanks.

[ADezii]

Thanks for the calendar!! Looks very promising. Can't figure out what the letter sets of 3 are, i.e. BDF, CAE, DCF ???? What a whopping amount of code! =) Thanks again.

This Calendar was designed for the Philadelphia Fire Department, of which I was a member for 32+ years. The letter sets represent Platoon Working Schedules.

[ADezii]

Oops! LOL,

I found the solution to the letter sets. Ok, new question. (You may regret giving me the calendar because of a whole new set of questions it will produce.) =)
I have a maintenance table and form. I have placed a check box on the form to indicate whether or not the user wishes to send the info to the events/calendar. This procedure works. I would like to be able to go to a different month quicker and easier. It takes a good many clicks to get back to 2005. Seeing the calendar is based solely on VB and there is no record source for it...Is there a way to choose a specific month? Maybe a combobox that I can select a month that OnChange the VB would interpet my selection and show that month?
Well, maybe I'm grasping at straws. As always, Thanks.

Seeing the calendar is based solely on VB and there is no record source for it...Is there a way to choose a specific month? Maybe a combobox that I can select a month that OnChange the VB would interpet my selection and show that month?

1. The Record Source for the Form is not explicitly stated, but it does write to tblEvent.
2. When I get a chance, I'll modify the code so it can Navigate to a given Year/Month more quickly, but you must be patient.

[MyWaterloo]

1. The Record Source for the Form is not explicitly stated, but it does write to tblEvent.
2. When I get a chance, I'll modify the code so it can Navigate to a given Year/Month more quickly, but you must be patient.

Thank you ADezii. I really appreciate your time and help on this issue. I did notice the table "tblEvent." What I meant by not having a record source referred to the year/month/day not being based on a table but all figured in VB. My own limited knowledge of VBA has been self taught and wrought with much frustration (and much reward) at times. I have been seeking out an effective event calendar for somewhere around ?3? years? Thank you again for your time and sharing your resource.
Waiting Patiently, =)
MyWaterloo

[ADezii]

Thank you ADezii. I really appreciate your time and help on this issue. I did notice the table "tblEvent." What I meant by not having a record source referred to the year/month/day not being based on a table but all figured in VB. My own limited knowledge of VBA has been self taught and wrought with much frustration (and much reward) at times. I have been seeking out an effective event calendar for somewhere around ?3? years? Thank you again for your time and sharing your resource.
Waiting Patiently, =)
MyWaterloo

I made some modifications to the code to make it more 'usable' and 'generic'. Try it out and let me know what you think:

[MyWaterloo]

I made some modifications to the code to make it more 'usable' and 'generic'. Try it out and let me know what you think:

Thank You.
Sorry, I have been away from the Access world for some time. Yes, the calendar modifications were very helpful. =)

[ADezii]

Here is the latest, TOP SECRET, super dooper, new and improved, very simple, Access Calendar. Just dbl-click on any given Day to enter Event(s) for that Day. Have fun!

[pdebaets]

Very nice calendar, ADezii. Thanks!

[lostdog]

Thank Adezii,

I've been looking for something like this for quite a while now, I had been making do with a normal datasheet for this function.

The blank blocks at the start of the month seem to run slighty off, today is highlighted as a Sunday. Is there a way I can ammend the code behind to coreect this?

Thanks again!

[ADezii]

Thank Adezii,

I've been looking for something like this for quite a while now, I had been making do with a normal datasheet for this function.

The blank blocks at the start of the month seem to run slighty off, today is highlighted as a Sunday. Is there a way I can ammend the code behind to coreect this?

Thanks again!

This anomaly seems to be related to specific Time Zones, especially European, and has to do with the formatting of the 1st Day of the Month. In the PopulateCalendar() Sub-Routine in the frmCalendar's Code Module, make sure the following line reads as follows:

'Correct syntax for your Time Zone
strFirstOfMonth = Str(intMonth) & "/1/" & Str(intYear)
 
'Incorrect syntax for your Time Zone (necessary for some Time Zones)
strFirstOfMonth = Str(intYear) & "/1/" & Str(intMonth)

[lostdog]

This anomaly seems to be related to specific Time Zones, especially European, and has to do with the formatting of the 1st Day of the Month. In the PopulateCalendar() Sub-Routine in the frmCalendar's Code Module, make sure the following line reads as follows:

'Correct syntax for your Time Zone
strFirstOfMonth = Str(intMonth) & "/1/" & Str(intYear)
 
'Incorrect syntax for your Time Zone (necessary for some Time Zones)
strFirstOfMonth = Str(intYear) & "/1/" & Str(intMonth)

Thanks for the quick reply!

It makes sense now after reading your post, the code I replaced it with was

 strFirstOfMonth = "1/" & Str(intMonth) & Str(intYear) 

Which sorted it out for the UK day/month/year date format.

Thanks again!

[blad3runn69]

wow ADezii way cool, kudos 2 u and thank you. You are a gentleman and a scholar!

[ADezii]

wow ADezii way cool, kudos 2 u and thank you. You are a gentleman and a scholar!

Thank you for the compliment, but as far as the 'gentleman' and 'scholar' part, you seems to be wrong on both parts! (LOL).

[truthlover]

Well you're definitely a hero to me (and I'm sure to many others after 32+ years on the fire department.)

Anyway, this calendar is exactly what I've been looking for. I tried to modify it by replacing the tblEvent reference with the table I wanted the values to be pulled from, but I got an error message saying "Item not found in this collection" and a blank calendar.

Is there a way to get the dates in my current database to tie in to this calendar?

Thanks!

Thank you for the compliment, but as far as the 'gentleman' and 'scholar' part, you seems to be wrong on both parts! (LOL).

[ADezii]

Well you're definitely a hero to me (and I'm sure to many others after 32+ years on the fire department.)

Anyway, this calendar is exactly what I've been looking for. I tried to modify it by replacing the tblEvent reference with the table I wanted the values to be pulled from, but I got an error message saying "Item not found in this collection" and a blank calendar.

Is there a way to get the dates in my current database to tie in to this calendar?

Thanks!

What exactly do you mean by 'tying' the Dates in your DB to this Calendar?

[truthlover]

I have a query qry_CrewList that generates a schedule of who is working on which project on any given day. The managers want to be able to look at a calendar and see in one view where everyone is scheduled.

Your calendar is perfect for that. I just need to know how to get the calendar to read the dates from the query.

Thanks!

What exactly do you mean by 'tying' the Dates in your DB to this Calendar?

[ADezii]

I have a query qry_CrewList that generates a schedule of who is working on which project on any given day. The managers want to be able to look at a calendar and see in one view where everyone is scheduled.

Your calendar is perfect for that. I just need to know how to get the calendar to read the dates from the query.

Thanks!

This may be very difficult, since the Calendar Application was never meant to 'Date Aware' in the sense that in can be Bound to a Date Field in a Data Source. I may be able to integrate it into your Database, but I will need qry_CrewList and its underlying Table(s), with some valid data. I will also need to know exactly what you want displayed in the Calendar for each Date since space is definately at a premium (Name and ProjectID)? Let me know if this is acceptable to you, and I'll give you my E-Mail address in a Private Message, where you can send the info as an Attachment.

[truthlover]

Yes, that would be great. And Name and ProjectID (or more likely SurveyWorkOrderID) is all that's needed. I'll get the file ready for you.

Thanks!

This may be very difficult, since the Calendar Application was never meant to 'Date Aware' in the sense that in can be Bound to a Date Field in a Data Source. I may be able to integrate it into your Database, but I will need qry_CrewList and its underlying Table(s), with some valid data. I will also need to know exactly what you want displayed in the Calendar for each Date since space is definately at a premium (Name and ProjectID)? Let me know if this is acceptable to you, and I'll give you my E-Mail address in a Private Message, where you can send the info as an Attachment.

[truthlover]

I dont know if this helps any, but I dont need the ability to add an appointment from the calendar and I dont want the buttons to clear the appointments to be available at all.

I just need the calendar to display the schedules generated by the existing forms (which populates the query).

Thanks!

Yes, that would be great. And Name and ProjectID (or more likely SurveyWorkOrderID) is all that's needed. I'll get the file ready for you.

Thanks!

[ADezii]

Yes, that would be great. And Name and ProjectID (or more likely SurveyWorkOrderID) is all that's needed. I'll get the file ready for you.

Thanks!

I was able to create a certain effect utilizing [ProjectID], [ProjectSurveyor], and [DateCreated], with test data. I assumed that Name referred to the Project Surveyor, if not Who? Look at the Attachment.

[truthlover]

Wow, that's great!

The only question I have is, it seems since the only table left is tbl_SurveyWorkOrder, that the dates needed for the calendar are no longer being accessed. (The dates in tbl_CrewSchedule are the dates needed for the calendar, hence the queries).

Can a query be used to populate the calendar?

Thanks!

I was able to create a certain effect utilizing [ProjectID], [ProjectSurveyor], and [DateCreated], with test data. I assumed that Name referred to the Project Surveyor, if not Who? Look at the Attachment.

[ADezii]

Wow, that's great!

The only question I have is, it seems since the only table left is tbl_SurveyWorkOrder, that the dates needed for the calendar are no longer being accessed. (The dates in tbl_CrewSchedule are the dates needed for the calendar, hence the queries).

Can a query be used to populate the calendar?

Thanks!

Can a query be used to populate the calendar?

This really complicates matters, since the code is really not meant to function in this manner to begin with, let alone based on a Query.

1. What is the name of the [Date] Field in tblCrewSchedule that is the basis for the Calendar?
2. What is the name of the [Name] Field, and in what Table does it reside? I'm referring to the Name Field that will be displayed in the Calendar along with the Project ID.
3. What is the name of the [ProjectID] Field, and in what Table does it reside? I'm referring to the Project ID Field that will be displayed in the Calendar along with the Name.
4. E-Mail me the Query (in a *.mdb Database) which will consist of these 3 Fields along with the Tables that comprise it. Display any relevant joins in this Query.
5. I'll attempt to work this Query into the Calendar in the same fashion, but I can't promise anything.
6. I'm assuming that you still have my E-Mail Address.

[truthlover]

Right now, the query I wanted the calendar to relate to is based on another query. I did that query on the fly (not realizing the problem it could cause for the calendar) and I'm thinking there has to be a better way to write the query.

So before I send you the file etc, let me go in and see if i can clean up and restructure a few things. (yes, I still have your email address, thanks)

This really complicates matters, since the code is really not meant to function in this manner to begin with, let alone based on a Query.

1. What is the name of the [Date] Field in tblCrewSchedule that is the basis for the Calendar?
2. What is the name of the [Name] Field, and in what Table does it reside? I'm referring to the Name Field that will be displayed in the Calendar along with the Project ID.
3. What is the name of the [ProjectID] Field, and in what Table does it reside? I'm referring to the Project ID Field that will be displayed in the Calendar along with the Name.
4. E-Mail me the Query (in a *.mdb Database) which will consist of these 3 Fields along with the Tables that comprise it. Display any relevant joins in this Query.
5. I'll attempt to work this Query into the Calendar in the same fashion, but I can't promise anything.
6. I'm assuming that you still have my E-Mail Address.

[truthlover]

Ok, I restructured the query based on the underlying tables. You should have received the file I sent.

Thanks!

Right now, the query I wanted the calendar to relate to is based on another query. I did that query on the fly (not realizing the problem it could cause for the calendar) and I'm thinking there has to be a better way to write the query.

So before I send you the file etc, let me go in and see if i can clean up and restructure a few things. (yes, I still have your email address, thanks)

[ADezii]

Ok, I restructured the query based on the underlying tables. You should have received the file I sent.

Thanks!

I'll check later this afternoon for the File, and hopefully look at it over the next few days to see what I can do. I'll be keeping in touch.

[truthlover]

Thanks, ADezii.

I think in the mean time, I'm going to show them your original calendar and see if they want to use it until we can tie it into the database.

If you have any questions, let me know.

I'll check later this afternoon for the File, and hopefully look at it over the next few days to see what I can do. I'll be keeping in touch.

[ADezii]

I do believe that I got the Calendar to derive its data from your Query Structure. The Calendar cannot work with Parameterized Queries, so I made an exact copy of qryCrewList and named it qryCrewList_2, without the Parameter Prompt. The Calendar draws its data from this Query. Do not Modify, Rename, Delete, etc, this Query since the code will crash. I strongly suggest that you do not modify any code at all in the Calendar Form unless it is done on a copy. Good luck and let me know how you make out.

NOTE: Please download the Attachment which contains the revised code.

[truthlover]

It's fantastic!! I imported it into the database and everything is working perfectly.

And while I dont yet know much about VBA, I can tell by the code it's what my husband would call "elegantly written".

I have one question... is there a way to get the name and project number to appear on the same line in the calander?

If not, or if it would be difficult, dont worry. But if it's a simple thing to do, it would just help not to mix up the person with the project number.

Thank you soooo much!!!

I do believe that I got the Calendar to derive its data from your Query Structure. The Calendar cannot work with Parameterized Queries, so I made an exact copy of qryCrewList and named it qryCrewList_2, without the Parameter Prompt. The Calendar draws its data from this Query. Do not Modify, Rename, Delete, etc, this Query since the code will crash. I strongly suggest that you do not modify any code at all in the Calendar Form unless it is done on a copy. Good luck and let me know how you make out.

NOTE: Please download the Attachment which contains the revised code.

[ADezii]

It's fantastic!! I imported it into the database and everything is working perfectly.

And while I dont yet know much about VBA, I can tell by the code it's what my husband would call "elegantly written".

I have one question... is there a way to get the name and project number to appear on the same line in the calander?

If not, or if it would be difficult, dont worry. But if it's a simple thing to do, it would just help not to mix up the person with the project number.

Thank you soooo much!!!

I have one question... is there a way to get the name and project number to appear on the same line in the calander?

Yes there is, just download the Attachment to see it. (Truthlover_Calendar_3.zip).

[truthlover]

Perfect!

You know, I hadnt told anyone I had uploaded the calendar before I went to lunch, but when I got back everyone must have discovered it and were all thrilled.

ADezii, I dont know how to thank you for all your help and infinte patience.

Have a fantastic day!

Yes there is, just download the Attachment to see it. (Truthlover_Calendar_3.zip).

[nspader]

Do you think there is anyway you could post a sanitised version of your db up for me to look at. I am trying to do something similar but different. I am having a hard time getting started with it. I think that this could be a really good place to start and work my way forward on what I need it to do. I would greatly appreciate it.

Nick

[truthlover]

If you're wanting to see the calendar application, just download the last file ADezii posted. That has the calendar as well as the tables/query it relates to.

If you want to see the whole db, I should warn you, though it started out clean and well-ordered, as more functions and applications were requested, the construction of the db ended up messy. And because I've had help with some of the coding (I dont know VBA) I've not been able to clean it up for fear of the whole thing falling apart.

Ok, so now you've been warned. If you still want to see it, send me a PM with your email address and I'll send it along.

Have a wonderful day!

Do you think there is anyway you could post a sanitised version of your db up for me to look at. I am trying to do something similar but different. I am having a hard time getting started with it. I think that this could be a really good place to start and work my way forward on what I need it to do. I would greatly appreciate it.

Nick

[JOrsak]

Yes there is, just download the Attachment to see it. (Truthlover_Calendar_3.zip).

Hey, this is a really cool little calendar.

What are the chances of getting some help with further development? I need to be able to click on the day and schedule a specific time slot. 10 Minute increments would be perfect. I'd also like to be able to pull from an employee list to assign that specific time slot to an employee for appointments.

Would that be possible?

[ADezii]

Hey, this is a really cool little calendar.

What are the chances of getting some help with further development? I need to be able to click on the day and schedule a specific time slot. 10 Minute increments would be perfect. I'd also like to be able to pull from an employee list to assign that specific time slot to an employee for appointments.

Would that be possible?

Would that be possible?

I imagine it would be, instead of opening a Form based on a Table of Events for a specific Day, you would open a Form based on a Table of Appointments for that Date. The underlying Table would consist of Fields such as: [Activity], [Last Name], [First Name] (both derived from a Table of Employees), [Appt Date], and [Time]. You would need a Composite Primary Key based on the [Appt Date] and [Appt Time] Fields so that no 2 10-Minute Time Intervals could be utilized for the same Date. The [Appt Time] Field would consist of a Combo Box of Times based on 10 minute Intervals within a certain Range (8:00 A.M. to 5:00 P.M.). If you are 'really' interested in the concept, and are 'not' in any rush, let me know.

[typhoo]

I was able to create a certain effect utilizing [ProjectID], [ProjectSurveyor], and [DateCreated], with test data. I assumed that Name referred to the Project Surveyor, if not Who? Look at the Attachment.

ADezii
I love this calendar it is exactly what i am looking for, although, i need it to show projects my team are working on and show the events on the calendar from the start date to end date, so i can look at the calendar at a glance and see who is busy and when. does that make sense? hope you can help? thanks

[ADezii]

ADezii
I love this calendar it is exactly what i am looking for, although, i need it to show projects my team are working on and show the events on the calendar from the start date to end date, so i can look at the calendar at a glance and see who is busy and when. does that make sense? hope you can help? thanks

Download the File in Post #40 and see if you can adapt it to your needs.

[typhoo]

Download the File in Post #40 and see if you can adapt it to your needs.

I used to work loads with access 3 years ago and everything seems to have gone out the window, i have been trying for the last two weeks all sorts of different things, but just cant get the code right. Any ideas?

[ADezii]

I used to work loads with access 3 years ago and everything seems to have gone out the window, i have been trying for the last two weeks all sorts of different things, but just cant get the code right. Any ideas?

Explain in detail exactly what you are trying to accomplish, listing Table Names, Field Types, Field Names, etc.

[mshmyob]

First of all you won't be able to do what you want with the way this calendar is setup. It uses a single text control for each day and you will therefore not be able to line up events across multiple days.

There are 3 ways to display data in a calendar that I have done.

1. Use a single text or label control for each day like you see in Adezi calendar.
2. Use list boxes and emulate how Goldmine does their calendar.
3. Use multiple test or label controls for each day and change their widths dynamically based on start and end date for the task. This will emulate how the Outlook calendar works.

I am currently modifying my calendar programs (I have done type 1 and 2) and now have almost the Outlook calendar emulation done.

Each type have their pros and cons.

So to do what you want you need to use type 3. Modify Adezi's code to add multiple text/label controls for each day and dynamically change their width property to spread across multiple days.

cheers,

ADezii
I love this calendar it is exactly what i am looking for, although, i need it to show projects my team are working on and show the events on the calendar from the start date to end date, so i can look at the calendar at a glance and see who is busy and when. does that make sense? hope you can help? thanks

[typhoo]

Explain in detail exactly what you are trying to accomplish, listing Table Names, Field Types, Field Names, etc.

Adezii

I would like to use the calendar so I can view a month and see when my team members are busy.

Table Name:
tblEvent

Field Name/Type:
Start Date - Date/time
End Date - Date/time
Event ID - Autonumber, Primary Key
Owner - text/Drop down menu

I need the calendar to show projects on all days between start and end date. the calendar currently only shows the event for one day (start date), team members may take days if not weeks working on projects. As we are not all based in one office it is difficult to keep track of what everyone is working on. Also if we receive more project work I would be able to look at the calendar and see who is available.

There is no rush for this...

Thanks