HELP WITH AUTO EXE PROGRAMMING/CODE

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?

re: HELP WITH AUTO EXE PROGRAMMING/CODE

[color=blue]
> 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?[/color]

Loop through the database? You mean you're running a query, right?
Otherwise, you're making things MUCH harder than they should be. Open
a recordset based on a query that finds the records you want and
process inside a loop.

You'd have to run a scheduler to do something like what you propose. I
think there's code for one at www.mvps.org/access here...
http://www.mvps.org/access/modules/mdl0042.htm

Yes, call the event in the on click event of your button. You can
create a macro that does a RunCode call, and away you go.

re: HELP WITH AUTO EXE PROGRAMMING/CODE

Thankyou for such a speedy reply...yes I have query set up...that
query's the database based on a "DueDate" field...if any Record falls
within a 2month period of the "DueDate" then this code executes another
command "SendMail"..which emails me telling me to check the
database....

I have yet to look at those links you have provided me...hopefully they
shall work...
My query is as follows:
Dim rst As Object


Set rst = Me.Recordset.Clone


With rst
.MoveFirst
Do While Not .EOF
If .Fields("Due Date") >= VBA.Date And _
.Fields("Due Date") <= DateAdd("m", 2, VBA.Date) + 1
Then
SendMail ("gatecrasher_05@hotmail.com")
End If
.MoveNext
Loop
End With


End Sub

Obviously this then prompts the "SendMail" code.....do u know how I
could also...if a record is found...pass some other fields from this
record "into" my Email??????????

pietlin...@hotmail.com wrote:[color=blue][color=green]
> > 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?[/color]
>
> Loop through the database? You mean you're running a query, right?
> Otherwise, you're making things MUCH harder than they should be. Open
> a recordset based on a query that finds the records you want and
> process inside a loop.
>
> You'd have to run a scheduler to do something like what you propose. I
> think there's code for one at www.mvps.org/access here...
> http://www.mvps.org/access/modules/mdl0042.htm
>
> Yes, call the event in the on click event of your button. You can
> create a macro that does a RunCode call, and away you go.[/color]

re: HELP WITH AUTO EXE PROGRAMMING/CODE

Liam.M@awamarine.com.au wrote:[color=blue]
> Thankyou for such a speedy reply...yes I have query set up...that
> query's the database based on a "DueDate" field...if any Record falls
> within a 2month period of the "DueDate" then this code executes another
> command "SendMail"..which emails me telling me to check the
> database....
>
> I have yet to look at those links you have provided me...hopefully they
> shall work...
> My query is as follows:
> Dim rst As Object
>
>
> Set rst = Me.Recordset.Clone
>
>
> With rst
> .MoveFirst
> Do While Not .EOF
> If .Fields("Due Date") >= VBA.Date And _
> .Fields("Due Date") <= DateAdd("m", 2, VBA.Date) + 1
> Then
> SendMail ("gatecrasher_05@hotmail.com")
> End If
> .MoveNext
> Loop
> End With
>
>
> End Sub
>
> Obviously this then prompts the "SendMail" code.....do u know how I
> could also...if a record is found...pass some other fields from this
> record "into" my Email??????????
>[/color]
Again, why are you not just opening a query to filter out all the
records you don't even want to look at? Then you can scrap the IF..END
IF test completely, as that will be in the query. Basically you'd move
the filter to the query and be done with it. Then you could use
something like this to send the e-mail.

http://www.amazecreations.com/datafa...utlookMail.asp

re: HELP WITH AUTO EXE PROGRAMMING/CODE

The reason as to why I have constrcuted the database in this manner is
because I am using a "Main Form" so to speak...which is essentially a
"Summary List" of all the cords in the Database...based on a Query...it
does not show all the informaiton in the Records...just 4 Field:
1. A Name
2. A Short Description
3. The Date it was Issued, and;
4. THe Due Date

The Query that this "Summary List" or "Main Form" is run from is as
follows: SQL View:

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

I have therefore created a Command Button within this "SummaryList"
form to run the Command to Query the Database based on the "Due Date"
field and then prompt it to send an email.......how could I pass some
of the information, i.e the four fields mention above into this email
code :

Sub SendMail(strTo)

Dim strsubject As String
Dim varbody As Variant
Dim strattachment1 As String
Dim strattachment2 As String
Dim olApp As Outlook.Application
Dim olNs As Outlook.NameSpace
Dim olMail As Outlook.MailItem


