Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Code Archive _ Ms Outlook Style Calendar For Access Projects

Posted by: Peter Hibbs Jul 10 2011, 08:51 AM

The attached A2003 database file demonstrates a calendar form which is designed to simulate (as much as possible) the Calendar facilities in MS Outlook.
It can be used in three modes, Monthly, Weekly (see screen shots below) and Daily and allows users to enter appointments in any mode and to be able to easily switch between modes at any time.
It also has facilities to export appointments to or import appointments from MS Outlook, find scheduled appointments, print a monthly calendar, etc and it can be easily configured for different appointment time slot periods, different formats and so on.
A comprehensive PDF document file is included to show how to set up the system in an existing database project and how to configure the code for your different requirements.
It has been tested on Windows XP and Windows 7 and Access 2003/2007 (but not A2010).
If any bugs found please let me know and thanks to various UA members that have suggested amendments to version 1 which had been posted to this forum earlier.
Peter Hibbs.
(previous file download count: 879, replaced with Version3) ( 771.84K ): 2963 (in post #2 below)

This post has been edited by jleach: Sep 7 2012, 10:14 AM - Reason for edit: Replaced previous with Version3
Additionally edited by Cybercow: Dec 22 2012, 12:36 AM - Reason for edit: Added link to post #2 below for Access 2010 64 bit version


Posted by: dlathem Dec 17 2012, 05:10 PM

Hello again,
just wanted to leave a note for anyone that is using this on a 64-bit office 2010 system. I was running into an error "Please review and update Declare statements and then mark them with the PtrSafe attribute". I found that if you put PtrSafe right after the Declare statement it will eliminate this error.
And here's the 64 bit Access 2010 version tested and working! < (will work ONLY in the 64 bit version of Access 2010) ( 874.42K ): 1316

Posted by: Peter Hibbs Dec 22 2012, 04:40 AM

Hi dlathem,
Thanks for the Calendar update for 64 bit Access, I am sure it will be useful for some users.
I haven't got this version myself but looking at your amendments it seems that it is only the API calls that need to be changed for use with the 64 bit version of Access. I am thinking that we could just add those declarations to the 32 bit version and then Rem them out so that anyone wanting to use the calendar with the 64 bit version could just un-Rem them or maybe there is some way for the start up code to detect which version of Access is being used and enable the appropriate declaration code automatically (or is it more complicated than that?). I will have to think about it some more.
If you have any thoughts on this then please let us know.
Peter Hibbs.

Posted by: DatabaseMN Jan 5 2015, 04:52 AM

Peter, I have worked with your calendar in the past and it is a very nice tool.

I have wondered about why each time I open it or switch views, there is a rapid series of flickering. Is this just my machine or has this come up before?

Have been meaning to ask over the past couple years, but finally got around to it.

Thanks again!!


Posted by: Peter Hibbs Jan 5 2015, 06:58 AM

Hi Dana and thanks,

Yes, it is a nuisance. I am pretty sure it is because of the Conditional Formatting on the date fields and I don't think there is any way of eliminating it and I have tried various methods (unless anyone else knows differently).

Peter Hibbs.

Posted by: jenotc Jan 6 2015, 05:26 PM

I get error message.

Could anyone help on that?

Error message says:

Syntax error in date in query expression `#2015.1.7 10:00`.



Posted by: Peter Hibbs Jan 6 2015, 06:12 PM

Hi jenotc,

This is because your date format is different than the UK or US. Try the attached version below to see if that fixes the problem.

Peter Hibbs. ( 234.19K ): 577

Posted by: EvilBert Mar 23 2015, 12:09 AM

Hi Peter,

Thanks again for posting this awesome calendar.

I have one question.

I've embedded the frmCalendarMain into another form (so now it's a subform), and everything works great except when clicking on day in the calendar. I get an error on; Call Forms("frmCalendarMain").CheckMode. Because my form structure is now "frmSwitchboard!frmCalendarMain" the reference doesn't work anymore.

I've tried changing the reference to point it to the right place, but not having any luck so far. Maybe I'm overlooking something obvious.

