calendar question?

[Guest]

I have a form that displays different tasks that are to be completed either
daily, weekly, monthly, or quarterly.... Each task has a check box beside it,
if they check the checkbox that means they completed the task. I need to
somehow reset (or uncheck) the checkboxes at the beginning of every day (for
the daily tasks), beginning of every week (for weekly), beginning of every
month (for monthly), and beginning of every quarter (for quarterly). Any
ideas on how to easily accomplish this? I'm using visual studio/visual basic
but its very similar to VBA, so any help will be greatly appreciated. Thanks
in advance!

 

[Arvin Meyer [MVP]]

Either build a recordset and update it:

Set rst = db.OpenRecordset("YourTable")

With rst
.MoveFirst
Do Until .EOF
If !Fieldname = True Then
.Edit
!Fieldname = False
.Update
.MoveNext
Loop
End With

rst.Close
Set rst = Nothing

or use an Update query:

UPDATE MyTable SET FieldName = False WHERE FieldName = True;

 

[Guest]

Thanks for your response! I know how to update it... but how should I go
about checking for the day, week, month, quarter? For the week it has to be
business week...like Monday through Friday. Should I just have something in
the form load event to check each of these? How would I do that? Thanks!

 

[Arvin Meyer [MVP]]

Does it matter? If a job has been marked complete, it's complete. If your
users are marking them complete before they are, you need to stop that as a
training issue. The code you already have to create a new task is what you'd
use to generate that task.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

No its not that.... the datagrid view that has the tasks displayed comes from
a table called tasks... it will always be the same. It has a checkbox in the
last column. Whenever they check the checkbox it adds that record along with
a few more columns (date, time etc...) to a table called completed tasks. So
I need the Task table to reset and uncheck the checkboxes every day, week,
month, or quarter, depending on what frequency that task is assigned to.
Thanks for your help and any suggestions.

 

[Arvin Meyer [MVP]]

1. Are you saying that the task may not be complete when they check the
checkbox? And that writing to the completed task table may be premature?

2. Are they checking a weekly checkbox the first day, and you don't want
them to do it again until next week?

If the tasks are always repetitive. I suggest that you use a task table with
the future date, so that the check mark, if present, is locked until that
date. Then you could do something like:

UPDATE MyTable SET FieldName = False WHERE FieldName = True AND
FutureDateField <= Date();
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

No they aren't checking the check box early or late, and it being locked is
not the issue. But I have a task table that has a checkbox column at the
end. That is what is being shown in the datagrid view, for example for each
person their tasks will come up. When they check the checkbox that row is
being added to a completed task table along with the date and time. I need
to reset the checkboxes from the tasks table, for example if today is
wednesday, thursday when they open up their daily tasks the checkboxes need
to reset and become unchecked. Then for their weekly tasks, the next week
the checkboxes need to reset in the tasks table for the weekly tasks. When
these tasks are checked the first time they are added to the completed tasks
table (whether its premature or not doesn't matter), but I just need the
checkboxes in the general tasks table which has all the tasks to reset.

 

[Arvin Meyer [MVP]]

There are 2 ways to handle unchecking the box. The first is to write the
record to the table, then uncheck in the same code. The drawback to this is
that computers are so fast that the user may think that he/she hasn't
checked it and try doing it again. That's handled by locking the check box
after unchecking it. Still, it's not user-friendly.

The second method is to run an update query to uncheck the boxes. It does
not matter if it's day. eek. month, etc. If a box is checked, the record of
completion has been written, and the box must be unchecked. You cannot reset
the checkboxes from the table (except manually, one at a time) because
Access cannot run functions from a table. No user should ever even see a
table anyway. You can use a datasheet view of a form, to run the update
query code, but it is better written from an event at the end of the day,
like when the last user closes his copy of the database.

Again, you seem to think that a calendar is involved, but based on the
information you have provided, a calendar is unnecessary. EVERY check box
must be reset when the task is completed and checked. It doesn't matter when
it's completed. The only time a calendar might come into play is if you are
trying to generate the next start date and are building a NEW set of empty
tasks. For that you need a set of date functions. Here are some on the MS
Knowledge Base:

http://support.microsoft.com/kb/88657/en-us
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

Thanks for your time and responses I think I've figured out the best way to
do this. The reason the subject is "calendar question" is because I thought
it would involve some date functions. I don't want the check box to be
unchecked immediatley because the users maybe need to look back throughout
the day to see what daily tasks aren't complete yet and which ones are.
Here's the solution that i've come up with for the daily check boxes... I
have this in the form load event.

day1 is todays day of the month...

rowcount = RoutineDataSet1.CompletedTasks.Rows.Count
rcount = rowcount - 1

'Checking the date of latest row in completed tasks table and resetting the
checkbox if that date isn't the same as today

For r = rcount To rowcount

day2 = RoutineDataSet1.CompletedTasks.Rows(r).Item(6)
day3 = day2.Day


If day3 = day1 Then
Exit For
End If
Next


'Resetting Daily Tasks
If day3 <> day1 Then
Me.TRoutineTableAdapter1.Fill(Me.RoutineDataSet1.TraderRoutine)
rowcount2 = RoutineDataSet1.TRoutine.Rows.Count

rcount2 = rowcount2 - 1
For r2 = 0 To rcount2
If RoutineDataSet1.TRoutine.Rows(r2).Item(4) = "Daily" Then
RoutineDataSet1.TRoutine.Rows(r2).Item(6) = False
End if
Next
Me.TRoutineTableAdapter1.Update(Me.RoutineDataSet1.TRoutine)
End If