strsubject = "ATTN:Shore-Based Maintainance Agreements"
varbody = "Please check the Database A.S.A.P, as it appears that a
Record is up for renewal within a two-month period "

Set olApp = CreateObject("Outlook.Application")
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon
Set olMail = olApp.CreateItem(olMailItem)


olMail.To = strTo
olMail.Subject = strsubject
olMail.Body = varbody


olMail.Send


Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing


End Sub

Any suggestions?

And thankyou for spending the time...I know they must seem to be very
silly questions...it is very much appreciated

Regards

Liam



pietlinden@hotmail.com wrote:[color=blue]
> Liam.M@awamarine.com.au wrote:[color=green]
> > Thankyou for such a speedy reply...yes I have query set up...that
> > query's the database based on a "DueDate" field...if any Record falls
> > within a 2month period of the "DueDate" then this code executes another
> > command "SendMail"..which emails me telling me to check the
> > database....
> >
> > I have yet to look at those links you have provided me...hopefully they
> > shall work...
> > My query is as follows:
> > Dim rst As Object
> >
> >
> > Set rst = Me.Recordset.Clone
> >
> >
> > With rst
> > .MoveFirst
> > Do While Not .EOF
> > If .Fields("Due Date") >= VBA.Date And _
> > .Fields("Due Date") <= DateAdd("m", 2, VBA.Date) + 1
> > Then
> > SendMail ("gatecrasher_05@hotmail.com")
> > End If
> > .MoveNext
> > Loop
> > End With
> >
> >
> > End Sub
> >
> > Obviously this then prompts the "SendMail" code.....do u know how I
> > could also...if a record is found...pass some other fields from this
> > record "into" my Email??????????
> >[/color]
> Again, why are you not just opening a query to filter out all the
> records you don't even want to look at? Then you can scrap the IF..END
> IF test completely, as that will be in the query. Basically you'd move
> the filter to the query and be done with it. Then you could use
> something like this to send the e-mail.
>
> http://www.amazecreations.com/datafa...utlookMail.asp[/color]

re: HELP WITH AUTO EXE PROGRAMMING/CODE

Note: this approach will require that you be able to write some VBA code,
but it won't be _difficult_ VBA code.

Create a separate database for those times when you won't have your database
open. Link the tables

Create an AutoExec macro, and use it to Run your code. Don't use a regular
user interface, because this is just for the run-on-its-own case. As Piet
has suggested, create a Query that returns only the Records about which you
should be e-mailed. Read through all the Records, sending an e-mail about
each item that was returned by your Query, or building a text message which
you will send when you finish processing the records -- if the latter, send
that list. Then, Quit the application.

Schedule the standalone, no-user-interface application using the Windows
Scheduler (I am not familiar with details of Windows Scheduler, so you'll
have to Google or ask a separate question in a Windows newsgroup).

I'd suggest, in the application that you do open from time to time, the
first Form be in continuous forms view, using the same
query as its Record Source, listing the Records for which you need an alert,
and print those Records -- unless there is a good reason for sending an
e-mail, too.

Larry Linson
Microsoft Access MVP


