HELP WITH AUTO EXE PROGRAMMING/CODE

[Liam.M]

Hey guys,

Forgive me if my question my be alittle silly, but I would very much
appreciate and assistance that could be given!

My situation is as follows:

I have created a Button, and set it's "On Click" Event proceedure to
Loop through my Database and find any records that fall within a
Certain Date...if a record is found...it then emails me that a record
has been found and tells me to check the Database....

Now....I would like to fully automate this process...as the database is
not always used...on a daily basis or even on a weekly basis..... and I
would therefore like to ensure that this process is at least performed
on a weekly basis, is there some manner in which I can achieve
this.....please advise!

I understand that I can set-up and auto-exe process to open the
database...but how can I programme the database to "run" the on click
event proceedure that contains this code...do I have to set it as a
moduel...and create a macro to run when the database is executed or
what?

[David W. Fenton]

Li****@awamarin e.com.au wrote in
news:11******** **************@ u72g2000cwu.goo glegroups.com:

Also...if you have any idea...I'm going to guess not....no one has
been able to provide assistance as yet

You've been given the answer, but you appear to lack the knowledge
and experience to understand it.

That is not the fault of the people giving you the answer.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Liam.M]

Apologies GENTLEMEN....th at sentence has been taken completely out of
context...I do apologize....an d yes you are very correct, I do lack the
experience and knowledge....an d I am ALWAYS very grateful for the
assistance I am provided by very kind people,such as yourselves, who
spare their time to answer and help me with my questions, however, I do
sometimes get a little bit frustrated, so please do not take it
personally, once again thankyou for taking the time to answer my
threads!

Kind Regards,

Liam

David W. Fenton wrote:

Li****@awamarin e.com.au wrote in
news:11******** **************@ u72g2000cwu.goo glegroups.com:

Also...if you have any idea...I'm going to guess not....no one has
been able to provide assistance as yet

You've been given the answer, but you appear to lack the knowledge
and experience to understand it.

That is not the fault of the people giving you the answer.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[pietlinden]

passing data to a subroutine...

I modified Danny Lesandrini's code a little.... I should probably have
declared the arguments as variants so I could pass a null value, but
anyway...

query:

SELECT ShipsInformatio n.[SBMA Number], ShipsInformatio n.[Vessel Name],
ShipsInformatio n.[IMO Number], ShipsInformatio n.[Date of Issue],
ShipsInformatio n.RecordID, ShipsInformatio n.[Date of Attendance],
DateAdd("yyyy", 1,ShipsInformat ion.[Date of Issue]) AS [Due Date],
ShipsInformatio n.EmailAddress
FROM ShipsInformatio n;

Personally, I would modify the SQL statement to filter out all the
records I am not interested in.

IOW, add a valid WHERE clause to your query...

SELECT...
FROM...
WHERE DueDate <=DateAdd(Date, "d",-5)

(all DueDates that fall within the next five days).

Then you would do something like

'===I stole this in its entirety from Danny's website. (cited
previously)
' and then modified it, so if it doens't work, that's my doing.

' Next function reads user entered values and
' actually sends the message

Public Function SendMessage(byv al strRecip As String, _
byval strSubject As String, _
byval strMsg As String, _
byval strAttachment As String) As Boolean

On Error Resume Next

If Len(strRecip) = 0 Then
strMsg = "You must provide a recipient."
MsgBox strMsg, vbExclamation, "Error"
Exit Function
End If
' Assume success
fSuccess = True

If GetOutlook Then
Set mItem = mOutlookApp.Cre ateItem(olMailI tem)
mItem.Recipient s.Add strRecip
mItem.Subject = strSubject
mItem.Body = strMsg

If Len(strAttachme nt) > 0 Then
mItem.Attachmen ts.Add strAttachment
End If

mItem.Save
mItem.Send
End If

If Err.Number > 0 Then fSuccess = False
SendMessage = fSuccess

End Function
sample call:
blnSuccess=Send Message("jd**@a bc.com", "subject: notification", _
"Your membership is about to expire!",
"C:\somefolder\ reminder.doc")

so assuming you wanted this done without opening a form at all, you
could do something like this:

Public Sub SendNotificatio ns()
dim rs as dao.recordset
dim qdf as dao.querydef

set rs=dbengine(0)( 0).openquerydef ("qryNotificati ons")
do until rs.EOF
rs.Edit
blnSuccess=Send Message("jd**@a bc.com", "subject: notification", _
"Your membership is about to expire!",
"C:\somefolder\ reminder.doc")

rs.Fields("Mess ageDatestamp")= Now()
rs.Update
rs.MoveNext

loop

rs.close
set rs=nothing

end sub

[Liam.M]

Thankyou so much for your assitance/ reply....very much appreciated...I
have taken your advice and constructed a new Query, which only shows
the records I am interested in (and thus excludes all of the
others)...there fore this query only shows the records that are due
within the next two months...I fine tuned the code, through the very
professional assistance of John Vinson:

WHERE ShipsInformatio n.[Date of Issue] Between DateAdd("m",-12,Date())
And DateAdd("m",-10,Date());

I have a few questions regarding your automation of the email
process...if you could spare the time to help me further...it would be
very much appreciated:

Now that I have created this separate Query that only shows the records
I want....I now no longer need to loop through the records to see if
they fit within a certain criteria, do I? All I need to do now is send
an email to ALL the records in this query...

THe easiest manner in which to do this I assume...would be an onload
event proceedure that emails to all the record shown in the query....
How do I command the database to do this...loop through all of these
records?
And How can I pass the Fields into the body of the email...i.e details
pertaining to that particular record?

