Calendar based on task

B

bionicman

Registered User.
Local time
Today, 08:06
Joined
Jul 22, 2003
Messages
145
As a continuation from this thread...

Currently, I have a list of programs, each program could be considered the "task". When the task is completed, I want the user to click the date on the calendar and have it grayed out. As I mentioned before I have this working correctly, but I have the calendar pulling up based on a query that relys on the program ID. No problem, that way each program has it's own calendar. Now I need to be able to have it pull up the current month if one exists, and if it does not exist create it. For instance it works fine now, but once we hit 1/1/06, it will still be the same calendar... I want it to create a new record, so I could theoretically look at the old calendar if need be.

I tried to tie my query to a month field as well, but to no avail.

Thanks for your help!
 
Hi again!

I think that I may still be a little confused, but tell me if this is along the right lines.

I would start with a form based on the Program ID. I would include the basic calendar on that form (maybe as a subform??). The calendar would always be set to display the desired month. The ideas that we discussed in the previous thread will work for any arbitrary date. You can include buttons on the calendar for Previous Month / Next Month. Clicking on the button would change the base date for the calendar and force the calendar to do a refresh.

As part of the refresh, the calendar VBA would need to run the query to check what days are already grayed out.

I may just be repeating what you already have tried. If so, please let me know what you are specifically getting stuck on and I'll try to help.

- g
 
OK...

I have a list of programs, when someone clicks on a program they are presented with details about the program (this form is called program_details). I have the calendar we just created on a subform on Program_Details. The subform (the calendar) is based on a query that looks at the program ID from the program details forms so it displays the correct calendar for the correct program.

Theoretically (sp?) I would need 12 records (remember what I said in the other thread about having other text boxes related to a table) for each program (one for each month). But I don't know how to base the current recordset of the subform (the calendar) off of both the program ID and the current month. Like I said before, I tried adding a month field, but could not get it to work correctly... and furthermore, If the current month does not exist, create it. So today is 12/14, it should look to see if the month of december exists... if it does, display that data, if there is no record for December, i need it to create one.

I hope this makes more sense.

Thanks
 
Okay, I think that I am getting it.

It sounds like you are trying to have a single record for each month (per ProgramID). Or have a family of records to cover every day of each month.

Instead, I think that I would approach this by having another table:

tblTaskCompleted
-------------------
TaskCompletedID - PK Autonumber
ProgramID - Number, FK to Programs table
DateCompleted - Date

The key difference here is that I would only store those dates where the task is completed, rather than all dates and a 1 or 0 to indicate if the task was done or not.

By doing an appropriate query you can select only those completion dates for a given task in a given month.

That way, for *any* month, you just display the data that is available. [vs. trying to decide if you need to create a record to display.] Think of the calendar as a moving window frame, and you just hold it in the right place and report what you can see.

The hard part about this approach is that I think that you will have to programmatically move through the query records to decide what dates to gray out. Actually it is not that hard, but may just be unfamiliar. There are also some details about how to add/delete records as changes are made to the month.

Am I getting closer? If so, let me know if this makes sense and we can see what it takes to make it work.

Otherwise, please help my tired brain understand better.

- g
 
Last edited:
I'm confused now :)

I have attached a copy of my database... maybe that will help us. Open the form "Program Center" then double click on a program name and you will see what I have going so far...

Feel free to tell me how horrible it is as well :)
 

Attachments