<Liam.M@awamarine.com.au> wrote in message
news:1150253122.166332.99790@i40g2000cwc.googlegro ups.com...[color=blue]
> The reason as to why I have constrcuted the database in this manner is
> because I am using a "Main Form" so to speak...which is essentially a
> "Summary List" of all the cords in the Database...based on a Query...it
> does not show all the informaiton in the Records...just 4 Field:
> 1. A Name
> 2. A Short Description
> 3. The Date it was Issued, and;
> 4. THe Due Date
>
> The Query that this "Summary List" or "Main Form" is run from is as
> follows: SQL View:
>
> SELECT ShipsInformation.[SBMA Number], ShipsInformation.[Vessel Name],
> ShipsInformation.[IMO Number], ShipsInformation.[Date of Issue],
> ShipsInformation.RecordID, ShipsInformation.[Date of Attendance],
> DateAdd("yyyy",1,ShipsInformation.[Date of Issue]) AS [Due Date],
> ShipsInformation.EmailAddress
> FROM ShipsInformation;
>
> I have therefore created a Command Button within this "SummaryList"
> form to run the Command to Query the Database based on the "Due Date"
> field and then prompt it to send an email.......how could I pass some
> of the information, i.e the four fields mention above into this email
> code :
>
> Sub SendMail(strTo)
>
> Dim strsubject As String
> Dim varbody As Variant
> Dim strattachment1 As String
> Dim strattachment2 As String
> Dim olApp As Outlook.Application
> Dim olNs As Outlook.NameSpace
> Dim olMail As Outlook.MailItem
>
>
> strsubject = "ATTN:Shore-Based Maintainance Agreements"
> varbody = "Please check the Database A.S.A.P, as it appears that a
> Record is up for renewal within a two-month period "
>
> Set olApp = CreateObject("Outlook.Application")
> Set olNs = olApp.GetNamespace("MAPI")
> olNs.Logon
> Set olMail = olApp.CreateItem(olMailItem)
>
>
> olMail.To = strTo
> olMail.Subject = strsubject
> olMail.Body = varbody
>
>
> olMail.Send
>
>
> Set olNs = Nothing
> Set olMail = Nothing
> Set olApp = Nothing
>
>
> End Sub
>
> Any suggestions?
>
> And thankyou for spending the time...I know they must seem to be very
> silly questions...it is very much appreciated
>
> Regards
>
> Liam
>
>
>
> pietlinden@hotmail.com wrote:[color=green]
>> Liam.M@awamarine.com.au wrote:[color=darkred]
>> > Thankyou for such a speedy reply...yes I have query set up...that
>> > query's the database based on a "DueDate" field...if any Record falls
>> > within a 2month period of the "DueDate" then this code executes another
>> > command "SendMail"..which emails me telling me to check the
>> > database....
>> >
>> > I have yet to look at those links you have provided me...hopefully they
>> > shall work...
>> > My query is as follows:
>> > Dim rst As Object
>> >
>> >
>> > Set rst = Me.Recordset.Clone
>> >
>> >
>> > With rst
>> > .MoveFirst
>> > Do While Not .EOF
>> > If .Fields("Due Date") >= VBA.Date And _
>> > .Fields("Due Date") <= DateAdd("m", 2, VBA.Date) + 1
>> > Then
>> > SendMail ("gatecrasher_05@hotmail.com")
>> > End If
>> > .MoveNext
>> > Loop
>> > End With
>> >
>> >
>> > End Sub
>> >
>> > Obviously this then prompts the "SendMail" code.....do u know how I
>> > could also...if a record is found...pass some other fields from this
>> > record "into" my Email??????????
>> >[/color]
>> Again, why are you not just opening a query to filter out all the
>> records you don't even want to look at? Then you can scrap the IF..END
>> IF test completely, as that will be in the query. Basically you'd move
>> the filter to the query and be done with it. Then you could use
>> something like this to send the e-mail.
>>
>> http://www.amazecreations.com/datafa...utlookMail.asp[/color]
>[/color]

re: HELP WITH AUTO EXE PROGRAMMING/CODE

Hey Larry,
I have been working on this Database for roughly about 6 weeks now...I
have come so close to completing it...it would be extremely not
practical to even attempt to implement what you have
suggested...although very valuable information...I'll explain what I
have...and perhaps you may be able to help me...

I current HAVE a form...a "Summary List" it could be called...which is
a contineous form...on this form Four fields from EVERY record are
exhibited each on their own line, so to seak...like a running summary
of ALL the Records in the Database...upon click one of these lines...it
then opens that specific Record in the Database...they are linked by a
Record ID! The four fields from the Database that are exhibited in this
contineous form, and linked by the Record ID...are Name, Description,
Date of Issue, and Due Date!
This contineous form is powered obviously....by a Query...to extract
the informaiton from the database and display it on this form!

Within the Summary List or Summary form...I have created a COmmand
Button which executed a CODE that Queries the database...NOT AN ACTUAL
QUERY itself...to see if any of the DUEDATES fall within the NEXT TWO
MONTHS....if they do...then it executes an Email to ME........NOW....I
have already DONE ALL of THIS...all I need help on doing it somehow
Automating this Command Button to Run this Code Once a Week...I have
already downloaded a Scheduler...thats easy....commanded it to run the
database on a certain day and time each week...all I need to know is
how would I automate code to Run co-scheduler....the easiest option
would probably be just to place it on the Form's On Load Event or On
Open event I am assuming now....

Any sugesstions?

