MS Access Calendar - OVERVIEW

[ADezii]

MS Access Calendar – OVERVIEW

Many times over the years, a similar type question has been asked of us here at Bytes, namely: How Can I Incorporate Calendar Like Functionality Solely Within The Context Of My Access Application? The individuals posting these questions are not referring to the varied ActiveX Calendar Controls that essentially present a Popup Dialog that enables you to select a Date then populate a Form Field with that Date, but something a little more interactive such as the Outlook Calendar where you can double click on a Date, then add some data relative to that Date for display and later retrieval.

This all started back on January 19, 2008 when a regular Member asked if there was an Outlook like Calendar option that can be used for data entry. Now, 340 Posts later, Bytes finds itself in a situation, where it must Close the Thread due primarily for efficiency reasons. Needless to say, this Topic has generated an unprecedented amount of interest which we will now attempt to keep alive via this Insight as well as a Link to the Original Thread.

Before I go any further, I would like to state that the basic Calendar functionality that I previously described does exist here at Bytes in what we affectionately call the MS Access Calendar. After proceeding with some background information on its inception, I will then go on to describe its implementation, as well as providing Attachments for the two Versions available, namely the Wide and Standard Calendars.

The Original Calendar was created many years ago by a friend and co-worker of mine, Carl Broll. We were both Fire Fighters at the time, and the Department needed a Scheduler complete with Date listings, a display of working platoons for each day of the week, and a mechanism to input basic information referring to scheduled appointments. This was how the Original Calendar came to be.

I joined TheScripts (now Bytes) back in April of 2006, and soon realized that this need for an Access based Calendar can be mostly fulfilled by an adaption of the Original Calendar. With Carl’s explicit permission, I soon made some modifications and enhancements to it, basically tailoring it to each requestor’s specific needs. It now exists in two Versions: Wide (for Screen Resolutions >= 1024 X 768) and Standard (all other Resolutions).

The primary function of the MS Access Calendar is to provide a graphical representation of a limited amount of Data generated from a DAO Recordset. The source of this Recordset can be a Query, Table, or SQL Statement. This Recordset can contain either a single Date Field or a Range of Date Fields (From…To) and populates the Calendar Grid accordingly. It can also be enhanced so that you can double-click on a specific Date Block, and Open a Form in which you can now Add, Modify, or Delete Records in the underlying Data Source. At this point, the Calendar will need to be in sync with the Data Source and this can easily be accomplished via a Sync Command Button.

I will not get into any of the technical aspects of the MS Access Calendar at this point, but reserve that discussion for the IMPLEMENTATION Section to follow.

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

[MMcCarthy]

A very nice introduction ADezii.

I worked on something a couple of years ago which had to show a one year / 365 day view. I remember how complex it was and I only had to deal with a single piece of data, nothing like a recordset.

I'm looking forward to seeing the implementation of this laid out in an insight.

Mary

[NeoPa]

Me too :-D (even as far as chatting with Mary about the project she's talking about).

Here's a thought I just had, and it may be somewhat more than trivial I know. What about producing a calendar facility (or possibly Diary as the term Calendar doesn't really do it justice) which determines dynamically whether or not it can be a Wide version or Standard. Just a thought.

[ADezii]

@NeoPa - Not crystal clear on what you are saying, NeoPa. Are you suggesting that I dynamically determine the User's Screen Resolution, then Load the appropriate Version of the Calendar Form? It could be done, but it would add to the complexity of the Code, make the OVERVIEW already obsolete, and add another Control laden Form. Think it is worth the trade-off, assuming this was the original suggestion?

[NeoPa]

That's certainly what I was suggesting you consider ADezii. Please bear in mind that I am not in a position to know all about your considerations as I am not the architect of the project. My understanding of it is considerably more limited than yours.

That said, I would be interested to see such a project, and probably even interested enough to work with you on it, though my time currently is more limited than yours I suspect. Maybe you will decide it is not practical enough for now, but might be considered again sometime in the future. I can't say, as it is for you to consider.

[ADezii]

@NeoPa - Definitely worth considering, I'll have to sleep on it for awhile. Thanks for the insight.

[ADezii]

@NeoPa - Just wanted to let you know that I carefully considered both your suggestions concerning the Vertical Scroll Bars and Resolution Determination within the MS Access Calendar. Both have now been integrated within the Calendar, and there is no longer any need for a 'Standard' and 'Wide' Version. What I have done is:

1. At an entry point into the Database, in my case the AutoExec Macro, call a Public Function (fMain()). This Function will determine the User's Screen Resolution, and if it is >=1024 AND >=768, then The Wide Version (Form) of the Calendar will be Opened, and if not the Standard Version (Form) will. A Public Variable (gstrCalForm) will also be Set indicating which Form is Loaded, either frmStandardCale ndar or frmWide Calendar. The Application needs to know which Form is being utilized at any given time.
2. After the Calendar itself is populated, a Sub-Routine (SetScrollBars( )) is executed only if the 'Wide' Version is being utilized. This Routine Loops thru every Date Block (Text Boxes) on the Current Form, then counts the Number of CrLfs in its Value. If this Value is greater than a pre-determined Number, 4 in this case, there is a need for Vertical Scroll Bars, if not, not are required.
3. It all seems to work quite well, with a minimal amount of overhead. If you would like to see the specific Code, I would be more than happy to supply it. Once I tweak it a little, I would like to send you and Mary the DB in its entirety, and perhaps you can come up with a couple of other valued suggestions. Thanks for the input, it is appreciated.
4. Once this gets all ironed out, I'll start working on the IMPLEMENTATION aspect. For now, I cannot go in and either Edit or Add information to what I previously posted, and made Mary aware of this situation.

[NeoPa]

Stunning job!

I'd be very interested in seeing what you've got my friend. Whenever you're ready is good.

[labessade]

Hi, I have downloaded the calendar app's to have a look at. Are they the latest though as this thread implies there is just the one app now which works out screen res rather than the two (wide & std). Also are you in the US? I'm in the UK and when I enter todays date it appears as a Friday not a Tuesday in the graphical calendar and just wondered if that is why. I would love to use this to track holidays in our organisation although I'd have to amend to include start and end dates. Thanks...

[ADezii]

@labessade
I have attached one of the later Versions of the Calendar without the Resolution determination Code that seems to work quite well. Download it and see if it suits your needs.