(Sorry, overwhelmed on this end, but I'll try to get back to this soon!)

-g
 
no problem, i appreciate the help, so take your time.
 
Sorry for the delay. Life was very complicated here for several days.

Okay, thanks for posting your database. [By the way, very nice clean looking interface!) As I thought, you are creating a record for each month and each program. While you could in theory make this work, it goes against the rules of good database design. [I.e. eliminate columns that duplicate similar information.]

In this case, your tblCalendar has very simiilar information (whether a task was completed) in different columns which just indicate which day. In properly designed table, all information should be in the value of the field(s), not the position of the entry.

So, I would change the design of the completion information. As I noted before, the table would look like -

tblTaskCompleted
-------------------
TaskCompletedID - PK Autonumber
ProgramID - Number, FK to Programs table
DateCompleted - Date

Notice that each day's completion is a separate record. But think how easy it would be to check this table to see if a task is completed on a specific day vs. your calendar table.

So it is very easy to build a query that will look up a specific ProgramID, Task, Month and year, and get a list of records that show when the task is completed. The slightly harder part is how to build this into the form so that it can be displayed.

You first have to learn how to create a recordset in VBA. It's not all that hard to do, but you have to learn the correct syntax. Once you have a recordset that contains the list of records, then we just step through them and figure the day number and then gray out that box on the calendar. To make this easier, I would include an extra field in the query that calculates the day.

I'm being quite general here but have included a sample db that illustrates much of this approach. See the frmPrograms for how it all comes together. There are comments in the code, but please feel free to ask questions about things you don't get. There is also a query that illustrates what the basic recordset looks like too.

There is still a big piece that I haven't addressed - once you have all this, how do you enter new completion dates? In broad terms, I would let the user click on the calendar to select/deselect dates (think of the form working as a *temporary* table like your Calendar table). Then when the user closes the form, the VBA would run through all the selected days and generate the appropriate records. There are a few details that take extra work, like making sure there aren't duplicates.

Anyway, take a look at the attached and see if it makes sense and we can move forward!

- g
 

Attachments

Thanks for replying, and thanks for including a sample (i'm more of a visual person :) )

I just had a quick look, and it looks straigt foward. Give me a couple days or so to really dig into it, and I will post back with questions, or how it went :)

Thanks again!
 
Alright, i took another peek at it, and it all makes sense except this part:

There is still a big piece that I haven't addressed - once you have all this, how do you enter new completion dates? In broad terms, I would let the user click on the calendar to select/deselect dates (think of the form working as a *temporary* table like your Calendar table). Then when the user closes the form, the VBA would run through all the selected days and generate the appropriate records. There are a few details that take extra work, like making sure there aren't duplicates.
Click to expand...

I want to be sure I understand this correctly (I am not the greatest with the VBA :) ). The data from me clicking on the calendar would just be held temporarily somewhere until the form is closed right? So does that mean that each time the form is closed it would re-write each date to the table? Wouldn't that run pretty slow if it has a lot of date to write? Or when you said it would need to check for duplicates, basically if that date is already in the table it would skip it and go to the next one? Again, I would think that writing to the table each time it is closed would make the DB run pretty slow, but as I said before, i'm not too great with VBA, so I am only speculating.

In anycase, could you show me an example of this at work, or at least set me in the right direction so i can stumble through it?

Thanks!
 
Great! Sounds like you are getting the hang of this stuff.

I'll outline my thinking on how to handle the remaining piece, although I'm sure that there are other approaches (and no doubt better).

1. When I say that the data from the calendar is "stored" temporarily, that could be as simple as the status of each textbox. So you can "store" the user clicks on the date by just toggling the background color. However, I would probably want to store the initial status and current status of the text boxes to minimize the amount of writing to the database see below. I would probably use an array of integers to do this. (An array declaration looks like "Dim MyArray(10) as Integer" )

2. You certainly could write each record into the database as the date is clicked. However, in general, it is good to try to reduce the amount of traffic into/out of the db. I'm also thinking that some users might click on one date, realize that it is an error then "unclick" it and click the correct one.

3. I'm certainly no expert here, but my feeling is that you would have better performance doing a small batch of records at the end of the interaction, than saving each change. However, unless you are designing this for a large multi-user environment, either method would probably be adequate. Someone else might be able to give you better advice, but I'd try whichever you are comfortable with and see how it performs.

4. I would try to avoid duplicates in the DateCompleted table, it just seems cleaner to me. Although with a little thought, you might be able to permit them and not run into major problems. Remember, you have to save the information each time you load the calendar - or switch between months - or move to a different task - or move to a different Program record - or close the form.


