Hi,
I want to cycle a group of records in code to create a PDF file from a
report and then email it. however, when i click the button on the form to
accomplish this i get an error. "Object Required" in the SQL statement
that
is trying to set the recordset.
The code is below.
Basically for anyone listed on the subform (students) of the Class I want
to
send an email. (providing their email address is not blank.
the code is as follows:
Private Sub Command103_Click()
On Error GoTo Err_cmdEmailLtr_Click
Dim strPathandFileName As String
Dim stDocName As String
Dim strPath As String
Dim dbs As dao.Database, rst As dao.Recordset
Dim strSQL As String
strSQL = Forms!events![Events
Subform].Form.RecordSource.RecordsetClone
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
stDocName = "rptstudentinvoice"
strPath = "u:\database\Emergency Cardiac Care\temp\"
With rst
.MoveFirst
.MoveLast
Debug.Print "Total records " & .RecordCount
Do While .EOF = False
If Not IsNull(!EmailName) Then
ConvertReportToPDF stDocName, "", strPath & stDocName & ".PDF", False,
False
SendOutlookMessage !EmailName, "", "", "Completion
Certificate/Receipt.", _
"Attached is your receipt, completetion certificate for all courses
registered with ECC. File is in adobe format; you may need to go to
www.adobe.com to view this file.", _
False, strPath & stDocName & ".PDF"
Else
End If
.MoveNext
Loop
End With
Exit_cmdEmailLtr_Click:
Exit Sub
Err_cmdEmailLtr_Click:
MsgBox Err.Description
Resume Exit_cmdEmailLtr_Click
End Sub
I really hope someone can tell me how to "clone" the recordset of the open
form/subform.
There are a couple of errors in your code. First, replace these lines ...
----------------------------------
Dim strPathandFileName As String
Dim stDocName As String
Dim strPath As String
Dim dbs As dao.Database, rst As dao.Recordset
Dim strSQL As String
strSQL = Forms!events![Events
Subform].Form.RecordSource.RecordsetClone
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
----------------------------------
... with these:
----------------------------------
Dim strPathandFileName As String
Dim stDocName As String
Dim strPath As String
Dim rst As dao.Recordset
Set rst = Forms!events![Events Subform].Form.RecordsetClone
----------------------------------
You don't need to open the recordsetclone -- Access will create the clone
for you.
Second, replace these lines ...
----------------------------------
.MoveFirst
.MoveLast
----------------------------------
... with these:
----------------------------------
If .RecordCount <> 0 Then
.MoveLast
.MoveFirst
End If
----------------------------------
Note that I've reversed the order of the moves (first move to the last
record, then back to the first record(, as well as wrapping then in an If
statement to avoid raising an error if the recordset happens to be empty.
I think that should fix the main errors; please post back if it still
doesn't work.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