Can you advise how I need to change this to reflect that frmCalendarMain is now a subform in my frmSwitchboard form?

Thanks in advance.

Kind regards,

Posted by: Peter Hibbs Mar 23 2015, 09:37 AM

Hi Michael,

Hmmm.. good question. Probably like you I tried changing the reference to the parent form by adding the name to the reference but I could not get it to work, maybe there is a limit on the number of forms a CALL function can be nested.

Anyway, the way round it is to move the CheckMode sub-routine to a Code Module (I used modCalendarCode) and then pass the main form object reference to the sub-routine so that the various commands in the sub-routine can make the required changes. Actually I think this is a better way of doing it as I never liked calling a form's sub-routine form another form.

For the calls from the main form (frmCalendarMain) you can just use the Me parameter and for the calls from the three sub-forms you can use Me.Parent. In the sub-routine itself you would simply replace the Me words with frm which is the alias used for the calling form. I have made the required mods in the demo file below so that you can see how it is done. To find the updates just search the VBA code for ### which I used to mark the changes made. One other change you may need to make is to make sure the Name property of the main sub-form (which was the original main form but is now a subform) is set to frmCalendarMain, otherwise the report title does not work (if you are using the report option).


Peter. ( 165.91K ): 875

Posted by: robertocm Jan 8 2017, 01:26 PM

Dear Peter,

Many Thanks for your calendar and also for the detailed guide 'Outlook Calendar Demo Manual'

Let my just show my gratitude sharing a little 'exercise' of adapting some of your code to an excel week calendar connected to an Access table (instead of the original Access form).

I have used the attached files to try to learn something about date and time queries, crosstab queries and ADO
from my beginner perspective
don't expect any comparable tool

Kind Regards ( 74.09K ): 268

Posted by: Peter Hibbs Jan 9 2017, 06:56 AM

Hi Robertocm,

OK, interesting idea, perhaps someone will find it useful.

Peter Hibbs.

Posted by: frm Apr 28 2018, 01:43 PM

Hello Mr. Peter Hibbs,

I saw his work and I found it excellent.

I wanted to ask if was foreseen add in the database the appointment recurrence management,

that is the repetition of the appointment:


as expected in Outlook 2007.

Posted by: Peter Hibbs Apr 29 2018, 02:16 AM

Hi frm,

I have added a 'recurrence' facility to this demo. Have a look at the new version in Post #7

Peter Hibbs.

Posted by: Peter Hibbs Apr 30 2018, 03:13 AM

Hi frm,

OK, try this version which may or may not work but I have an idea what the problem might be. We shall see!

Peter. ( 342.46K ): 76

Posted by: frm May 3 2018, 01:29 PM


I sent a post today on this Topic but I do not find it, as I do not find the other two pages of this topic either.

May I know, please, why?

Thank you

Posted by: doctor9 May 3 2018, 01:37 PM


Please check your New Messages. I have moved your discussion so it's in the appropriate forum. You can find your most recent posts by clicking on the "My Discussions" hyperlink in the upper right corner of the page.

UA Editor

Posted by: frm May 3 2018, 02:25 PM

Hi Mr Dennis

Thanks for the explanation.

A question.
When the Topics are inserted in the appropriate forum: "My Discussions"?

Posted by: doctor9 May 3 2018, 04:01 PM


Yes, it's in the group of hyperlinks in the upper right corner. I've highlighted it in the attachment.

Hope this helps,



Posted by: dulcmr7 Jun 26 2018, 08:30 AM

Hello Peter.
I am new here and basically signed up because of this great calendar example. Thank you very much for your work on this and making it available to the masses!
I have adapted your work to my project and all is working with the exception of 2 things (so far).

1) This is more of an annoyance than anything and I can easily work around it if needed. If I have the calendar on my primary screen everything works as it should, but if I have it on a second or third screen the date search button (right next to the "Monthly" button in the top right corner) doesn't work and gives an error "An expression you entered is the wrong data type for one of the arguments". I am guessing because it is trying to get the coordinate location, but it is getting confused with the multiple monitors. I am guessing either the Sub btnStartDate_Click or the Function FetchFormCoods needs to modified, but I am not sure how. Are you able to let me know what needs to be changed?