So here is the basic approach that I would use -
a) When you perform the query to determine which dates are grayed out, store the status of each day in the month in an integer array, called OriginalStatus().

b) Let the user muck about and click dates on/off to their content.

c) Before you leave the calendar (for a different month, different task or different program), trigger an event to save the status of the dates.

d) Loop through each day of the month and compare the status of the text box (gray or white) with the OrginalStatus array. If the status is the same, no change. If the status is different, then either add a new record (Text box clicked, but OriginalStatus is zero) or delete an existing one (Text box unclicked, and OriginalStatus is one).

There may be a little work in trapping out all the ways that a user can leave the calendar. Switching months or tasks use controls on the form which already have VBA code associated with them. There is supposedly an OnRecordExit event, but I'm not clear how to set it. Check the documentation. One work around would be to eliminate the record selectors on the bottom of the Program form and implement your own record navigation buttons, but then you start having to duplicate some of the built in functions.

One more thing, rather than have 42 OnClick events for the day boxes, I would probably overlay a transparent button and convert the x and y of the mouse click into a number that represents the correct box. You can then use the same approach that we used for setting the day numbers to address the appropriate text box and set the background color.

Good luck with things and post back if you have questions!

- g

=======================

Note regarding OnRecordExit !!

I did some additional looking and found out that OnRecordExit appears in the help system, it is not actually available! So, you would need to find some other means. There is supposedly a discussion of how to do this at the following site:
http://support.microsoft.com/default.aspx?scid=kb;en-us;304139

However, I haven't looked at it or tried it to see whether it works.


On
 
Last edited:
ok, lots of questions, sorry but like i said before i am not that good with VBA, I don't understand a lot of it... mainly the syntax, I know what I want it to do, but i don't know functions and things well enough to tell access how to do it :)

1. so I am going to use an integer array to store the status of the text box (whether it is gray or white) when I enter the form. The only example I could find in the help file was this:
Code: 
Dim MyWeek, MyDay
MyWeek = Array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")
' Return values assume lower bound set to 1 (using Option Base
' statement).
MyDay = MyWeek(2)    ' MyDay contains "Tue".
MyDay = MyWeek(4)    ' MyDay contains "Thu".

Am I barking up the right tree here? I don't understand how I would get a property value into the integer array, it looks like I would have to list out the colors, then decide which one is which.

When I leave the form, I am going to store the values again, then compare them with the first values I stored (with a loop similar to what was created for the calendar), if there is a change, make a new record, if not, do nothing... is that right?

If so, then i understand what needs to happen, but like i said above, i don't know how to tell access what to do :) Anyway you can share some sample or example code?

2.
One more thing, rather than have 42 OnClick events for the day boxes, I would probably overlay a transparent button and convert the x and y of the mouse click into a number that represents the correct box. You can then use the same approach that we used for setting the day numbers to address the appropriate text box and set the background color.
Click to expand...

How do i capture the x and y value or where the mouse is? I have never done anything like this before.

Thanks again!
 
yes, that's my problem too! I know what I want to do, but Access just won't do it :)

Again, my comments are just one suggested way to approach this, you might have some other ideas that work even better.

Basically, your questions (and barking) is spot on. I think you just need a little code to hang it all on...

1. Arrays
I would not exactly store the properties of the text boxes, just a flag to let you know whether it is grey or blank. You could use yes/no or true/false, but I would just use integers and 0 for grey (not completed) and 1 for blank (task completed).

When you first load the records from the query, you first need to initialize the array (basic programming, never assume that variables have a certain value unless you put that value there).
Code: 
    Dim InitialValue(31) As Integer
    Dim Index As Integer
    
    ' Initialize array of Initial values
    For Index = 1 To 31
        InitialValue(Index) = 0
    Next
