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:
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@xxxxxxxxxxxxxxxx> wrote in message
news:1150253122.166332.99790@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxx wrote:
Liam.M@xxxxxxxxxxxxxxxx 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@xxxxxxxxxxx")
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/datafast/CodePages/CodeOutlookMail.asp


.

Relevant Pages

  • Re: DAO takes too much time to link tables
    ... I am trying to link some tables to a back-end database. ... 'Link the tables contained at the given query. ... Dim rst As DAO.Recordset ... Dim success As Boolean ...
    (microsoft.public.access.modulesdaovba)
  • Re: HELP WITH AUTO EXE PROGRAMMING/CODE
    ... Create a separate database for those times when you won't have your database ... create a Query that returns only the Records about which you ... Dim strsubject As String ...
    (comp.databases.ms-access)
  • Re: enumerating objects - repost
    ... I believe I just need the delete command for the forms, ... think of it as only exporting a source-code definition of the form. ... > Dim dbThis As Database, ...
    (microsoft.public.access.modulesdaovba)
  • Re: mailmerge and sql
    ... means that you will not be able to see them in a database you open using the ... I believe you may have to use DAO instead of ADO to ... then creates a View containing a UNION query. ... Dim oCatalog As ADOX.Catalog ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Using a module in a query
    ... > query builder: ... > in modules in your database are visible using the ... > them you will see the custom functions they contain. ... >>Dim WholeWeeks As Variant ...
    (microsoft.public.access.queries)