Regards
Liam.
Larry Linson wrote:[color=blue]
> Note: this approach will require that you be able to write some VBA code,
> but it won't be _difficult_ VBA code.
>
> Create a separate database for those times when you won't have your database
> open. Link the tables
>
> Create an AutoExec macro, and use it to Run your code. Don't use a regular
> user interface, because this is just for the run-on-its-own case. As Piet
> has suggested, create a Query that returns only the Records about which you
> should be e-mailed. Read through all the Records, sending an e-mail about
> each item that was returned by your Query, or building a text message which
> you will send when you finish processing the records -- if the latter, send
> that list. Then, Quit the application.
>
> Schedule the standalone, no-user-interface application using the Windows
> Scheduler (I am not familiar with details of Windows Scheduler, so you'll
> have to Google or ask a separate question in a Windows newsgroup).
>
> I'd suggest, in the application that you do open from time to time, the
> first Form be in continuous forms view, using the same
> query as its Record Source, listing the Records for which you need an alert,
> and print those Records -- unless there is a good reason for sending an
> e-mail, too.
>
> Larry Linson
> Microsoft Access MVP
>
>
> <Liam.M@awamarine.com.au> wrote in message
> news:1150253122.166332.99790@i40g2000cwc.googlegro ups.com...[color=green]
> > The reason as to why I have constrcuted the database in this manner is
> > because I am using a "Main Form" so to speak...which is essentially a
> > "Summary List" of all the cords in the Database...based on a Query...it
> > does not show all the informaiton in the Records...just 4 Field:
> > 1. A Name
> > 2. A Short Description
> > 3. The Date it was Issued, and;
> > 4. THe Due Date
> >
> > The Query that this "Summary List" or "Main Form" is run from is as
> > follows: SQL View:
> >
> > SELECT ShipsInformation.[SBMA Number], ShipsInformation.[Vessel Name],
> > ShipsInformation.[IMO Number], ShipsInformation.[Date of Issue],
> > ShipsInformation.RecordID, ShipsInformation.[Date of Attendance],
> > DateAdd("yyyy",1,ShipsInformation.[Date of Issue]) AS [Due Date],
> > ShipsInformation.EmailAddress
> > FROM ShipsInformation;
> >
> > I have therefore created a Command Button within this "SummaryList"
> > form to run the Command to Query the Database based on the "Due Date"
> > field and then prompt it to send an email.......how could I pass some
> > of the information, i.e the four fields mention above into this email
> > code :
> >
> > Sub SendMail(strTo)
> >
> > Dim strsubject As String
> > Dim varbody As Variant
> > Dim strattachment1 As String
> > Dim strattachment2 As String
> > Dim olApp As Outlook.Application
> > Dim olNs As Outlook.NameSpace
> > Dim olMail As Outlook.MailItem
> >
> >
> > strsubject = "ATTN:Shore-Based Maintainance Agreements"
> > varbody = "Please check the Database A.S.A.P, as it appears that a
> > Record is up for renewal within a two-month period "
> >
> > Set olApp = CreateObject("Outlook.Application")
> > Set olNs = olApp.GetNamespace("MAPI")
> > olNs.Logon
> > Set olMail = olApp.CreateItem(olMailItem)
> >
> >
> > olMail.To = strTo
> > olMail.Subject = strsubject
> > olMail.Body = varbody
> >
> >
> > olMail.Send
> >
> >
> > Set olNs = Nothing
> > Set olMail = Nothing
> > Set olApp = Nothing
> >
> >
> > End Sub
> >
> > Any suggestions?
> >
> > And thankyou for spending the time...I know they must seem to be very
> > silly questions...it is very much appreciated
> >
> > Regards
> >
> > Liam
> >
> >
> >
> > pietlinden@hotmail.com wrote:[color=darkred]
> >> Liam.M@awamarine.com.au wrote:
> >> > Thankyou for such a speedy reply...yes I have query set up...that
> >> > query's the database based on a "DueDate" field...if any Record falls
> >> > within a 2month period of the "DueDate" then this code executes another
> >> > command "SendMail"..which emails me telling me to check the
> >> > database....
> >> >
> >> > I have yet to look at those links you have provided me...hopefully they
> >> > shall work...
> >> > My query is as follows:
> >> > Dim rst As Object
> >> >
> >> >
> >> > Set rst = Me.Recordset.Clone
> >> >
> >> >
> >> > With rst
> >> > .MoveFirst
> >> > Do While Not .EOF
> >> > If .Fields("Due Date") >= VBA.Date And _
> >> > .Fields("Due Date") <= DateAdd("m", 2, VBA.Date) + 1
> >> > Then
> >> > SendMail ("gatecrasher_05@hotmail.com")
> >> > End If
> >> > .MoveNext
> >> > Loop
> >> > End With
> >> >
> >> >
> >> > End Sub
> >> >
> >> > Obviously this then prompts the "SendMail" code.....do u know how I
> >> > could also...if a record is found...pass some other fields from this
> >> > record "into" my Email??????????
> >> >
> >> Again, why are you not just opening a query to filter out all the
> >> records you don't even want to look at? Then you can scrap the IF..END
> >> IF test completely, as that will be in the query. Basically you'd move
> >> the filter to the query and be done with it. Then you could use
> >> something like this to send the e-mail.
> >>
> >> http://www.amazecreations.com/datafa...utlookMail.asp[/color]
> >[/color][/color]

