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?

[pietlinden]

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?

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.

[Liam.M]

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"..whi ch 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.Cl one
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 ("ga*********** *@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...@hotm ail.com wrote:

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?

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.

[pietlinden]

Li****@awamarin e.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"..whi ch 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.Cl one
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 ("ga*********** *@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

[Liam.M]

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...base d 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 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;

I have therefore created a Command Button within this "SummaryLis t"
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.Applica tion
Dim olNs As Outlook.NameSpa ce
Dim olMail As Outlook.MailIte m
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("O utlook.Applicat ion")
Set olNs = olApp.GetNamesp ace("MAPI")
olNs.Logon
Set olMail = olApp.CreateIte m(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

pi********@hotm ail.com wrote:

Li****@awamarin e.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"..whi ch 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.Cl one
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 ("ga*********** *@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

[Larry Linson]

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
<Li****@awamari ne.com.au> wrote in message
news:11******** *************@i 40g2000cwc.goog legroups.com...

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...base d 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 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;

I have therefore created a Command Button within this "SummaryLis t"
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.Applica tion
Dim olNs As Outlook.NameSpa ce
Dim olMail As Outlook.MailIte m
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("O utlook.Applicat ion")
Set olNs = olApp.GetNamesp ace("MAPI")
olNs.Logon
Set olMail = olApp.CreateIte m(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

pi********@hotm ail.com wrote:

Li****@awamarin e.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"..whi ch 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.Cl one
>
>
> 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 ("ga*********** *@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

[Liam.M]

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...alt hough 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...tha ts easy....command ed 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....th e 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:

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
<Li****@awamari ne.com.au> wrote in message
news:11******** *************@i 40g2000cwc.goog legroups.com...

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...base d 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 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;

I have therefore created a Command Button within this "SummaryLis t"
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.Applica tion
Dim olNs As Outlook.NameSpa ce
Dim olMail As Outlook.MailIte m
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("O utlook.Applicat ion")
Set olNs = olApp.GetNamesp ace("MAPI")
olNs.Logon
Set olMail = olApp.CreateIte m(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

pi********@hotm ail.com wrote:

Li****@awamarin e.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"..whi ch 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.Cl one
>
>
> 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 ("ga*********** *@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

[Liam.M]

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 SBMACheckAndEma il_Click()
Dim rst As Object
Set rst = Me.Recordset.Cl one
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 ("ga*********** *@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.Applica tion
Dim olNs As Outlook.NameSpa ce
Dim olMail As Outlook.MailIte m
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("O utlook.Applicat ion")
Set olNs = olApp.GetNamesp ace("MAPI")
olNs.Logon
Set olMail = olApp.CreateIte m(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", "Descriptio n" fields from my "Summary List" or
Record itself (same thing) to the email?????????? ???????????

[Liam.M]

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 SBMACheckAndEma il_Click()
Dim rst As Object
Set rst = Me.Recordset.Cl one
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 ("ga*********** *@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.Applica tion
Dim olNs As Outlook.NameSpa ce
Dim olMail As Outlook.MailIte m
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("O utlook.Applicat ion")
Set olNs = olApp.GetNamesp ace("MAPI")
olNs.Logon
Set olMail = olApp.CreateIte m(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", "Descriptio n" fields from my "Summary List" or
Record itself (same thing) to the email?????????? ???????????

[pietlinden]

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

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

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.