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]

I assume that was for a new version ADezii. Clearly there would be no point in reposting it if it's the same version as the last one yes?

It is a newer Version, NeoPa. The Original creator and myself have been working on it off and on. The most significant improvement is the elimination of the Intermediate Table for Multiple Date Ranges, along with Global Error Logging, more efficient code structures, etc.

[DCanatella]

Sorry new to this message board.

I have tried to enter data into the calendar and I cannot. I have double clicked, I have done all sorts of things such as unlock all the text boxes to enter data. What am I doing wrong. I run 2007.. Thank you.

[ADezii]

You do not enter Data into the Calendar itself but into the Training_Events Table, preferably via a Form Interface. You can have the Calendar Open a Form through which you enter Data into Training_Events, but the primary function of the Calendar is for visual display.

[DCanatella]

Thank you works great now that I understand the process.

[DCanatella]

Adezii, just thought you'd like to see what I have done to the calendar. Thank you again for sharing the bones of it with us.

DCanatella

[ADezii]

You are quite welcome. If possible, I would like to see your adaptation of the Calendar once it gets populated with some data.

[DCanatella]

Will do. I will resend to you once we have data entered and I write a few reports.

DCanatella

[edewing]

Adezii
I have read all posts in this thread and am very intrigued by this concept. I too work in the fire service and recently inherited 2 separate scheduling files, an excel file for monthly station acitivities and the other an access file for tracking vacations. I understand the benefits of a database over the excel spreadsheet and would like to move to access fully. Our current format resembles that noted in post #160, this has become more widely accepted in our department.

My question is this format even feasible in access, or should I convince others of a different format?

Thanks

[ADezii]

@edewing
I would need much more detail than that which you have provided, such as: Database Structure, contents of the Excel File, Fields and their Data Types, size of the Department, what you would like to see displayed in the Calendar, etc...

[Frinavale]

I just learned about the Bulk Insert command a week ago...

I tried doing a quick google to see if Access supports it but I wasn't able to get a clear picture of whether or not it does.

I'm just curious...if you know this off the top of your head....does Access support Bulk Insert?

If you don't know I can put a bit more effort into looking it up later :)

[NeoPa]

Did you mean to post this here Frinny?

Seems a bit of a blatant hijack to me ;)

BTW. I've not heard of it in Access myself and could find no reference to it in the help system. I guess that's a no.

[edewing]

@ADezii
Here is the excel file. It is formatted for a 28 day (4wk) work period where the "X" indicates a normal duty day. The "24" in bright yellow indicates overtime worked and the red at the bottom indicates coverage needed.

I am starting small with just one facility, however when I find my way this will include 15 facilities with about 250 employees.

[Frinavale]

NeoPa:
Did you mean to post this here Frinny?

Seems a bit of a blatant hijack to me ;)

BTW. I've not heard of it in Access myself and could find no reference to it in the help system. I guess that's a no.

Yes I meant to post here.

I'm not exactly hijacking...

I was thinking that the bulk insert would help with inserting comma separated value files into databases easily which could help Ed in this case.

But since it doesn't exist for Access I guess it isn't all that helpful.

:)

[ADezii]

@edewing
Are you looking to somehow incorporate this data into a graphical, calendar view?

[NeoPa]

Frinavale: But since it doesn't exist for Access I guess it isn't all that helpful.

It doesn't, but loading CSV files into Access is certainly implemented. It would be VBA controlled rather than managed within the SQL, but it can certainly work.

What did you have in mind?

[Frinavale]

Well I was thinking that you could use the Bulk Insert command to load the data easily but there's a couple of problems with my thinking: there's no Bulk Insert command; it's a work book and not a CSV file.

I just get excited when I learn new things.
Sometimes you need a saw instead of a hammer for the job :D

[NeoPa]

Spreadsheets can also be loaded up in VBA without too much difficulty, but I suggest we leave this one to ADezii for now. He has so much experience in this area and I get the impression he quite enjoys answering these questions. He's certainly done it well for the longest time.

[edewing]

@ADezii
Well I am sort of open to ideas. The spreadsheet concept has been used for quite sometime, so I feel if I stayed along those lines a change in practice might be better received.

Was your original calendar (the one that started this discussion) a graphical type?

[edewing]

@NeoPa
He seems to be the subject matter expert and certainly has alot of time vested in this topic.

[ADezii]

@edewing
Give me a little time, and I'll produce a simple Calendar that will graphicaly display your Excel Data for the Month of May.

[edewing]

@ADezii
Thanks for the help

[ADezii]

@edewing
Anytime. Here is a little something for you to play with, download the Attachment and see if it could possibly suit your needs.

[edewing]

Adezii

Thanks for the file. This shows some promise. Its going to take me some time to digest all the elements as I am still learning.

Thanks again.

[NeoPa]

ADezii: You can also download the Attachment in Post #311, since the code base is exactly the same, just different data.

