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.

[ADezii]

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

If possible, kindly provide me with say 2 Months worth of data, and I'll see what I can do when I get a chance. What exactly would you want displayed in each Day Block for the Calendar, the Event IDs, Event Descriptions, Owner, etc.? I would suggest keeping it as imple and restrictive as possible.

P.S. - Download the Attachment in Post #40 of this very Thread and look at the Months Feb. to Apr. 2008. I did something 'very' similar for another member.

[aeromon]

Hi all,
I too have been on the search for a calendar type form to link to various tables in my database! As some have said, this search is hopeless - UNTIL NOW!
Anyway,
I have had a look at this calendar, and am very impressed, it is a shame microsoft did not impliment this very useful feature into access.
Despite this, I am having some dramas
Firstly, I am using access 2007, and whenever I try to copy the forms/tables/macros into my database, suddenly the calander cannot perform the numbering of the days, and i get blank days - (the more specific error is "Error inPopulateCalendar" - "Type Mismatch")
How (In really dumb layman terms) would I go about fixing this.
Secondly, can one tell the calander to look up specific feilds in a table as opposed to the preset "Date" and "Event" feilds on the table tblEvent?
If so, how? (again with access 2007)


Thanks for all replies- aeromon

[ADezii]

Hi all,
I too have been on the search for a calendar type form to link to various tables in my database! As some have said, this search is hopeless - UNTIL NOW!
Anyway,
I have had a look at this calendar, and am very impressed, it is a shame microsoft did not impliment this very useful feature into access.
Despite this, I am having some dramas
Firstly, I am using access 2007, and whenever I try to copy the forms/tables/macros into my database, suddenly the calander cannot perform the numbering of the days, and i get blank days - (the more specific error is "Error inPopulateCalendar" - "Type Mismatch")
How (In really dumb layman terms) would I go about fixing this.
Secondly, can one tell the calander to look up specific feilds in a table as opposed to the preset "Date" and "Event" feilds on the table tblEvent?
If so, how? (again with access 2007)


Thanks for all replies- aeromon

(the more specific error is "Error inPopulateCalendar" - "Type Mismatch")

You would have to single-step through the code and note the specific line that the Error is generated on. We could now get a much clearer idea of exactly what is causing the Error.

Secondly, can one tell the calander to look up specific feilds in a table as opposed to the preset "Date" and "Event" feilds on the table tblEvent?
If so, how? (again with access 2007)

It would require multiple code changes, but it could probably be accomplished. Download the Truthlover_Calendar_3.Zip Database Attachment and see if you can figure out how to do this. The code you need to look at is in the PopulateCalendar() Sub-Routine.

[ADezii]

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

Here is most of the functionality you requested in the Attachment, any further problems, let me know.

[ADezii]

I had a little extra time, so this should be the finished product.

P.S. - Having trouble with the Attachment, will try later.

[ADezii]

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

This should suit your needs quite well. Get back to me on this, and let me know what you think. The Calendar will display the EventID and Owner for each Date within the Date Range.

[typhoo]

This should suit your needs quite well. Get back to me on this, and let me know what you think. The Calendar will display the EventID and Owner for each Date within the Date Range.

Adezii

im absolutely over the moon with this, you did it so quickly aswell!! thanks very very very very much!

[ADezii]

Adezii

im absolutely over the moon with this, you did it so quickly aswell!! thanks very very very very much!

You are quite welcome, typhoo. One little suggestion, the main code logic dynamically Deletes all Records in tblEventTemp, then Appends Records from tblEvent in a linear fashion to this Table. This is probably not the best approach, but the Calendar was never meant to do this in the first place. What I am trying to get at, sorry for deviating, is that with all this Deleting and Appending, you should periodically Compact/Repair this Database. As a side note, still enter your Events the way you normally do, e.g. EventID, Date, Date Finished, Owner, etc., the code will do the rest (breaking down the individual Ranges). Have fun!

P.S. - You should also set a Table Level Validation Rule, namely:

[Date Finished] > [Date]

[aeromon]

This calander is great,
Could someone please correct it for use with access 2007 though?
It might be a big ask, I have had a go but have reached the limits of what i have stored in my brain.
Anyone up for the challange? ;-)

It would be greatly appreciated.