2) Because of a multi-user environment, I will have several instances where there are multiple appointments for the same time slots with each person's appointment on a separate line. I have been trying to figure out how to get the Month, Week, and Day calendars to each expand/grow if there are more appointments than space available so we can see everything. I realize the entire row will need to grow even if only one day has the extra rows, but that is acceptable. I have been changing several setting to Can Grow, but have not had luck with this yet. Any thoughts?

Thanks again!

Posted by: dulcmr7 Jun 26 2018, 01:34 PM

While I'm at it, I've also had a request to bypass the weekends in the calendar view so that it basically just shows a work week. I am scouring through the code right now to see if I can interpret it enough to figure it out. I have my doubts I will be able to get it by myself, though.
Thanks for any help or insight you could provide.

Posted by: Peter Hibbs Jun 26 2018, 02:22 PM

Hi dulcmr7,

Thank you for your kind comments, they are much appreciated.

Regarding the positioning of the pop-up calendar form on multiple monitors, this is a problem. I don't have monitors like this so I can't really try to find a fix, although I am sure it must be possible. As you suggested, you probably need to add some offset values for the horizontal and vertical co-ordinates in the sub-routine to get the form to appear in the correct position but I don't know what these would be. I guess you could experiment with code in the btnStartDate_Click() event to see what happens. I would move the calendar to another screen and then add some extra twips to the x1 and y1 variables just to see if that will move the form but other than that I can't really help.

One possible option you could try is, if you are using Access 2007 or later, you could use the built-in Access pop-up calendar. If you make the btnStartDate control invisible, change the Enabled and Locked properties of the txtDate control to True and False respectively and make sure the Show Date Picker property is set to 'For dates', this might be acceptable. You would also need to add this line of code , CalendarUpdate to the AfterUpdate event of the txtDate control so that the calendar form updates when you choose a new date. You could probably also add some code to move the focus after the update to highlight the selected date, etc.

Since this calendar uses a Continuous type form for the dates, it is not possible to use the Can Grow parameter (that only works on reports) so the only thing you could do to show more appointments in each date cell is to make the controls taller on the sub-form which will make all the rows taller. I guess it depends on the size of your screens as to whether this will work OK.

To remove the Sunday and Saturday columns on the Monthly calendar would require a major change to the VBA code and would take a lot of effort. I suggest that you make those columns very narrow, perhaps just enough to show the day as Sun and Sat and then make the other days of the week wider. In this way you still have the display for the weekend dates, in case you ever needed them, but it would provide more space for the working days. Changing the control sizes will still be quite a bit of work but would be easier than rewriting a lot of code.

Sorry I can't be of more help but let us know how you get (especially if you solve the multiple screen problem as other users have mentioned the sam eissue to me).

Peter Hibbs.

Posted by: dulcmr7 Jun 26 2018, 06:57 PM

Thanks for the quick reply Peter and for your thoughts.
I will look at the btnStartDate_Click() code tomorrow to see if I can get the calendar pop-up to work correctly. I think you are correct, though, I could probably just use the Show Date Picker property that is now available and use the built-in options. That sounds a lot easier to me!

I'll have to give my dilemma with the form appearing correctly some more thought on how I want to handle that. Thanks for reminding about the Can Grow property not working in continuous forms.

