coding a report from a form.

  • Thread starter Thread starter LeeTV
  • Start date Start date
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.
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? (this will enable the
recipient to receive only their invoice).
thanks
Lee
 
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.
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? (this will enable the
recipient to receive only their invoice).
thanks
Lee
Click to expand...
 
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)
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? (this will enable the
recipient to receive only their invoice).
thanks
Lee
Click to expand...
Click to expand...
 
Back
Top