re: HELP WITH AUTO EXE PROGRAMMING/CODE

Also...if you have any idea...I'm going to guess not....no one has been
able to provide assistance as yet......BUT....I want from the CODE that
checks the records if the date falls within the next two months....to
not only automate an email to me...which it does...but I want it to be
more specifc...i.e tell me exactily WHICH record it is....
the code I have at the moment...is as follows:

Private Sub SBMACheckAndEmail_Click()
Dim rst As Object


Set rst = Me.Recordset.Clone


With rst
.MoveFirst
Do While Not .EOF
If .Fields("Due Date") >= VBA.Date And _
.Fields("Due Date") <= DateAdd("m", 2, VBA.Date) + 1
Then
SendMail ("gatecrasher_05@hotmail.com")
End If
.MoveNext
Loop
End With


End Sub

That obviously checks the data criteria and then executes my email
command "SendMail"....

Sub SendMail(strTo)

Dim strsubject As String
Dim varbody As Variant
Dim strattachment1 As String
Dim strattachment2 As String
Dim olApp As Outlook.Application
Dim olNs As Outlook.NameSpace
Dim olMail As Outlook.MailItem


strsubject = "ATTN:Shore-Based Maintainance Agreements"
varbody = "Please check the Database A.S.A.P, as it appears that a
Record is up for renewal within a two-month period "

Set olApp = CreateObject("Outlook.Application")
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon
Set olMail = olApp.CreateItem(olMailItem)


olMail.To = strTo
olMail.Subject = strsubject
olMail.Body = varbody


olMail.Send


Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing


End Sub

And then that sends me the mail............how do I pass for
example....the "Name", "Description" fields from my "Summary List" or
Record itself (same thing) to the email?????????????????????

re: HELP WITH AUTO EXE PROGRAMMING/CODE

Also...if you have any idea...I'm going to guess not....no one has been
able to provide assistance as yet......BUT....I want from the CODE that
checks the records if the date falls within the next two months....to
not only automate an email to me...which it does...but I want it to be
more specifc...i.e tell me exactily WHICH record it is....
the code I have at the moment...is as follows:

Private Sub SBMACheckAndEmail_Click()
Dim rst As Object


Set rst = Me.Recordset.Clone


With rst
.MoveFirst
Do While Not .EOF
If .Fields("Due Date") >= VBA.Date And _
.Fields("Due Date") <= DateAdd("m", 2, VBA.Date) + 1
Then
SendMail ("gatecrasher_05@hotmail.com")
End If
.MoveNext
Loop
End With


End Sub

That obviously checks the data criteria and then executes my email
command "SendMail"....

Sub SendMail(strTo)

Dim strsubject As String
Dim varbody As Variant
Dim strattachment1 As String
Dim strattachment2 As String
Dim olApp As Outlook.Application
Dim olNs As Outlook.NameSpace
Dim olMail As Outlook.MailItem


strsubject = "ATTN:Shore-Based Maintainance Agreements"
varbody = "Please check the Database A.S.A.P, as it appears that a
Record is up for renewal within a two-month period "

Set olApp = CreateObject("Outlook.Application")
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon
Set olMail = olApp.CreateItem(olMailItem)


olMail.To = strTo
olMail.Subject = strsubject
olMail.Body = varbody