I feel like I'm so close on the Weekday only thing. I was looking at that this afternoon.
Unless I'm misunderstanding something, I think what you propose would only hide the weekends, but data would still be written to the tables for those days. I am wanting to not even write anything to the tables for Saturday or Sunday.
Here is the code I have modified (in bold). This is in the Module modCalendarCode under the Sub ShowMonthAppts. I have added a variable called vOffset and if the new calendar entry lands on a weekend, it is supposed to offset that particular day and future ones by 1 or 2 days:

          vOffset = 0
            'Now copy appt info into each row in array
            Do                                                                  'do--
                vCol = Weekday(vDate, conFirstDay) - 1 + vOffset               'calc column No for current date
                vRow = (DateDiff("d", vFirstDate, vDate) \ 7)                   'calc row number for current date

               If vCol = 6 Then
                    vCol = 1
                    vRow = vRow + 1
                    vOffset = vOffset + 2
                ElseIf vCol = 0 And vOffset = 0 Then
                    vCol = vCol + 1
                    vOffset = 1
                End If
                If conMonthHide = 0 Then                                        'if current date <> current month then skip *** (hide appts for dates not in current month)
                    If vRow <= 6 Then
                        vArray(vCol, vRow) = vArray(vCol, vRow) _
                        & Nz(rst!Subject) & vbCrLf                                  'add appt data to array + CRLF
                    End If
                    If Month(vMonthStart) = Month(vDate) Then                   'if current date <> current month then skip *** (hide appts for dates not in current month)
                        vArray(vCol, vRow) = vArray(vCol, vRow) _
                        & Nz(rst!Subject) & vbCrLf                              'add appt data to array + CRLF
                    End If
                End If
                vDate = vDate + 1                                               'inc date ref
            Loop Until vDate = vDateStop                                        'until all cells filled

It almost works. I think I know what's wrong, but I'm struggling to fix it. Maybe I'm taking the wrong approach altogether?
It actually does work for the actual month that the appointment takes place in. The weekends are blank and the rest of the appointment is moved out the 1 or 2 expected days. But if the appointment is in the "preview" or grayed out section for the next month (e.g. If the appointment is in the first weekend of March I can see it in the grayed out section at the bottom of February's calendar) then it gives me the warning that the "Subscript is out of range" (when viewing the February calendar in the previous example). I am guessing that is because I am moving days on the appointment forward that it is wanting to assign these values to the array beyond the set size of the array itself. I have been trying unsuccessfully to stop assigning values to the array within the Do loop, but no luck so far.
Sorry for the rambling. Hopefully something made sense.

Edit: I have tried setting conMonthHide=1 to hide the preview for the next month thinking that would solve my issue. It didn't.
My new theory is that I am having trouble with the modified code when the appointment spans over the end of the month in general (e.g. from 7/19/2018 - 8/7/2018).

Posted by: Peter Hibbs Jun 27 2018, 03:35 PM

Hi dulcmr7

Having looked at this again I think there is an easier way to do what you want regarding the requirement to only show the working week days. Basically you can just delete the Sunday and Saturday controls from the Month sub-form and change a few lines of code and this should work.

I have sent a demo version which shows the days Monday to Friday on the form (I haven't bothered to resize them but you can easily do that if you wish). Also, I haven't made any changes to the Weekly view so if you are using this as well then you will need to do something similar here as well. The Daily view should not need any changes.

So what happens is that the days of the week are shown as normal but in the pop-up Appointments form, you just need to prevent the users creating an appointment for a Sunday or a Saturday and that is pretty much it (unless there is some other factor that you have not told us about).

In the attached version I have marked any code changes or additions with ??? characters so you can search the VBA code for those to see the changes. The only change to the controls design you need to make is to the cmdDay button on the Month sub-form, it is a transparent button which runs along the top of the date cells (those colored green) so you will need to change the size of that button so that it covers the date cells for Monday to Friday. If you don't then you will probably show the wrong date when you click on a green date cell.

Anyway, try it out and let us know what you think.

Peter Hibbs. ( 182.01K ): 24

Posted by: dulcmr7 Jul 2 2018, 07:32 AM

Hi Peter -
I just wanted to follow up and say THANK YOU for your suggestions and start of the new code. Your method was much better than how I was trying to manipulate it.
My only hangup was I was needing to calculate the number of working days an appointment was scheduled over. If it went multiple weeks, it is still putting data in for Saturday and Sunday. I just did a quick calculation in a query to remove those from the count and all is well in my world once again.

Thank you so much for your help and support!

Posted by: Peter Hibbs Jul 2 2018, 08:34 AM