Over my head (again)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rick - 28 Jul 2006 16:23 GMT

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.

Reply to this Message

salad - 29 Jul 2006 18:55 GMT

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.

Reply to this Message

Rick - 29 Jul 2006 19:51 GMT

> I'll attempt to correct a couple of things.
>
[quoted text clipped - 96 lines]
>
> 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.

Reply to this Message

salad - 29 Jul 2006 20:23 GMT

> Thanks salad
>
[quoted text clipped - 5 lines]
> 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.

Reply to this Message

Rick - 31 Jul 2006 15:13 GMT

> > Thanks salad
> >
[quoted text clipped - 8 lines]
> 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.

Reply to this Message

salad - 31 Jul 2006 17:13 GMT

>>>Thanks salad
>>>
[quoted text clipped - 61 lines]
> 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.

Reply to this Message