hey guys,
I have one last problem to fix, and then my database is essentially
done...I would therefore very much appreciate any assistance anyone
would be able to provide me with.
Currently I have set up a Query to show only records that meet a
certain criteria...ther efore excluding all of the records that do not
meet this criteria (just for the record the criteria is any record
within my database that falls within two months of its "Due Date"
field). I need to find a way to cycle through all of these records
shown in the query and then automatically send an email to myself
notifying me that this particular record has almost expired...reach ed
its "Due Date".
Now I have already written the code to launch the "email" aspect of the
command...and this is working fine....I do, however, require some help
in automating the process....and was wondering if anyone could provide
me with the code to use, for example, within the form(s) "On Load"
event, that would initiate the "email" code I have already written
titled "SendMail"? ???? So essentially, if anyone could please spare the
time, would like to obtain some code that loops through all of the
records that query discovers to automate my "SendMail" command....it
would be very much appreciated!
Also does anyone know how to pass fields from a record into the body of
the email...i.e Outlook?
Kind Regards,
Liam
Jun 19 '06
36  3071  Th************* *@yahoo.com wrote:
Okay.. if I take the () away from fMsgBody, I don't get the compile
error and the email is created, but there is no data. Which tells me
that my query is not working. How do I test my query within the VBA in
access window?
Why not isolate the query problems? Couple of ways to go about this -
one is to copy the SQL statement that your code builds, and then paste
it into a query (just click the view button on the top left in design
view, and switch to SQL view). Then try running it. The problem with
running the SQL statement in code is that you can't really see what
records it returns. You can get a recordcount, but that's it. (Well,
unless you loop through everything...)
The other way is to create a function that creates the SQL statement
for you. You could pass in your criteria or whatever... Doesn't
matter. And then you'd just copy that back into your blank query and
run it. (or change the SQL property of a junk/temp query) and run it.
Function fMsgBody() As String
dim strSQL as String
dim rsDue as dao.recordset
dim strList as string '---place to dump all the names in the
query
'---build the query... (personally, I'd use a stored query)
strSQL = "SELECT [Current IACUC
Protocols].[Protocol Number], [Current IACUC Protocols].[Protocol
Name], [New Personnel].[Member ID] "
strSQL = strSQL & "FROM ([New Personnel] INNER JOIN [registrations
list] ON [New
Personnel].[Member ID] = [registrations list].[Member ID]) "
strSQL = strSQL & "INNER JOIN [Current IACUC Protocols] ON
[registrations list].[RegistrationFor m ID] = [Current IACUC
Protocols].[Protocol Number] " & _
"WHERE ((([New Personnel].[Member ID])=[Forms]![Personnel
Form2]![Member ID]))"
'---open the recordset based on the query SQL
set rsDue=DBEngine( 0)(0).OpenRecor dset(strSQL)
'---concatenate the results into a single string
do until rsDue.EOF
'---MODIFY THIS SECTION TO INCLUDE YOUR FIELDS.
strList=strList & rsDue.Fields("I MO Number") & vbtab & _
rsDue.Fields("S BMA Number") & vbtab & _
rsDue.Fields("D ate of Issue") & vbtab & _
rsDue.Fields("D ue Date") & vbtab & _
rsDue.Fields("V essel Name") & vbcrlf
rsDue.MoveNext
loop
fMsgBody="The following accounts are due:" & vbcrlf & strList
rsDue.Close
set rsDue=Nothing
End Function
There. Can't get much easier than that. Does that work?
It works when I create a new query in the db window and paste the SQL
code into the sql editor (...ommitting the "" and () at beginning and
end.) I run the query and have 4 records.
When I "test it in vba" by hitting the button that creates the email
that calls the query looped string in the body of the message, it is
blank.
Is my loop code correct?
Do Until rsDue.EOF
strList = rsDue.Fields([Protocol Number]) & vbCrLf
rsDue.MoveNext
Loop pi********@hotm ail.com wrote:
Th************* *@yahoo.com wrote:
Okay.. if I take the () away from fMsgBody, I don't get the compile
error and the email is created, but there is no data. Which tells me
that my query is not working. How do I test my query within the VBA in
access window?
Why not isolate the query problems? Couple of ways to go about this -
one is to copy the SQL statement that your code builds, and then paste
it into a query (just click the view button on the top left in design
view, and switch to SQL view). Then try running it. The problem with
running the SQL statement in code is that you can't really see what
records it returns. You can get a recordcount, but that's it. (Well,
unless you loop through everything...)
The other way is to create a function that creates the SQL statement
for you. You could pass in your criteria or whatever... Doesn't
matter. And then you'd just copy that back into your blank query and
run it. (or change the SQL property of a junk/temp query) and run it.
Hi Jessica,
See a couple of messages before.
You can better use
strList = strList & rsDue.Fields([Protocol Number]) & vbCrLf
HBInc. Th************* *@yahoo.com wrote:
It works when I create a new query in the db window and paste the SQL
code into the sql editor (...ommitting the "" and () at beginning and
end.) I run the query and have 4 records.
When I "test it in vba" by hitting the button that creates the email
that calls the query looped string in the body of the message, it is
blank.
Is my loop code correct?
Do Until rsDue.EOF
strList = rsDue.Fields([Protocol Number]) & vbCrLf
rsDue.MoveNext
Loop
pi********@hotm ail.com wrote:
Th************* *@yahoo.com wrote:
Okay.. if I take the () away from fMsgBody, I don't get the compile
error and the email is created, but there is no data. Which tells me
that my query is not working. How do I test my query within the VBA in
access window?
Why not isolate the query problems? Couple of ways to go about this -
one is to copy the SQL statement that your code builds, and then paste
it into a query (just click the view button on the top left in design
view, and switch to SQL view). Then try running it. The problem with
running the SQL statement in code is that you can't really see what
records it returns. You can get a recordcount, but that's it. (Well,
unless you loop through everything...)
The other way is to create a function that creates the SQL statement
for you. You could pass in your criteria or whatever... Doesn't
matter. And then you'd just copy that back into your blank query and
run it. (or change the SQL property of a junk/temp query) and run it.
Sorry... I think we crossed postings for a minute. I re-wrote strSQL
as indicated above and the strList as supplied below. (I have pasted my
code) however, my email still comes up blank when I call fMsgBody in
the body of the email.
NOw, when I try to "test" the sql code in the database window, it does
not work... it prompts me for the parameter values of Protocol Number,
Protocol Name etc with the first strSQL statement. You wanted me to
separate the sql portion into 3 portions right?
Public Function fMsgBody() As String
Dim db As Database
Dim qdf As QueryDef
Dim rst As Recordset
Set db = CurrentDb()
Dim rsDue As DAO.Recordset
Dim strList As String '---place to dump all the names in the Query
Dim strSQL As String
strSQL = "SELECT [Current IACUC Protocols].[Protocol Number], [Current
IACUC Protocols].[Protocol Name], [New Personnel].[Member ID] "
strSQL = strSQL & "FROM ([New Personnel] INNER JOIN [registrations
list] ON [New Personnel].[Member ID] = [registrations list].[Member
ID]) "
strSQL = strSQL & "INNER JOIN [Current IACUC Protocols] ON
[registrations list].[RegistrationFor m ID] = [Current IACUC
Protocols].[Protocol Number] " & _
"WHERE ((([New Personnel].[Member ID])=[Forms]![Personnel
Form2]![Member ID]))"
'---open the recordset based on the query SQL
Set rsDue = DBEngine(0)(0). OpenRecordset(s trSQL)
'---concatenate the results into a single string
Do Until rsDue.EOF
strList = strList & rsDue.Fields([Protocol Number]) & vbCrLf
rsDue.MoveNext
Loop
fMsgBody = "You have indicated this person will work on the
following protocols:" & vbCrLf & strList
rsDue.Close
Set rsDue = Nothing
End Function
hbinc wrote:
Hi Jessica,
See a couple of messages before.
You can better use
strList = strList & rsDue.Fields([Protocol Number]) & vbCrLf
HBInc.
Th************* *@yahoo.com wrote:
It works when I create a new query in the db window and paste the SQL
code into the sql editor (...ommitting the "" and () at beginning and
end.) I run the query and have 4 records.
When I "test it in vba" by hitting the button that creates the email
that calls the query looped string in the body of the message, it is
blank.
Is my loop code correct?
Do Until rsDue.EOF
strList = rsDue.Fields([Protocol Number]) & vbCrLf
rsDue.MoveNext
Loop
pi********@hotm ail.com wrote:
Th************* *@yahoo.com wrote:
Okay.. if I take the () away from fMsgBody, I don't get the compile
error and the email is created, but there is no data. Which tells me
that my query is not working. How do I test my query within the VBA in
access window?
>
Why not isolate the query problems? Couple of ways to go about this -
one is to copy the SQL statement that your code builds, and then paste
it into a query (just click the view button on the top left in design
view, and switch to SQL view). Then try running it. The problem with
running the SQL statement in code is that you can't really see what
records it returns. You can get a recordcount, but that's it. (Well,
unless you loop through everything...)
>
The other way is to create a function that creates the SQL statement
for you. You could pass in your criteria or whatever... Doesn't
matter. And then you'd just copy that back into your blank query and
run it. (or change the SQL property of a junk/temp query) and run it.
If something isn't working, simplify or go take enough steps backwards
so you're at a spot where it does. Then change ONE thing at a time
from there.
What happens if you just create a normal query from this:
SELECT [Current IACUC Protocols].[Protocol Number], [Current
IACUC Protocols].[Protocol Name], [New Personnel].[Member ID] FROM
([New Personnel] INNER JOIN [registrations list] ON [New
Personnel].[Member ID] = [registrations list].[Member ID]) INNER JOIN
[Current IACUC Protocols] ON
[registrations list].[RegistrationFor m ID] = [Current IACUC
Protocols].[Protocol Number] WHERE ((([New Personnel].[Member
ID])=[Forms]![Personnel
Form2]![Member ID]))
just make sure Form2 is open, and then copy and paste this into the SQL
view of a query and run it. What happens? If this works, it's how
you're manipulating the functions or whatever. Once you start there,
you know that it's the code causing the problem or it's the SQL.
Say you save the above query as "qryGetProtocol s". Building the query
on the fly and opening a canned query (as long as the SQL statements
are the same) is logically equivalent. So break the thing up and sort
of start over with the pieces you know work. Then move forward from
there.
Once that works, you can work on collecting the information into a
single record or whatever. But if you're not moving from
stable/functional state to stable/functional state in your database
design, you won't make much progress.
Just my two cents.
I have tested the Select Statements in the SQL query with Form2 open
and they work. I have tested the email without the fMsgBody and it
worked.
I added an if statement to enter the words none if the query returned
no values and the none did not appear in the body message.
To me it seems like I must have an error in the manner of calling the
function, I am calling the fuction as follows:
MyMail.Body = fMsgBody
I have written fMsgBody function as follows:
fMsgBody = "You have indicated this person will work on the following
protocols:" & vbCrLf & strList
I don't understand why nothing is showing up in the body of the email
with that code. pi********@hotm ail.com wrote:
If something isn't working, simplify or go take enough steps backwards
so you're at a spot where it does. Then change ONE thing at a time
from there.
What happens if you just create a normal query from this:
SELECT [Current IACUC Protocols].[Protocol Number], [Current
IACUC Protocols].[Protocol Name], [New Personnel].[Member ID] FROM
([New Personnel] INNER JOIN [registrations list] ON [New
Personnel].[Member ID] = [registrations list].[Member ID]) INNER JOIN
[Current IACUC Protocols] ON
[registrations list].[RegistrationFor m ID] = [Current IACUC
Protocols].[Protocol Number] WHERE ((([New Personnel].[Member
ID])=[Forms]![Personnel
Form2]![Member ID]))
just make sure Form2 is open, and then copy and paste this into the SQL
view of a query and run it. What happens? If this works, it's how
you're manipulating the functions or whatever. Once you start there,
you know that it's the code causing the problem or it's the SQL.
Say you save the above query as "qryGetProtocol s". Building the query
on the fly and opening a canned query (as long as the SQL statements
are the same) is logically equivalent. So break the thing up and sort
of start over with the pieces you know work. Then move forward from
there.
Once that works, you can work on collecting the information into a
single record or whatever. But if you're not moving from
stable/functional state to stable/functional state in your database
design, you won't make much progress.
Just my two cents.
Okay, now we know the problem is in fMsgBody. What happens when you
call fMsgBody in the Immediate window?
How exactly do you do that? I don't think I am doing it correctly pi********@hotm ail.com wrote:
Okay, now we know the problem is in fMsgBody. What happens when you
call fMsgBody in the Immediate window?
Th************* *@yahoo.com wrote:
How exactly do you do that? I don't think I am doing it correctly
pi********@hotm ail.com wrote:
Okay, now we know the problem is in fMsgBody. What happens when you
call fMsgBody in the Immediate window?
open a code window.
show the immediate window
type in
?fMsgBody
If it's working, the result should print below that. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Sameer |
last post by:
I am supplying you with Sample Data:-
Initial Classcode SampleSize Average
------- ---------- -------------------------------
ADK SSC 22 3.6800000000000002
ADK TSC 17 2.7599999999999998
ADK TSM 5 3.5499999999999998
ANB FCA 31 3.23
ANB FCB 50 3.0499999999999998
ANB FCC 30 3.0899999999999999
|
by: brendan_gallagher_2001 |
last post by:
Hi,
I have a view(A) and I am trying to do a join on another table (B) to
include only rows where date values in view A is greater than in table
B. I also want the view to pick up rows in viewA based on date values.
Here is what I have so far:
SELECT *
FROM viewA vw
left JOIN tableB tb ON
|
by: Marizel |
last post by:
I'm not sure there's an easy solution to this, but thought I'd ask. I
often find myself with a query which I'd like to reuse, but with a
different datasource. These datasources generally have identical
field names. The queries select a subset of the fields, so "Select *"
is not really an option.
Is there an easy way to change the source of a query, either in the
design grid or SQL display? I suppose I could copy the SQL into
WordPad...
|
by: Crimsonwingz |
last post by:
Need to calculate a sum based on a number of factors over a period of
years. I can use formula ^x for some of it, but need totals to carry
over in the sum and have only been able to do this thus far with a loop
in a form.
Basically, I have key sums
Current savings
Current Salary
Current deposit amount
|
by: P B via AccessMonster.com |
last post by:
I have a list of 160,000 records with these fields:
fname, lname, address, city, state, zip, dob
I need to generate a list with all fields where the first initial of lname
and the dob are equal.
How? I can't seem to get Left() to work in a query. Could I put this in a
VBscript and generate a recordset? What's the easiest way to generate this
list? This seems so simple.
| | |
by: jwgoerlich |
last post by:
Hello group,
I am working on a query string class. The purpose is to parse
name-value pairs from incoming text. Currently, I am using the Regex
code below. I have two questions.
First, the code below does not work if there is a space in the name.
For example, the text "Initial Catalog=test;" parses to name=Catalog
and value=test.
|
by: tomlebold |
last post by:
Look for query that changes Tom L LeBold into Tom LeBold and only when
the middle intial exist.
|
by: Curben |
last post by:
Hello all, two days of websearching and I cannot find an answer yet.
Reasonably new to access development.
Access 2000 DB using access 2003 on windows XP
I am having an issue with creating a query that I can enter the Part ID as value and hvae the description, cost etc. populate in the rest of the query.
I have two Tables; ItemMaster and
has one Value:
|
by: jglabas |
last post by:
For a report, I am using a query as my record source. The query produces 5 columns by 3272 records.
The data in columns 1 & 2 (“Objective” and “Rating”) repeats every 409 records The data for “initial” and “Date” are unique for the individual. Currently there are 8 individuals (409 * 8 = 3272)
. . . . .. . . . .Objective . . . Rating . . Initial . . . .. Date . . . . . Name
. . . . .abc. . . . . . .a. . . . . . jg. . . . . .1/12/07 . ....
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