ADezii, as I don't know which versions are suitable to be considered the latest version unless you tell me, please let me know whenever the version you think is most appropriate generally for download changes. If you tell me then I can update the Original Post to reflect the change.

[ADezii]

@NeoPa
Sorry NeoPa, but any Version displayed since the one in Post #283 will be essentially the same. The Code Base will be the same, and the only things that will change will be related to adapting different Data Structures to the Calendar Code. The overall Logic has been finalized as far as I am concerned. However you wish to handle the Versioning issue is fine by me. Again, sorry for the confusion.

[NeoPa]

I wasn't assuming a problem ADezii. Just reminding, in case you needed it, that I generally don't follow the details well enough in this thread to determine if new releases are fundamentally different, or simply little changes specific to an individual request. Because of that I will need you, if ever you do make a fundamental change, to let me know the status so that I can update the OP. If that hasn't happened then I'm fine with that. I just didn't want to miss updating it as required simply for my lack of understanding. It sounds like you have everything under control :)

[ADezii]

@NeoPa
Thanks again, NeoPa. I think that we can make a Sci-Fi Movie on this one, 'The Thread That Wouldn't Die'! (LOL)

[NeoPa]

If we made it now we could call it 2010.

Doh!! That one's gone already.

[Jerry Maiapu]

@NeoPa
Thanks alot all of you..this is a reply from post #316

[Gall Laminating]

@ADezii
Hi there! I found your posts after much searching online for any info regarding Access Scheduling/Calendar and found this thread. I have been developing a pretty basic database for the company I work for and was hoping to see this magical calendar you created, however I cannot find any link anywhere that you and others refer to on this site. Is there anyway to check out your magical calendar that I am desperate for? Any help would be much appreciated as I need to be able to schedule jobs in and out for customers.

The magical calendar I am referring to is the one you made for truthlover...

Thanks again!!!!

[ADezii]

This is a rather lengthy and confusing Thread. Download the Attachment at Post #311 in this Thread, navigate to May, 2010, then click on any Field with an entry for the date.

[Gall Laminating]

thanks for the reply - I checked out the calendar and it uses a excel spreadsheet to gather the info. This is not quite what I was looking for.

I wanted to use a calendar sort of like the one you pointed me to, but using my existing access data such as work orders and customers to create a calendar that we can schedule pending jobs and track jobs already inhouse till they are picked up or shipped out. These "records" would have to be editable and moveable on the calendar.

I believe in the very beginning of this thread you were doing just that for 2 people - one of them being truthlover.

I realize this thread has been continued for 2 years and still counting - however you are very informative and this is a subject obviously many people are interested in.

I appreciate any and all help you can give regarding my needs....

Thanks again!

[ADezii]

@Gall Laminating
Post some sample Data with as much detail as possible including To and From Date Fields, Table Name, Field names and their Data Types, info you would like to see visually in the Calendar keeping in mind that it must be limited, etc. I'll be returning from Vacation tomorrow, and I'll see if I can create a Demo for you over the weekend.

[Gall Laminating]

not sure how limited this must be as maybe you could give me a bigger calendar than the sample I viewed - however here is the info I need on the calendar and you can tell me if it is possible or not - plus maybe there is a better solution than a "calendar" - I just need it to function like a calendar, more like Microsoft Outlook's calendar, but with my database info.

Work Orders TABLE
Field name - Field Type
WO# - Auto number
PO# - Text
Job# - Text
Q# - Text
Date Received - Date/Time
Time Received - Date/Time
Date Expected - Date/Time
Date Job Pending - Date/Time
Notes - Memo

Customers TABLE
Field name - Field Type
Customer ID - Text
Company - Text
Payment Terms - Text

Hopefully not too much info!
Thanks for all your help!

[ADezii]

@Gall Laminating
How are the 2 Tables related?

[Gall Laminating]

both table are related by the customer ID field (both tables have a customer ID field)

[Gall Laminating]

Include ALL records from 'Work Orders' and only those records from 'Customers' where the joined fields are equal.

[ADezii]

@Gall Laminating
This will take a little time to come up with a workable Demo, so please be patient.

[Gall Laminating]

thanks for your help! this is me being patient <-----

[ADezii]

@Gall Laminating
Just one simple point to iron out. I'm assuming that Customers and Work Orders are involved in a 1 to MANY Relationship based on [Customer ID], namely:

tblCustomers.[Customer ID]{1} ==> tblWorkOrders.[Customer ID]{MANY}

Assuming this is correct, I cannot see the logic for the Outer Join, since you should never have an Order for which there is no Customer. If you wish to enforce Referential Integrity, then this condition definitely cannot exist. Please clarify.

[Gall Laminating]

@ADezii
thanks for that info - I am a self learned so your language is unknown to me - I understand this "you should never have an Order for which there is no Customer". Does that mean I do not need to join the tables by customer ID on both and could join by Customer ID to Workorder ID?