-aeromon

[mshmyob]

I use AC2007 and although I don't use this calendar code (I use my own) it works for me in AC2007. Maybe you could be more specific at what does not work.

'I just know Ade doesn't have AC2007 so I thought I would help him on this one. lol'

cheers,

This calander is great,
Could someone please correct it for use with access 2007 though?
It might be a big ask, I have had a go but have reached the limits of what i have stored in my brain.
Anyone up for the challange? ;-)

It would be greatly appreciated.

-aeromon

[ADezii]

I use AC2007 and although I don't use this calendar code (I use my own) it works for me in AC2007. Maybe you could be more specific at what does not work.

'I just know Ade doesn't have AC2007 so I thought I would help him on this one. lol'

cheers,

Thanks for the assist, and you are correct in that I do not have Access 2007. I do not, however, see any reason why it would not work in Access 2007, the code is generic enough.

[aeromon]

Well, I have a database (in access 2007 format and am trying to import the calander into it.
I am simply going to use the calander for weather stuff (the database is for a small construction firm and we would like to have a record of the weather so if a client calls and asks why we havent concreted their driveway, we can say it has been raining from 8/8/08 to 12/8/08 etc)
Anyway, when i try to copy and paste the calander files, or drag the calander files or even import them, I get the error

"Error in populateCalander()"
"Type Mismatch"

I can open the calander in its native format (2003) and dont have any dramas. The calander is fantastic though.
I have uploaded the database if anyone can help me out it would be greatly appreciated
http://rapidshare.com/files/138450652/SCG_Database_2.0.accdb.html



Thanks
aeromon

[mshmyob]

OK I am downloading it. Next time attach it to your message here.

I will get back to you.

cheers,

Well, I have a database (in access 2007 format and am trying to import the calander into it.
I am simply going to use the calander for weather stuff (the database is for a small construction firm and we would like to have a record of the weather so if a client calls and asks why we havent concreted their driveway, we can say it has been raining from 8/8/08 to 12/8/08 etc)
Anyway, when i try to copy and paste the calander files, or drag the calander files or even import them, I get the error

"Error in populateCalander()"
"Type Mismatch"

I can open the calander in its native format (2003) and dont have any dramas. The calander is fantastic though.
I have uploaded the database if anyone can help me out it would be greatly appreciated
http://rapidshare.com/files/138450652/SCG_Database_2.0.accdb.html



Thanks
aeromon

[ADezii]

OK I am downloading it. Next time attach it to your message here.

I will get back to you.

cheers,

Thanks mshyob, I'm dead in the water on this one. When you find the answer to the problem, and I know you will, kindly post it here since this little Calendar Application seems to be fairly popular, and I would like to get a handle on any potential problems which may arise. Thanks.

[mshmyob]

OK. Easy fix.

Access 2007 uses DAO by default again but AC200-2002 uses ADO by default.

You therefore need to look for the following line under the PopulateCalendar routine

Dim astrCalendarBlocks(1 To 42) As String, db As Database, rstEvents As Recordset
 

and change it to

Dim astrCalendarBlocks(1 To 42) As String, db As Database, rstEvents As DAO.Recordset
 

Or of course change your Access 2007 settings to use ADO again.

cheers,

Thanks mshyob, I'm dead in the water on this one. When you find the answer to the problem, and I know you will, kindly post it here since this little Calendar Application seems to be fairly popular, and I would like to get a handle on any potential problems which may arise. Thanks.

[ADezii]

OK. Easy fix.

Access 2007 uses DAO by default again but AC200-2002 uses ADO by default.

You therefore need to look for the following line under the PopulateCalendar routine

Dim astrCalendarBlocks(1 To 42) As String, db As Database, rstEvents As Recordset
 

and change it to

Dim astrCalendarBlocks(1 To 42) As String, db As Database, rstEvents As DAO.Recordset
 

Or of course change your Access 2007 settings to use ADO again.

cheers,

Appreciate the help, thank you.

[aeromon]

I appreciate this so much
You are all so fantastic. I got it working with your help
Thanks heaps

-aeromon

[ADezii]

Adezii

im absolutely over the moon with this, you did it so quickly aswell!! thanks very very very very much!

1. Hello typhoo, I noticed that the Horizontal Scroll Bar is visible in lstEvents. You can eliminate this by setting the following properties in lstEvents:
   

   Column Widths: 0";0.5";6.25"
   Width: 7"

2. You can also set the Column Heads of lstEvents to: Yes

[SteveShelton]

Thanks for all the help on this. I have vacation planner started and this is going to tie in great with what I started!!

Steve

[SteveShelton]

OK

I have rebuilt the calendar in my db. What I want the Event table to do is simply display the employee, type of vacation (event) and the range of the employee's vacation.

I have looked at the code on planner calendar but not sure why you put the tblTempEvent in place?

Steve

[ADezii]

OK

I have rebuilt the calendar in my db. What I want the Event table to do is simply display the employee, type of vacation (event) and the range of the employee's vacation.

I have looked at the code on planner calendar but not sure why you put the tblTempEvent in place?

Steve

As stated in the PM, I will need the Database in order to see first hand. This Calendar was never meant to be an Event Planner or Scheduler, that's why everything must basically be customized.

[ADezii]

OK

I have rebuilt the calendar in my db. What I want the Event table to do is simply display the employee, type of vacation (event) and the range of the employee's vacation.

I have looked at the code on planner calendar but not sure why you put the tblTempEvent in place?

Steve

Hello Steve, each Record in tblEvent represents a specifc Event (e.g. Event 1) between a specified Date Range, namely [Date] ==> [Date Finish]. This Range must be broken down into individual Days within the Range in order to accurately display within the Calendar.

If Event 1 was scheduled between 1/1/2008 and 1/5/2008, it would have to be expanded to 5 Days, 5 Records, so that they can be represent ed in the Calendar:

Event 1 - 1/1/2008
Event 1 - 1/2/2008
Event 1 - 1/3/2008
Event 1 - 1/4/2008
Event 1 - 1/5/2008

[SteveShelton]

That makes since. I have sent you the shell of the database. I have just started on it so some of of it is crude. Took out all the forms except the calendar.

Thanks again.

Steve

[ADezii]

That makes since. I have sent you the shell of the database. I have just started on it so some of of it is crude. Took out all the forms except the calendar.

Thanks again.

Steve

As per the problem which you had indicated to me earlier:

1. You must redefine the Variable strSQL2 in the PopulateEventsList() Sub-Routine, as in:
   

   Private Sub PopulateEventsList(ctlDayBlock As Control)
   On Error GoTo Err_PopulateEventsList
   Dim strSQL2 As String
    
   strSQL2 = "SELECT tblEventTemp.Owner, tblEventTemp.Event, tblEventTemp.EventID " & _
             "FROM tblEventTemp WHERE tblEventTemp.Date = " & _
              ctlDayBlock.Tag & " ORDER BY tblEventTemp.Date;"
    
   lstEvents.RowSource = strSQL2
    
   lblEventsOnDate.Caption = Format(ctlDayBlock.Tag, "m-dd-yyyy")
   lstEvents.Visible = True
   lblEventsOnDate.Visible = True
    
   Exit_PopulateEventsList:
     Exit Sub
    
   Err_PopulateEventsList:
     MsgBox Err.Description, vbExclamation, "Error in PopulateEventsList()"
     Resume Exit_PopulateEventsList
   End Sub

2. Change the Column Widths Property in the Events List Box, since it will now be used display the Owner and Event. Use settings similar to 2;1;0.

[SteveShelton]

Thanks that worked.

I am trying to prefill on one form before the calendar to set the vacation up. On the form I have a combo box that list the employees. I have a querry that list the employees from another table. I have been reading and found this code but I can not get it to prefill the the rest of the information about the employee I have tried to adapt it for mine not much luck.

Public Sub MatchCombo(frmData As Form)
Dim Rst As DAO.Recordset
Dim strMark As String
Dim strSql As String
 
On Error GoTo rBookmark_Error
 
Set Rst = frmData.RecordsetClone
 
Rst.FindFirst "[Emploee] = " & frmData!Combo8
strMark = Rst.Bookmark
frmData.Bookmark = strMark
 
Set Rst = Nothing
exit_Bookmark:
Exit Sub
rBookmark_Error:
MsgBox Err.Number & vbCrLf & Err.Description
Resume exit_Bookmark
End Sub

[ADezii]

Thanks that worked.

I am trying to prefill on one form before the calendar to set the vacation up. On the form I have a combo box that list the employees. I have a querry that list the employees from another table. I have been reading and found this code but I can not get it to prefill the the rest of the information about the employee I have tried to adapt it for mine not much luck.

Public Sub MatchCombo(frmData As Form)
Dim Rst As DAO.Recordset
Dim strMark As String
Dim strSql As String
 
On Error GoTo rBookmark_Error
 
Set Rst = frmData.RecordsetClone
 
Rst.FindFirst "[Emploee] = " & frmData!Combo8
strMark = Rst.Bookmark
frmData.Bookmark = strMark
 
Set Rst = Nothing
exit_Bookmark:
Exit Sub
rBookmark_Error:
MsgBox Err.Number & vbCrLf & Err.Description
Resume exit_Bookmark
End Sub

Try:

Public Sub MatchCombo(frmData As Form)
Dim Rst As DAO.Recordset
Dim strMark As String
Dim strSql As String
 
On Error GoTo rBookmark_Error
 
Set Rst = frmData.RecordsetClone
 
Rst.FindFirst "[Emploee] = '" & frmData!Combo8 & "'"
 
If Not Rst.EOF Then
  strMark = Rst.Bookmark
  frmData.Bookmark = strMark
End If
 
Rst.Close
Set Rst = Nothing
 
exit_Bookmark:
  Exit Sub
 
rBookmark_Error:
  MsgBox Err.Number & vbCrLf & Err.Description
  Resume exit_Bookmark
End Sub

[SteveShelton]

Man you are fast!! I will try that when I get in this evening. That is so cool

Steve

[DAVIDWATERBURY]

@ADezii
Im not sure if you are still doing this calendar, but i need a calendar that is basically like steves. In the calendar i would like the persons name and what type of time off it is (vacation, doctors appointment, other) and i would like to be able to tell the calendar when a day is closed for vacations (date block turns red or something) i can send you the database i have so far if you are still working on these

thanks

[ADezii]

I've attached a basic Template for the Calendar. See if you can adapt it to your specific needs.

[alexchu]

OK, I read all the positive responses about ADezii's calendar and I decided to download it. However, I am obviously stupid and I have no idea how. In the first ADezii made, I did not find anything but just text. I feel very confused. Can somebody tell me how to download this calendar?
Thanks

[ADezii]

@alexchu

1. Left Click on Test Calendar.Zip in the previous Post under Attached File.
2. Click Save when the next Dialog appears.
3. Select a Folder on your Hard Drive to store the Downloaded File.
4. Click Save Button.
5. Navigate to the Folder, then extract the File from Test Calendar.zip.

[alexchu]

Thank you. Although I didn't understand why there is an attachment just in the last post.
Nevermind, can you tell me how to edit the information in it, because the only thing I see are some names, and I have no idea what to do with them.
Thank you, one more time.

[ADezii]

@alexchu

1. Open the Database (Test Calendar.mdb).
2. Press the F11 Key to open the Database Window.
3. Enter a Record in 'tblEvent' and supply values in the following Fields:
   1. [Date]
   2. [Date Finish]
   3. [Event]
   4. [Owner]
4. Switch back to the Form.
5. Click on the Sync Up Command Button.
6. The EventID along with the Owner of the Event will be listed on the Calendar within the Date Range specified ([Date] to [Date Finish]).

[alexchu]

Thank you, that was really helpful!
Edit: OK, another silly question. How to make the calendar extract information from another table and from fields with different names? Also, is it possible for the calendar to use several tables?

[ADezii]

@alexchu
Post the Table Name, Field names and their Data Types, and what information you wish displayed, keeping in mind the limited space available for each Day in the Calendar. Also, post some sample data including Start and End Date for the Event(s).

[alexchu]

Table Name: Projects

# - Autonumber - 1
Topic - Text - Natural Philosophy
Subject - Text - Ethics
Type - Text - Essay
Deadline - Date/Time - 12/19/08
Finished - Yes/No - No
Partners - Text - N/A
Grade - Number - 95
CalendarID - Text - EthicsEssay

I want the CalendarID displayed on date from "deadline".


Table Name: Tests

# - Autonumber - 1
Subject - Text - Physics
Material - Text - Electricity
Date - Date/Time - 01/06/09
Finished? - Yes/No - No
Grade - Number - 95
StudyFrom - Text - textbook, ch.18
CalendarID - Text - PhysicsTest

I want the calendar ID displayed on the date from "date"

[ADezii]

@alexchu
Are the 2 Tables (Projects, and Tests) supposed to be related?

[alexchu]

@ADezii
No, they are not.

Blah-blah-blah (I need 20 characters)

[ADezii]

@alexchu
I can probably rework the code to display the CalendarID given the Deadline from the Projects Table, or the CalendarID given the Date from the Tests Table, but to display both IDs from both Tables given they are not even related would be asking a bit too much.

[alexchu]

@ADezii
Ok, forget about it. Thanks, anyway! :)

[Newbie23]

Hi ADezii,
This calendar looks fantastic, just what I have been trying to create (with a 0% success rate!!!!). I'm suppose I’m a novice at Access BUT as a little project I'm trying to create a little application for my partners plumbing/heating business to store customer and work details. The calendar will be used for viewing their overall work, entering it etc.

Is there any way (& how difficult would it be?) to do the following:

- being able to select & edit a work record from the calendar?
- show times in the calendar

I think I will be able to add work from the calendar pretty easily but if I can get the other two then its perfect!!

Any help/advise would be very much appreciated
Thank you
Jane

[ADezii]

@Newbie23


Hello Jane, this could probably be done, but it may not be easy. Provide me with specifics, such as:

• Table Name
• All Field Names
• All Field Data Types
• Any Related Tables, if so name them and their Fields and Data Types also, along with Linking Field(s).
• Post some sample data as it would exist in the Table.
• What to display in the Calendar, being aware that only a very limited amount of Text will be able to be displayed.
• Explain exactly what you would like to see happen.

Once I have this info, I'll see what, if anything, that I can come up with.

BTW, a tough Project to undertake for a Newbie.

[Newbie23]

Brilliant, I'll get the info together for you and post it asap.

Many Thanks
Jane

[River Otter]

@ADezii
When I tried to create a link table, my very new MS Access 2007 tells me I cannot link to a table created in a newer version of Access, then dumps me.
I really am new at this, but perhaps there is an easy answer for me to at least make it to the next step! ? Thanks for doing this!

[Newbie23]

Hi ADezii,

Sorry it took a little while (had a bit of trouble with my laptop), anyway I have attached a sample DB with basic tables relating to the work.

It would be great to be able to see the start-time, surname & postcode per job if possible. I can manage to add work to the calendar, however the ability to amend work from the calendar would be fantastic. I can create a form to input new work and amend details, however im not sure how this would work on the calendar (especially if there is multiple jobs on one day?)

Yes I know it a big task for a newbie... I've done bits here and there with access and a few niffty things. however the calendar idea was out of my league haha.

Many thanks for your help, its much appreciated!
Jane

[ADezii]

@Newbie23
I don't know if you realize it, but that is a prettly tall order that you have given me. I'll see what I can come up with, if anything, in my spare time but you must be patient.

[Newbie23]

I did think it might be! I have plenty to do on it so i'm in no rush, however I understand if its too much trouble!

[ADezii]

@Newbie23

1. Add data to tbl_customers, tbl_properties, and tbl_work as needed.
2. Open frmCalendar and navigate to February 2009.
3. The Calendar will display the JobID, Surname, Postcode, and Start Time in its grid.
4. Click on a Day/Date to display the JobID, Date, Date Finish, Start Time, Surname, and Postcode in a List Box below the Calendar.
5. Any information that cannot be displayed in the Calendar itself will be displayed in the List Box.
6. Click on Sync Up to synchronize the Calendar with the Records contained in the Tables, if necessary.
7. The Calendar dynamically draws its data from tblEventTemp which utilizes qryCalendarData, if you are remotely interested.
8. Just download the Attachment, and if you have any questions, feel free to ask.

[Newbie23]

Hi Adezii.

Sorry got carried away with getting the calendar all set up in my database. It's brilliant!! Thank you so much for your time and effort.

Many Thanks
Jane

[ADezii]

@Newbie23
You are very welcome, Jane.