Then, in the part of the code that shades the boxes, I would add this statement:
Code: 
        ' Shade the box
        ctl.BackColor = constShaded  ' Already existing code
        InitialValue(DayIndex) = 1    '  <= add this to set the initial value array
Note: I am using a different array index (DayIndex) here, because that is how I wrote this part of the loop in my earlier example. You may need to adjust to fit your code.

After this loop (the one that sets the background colors) is finished, you should have 0's and 1's in the InitialValue array corresponding to those colors.

2. Exiting the calendar
When you exit the calendar, you will have a loop that will go through all the days of the month and compare to the InitialValue array:
Code: 
    Dim DayIndex as Integer
    While (Month(Me.txtCalendarHeading + (DayIndex - 1)) = Month(Me.txtCalendarHeading))
        TextBoxIndex = DayIndex + Weekday(Me.txtCalendarHeading) - 1
        strNum = Right("00" & TextBoxIndex, 2)
        Set ctl = Me("CalDay" & strNum)
        
        if (ctl.BackColor = constUnshaded) AND (InitialValue(DayIndex) = 1) THEN
              ' Was set (completed) but now isn't - DELETE Record
              
              <add code to delete record>
        end if

        if (ctl.BackColor = constShaded) AND (InitialValue(DayIndex) = 0) THEN
              ' Wasn't set (uncompleted) but now is - ADD Record
              
              <add code to add record>
        end if

    Wend
Obviously, there is additional work needed in adding and deleting records. [And, I am still not quite certain what to do about the record navigation issue. Oh well, one thing at a time]

3. Mouse Clicks
Check out the MouseDown event in the documentation:
http://msdn.microsoft.com/library/en-us/vbaac11/html/acevtMouseDown_HV03079794.asp
Note that this event will provide the X and Y coordinates of the mouse. The trick is converting the X and Y coords into the day number.

Take a look at this and see if it makes sense. I'll see if I can expand my example to include some additional code.

- g
 
Okay, attached is an expanded example that now includes
a) You can click on any date on the calendar and it will gray the appropriate box
b) If you use the next month / previous month arrows, it will add / delete the proper records.

Comments
1. Mouse Clicks - I used the MouseDown event to catch the clicks, and then translate the X & Y into row and column. These are used to figure out the text box. Note: you can click (and set the color) of non-number (blank boxes). While you ultimately would want to trap these out, it doesn't affect the record keeping, because the code only looks at the actual days of the month.

2. Array / Initial Values - Doh! I hate it when I get dumb. The difficulty with using an array is that you would be accessing it from different procedures. It can be done, but I don't like to do it that way. So, instead I just made a duplicate set of boxes called Initial01, Initial02, etc. When the calendar loads, I just set the shading to be the same as the real calendar box. However, I don't update the InitialXX box on mouse clicks. So these boxes are our "memory". Note for final application, you can just set the property for the InitialXX boxes to invisible and the user never knows they are there!

3. Saving Changes - As noted, the only time this example saves the changes to the calendar is when you navigate to the next month / previous month. However, as we discussed, you really need to also check whenever the ProgramID changes, the Task changes, the form closes, etc.

4. Error Checking - I did very little error handling in this code. Nor testing. So it is strictly "as-is" and no guarantees! But hopefully it serves as an example (good or bad!).

- g
 

Attachments

Gromit I can't thank you enough!!! The example is exactly what I needed! After looking at the code, there is no way I would have figured that all out :) I have learned a lot just in reading through it though, that way i know for next time. So again, thank you and Merry Christmas!
 
You must log in or register to reply here.

Similar threads

Z
Calendar Control
2
Replies
22
Views
649
Pat Hartman
P
C
Automated Production Planner
Replies
11
Views
588
GPGeorge
GPGeorge
A
Calendar events but not in Outlook
Replies
13
Views
506
Pat Hartman
P
W
Hello, New here
Replies
2
Views
257
DHookom
DHookom
Dick7Access
Goggle Calendar
Replies
1
Views
331
Dick7Access
Dick7Access

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom