Thanks, I'm going to have to put all this info to use and see what I come up
with. I'm sure that I will have several attempts before I put the right info
in the right spot. Thanks for helping me out. I'm sure I'll have more
questions later. Really appreciate it.
Freehal04
:
Hi,
The Sendobject method will be fine then...you can just attach the query and
it will show everything.
About the due date...as said in my initial reply you do NOT want to store
the due date...you should only have ONE date/time field which records the
initial date (whatever that might be...maybe initial date of entry, or order,
or...).
You then calculate the due date at runtime in a query based on the initial
date.
This will of course produce dates which differ from each other just
depending on what the initial date was.
If any of the dates fall within the period specified within the criteria it
should pick them up. So there is no need for more the one query. This
somewhat relates back to the table structure issue I mentioned before so make
sure you are not storing any calculated values.
HTH
Good luck
--
Oli-S
Admin Specialist & Computer Science Major @ UMD - Go Terps -
http://www.oli-s.de
:
Sorry I wasn't clear about my date issue. An example would be best: say 5
entries have a due date of 12/15/06 and 6 entries have a due date of 1/20/07
and 10 entries have a due date of 1/31/07. Does that mean I have to run 3
different queries to correspond to the three different due dates?
As for the emails, I don't really need them to be to extravagant, just
enought to let the Project Manager know that his reports are due. If the
attachments can hold that date for each entry that would be fine, if not then
I can use that other method you pointed out. I'll have to play with it and
see which works best.
Thanks for the links. I'm gonna play with them and see what I come up with.
Freehal04
:
About the email part...as earlier mentioned there are many different
approaches.
One would be to use the Sendobject method. It is a build in supported method
and allows you to interact with MS outlook. Its whole syntax can be found
here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac10/html/acmthactSendObject.asp
However, it is somewhat limited. It can only send plain text emails, only
include internal db objects as attachments and only send one attachment at a
time. If none of this matters to you then go ahead and use it in the spot
specified of the code I gave you earlier.
If not then look for some more flexabile solutions e.g.:
http://support.microsoft.com/kb/209948/EN-US/
With your other question I'm not sure what you mean. Do you mean that each
day entered will have a different due date calculation?
You could add a new field to the table which holds the duration amount and
then one which holds the type of duration as well if you want. This way you
can still just use one expression with the DateAdd() function to calculate
the due date in the query e.g.:
DueDate: DateAdd([WhatDuration],[DurationAmount],[OriginalDate])
If your application will really be as complex as you said you might want to
make sure your data model is correct. A properly normalized data structure is
most important since everything will be based on that and you do not want to
rebuild the application some time down the road just because the model was
wrong initially.
HTH
Good luck
--
Oli-S
Admin Specialist & Computer Science Major @ UMD - Go Terps -
http://www.oli-s.de
:
Wow, freakazeud,
Thanks for that indepth answer. It really gives me something to go on.
It's looking like this db will be very extensive, meaning a large number of
fields and entries. I was thinking the more the db can do on its own the
better. I realize it might be harder initially to get working, but it would
be better in the long run. One problem I see it this, each entry more than
likely won't have the same date, although some will coincidentally have the
same date, there will be many dates to track. It sounds like from what you
are saying I'll need a query for each up coming date to have the db calculate
the 10 day alert email. Which would mean that I'd have multiple entires and
multiple queries. Am I understanding that right?
Also, with the emails, is it necessary to import a program to cover that or
is Access capable of doing that on its own. I'm not very good at writing
code. It at all possible I'd like to have Access tell Outlook to send the
message, but again writing and inputing the code in the right spot is
something I'm not really good at yet. I really appreciate the help you've
given me so far. Any further assistance would be GREATLY appreciated. If we
can figure this out, I'll really have something to WOW my boss. Thanks.
Freehal04
:
Hi,
you should be able to put something together.
I'm not sure if you want this to be triggered manually (e.g. check if emails
need to be send every time someone opens the application) or automatically
(schedule the check with some scheduling software periodically).
Either way it should be a somewhat similar approach. You would need to
create a query based on your table which holds the original date, which you
are going to use to calculate the due date. Within this query create a new
expression to calculate this due date e.g.:
DueDate: DateAdd("m",3,[OriginalDateField])
This expression utilizes the DateAdd() function and calculates a due date
off 3 month after the original date in the OriginalDateField. You can
recognize that we are calculating this value at runtime. There is hardly ever
a scenario where it is advised to store calculated values...they change
constantly based on their components, so storing them would only create old
redundant data.
You can now limit the records returned by this query using the criteria area
of this new expression e.g.:
BETWEEN DateAdd("d",-10,Date()) AND Date()
The query should now only return records which are going to become due in
the next 10 days.
Now you are done with this part...all that is left is to figure out if the
query holds any values at any point and send an email if so. To do that you
can use a Dcount() function e.g.:
If Dcount("*","YourQueryName") <> 0 Then
'there will be records due, so sent an email
Else
'nothing will be due, so do nothing
End If
Now you need to think about what I said in the beginning...if you want this
to happen when a user open the application manually then use the code on the
first form's on open event. If you want to automate it then create a function
in a new module which does all this. Then call this function with the RunCode
action within a macro, make sure the last action of the macro is to shut down
the application. You could then use a batch file to call this macro with the
macro command line switch and schedule the batch file with the Windows
Scheduler or any other scheduling software to execute whenever you want it to
run.
The only part left of the above given code is the emailing part. There are
again many options...the simplest, but also most limited is the SendObject
method. Others would be to use automation to create the Outlook message. It
really just depends what you are trying to achieve.
This should give you a good start and base of where you need to go. If you
hit any walls let us know and we can try to help you further.
HTH
Good luck
--
Oli-S
Admin Specialist & Computer Science Major @ UMD - Go Terps -
http://www.oli-s.de
:
Is it possible to have Access generate an email or prompt Outlook to generate
an email? I want to have an Access field that monitors due dates and then
send out an email 10 days prior to the due date. I am new to Access and I
don't even know if this is possible. If there is a way, I need detailed
intstructions. Thanks