Please forgive me...as I am relatively new to all of this...so any
assistance provided would be much appreciated.... .

Kind Regards,

Liam

[pietlinden]

I answered this in my response to your other post... basically, you
open a recordset based on the query (instead of the table) and then
loop through them and process.

Essentially, I created a function to collect the values from the query
into a single delimited string. then I just assigned the result of
that function to the .Body property of the message. (So the function
just does the "collecting " of records, and then your message routine
just deals with the sending the message.

Now that I have created this separate Query that only shows the records
I want....I now no longer need to loop through the records to see if
they fit within a certain criteria, do I? All I need to do now is send
an email to ALL the records in this query...
I'm confused. I thought you were mailing them to yourself... at any
rate, you'd loop through the recordset, grab the individual e-mail
address, and pass that to your SendEmail function.
THe easiest manner in which to do this I assume...would be an onload
event proceedure that emails to all the record shown in the query....
How do I command the database to do this...loop through all of these
records?
And How can I pass the Fields into the body of the email...i.e details
pertaining to that particular record?

Please forgive me...as I am relatively new to all of this...so any
assistance provided would be much appreciated.... .

Kind Regards,

Liam

I think I answered the rest of this in the other message. Just for
clarification. Are you:
A. Sending the SAME message to all the recipients (in which case you
can BCC them all).
OR
B. Sending a *custom* message to each recipient
?

The difference is small, but significant, because in the first case,
you create the message once, and in the second once *for each record*
(inside a loop)

hope this helps,

Pieter

[Liam.M]

Hey Pieter,

thankyou so much for your reply...your answers are always very
enlightening and helpful...and more importantly helping me move closer
and closer to finishing this project...yay!

I would like to be able to customize each email to these
records....plea se note, however, that these emails are only being sent
to MYSELF as reminders, not different recipients for each
records...just to myslef...howeve r, for each email I would like to be
able to specifically mention which record it is that I am being
reminded about....I would like it to be able to pass, for example,
three to four fields from each of the records into Body of the
email....(i.e the "Vessel Name", the "IMO Number", the "Date of Issue"
and Perhaps the "Short Description") fields....

Kind Regards,

Liam.

[jahoobob via AccessMonster.com]

If the table is not automatically updated by say an instrument passing on
readings, which I don't believe it is since it is about ships, then someONE
has to update it.
If that is the case then you don't have to "run" it unattended. Get the db
to send the email everytime the table gets changed. Put the code/macro that
checks the table in an event (such as Afterupdate of the last field) in the
data input form!

Li****@awamarin e.com.au wrote:

Hey guys,

Forgive me if my question my be alittle silly, but I would very much
appreciate and assistance that could be given!

My situation is as follows:

I have created a Button, and set it's "On Click" Event proceedure to
Loop through my Database and find any records that fall within a
Certain Date...if a record is found...it then emails me that a record
has been found and tells me to check the Database....

Now....I would like to fully automate this process...as the database is
not always used...on a daily basis or even on a weekly basis..... and I
would therefore like to ensure that this process is at least performed
on a weekly basis, is there some manner in which I can achieve
this.....pleas e advise!

I understand that I can set-up and auto-exe process to open the
database...b ut how can I programme the database to "run" the on click
event proceedure that contains this code...do I have to set it as a
moduel...and create a macro to run when the database is executed or
what?

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200606/1

[pietlinden]

Li****@awamarin e.com.au wrote:

Hey Pieter,

thankyou so much for your reply...your answers are always very
enlightening and helpful...and more importantly helping me move closer
and closer to finishing this project...yay!

I would like to be able to customize each email to these
records....plea se note, however, that these emails are only being sent
to MYSELF as reminders, not different recipients for each
records...just to myslef...howeve r, for each email I would like to be
able to specifically mention which record it is that I am being
reminded about....I would like it to be able to pass, for example,
three to four fields from each of the records into Body of the
email....(i.e the "Vessel Name", the "IMO Number", the "Date of Issue"
and Perhaps the "Short Description") fields....

Kind Regards,

Liam.

If you want one e-mail per record, you would put the
SendObject/SendEMail (whatever your routine is to send the e-mail)
*inside* the loop. (I was wondering about that!!!)

If you want to customize for each e-mail, you would build the body of
the e-mail inside the loop that walks the recordset.

Something along these lines:

Say your query is called qryReminders
const strEMAIL = "my*****@myISP. com"

dim rsReminders as dao.recordset
dim strMsg as string

set rsReminders =dbengine(0)(0) .OpenQuerydef(" qryDueEmails")

do until rsReminders.EOF '---loop thru entire recordset

strMsg="Somethi ng due on " &
rsReminders.Fie lds("SomeDateFi eld") & vbcrlf &
rsReminders.Fie lds("SomeName")
olkMsg.Recipien t = strEMAIL
olkMsg.Body = strMsg
....
olkMsg.Send
rsReminders.Mov eNext

loop
<other code to clean up etc>
I hope this is clear. No, it's not complete code. But the general
idea is this.
1. open the recordset of "due e-mails" based on some query.
2. loop through the recordset and do something with the individual
record
3. close the recordset, and clean up <set refs to Nothing, etc>

Here's a really simple example of looping through the query results in
code:

Public Sub LoopThruQdf(ByV al strQuery As String)
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set qdf = DBEngine(0)(0). QueryDefs(strQu ery)
Set rst = qdf.OpenRecords et
Do Until rst.EOF
'===You'd put your e-mailing code here.
Debug.Print rst.Fields(0)
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
Set qdf = Nothing
End Sub