olMail.Send


Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing


End Sub

And then that sends me the mail............how do I pass for
example....the "Name", "Description" fields from my "Summary List" or
Record itself (same thing) to the email?????????????????????

re: HELP WITH AUTO EXE PROGRAMMING/CODE

Liam.M@awamarine.com.au wrote:[color=blue]
> Also...if you have any idea...I'm going to guess not....no one has been
> able to provide assistance as yet....[/color]

Yeah, and if you continue to act like a smartass, nobody will help you.

Question for ya... how do you pass values into Subroutines and
functions? Try that. That's your answer. Happy hunting.

Yes, it's a deliberately oblique answer. Try using your brain and your
manners and you'll no doubt get better answers.

re: HELP WITH AUTO EXE PROGRAMMING/CODE

Liam.M@awamarine.com.au wrote in
news:1150260146.627492.127470@u72g2000cwu.googlegr oups.com:
[color=blue]
> Also...if you have any idea...I'm going to guess not....no one has
> been able to provide assistance as yet[/color]

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/

re: HELP WITH AUTO EXE PROGRAMMING/CODE

Apologies GENTLEMEN....that sentence has been taken completely out of
context...I do apologize....and yes you are very correct, I do lack the
experience and knowledge....and 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:[color=blue]
> Liam.M@awamarine.com.au wrote in
> news:1150260146.627492.127470@u72g2000cwu.googlegr oups.com:
>[color=green]
> > Also...if you have any idea...I'm going to guess not....no one has
> > been able to provide assistance as yet[/color]
>
> 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/[/color]

re: HELP WITH AUTO EXE PROGRAMMING/CODE

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 ShipsInformation.[SBMA Number], ShipsInformation.[Vessel Name],
ShipsInformation.[IMO Number], ShipsInformation.[Date of Issue],
ShipsInformation.RecordID, ShipsInformation.[Date of Attendance],
DateAdd("yyyy",1,ShipsInformation.[Date of Issue]) AS [Due Date],
ShipsInformation.EmailAddress
FROM ShipsInformation;

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(byval 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.CreateItem(olMailItem)
mItem.Recipients.Add strRecip
mItem.Subject = strSubject
mItem.Body = strMsg

If Len(strAttachment) > 0 Then
mItem.Attachments.Add strAttachment
End If

mItem.Save
mItem.Send
End If

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

End Function


sample call:
blnSuccess=SendMessage("jdoe@abc.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 SendNotifications()
dim rs as dao.recordset
dim qdf as dao.querydef

set rs=dbengine(0)(0).openquerydef("qryNotifications")
do until rs.EOF
rs.Edit
blnSuccess=SendMessage("jdoe@abc.com", "subject: notification", _
"Your membership is about to expire!",
"C:\somefolder\reminder.doc")

rs.Fields("MessageDatestamp")=Now()
rs.Update
rs.MoveNext

loop

rs.close
set rs=nothing

end sub

re: HELP WITH AUTO EXE PROGRAMMING/CODE

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)...therefore 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 ShipsInformation.[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

re: HELP WITH AUTO EXE PROGRAMMING/CODE

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.[color=blue]
>[/color]
[color=blue]
>
> 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...[/color]

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.
[color=blue]
> 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[/color]

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

re: HELP WITH AUTO EXE PROGRAMMING/CODE

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....please note, however, that these emails are only being sent
to MYSELF as reminders, not different recipients for each
records...just to myslef...however, 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.

re: HELP WITH AUTO EXE PROGRAMMING/CODE

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!

Liam.M@awamarine.com.au wrote:[color=blue]
>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?[/color]

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

re: HELP WITH AUTO EXE PROGRAMMING/CODE

Liam.M@awamarine.com.au wrote:[color=blue]
> 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....please note, however, that these emails are only being sent
> to MYSELF as reminders, not different recipients for each
> records...just to myslef...however, 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.[/color]

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 = "myemail@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="Something due on " &
rsReminders.Fields("SomeDateField") & vbcrlf &
rsReminders.Fields("SomeName")
olkMsg.Recipient = strEMAIL
olkMsg.Body = strMsg
....
olkMsg.Send
rsReminders.MoveNext

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(ByVal strQuery As String)
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set qdf = DBEngine(0)(0).QueryDefs(strQuery)
Set rst = qdf.OpenRecordset
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