Need Help with programming an 'auto scheduler'

[KAitchison]

So I have maybe an unusual question but here goes, thank you to anyone who
can help me

So part of the database I'm designing is kind of a task scheduler, you can
input data for things that need to be done, search them, when you start up it
tells you what needs to be done for the day etc..

My boss wants to simplify the data entry a little bit but I dont really know
how to do this:

So what he wants is for you to be able to enter a starting date, then a
frequency, then have the database add to the start date for a specified
period of time and make new records until a specified end date..

For example:
Task: Eat Lunch (and other info specific to the task)
Start Date: Sept 28, 2009
End Date Oct 2, 2009
Frequency: Every 2 days

so what he wants to happen is that when you submit this data, the database
will put in a record for sept 28, sept 30, and oct 2 into the table

(table headings are: ItemID, Item, Location, Date Assigned, Date to be
Completed, Comments, etc)

Personally I really dont have any idea how to go about programing this??
does anyone have any ideas??? or links to information on the web that I could
use?? I really appreciate the help.

Krista

 

[Marshall Barton]

So I have maybe an unusual question but here goes, thank you to anyone who
can help me

So part of the database I'm designing is kind of a task scheduler, you can
input data for things that need to be done, search them, when you start up it
tells you what needs to be done for the day etc..

My boss wants to simplify the data entry a little bit but I dont really know
how to do this:

So what he wants is for you to be able to enter a starting date, then a
frequency, then have the database add to the start date for a specified
period of time and make new records until a specified end date..

For example:
Task: Eat Lunch (and other info specific to the task)
Start Date: Sept 28, 2009
End Date Oct 2, 2009
Frequency: Every 2 days

so what he wants to happen is that when you submit this data, the database
will put in a record for sept 28, sept 30, and oct 2 into the table

(table headings are: ItemID, Item, Location, Date Assigned, Date to be
Completed, Comments, etc)

Personally I really dont have any idea how to go about programing this??
does anyone have any ideas??? or links to information on the web that I could
use??

Adding records (using a recordset) is easy enough to do, the
difficulty come from figuring out the start day and
frequency. If the start day is always a date and the
frequency is always a number of days, no problem, but when
the start day and frequency are things like:

Second Monday Every month
Last Thursday in Novenber Every year
Wednesday Every other week
Last workday Second week every month
etc.
then you have an extremely advanced, complex problem.

In other words, your example does not provide enough
information to figure out what you need to do.

Note: if you do not have to retain information about what
was done on the designiated days, then it may be better to
calculate the next event date instead of creating records
for the next several months/years.

 

[Jeff Boyce]

That sure sounds a lot like what Outlook does... any reason not to be using
Outlook instead of reinventing this wheel?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KAitchison]

thank you for your help... i have no experience in these type of things..

the start date will always be a date.. and the frequency can be a number of
days.. thats not a problem... is it possible to relate some words eg biweekly
to like 2 days after the start date... so you add 2 and 2 and 2 and so on for
the specified time

unfortunaly we do need to keep all records.. it would make sense to use
outlook but this will be taken out to sites on a small tablet.. so we want it
to be mostly self contained

 

[Marshall Barton]

I do not understand your biweekly example, but it feels like
a camel's nose under the tent of the complex things I
mentioned earlier ;-)

If your question is how to write the code to add the
records, then it could look something like this air code:

Dim rs As Recordset
dim dt As Date
Set rs = CurrentDb.OpenRecordset("tasktable", dbOpenDynaset)
dt = Me.txtStart
Do Until dt > Me.txtEnd
rs.AddNew
rs!taskfield = Me.txtTask
rs.datefield = dt
rs.Update
dt = DateAdd("d", Me.txtFrequency, dt)
Loop
rs.Close : Set rs = Nothing

 

[KAitchison]

let me clarify.... so hopefully this makes sense...

on the form they fill in the following information:
task name
start date
end date
and frequency

the frequency we want to be as simple as possible.. so they would have a
choice of values here... eg if they chose biweekly could we program it so
when they click submit the database creates the origional record and then
creates one depending on what 'number of days' value we choose to assign
'biweekly'....

so lets say:
task = have lunch
start date= sept 29
end date = oct 2
frequency = biweekly..... where biweekly=every 2 days

when you click submit.. the database then makes a record for sept 29, and
oct 1...

does that make sense...

i am not a programmer in any sense of the word.. infact i'd put my self more
as the anti programmer.. lol.. just thinking about this gives me bit of a
headache...

and i really appreciate your help/ideas...

Krista

I do not understand your biweekly example, but it feels like
a camel's nose under the tent of the complex things I
mentioned earlier ;-)

If your question is how to write the code to add the
records, then it could look something like this air code:

Dim rs As Recordset
dim dt As Date
Set rs = CurrentDb.OpenRecordset("tasktable", dbOpenDynaset)
dt = Me.txtStart
Do Until dt > Me.txtEnd
rs.AddNew
rs!taskfield = Me.txtTask
rs.datefield = dt
rs.Update
dt = DateAdd("d", Me.txtFrequency, dt)
Loop
rs.Close : Set rs = Nothing
--
Marsh
MVP [MS Access]

thank you for your help... i have no experience in these type of things..

the start date will always be a date.. and the frequency can be a number of
days.. thats not a problem... is it possible to relate some words eg biweekly
to like 2 days after the start date... so you add 2 and 2 and 2 and so on for
the specified time

unfortunaly we do need to keep all records.. it would make sense to use
outlook but this will be taken out to sites on a small tablet.. so we want it
to be mostly self contained

 

[KAitchison]

i think i owe you a giant hug.. thank you so much for your help.. ...

I do not understand your biweekly example, but it feels like
a camel's nose under the tent of the complex things I
mentioned earlier ;-)

If your question is how to write the code to add the
records, then it could look something like this air code:

Dim rs As Recordset
dim dt As Date
Set rs = CurrentDb.OpenRecordset("tasktable", dbOpenDynaset)
dt = Me.txtStart
Do Until dt > Me.txtEnd
rs.AddNew
rs!taskfield = Me.txtTask
rs.datefield = dt
rs.Update
dt = DateAdd("d", Me.txtFrequency, dt)
Loop
rs.Close : Set rs = Nothing
--
Marsh
MVP [MS Access]

thank you for your help... i have no experience in these type of things..

the start date will always be a date.. and the frequency can be a number of
days.. thats not a problem... is it possible to relate some words eg biweekly
to like 2 days after the start date... so you add 2 and 2 and 2 and so on for
the specified time

unfortunaly we do need to keep all records.. it would make sense to use
outlook but this will be taken out to sites on a small tablet.. so we want it
to be mostly self contained

 

[Marshall Barton]

Well, it makes sense, but it's an unneeded complexity when
setting frequency to 2 will accomplish the same thing.

The air code I posted before should do what you want when
frequency is set to a number of days.

 

[David H]

Agreed. There is a great deal that can be done in Outlook with VBA in and of
itself. Not to mention that you can build on existing functionality such as
having a task reoccur every [x] number of days or every Monday or whatever.