[ADezii]

@Gall Laminating
The Join could be exactly that as described in Post #338 with Referential Integrity enforced. In this manner, a Customer can have 0, 1, or multiple Work Orders, but there can never exist a Work Order with no associated Customer. Let me get started and work on this premise for now. Stay tuned.

[ADezii]

@Gall Laminating
Download the Attachment and Open the Demo. For each Date that a Work Order is Received, the Customer's Name is displayed in the Calendar (5 Customers - 6 Orders, ranging from 7/26/2010 to 7/31/2010). Click on that Date to expand information relating to that Order/Customer combination into the List Box below the Calendar. Is this is relatively new to you, then I am afraid that the code may be a little challenging.

[Gall Laminating]

thanks! it looks good - I will try to pick apart and shuffle around codes - I am new, but I can also usually learn pretty quick by seeing and copying, etc....however I sometimes miss things such as "I cannot see the logic for the Outer Join, since you should never have an Order for which there is no Customer. If you wish to enforce Referential Integrity, then this condition definitely cannot exist." so sometimes I trip up, but I REALLY appreciate this code!

[ADezii]

@Gall Laminating
If there are any questions that you may have, please feel free to ask. Simply populate tblCustomers and tblWorkOrders, preferably via a Form or Form/Sub-Form Interface. When you Open the Calendar Form, all data will dynamically be displayed in the Calendar based on the Date Received. Make sure that both the [Date Received] and [Company] Fields have valid data in them (Required = Yes).

[CaptBravo]

ADezii;

Thanks for the calendar info and help. I'll be sure to reply here should I need additional help.

-Capt Bravo

[ADezii]

We'll be here.

[ADezii]

We still obtain requests for the Access Calendar, so I've made the Calendar available as an Attachment to this Post, rather than having someone navigating through the many previous Posts in order to obtain the latest Version.

[nerd4access]

I have downloaded this calendar and put it to great use. I am very thankful for all of the hard work that was put into this calendar. However, I am stuck on something and have looked everywhere for an answer.

I have an access front end and a SQL Server 2005 backend. Most of the dates along with the corresponding information is displayed on the calendar except any events that fall on the 1st, 2nd and sometimes the 15th & 22nd of the month. The information shows perfectly fine in the list box at the bottom of the form.

Does anyone have a clue why this is happening and what I can do to fix it?

Any help is appreciated!

[ADezii]

1. Is there any chance that you can duplicate this problem with an Access Back End, with both Front and Back Ends in 2003 Format? This behavior is very strange to say the least, if the BE can be converted to Access 2003, then at least we may be able to tell whether it is a Coding or SQL Server issue.
2. If the conversion is not practical, can you Upload the Front End? I would like to specifically see the Code in the PopulateCalendar() and PoppulateEventsList() Procedures.
3. What Locale are you running the Database in? In certain locations, like the UK, the Date Format needs to be changed as indicated in Code Lines 26 to 30 below:
   

   Private Sub PopulateCalendar()
   On Error GoTo Err_PopulateCalendar
   Dim strFirstOfMonth As String, bytFirstWeekdayOfMonth As Byte, bytBlockCounter As Byte
   Dim bytBlockDayOfMonth As Byte, lngBlockDate As Long, ctlDayBlock As TextBox
   Dim bytDaysInMonth As Byte, bytEventDayOfMonth As Byte, lngFirstOfMonth As Long
   Dim lngLastOfMonth As Long, lngFirstOfNextMonth As Long, lngLastOfPreviousMonth As Long
   Dim lngEventDate As Long, bytBlankBlocksBefore As Byte, bytBlankBlocksAfter As Byte
   Dim astrCalendarBlocks(1 To 42) As String, db As DAO.Database, rstEvents As DAO.Recordset
   Dim strEvent As String
   Dim lngSystemDate As Long   'CFB added 1-25-08
   Dim ctlSystemDateBlock As TextBox, blnSystemDateIsShown As Boolean  'CFB added 1-25-08
   Dim strSQL As String        'Added 4/16/2008
   Dim lngFirstDateInRange As Long     'CFB added 2-18-10
   Dim lngLastDateInRange As Long      '
   Dim lngEachDateInRange As Long      '
   Dim strStartTime As String          '
    
   lngSystemDate = Date        'CFB added 1-25-08
   intMonth = objCurrentDate.Month
   intYear = objCurrentDate.Year
   lstEvents.Visible = False
   lblEventsOnDate.Visible = False
   lblMonth.Caption = MonthAndYear(intMonth, intYear)
   strFirstOfMonth = str(intMonth) & "/1/" & str(intYear)
    
   '*************************************************************************
     'ADezii
     'NOTE: Will work in the UK (United Kingdom) and other European Nations
     'strFirstOfMonth = "1/" & Str(intMonth) & Str(intYear)
   '*************************************************************************
   '...Code intentionally omitted