Sorry Doug, I mean I'm really sorry, but its still does not pull the
"Select
Team_Leader_Email.... It has not email addresses in the to:
The email comes up, with the subject line.... but nothing pertaining to
the
Charter_ID.
I made the last change as suggested. I normally will not have a wild
card,
but I made the change anyway. I have been doing alot of testing and
happened
to use ?? incorrectly. But I made the change anyway.
Is there something else I could be missing. I feel like I have messed
this
up. We started with some good code that worked..... until I wanted to add
some other fields to the body of the email.
I'm determined to get this right..... with you help of course. Thanks for
hanging in there with me, I'm sure this is frustrateing to you as well.
Here is where we are:
Private Sub Command57_Click()
Me.Dirty = False
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT Team_Leader_Email, Review_Title, Fiscal_Year,
Program_Reviewed, Expected_Review_Completion_Date FROM
EmailTestCharterHeader
" & "Where Charter_ID Like '" & Me.Charter_ID & "'"
Debug.Print sql
'sql = sql & Me.Charter_ID
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
Do Until rs.EOF
ToVar = ToVar & rs!Team_Leader_Email & ";"
rs.MoveNext
Loop
DoCmd.SendObject acSendNoObject, , , ToVar, , , "Charter Notification",
"Your Charter has been recently updated", True
End Sub
The "Immediate window looks like this:
SELECT Team_Leader_Email, Review_Title, Fiscal_Year, Program_Reviewed,
Expected_Review_Completion_Date FROM EmailTestCharterHeader Where
Charter_ID
='CH2006??1'SELECT Team_Leader_Email, Review_Title, Fiscal_Year,
Program_Reviewed, Expected_Review_Completion_Date FROM
EmailTestCharterHeader
Where Charter_ID Like 'CH2006??1'
Thanks for your time Doug.
-
Your guidance is greatly appreciated!
Douglas J. Steele said:
Are you trying to have wildcards in there? (You've got the two question
marks in what's in the text box.)
If so, you need
& "Where Charter_ID Like '" & Me.Charter_ID & "'"
instead of
& "Where Charter_ID ='" & Me.Charter_ID & "'"
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Apprentice said:
Sorry I left off some information:
It also does not "Select Team_Leader_Email, Review_Title, Fiscal_Year,
Program_Reviewed, Expected_Review_Completion_Date FROM
EmailTestCharterHeader
" & "Where Charter_ID ='" & Me.Charter_ID & "'"
On the Dbug.Print sql...... it shows in the "Immediate" window of the
SQL
screen:
SELECT Team_Leader_Email, Review_Title, Fiscal_Year, Program_Reviewed,
Expected_Review_Completion_Date FROM EmailTestCharterHeader Where
Charter_ID
='CH2006??1'
--
Your guidance is greatly appreciated!
:
Thanks Doug, that cleared my error. Not it does not pull the
[Team_Leader_Email]address that relates to the [Charter_ID]. It opens
up
the
email with no To:
This is what I have:
Private Sub Command57_Click()
Me.Dirty = False
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT Team_Leader_Email, Review_Title, Fiscal_Year,
Program_Reviewed, Expected_Review_Completion_Date FROM
EmailTestCharterHeader
" & "Where Charter_ID ='" & Me.Charter_ID & "'"
Debug.Print sql
'sql = sql & Me.Charter_ID
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
Do Until rs.EOF
ToVar = ToVar & rs!Team_Leader_Email & ";"
rs.MoveNext
Loop
DoCmd.SendObject acSendNoObject, , , ToVar, , , "Charter
Notification",
"Your Charter has been recently updated", True
End Sub
--
Your guidance is greatly appreciated!
:
Remove the line
sql = sql & Me.Charter_ID
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Oh here is the whole code once again:
Private Sub Command57_Click()
Me.Dirty = False
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT Team_Leader_Email, Review_Title, Fiscal_Year,
Program_Reviewed, Expected_Review_Completion_Date FROM
EmailTestCharterHeader
" & "Where Charter_ID ='" & Me.Charter_ID & "'"
Debug.Print sql
sql = sql & Me.Charter_ID
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
Do Until rs.EOF
ToVar = ToVar & rs!Team_Leader_Email & ";"
rs.MoveNext
Loop
DoCmd.SendObject acSendNoObject, , , ToVar, , , "Charter
Notification",
"Your Charter has been recently updated", True
End Sub
Thanks
--
Your guidance is greatly appreciated!
:
Are you sure that you're searching for a valid Charter_ID? In
your
code,
after you set the value for sql, put the line
Debug.Print sql
Once your code runs (and nothing happens), go to the Immediate
window
(Ctrl-G) and check the SQL. Copy it, and go to create a new
query.
Rather
than select a table or anything, select the SQL View of the query
and
paste
the SQL you got from the Immediate window. Run the query. Do you
get
anything?
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
message
Oh noooo!
What did I do now? I think my SQL SELECT statement is wacked.
Nothing happens when I click.
Sorry, can you help?
Here is the code:
Private Sub Command57_Click()
Me.Dirty = False
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
Dim strTitle As String
Dim strFY As String
Dim strPR As String
Dim strDate As Date
sql = "SELECT Team_Leader_Email, Review_Title, Fiscal_Year,
Program_Reviewed, Expected_Review_Completion_Date FROM
EmailTestCharterHeader
" & "Where Charter_ID ='" & Me.Charter_ID & "'"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
If rs.EOF = False Then
strTitle = rs!Review_Title
strFY = rs!Fiscal_Year
strPR = rs!Program_Reviewed
strDate = rs!Expected_Review_Completion_Date
Do Until rs.EOF
ToVar = ToVar & rs!Team_Leader_Email & ";"
rs.MoveNext
Loop
ToVar = Left(ToVar, Len(ToVar) - 1)
DoCmd.SendObject acSendNoObject, , , ToVar, , , "Charter
Notification",
"Your Charter has been recently updated: Review Title:" &
strTitle & "
Fiscal Year:" & strFY & " Program Reviewed:" & strPR & " Review
Due
Date:"
&
strDate, True
End If
End Sub
--
Your guidance is greatly appreciated!
:
By "the 4th field", are you talking about where you've got
ToVar?
It
should
be fine. The only change you might consider is removing the
semi-colon
from
the end of the string. In other words, between the Loop
statement
and
the
DoCmd.SendObject statement, put
ToVar = Left(ToVar, Len(ToVar)-1)
I don't see anything wrong with the syntax of your SQL
statement
(I'm
assuming there's a space between EmailTestCharterHeader and
the
double
quote), but I can't comment whether it's correct. Only you
know
whether
you've got the correct names there.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message
Ok thanks, I'm getting it.
1. I'm not sure as to how the 4th field should look in the
DoCmd
Line.
2. Also I'm not sure if the SQL Select statement looks
right.
I
added
all
of the field names I'm trying to select.
Here is now what I have. I haven't tried the code yet, but
I
think
I'm
getting closer.
Private Sub Command57_Click()
Me.Dirty = False
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
Dim strTitle As String
Dim strFY As Integer
Dim strPR As String
Dim strDate As Date
sql = "SELECT Team_Leader_Email, Review_Title, Fiscal_Year,
Program_Reviewed, Expected_Review_Completion_Date FROM
EmailTestCharterHeader
" & "Where Charter_ID ='" & Me.Charter_ID & "'"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
If rs.EOF = False Then
strTitle = rs!Review_Title
strFY = rs!Fiscal_Year
strPR = rs!Program_Reviewed
strDate = rs!Expected_Review_Completion_Date
Do Until rs.EOF
ToVar = ToVar & rs!Team_Leader_Email & ";"
rs.MoveNext
Loop
DoCmd.SendObject acSendNoObject, , , ToVar, , , "Charter
Notification",
"Your Charter has been recently updated" & strTitle & " " &
strFY &
"
(" &
Program_Reviewed & ")", True
End If
End Sub
