coding a report from a form.


L

LeeTV

please see below for my original question and then my subsequent question
with regards to coding the filter for a report from forms code.
thanks
Lee



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.


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)

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? (this will enable the
recipient to receive only their invoice).
thanks
Lee
 
Ad

Advertisements

D

Dennis

You need to generate a discrete report for each person, email it, close it,
and generate the next one.

LeeTV said:
please see below for my original question and then my subsequent question
with regards to coding the filter for a report from forms code.
thanks
Lee



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.


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)

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? (this will enable the
recipient to receive only their invoice).
thanks
Lee
 
Ad

Advertisements

E

Erez Mor

hi
if your pdf making code is something that "prints" the report to pdf, then
use a public varibale (say we call it "CurrentPersonID"), set it's value to
rst!PersonID or some other identifying field before calling the ConvertToPDF
function,
then in the report's Open event set a filter that says
me.Filter="PersonID=" & currentPersonID
me.FilterOn=True

that way, every time the reports opens to be printed by the PDF creator, it
gets a different filter that matches the person your making it for

good luck
Erez.
Dennis said:
You need to generate a discrete report for each person, email it, close it,
and generate the next one.

LeeTV said:
please see below for my original question and then my subsequent question
with regards to coding the filter for a report from forms code.
thanks
Lee



Dirk Goldgar 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.


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)

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? (this will enable the
recipient to receive only their invoice).
thanks
Lee
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top