RecordSet Clone

  • Thread starter Thread starter Lee
  • Start date Start date
L

Lee

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.
TIA.
lee
 
Lee said:
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.
Click to expand...


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)
Click to expand...

----------------------------------

.... 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
Click to expand...

----------------------------------

.... 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 said:
Lee said:
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.
Click to expand...


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)
Click to expand...

----------------------------------

... 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
Click to expand...

----------------------------------

... 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)
Click to expand...

Thank you, your reply helped my problem. Now I have a second problem. The
issue is that the report is being created with every record in its table and
every invoice(rpt) is being complied to one PDF file and then emailed to the
student. The student is getting emailed a large PDF with every other persons
invoice... How do i send the specific report for the current record in the
subform/recordsetclone as it loops through the code.
thanks
Lee
 
Lee said:
Thank you, your reply helped my problem. Now I have a second problem. The
issue is that the report is being created with every record in its table
and
every invoice(rpt) is being complied to one PDF file and then emailed to
the
student. The student is getting emailed a large PDF with every other
persons
invoice... How do i send the specific report for the current record in the
subform/recordsetclone as it loops through the code.
Click to expand...


I believe that what you're going to have to do is open the report in print
preview, filtered and hidden, inside your loop, output it to PDF, and then
close it. Something like this:

'----- start of revised "air code" -----
With rst

If Not .EOF Then
.MoveLast
.MoveFirst
End If

Debug.Print "Total records " & .RecordCount

Do While .EOF = False

If Not IsNull(!EmailName) Then

DoCmd.OpenReport stDocName, acViewPreview, _
WhereCondition:="StudentID=" & !StudentID, _
WindowMode:=acHidden

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"

DoCmd.Close acReport, stDocName

End If

.MoveNext

Loop

End With
'----- end of revised "air code" -----

I don't promise that the above is exactly right, and I can't recall if
Stephen Lebans' ConvertReportToPDF function needs a special argument to be
passed to work from the opened report, but I'm pretty sure it's something
like that.
 
Dirk, Thanks so much for your reply. Although, I am getting an error at the
..move next and it states "Object invalid or no longer set". It seems that the
rst has closed. I dont have any code set in the report, and i dont understand
why the rst is being closed. Basically the code does run for the first record
in the recordset, and it only has the one page for the correct student, but
it loops once through the code and then terminates with the aforementioned
error...

Any help would be appreciated.

Thanks
Lee
 
LeeTV said:
Dirk, Thanks so much for your reply. Although, I am getting an error at
the
.move next and it states "Object invalid or no longer set". It seems that
the
rst has closed. I dont have any code set in the report, and i dont
understand
why the rst is being closed. Basically the code does run for the first
record
in the recordset, and it only has the one page for the correct student,
but
it loops once through the code and then terminates with the aforementioned
error...
Click to expand...

A simple test, looping through a recordset and creating a separate report
PDF for each record, works for me. So I suspect that the code in your
SendOutlookMessage procedure may be doing something it shouldn't --
attempting to close the current database, maybe. Try this: comment out the
call to SendOutlookMessage, then run the code and see if it works without
error.

It occurs to me that you may also need to erase the output PDF file after
sending it in each iteration of the loop, since you're using the same name
each time. However, I don't see why that would cause the error you're
reporting now.
 
Dirk, again, Thanks so much for your help. I comented out the code and set a
breakpoint. I discovered that on the "on activate" event of the form that
this was coming from that the subform was being 'requeried' once i commented
out this line of code then the sendemail works, perfectly. You have no idea
how thrilled i am that finally, with help, this now works, and make my life
so much easier. You guys really are brilliant.
I had no clue that opening a report triggered the forms "On Activate" event.
Lee
 
Back
Top