I have a continous form based on a query. The Query checks a students
record to see if a letter has been selected. The letters are selected
from a combo box so each letter has a corresponding number. It also
checks a check box to see if the letter has been sent. If it hasn't
it's listed in the continious form along with the students name, Email
address and StudentID. There are also txt boxes for Email Subject and
Email Message. On the form I have a button to Email a report to the
student. The report is based on a different query. The code to Email
is as follows.
Private Sub Command12_Click()
If IsNull(Me.txtSubject) Or IsNull(Me.txtMessage) Then
MsgBox ("You must Enter Subject and message for Email")
Me.Undo
Else
Dim db As DAO.Database
Dim strSQL As String
Dim strEmail As String
Dim rs As DAO.Recordset
Dim strID As Integer
Set db = CurrentDb
strSQL = "SELECT tblDemoTrack.EmailName, tblDemoTrack.MCTCID,
tblDemoTrack.StudentID,
tblDemoTrack.FirstName, tblDemoTrack.LastName, tblStage.LetterID,
tblStage.LetterSent
FROM (tblStage INNER JOIN tblDemoTrack ON tblStage.StudentID =
tblDemoTrack.StudentID)
INNER JOIN tblStageTrack ON tblDemoTrack.StudentID =
tblStageTrack.StudentID
WHERE (((tblDemoTrack.EmailName) Is Not Null) AND
((tblStage.LetterID)=22) AND ((tblStage.LetterSent)=0))"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If IsNull(Me.EmailName) Then
MsgBox ("No Records have been chosen")
Me.Undo
DoCmd.Close
Exit Sub
End If
rs.MoveFirst
While Not rs.EOF
strEmail = rs!EmailName '[EmailName]
strID = rs!StudentID
[ThisID] = strID
Debug.Print "Send Email to "; strEmail; ThisID
DoCmd.OpenReport "NotComplete1", acViewPreview, , "[StudentID] = "
& Forms![frm_qry_22_EmailIncomplete]![ThisID]
DoCmd.SendObject acSendReport, "NotComplete1", acFormatRTF,
strEmail, , , txtSubject, txtMessage, False
DoCmd.Close acReport, "NotComplete1"
rs.MoveNext
If Not rs.EOF Then strEmail = strEmail & ", "
If Not rs.EOF Then strID = strID & ", "
wend
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End If
End Sub
The strSQL string is the same as the query source for the form. What
I'm trying to do is go through each record that meets the criteria,
open a report for that studentID and email the report, close the report
and go on to the next student. I had this working perfect in my
development data base but when I imported in to the working data base
it refuses to pass an ID to the report. I basically use the txtfield
ThisID to hold the StudentID and update it to the next record. I tried
passing strID Variable to the report but it didn't like that. The
debug statement just before the open report statement prints out the
Email address and the ThisID correctly. Is there a reason the report
won't accept the StudentID in the open report statement? Is there an
eaiser way to do this? Can I just read each record on the form and
somehow loop through them one at a time (Not sure how to code that)
keep in mind I need to pass the Email address to the SendObject and the
StudentID to the report, or is opening a record set the best way to go.
I've learned enough from this group to be dangerous and not enough to
be good! (Yet)
Thanks for your help. 5 1111
Rick wrote:
I'll attempt to correct a couple of things.
Private Sub Command12_Click()
If Me.txtSubject) Or IsNull(Me.txtMessage) Then
MsgBox ("You must Enter Subject and message for Email")
Me.Undo
Dim strEmail As String
Dim rs As DAO.Recordset
Dim strID As Integer
Dim blnOK As Boolean
blnOK = True
Set rs = Me.RecordsetClone
If rs.RecordCount 0 then
rs.MoveFirst
'loop through form's recordset and see if any
'records are missing an email name. I suppose this list
'is small, but you could also do this with a SQL statement
Do while not rs.EOF
If NZ(rs!EmailName,"") = "" then
msgbox "Some records don't have email addresses"
blnOK = False
Exit Do
Endif
rs.MoveNext
Loop
If blnOK Then
'all records have an email address
rs.MoveFirst
While Not rs.EOF
strEmail = rs!EmailName '[EmailName]
strID = rs!StudentID
[ThisID] = strID
'Debug.Print "Send Email to "; strEmail; ThisID
'I have no idea why you need to open the
'report first...but what the heck. I'll comment
'it out since I don't think it's necessary. remove
'the ' in case you need it
'DoCmd.OpenReport "NotComplete1", _
acViewPreview, , "[StudentID] = " & strID
DoCmd.SendObject acSendReport, "NotComplete1", _
acFormatRTF, strEmail, , , txtSubject, _ txtMessage, False
'remove comment mark in front of Docmd if for some
'odd reason you need the report open in preview
'mode.
'DoCmd.Close acReport, "NotComplete1"
rs.MoveNext
'I have no idea what the next 2 lines is for _
'is for except to create a couple of strings
'that are immediately overwritten. Commented out
'If Not rs.EOF Then strEmail = strEmail & ", "
'If Not rs.EOF Then strID = strID & ", "
wend
rs.Close
Set rs = Nothing
db.Close
Endif
else
MsgBox ("You must Enter Subject and message for Email")
Me.Subject.setfocus
End If
End Sub
Now...I see no need to open and close your report. Maybe someone more
experienced with sending reports can tell me differently. First thing,
I will assume this report is only called from this form. If not, look
in GoogleGroups, group *Access*, for IsLoaded. This is a function to
determine if a form is open. Next, I'll assume you have the student ID
on the report, hidden or visible, doesn't matter.
Now, in the OnOpen event of NotComplete1 report you could do something
like this
If IsLoaded("YourFormName") Then
Me.Filter = "[StudentID] = " & _
Forms![frm_qry_22_EmailIncomplete]![ThisID]
Me.FilterOn = True
Endif
If this report is only called from this form then enter
Me.Filter = "[StudentID] = " & _
Forms![frm_qry_22_EmailIncomplete]![ThisID]
Me.FilterOn = True
This will filter the report to 1 student record.
You can also set flags in the OnOpen event to preview will all records
or filter to 1 record. I'm trying to keep this simple.
See if the above code makes more sense now.
salad wrote:
Rick wrote:
I'll attempt to correct a couple of things.
Private Sub Command12_Click()
If Me.txtSubject) Or IsNull(Me.txtMessage) Then
MsgBox ("You must Enter Subject and message for Email")
Me.Undo
Dim strEmail As String
Dim rs As DAO.Recordset
Dim strID As Integer
Dim blnOK As Boolean
blnOK = True
Set rs = Me.RecordsetClone
If rs.RecordCount 0 then
rs.MoveFirst
'loop through form's recordset and see if any
'records are missing an email name. I suppose this list
'is small, but you could also do this with a SQL statement
Do while not rs.EOF
If NZ(rs!EmailName,"") = "" then
msgbox "Some records don't have email addresses"
blnOK = False
Exit Do
Endif
rs.MoveNext
Loop
If blnOK Then
'all records have an email address
rs.MoveFirst
While Not rs.EOF
strEmail = rs!EmailName '[EmailName]
strID = rs!StudentID
[ThisID] = strID
'Debug.Print "Send Email to "; strEmail; ThisID
'I have no idea why you need to open the
'report first...but what the heck. I'll comment
'it out since I don't think it's necessary. remove
'the ' in case you need it
'DoCmd.OpenReport "NotComplete1", _
acViewPreview, , "[StudentID] = " & strID
DoCmd.SendObject acSendReport, "NotComplete1", _
acFormatRTF, strEmail, , , txtSubject, _ txtMessage, False
'remove comment mark in front of Docmd if for some
'odd reason you need the report open in preview
'mode.
'DoCmd.Close acReport, "NotComplete1"
rs.MoveNext
'I have no idea what the next 2 lines is for _
'is for except to create a couple of strings
'that are immediately overwritten. Commented out
'If Not rs.EOF Then strEmail = strEmail & ", "
'If Not rs.EOF Then strID = strID & ", "
wend
rs.Close
Set rs = Nothing
db.Close
Endif
else
MsgBox ("You must Enter Subject and message for Email")
Me.Subject.setfocus
End If
End Sub
Now...I see no need to open and close your report. Maybe someone more
experienced with sending reports can tell me differently. First thing,
I will assume this report is only called from this form. If not, look
in GoogleGroups, group *Access*, for IsLoaded. This is a function to
determine if a form is open. Next, I'll assume you have the student ID
on the report, hidden or visible, doesn't matter.
Now, in the OnOpen event of NotComplete1 report you could do something
like this
If IsLoaded("YourFormName") Then
Me.Filter = "[StudentID] = " & _
Forms![frm_qry_22_EmailIncomplete]![ThisID]
Me.FilterOn = True
Endif
If this report is only called from this form then enter
Me.Filter = "[StudentID] = " & _
Forms![frm_qry_22_EmailIncomplete]![ThisID]
Me.FilterOn = True
This will filter the report to 1 student record.
You can also set flags in the OnOpen event to preview will all records
or filter to 1 record. I'm trying to keep this simple.
See if the above code makes more sense now.
Thanks salad
I'm just learning to deal with recordsets and it never dawned on me
that a form based on a query would already have it's own record set.
Makes sence now that I think about it. I'll give the code a try on
Monday and let you know how it all works. The query the forms based on
already checks for null email field so I don't think I'll need to check
if it's null. I can't say enough about this news group, although I
think I've learned who's postings I can trust and who's waste my time.
Rick wrote:
Thanks salad
I'm just learning to deal with recordsets and it never dawned on me
that a form based on a query would already have it's own record set.
Makes sence now that I think about it. I'll give the code a try on
Monday and let you know how it all works. The query the forms based on
already checks for null email field so I don't think I'll need to check
if it's null. I can't say enough about this news group, although I
think I've learned who's postings I can trust and who's waste my time.
This is an excellent group with the most knowledgable people in Access
you'll ever meet. Good luck.
salad wrote:
Rick wrote:
Thanks salad
I'm just learning to deal with recordsets and it never dawned on me
that a form based on a query would already have it's own record set.
Makes sence now that I think about it. I'll give the code a try on
Monday and let you know how it all works. The query the forms based on
already checks for null email field so I don't think I'll need to check
if it's null. I can't say enough about this news group, although I
think I've learned who's postings I can trust and who's waste my time.
This is an excellent group with the most knowledgable people in Access
you'll ever meet. Good luck.
Salad:
Thanks for all the help, here's my final code
If IsNull(Me.txtSubject) Or IsNull(Me.txtMessage) Then
MsgBox ("You must Enter Subject and message for Email")
Me.Undo
Me.txtSubject.SetFocus
Else
Dim strEmail As String
Dim rs As DAO.Recordset
Dim strID As Integer
'no need to check for null email as form query already checks for that
Set rs = Me.RecordsetClone
If rs.RecordCount 0 Then
rs.MoveFirst
While Not rs.EOF
strEmail = rs!EmailName '[EmailName]
strID = rs!StudentID
[ThisID] = strID
'Print copy of emailed report
'You where correct. No need to open report in preview to send as
attachment but I'm printing a copy for students file.
DoCmd.OpenReport "NotComplete1", acViewNormal, ,
"[StudentID] =" & Forms![frm_qry_22_EmailIncomplete]![ThisID]
DoCmd.SendObject acSendReport, "NotComplete1",
acFormatRTF, strEmail, , , txtSubject, txtMessage, False
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
'no need to do db.close
End If
End If
End Sub
The report is only opened from the form so I did as you suggested and
filtered the report with the following in the reports onOpen event
Me.Filter = "[StudentID] = " & _
Forms![frm_qry_22_EmailIncomplete]![ThisID]
Me.FilterOn = True
Every thing works perfect, thanks for teaching this old dog something
new. It's much appreciated.
Rick wrote:
salad wrote:
>>Rick wrote:
>>>Thanks salad
I'm just learning to deal with recordsets and it never dawned on me that a form based on a query would already have it's own record set. Makes sence now that I think about it. I'll give the code a try on Monday and let you know how it all works. The query the forms based on already checks for null email field so I don't think I'll need to check if it's null. I can't say enough about this news group, although I think I've learned who's postings I can trust and who's waste my time. This is an excellent group with the most knowledgable people in Access you'll ever meet. Good luck.
Salad:
Thanks for all the help, here's my final code
If IsNull(Me.txtSubject) Or IsNull(Me.txtMessage) Then
MsgBox ("You must Enter Subject and message for Email")
Me.Undo
Me.txtSubject.SetFocus
Else
Dim strEmail As String
Dim rs As DAO.Recordset
Dim strID As Integer
'no need to check for null email as form query already checks for that
Set rs = Me.RecordsetClone
If rs.RecordCount 0 Then
rs.MoveFirst
While Not rs.EOF
strEmail = rs!EmailName '[EmailName]
strID = rs!StudentID
[ThisID] = strID
'Print copy of emailed report
'You where correct. No need to open report in preview to send as
attachment but I'm printing a copy for students file.
DoCmd.OpenReport "NotComplete1", acViewNormal, ,
"[StudentID] =" & Forms![frm_qry_22_EmailIncomplete]![ThisID]
DoCmd.SendObject acSendReport, "NotComplete1",
acFormatRTF, strEmail, , , txtSubject, txtMessage, False
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
'no need to do db.close
End If
End If
End Sub
The report is only opened from the form so I did as you suggested and
filtered the report with the following in the reports onOpen event
Me.Filter = "[StudentID] = " & _
Forms![frm_qry_22_EmailIncomplete]![ThisID]
Me.FilterOn = True
Every thing works perfect, thanks for teaching this old dog something
new. It's much appreciated.
Responses like your makes this all worthwhile.
One last note...I'm not sure why you put in a Me.Undo...not sure it's
really needed as it will clear out the values to the values of last save
if the fields are bound to a recordset. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Shalen chhabra |
last post by:
Hey,
Can anyone give me a snippet for running a python program over all the files
in the directory.
For ex: I have ten files in a directory and I want to run a python program
against all of...
|
by: Reply Via News Group Please |
last post by:
Folks,
I'm new to CSS and realise that by posting in the html newsgroup, I
might upset one or two readers by raising a CSS question in an html
newsgroup however my ISP only has one small CSS...
|
by: Martial Spirit |
last post by:
Hello-
I was amazed at the suckerfish drop-downs from AListApart.com. They
work great except for one thing, if I position the drop-downs over an
iframe element, on mouseover the menus disappear....
|
by: Larry R Harrison Jr |
last post by:
I am looking for javascript and a basic tutorial on how to make mouse-over
drop-down menus--the type that when you "hover" over a subject links
relevant to that subject "emerge" which you can then...
|
by: Paul |
last post by:
See this post, C seems to be loosing here
Are we still programming in C, just b'cause those mortals find it easy
to develop a C compiler than C++ compiler. Considering C++ can do
every thing that...
|
by: jason.m.ho |
last post by:
>From the common user perspective (like my grandma), why would they care
if its a java applet or an ajax application? Say I want to make a chat
system on my website...If i'm doing really involved...
|
by: Phil Endecott |
last post by:
Dear All,
For some reason I had got it into my head that std::vector<T>::iterator
and std::basic_string<T>::iterator were certain to be T*. I now see
that this isn't true.
So here's why this...
|
by: cassbiz |
last post by:
Could use some help here.
This script is carrying over an image just fine but the text isn't coming over. can you see why it is not working????
from the form I want to carry over two lines of...
|
by: pereges |
last post by:
Hi, I am wondering which of the two data structures (link list or
array) would be better in my situation. I have to create a list of
rays for my ray tracing program.
the data structure of ray...
|
by: issentia |
last post by:
I'm working on this site:
http://www.essenceofsoy.com/redesign/index2.html
and I'm having a few problems with getting the layout exactly right.
1) When the menu items are rolled over, they...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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...
|
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,...
|
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...
|
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,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
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...
| |