Get report to print data from ADO recordset created at runtime

M

malcolm

I posted this in Module Programming but I think it belongs here

A client wants to send an email of changes to participants but only send the
changed information. The change could be to any of a couple of dozen tables a
couple of which have several hundred fields. (The data is on SQL Server 2000
& the access client is an .ADP file). I plan to create a recordset, assign it
to a report and use SendTo to email the report as an .rtf file. This also
kills 2 birds with 1 stone as the client wants to preview the info before
emailing it.

My strategy was to set up a global recordset as follows and a variable to
store the initial info before the change:

Global grstPart As ADODB.Recordset
Global gOldValue As Variant

Then when the participant form opened the OnLoad event made sure the
recordset did not exist:

On Error Resume Next
grstPart.Close
Set grstPart = Nothing
Err = 0
On Error GoTo Form_Load_Err

Then, when the user selected a participant from a combo box, the combobox's
AfrerUpdate event terminated any existing recordset and created a fresh
recordset for the newly selected participant as follows:

On Error Resume Next
grstPart.Close
Set grstPart = Nothing
Err = 0
On Error GoTo cboFindObject_AfterUpdate_Err

Set grstPart = New ADODB.Recordset
grstPart.Fields.Append "ParticipantID", adInteger, 8
grstPart.Fields.Append "Item", adVarChar, 50
grstPart.Fields.Append "Prior", adVariant
grstPart.Fields.Append "Current", adVariant
grstPart.Fields.Append "Date", adDate, 50
grstPart.Open

=====================
So far all works fine.
=====================

As a test, I added the following code to the First_Name and Last_Name fields
on the Participant form.

Note: the forms are unbound so used the OnEnter event to grab the initial
value. The OldValue property only works for bound forms.

Private Sub First_Name_Enter()
gOldValue = Me.First_Name
End Sub

Private Sub First_Name_AfterUpdate()

On Error GoTo First_Name_AfterUpdate_Err

grstPart.AddNew _
Array("ParticipantID", "Item", "Prior", "Current", "Date"), _
Array(Me.ParticipantID, "First Name", gOldValue, Me.First_Name, Date)
grstPart.Update

gOldValue = Null

First_Name_AfterUpdate_Exit:

Err = 0
On Error GoTo 0
Exit Sub

First_Name_AfterUpdate_Err:

Dim EReply As Variant
EReply = ErrorTrap(mstrModuleName, "First_Name_AfterUpdate", Err,
Err.Description, "")
Resume First_Name_AfterUpdate_Exit

End Sub

Private Sub Last_Name_Enter()
gOldValue = Me.Last_Name
End Sub

Private Sub Last_Name_AfterUpdate()

Dim strSQL As String

On Error GoTo Last_Name_AfterUpdate_Err

grstPart.AddNew _
Array("ParticipantID", "Item", "Prior", "Current", "Date"), _
Array(Me.ParticipantID, "Last Name", gOldValue, Me.Last_Name, Date)
grstPart.Update

DoCmd.OpenReport "EmailReport", acViewPreview

gOldValue = Null

Last_Name_AfterUpdate_Exit:

Err = 0
On Error GoTo 0
Exit Sub

Last_Name_AfterUpdate_Err:

Dim EReply As Variant
EReply = ErrorTrap(mstrModuleName, "Last_Name_AfterUpdate", Err,
Err.Description, "")
Resume Last_Name_AfterUpdate_Exit

End Sub

=====================
Again, all is OK until I open the report "EmailReport." The report shows two
records but the displayed values in all controls is #ERROR as I am now sure
the OnOpen event for the report cannot use the ADO recordset in the statement

SET Me.Recordset = grstPart

I am sure the report can only use a DAO recordset but only ADO lets me
create the recordset at runtime.

Is there a solution?

If all else fails, I'll store the changes in a table on the server and fetch
them back for the report. But I want to minimize network traffic.
 
A

Alex Dybenko

Hi,
I would make this via local table - just insert all changes into table
(instead of recordset), then build a report, bound to this table, and after
send - clear it.
this will also solve other problem - if your (user) computer crashed - you
do not loose this information

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com
 
M

malcolm

The Access client is an .ADP file and, as far as I know, cannot create a
local table. I believe all tables must reside on SQL Server
 
R

RoyVidar

malcolm wrote in message
I posted this in Module Programming but I think it belongs here

A client wants to send an email of changes to participants but only
send the changed information. The change could be to any of a couple
of dozen tables a couple of which have several hundred fields. (The
data is on SQL Server 2000 & the access client is an .ADP file). I
plan to create a recordset, assign it to a report and use SendTo to
email the report as an .rtf file. This also kills 2 birds with 1
stone as the client wants to preview the info before emailing it.

My strategy was to set up a global recordset as follows and a
variable to store the initial info before the change:

Global grstPart As ADODB.Recordset
Global gOldValue As Variant

Then when the participant form opened the OnLoad event made sure the
recordset did not exist:

On Error Resume Next
grstPart.Close
Set grstPart = Nothing
Err = 0
On Error GoTo Form_Load_Err

Then, when the user selected a participant from a combo box, the
combobox's AfrerUpdate event terminated any existing recordset and
created a fresh recordset for the newly selected participant as
follows:

On Error Resume Next
grstPart.Close
Set grstPart = Nothing
Err = 0
On Error GoTo cboFindObject_AfterUpdate_Err

Set grstPart = New ADODB.Recordset
grstPart.Fields.Append "ParticipantID", adInteger, 8
grstPart.Fields.Append "Item", adVarChar, 50
grstPart.Fields.Append "Prior", adVariant
grstPart.Fields.Append "Current", adVariant
grstPart.Fields.Append "Date", adDate, 50
grstPart.Open

=====================
So far all works fine.
=====================

As a test, I added the following code to the First_Name and Last_Name
fields on the Participant form.

Note: the forms are unbound so used the OnEnter event to grab the
initial value. The OldValue property only works for bound forms.

Private Sub First_Name_Enter()
gOldValue = Me.First_Name
End Sub

Private Sub First_Name_AfterUpdate()

On Error GoTo First_Name_AfterUpdate_Err

grstPart.AddNew _
Array("ParticipantID", "Item", "Prior", "Current", "Date"), _
Array(Me.ParticipantID, "First Name", gOldValue, Me.First_Name, Date)
grstPart.Update

gOldValue = Null

First_Name_AfterUpdate_Exit:

Err = 0
On Error GoTo 0
Exit Sub

First_Name_AfterUpdate_Err:

Dim EReply As Variant
EReply = ErrorTrap(mstrModuleName, "First_Name_AfterUpdate", Err,
Err.Description, "")
Resume First_Name_AfterUpdate_Exit

End Sub

Private Sub Last_Name_Enter()
gOldValue = Me.Last_Name
End Sub

Private Sub Last_Name_AfterUpdate()

Dim strSQL As String

On Error GoTo Last_Name_AfterUpdate_Err

grstPart.AddNew _
Array("ParticipantID", "Item", "Prior", "Current", "Date"), _
Array(Me.ParticipantID, "Last Name", gOldValue, Me.Last_Name, Date)
grstPart.Update

DoCmd.OpenReport "EmailReport", acViewPreview

gOldValue = Null

Last_Name_AfterUpdate_Exit:

Err = 0
On Error GoTo 0
Exit Sub

Last_Name_AfterUpdate_Err:

Dim EReply As Variant
EReply = ErrorTrap(mstrModuleName, "Last_Name_AfterUpdate", Err,
Err.Description, "")
Resume Last_Name_AfterUpdate_Exit

End Sub

=====================
Again, all is OK until I open the report "EmailReport." The report
shows two records but the displayed values in all controls is #ERROR
as I am now sure the OnOpen event for the report cannot use the ADO
recordset in the statement

SET Me.Recordset = grstPart

I am sure the report can only use a DAO recordset but only ADO lets
me create the recordset at runtime.

Is there a solution?

If all else fails, I'll store the changes in a table on the server
and fetch them back for the report. But I want to minimize network
traffic.
Click to expand...

When you create the recordset, try also specify locktype

....
Set grstPart = New ADODB.Recordset
With grstPart
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Fields.Append "ParticipantID", adInteger, 8
...
 

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

Similar Threads

Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Export specific fields from access to Excel 0
Blank Report Message 2
Getting recordset from Webservice 1
Looping through a recordset and sending a report as an email 8
Email Report 2
Email select page of report -- need help 0
I'm getting a runtime error 10

Top