Recordset getRows to string

[Guest]

I'm trying to populate a string to send in an email from a recordset. I've
tried a couple things. I get the email, but the body of the email only has
the first sentence, not the list I need from the query. Anybody got any
clues? If so, is there a way to insert a vbCrlf between each dimension of
the array as it goes into strMsg?

Dim rs As DAO.Recordset
Dim vArray As Variant
Dim strTo As String
Dim strCC As String
Dim strSubject As String
Dim strMsg As String

Set rs = CurrentDb.OpenRecordset("Select REGULAR-MD from qryFHCxlsRpt")
rs.MoveLast
vArray = rs.GetRows
strMsg = "Detailed summary report for the following docs:" & vbCrLf
strMsg = strMsg & Join(vArray)

strTo = "(e-mail address removed)"
strSubject = "FHC text"
strCC = ""
DoCmd.SendObject acSendReport, "Family Practice Service Detailed
Report", "Snapshot Format", strTo, strCC, , strSubject, strMsg, False

rs.Close

 

[Rob Oldfield]

You'll need to loop through the records returned, building the string as you
go. I also don't see any advantage to reading the recordset into an array
so I'd modify it to something like...

Set rs = CurrentDb.OpenRecordset("Select REGULAR-MD from qryFHCxlsRpt")
strMsg = "Detailed summary report for the following docs:" & vbCrLf
with rs
do until .eof
strmsg=strmsg & !REGULAR-MD & vbcrlf
.movenext
loop
end with

If you did want to use an array to do it then again you'd have to loop e.g.

dim i as integer
for i = 0 to ubound(varray)
strmsg = strmsg & varray(i) & vbcrlf
next

 

[Guest]

Rob,
Thanks for the try, but this hangs Access into not responding and freezes up
the application until I end it in Task Manager. I'll keep plugging away, and
would appreciate any additional thoughts.

Janet

 

[Guest]

Rob,
Ummm. Well, I commented out everything and then un-commented one step at a
time. It started hanging when I got to the Do While statement. I commented
out the rs movement and just tried the field stuff, and the email came
through blank except for the line before the rs. When I execute, I get the
calculating... in the lower left, so I think something is happening. Put
back the rs movement, and it froze again. I copy/pasted the sql syntax into
a query to test and I get five records.

Been looking at the url from ms and I'm stumped...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoado.asp


strMsg = "Detailed summary report for the following docs: "

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Set rs = db.OpenRecordset("select [REGULAR-MD] from qryFHCxlsRpt",
dbOpenForwardOnly, dbReadOnly)

Do While Not rs.EOF
'For Each fld In rs.Fields
strMsg = strMsg & fld.Value & "; "
'Next
rs.MoveNext
Loop

 

[Guest]

Rob, finally hit on a combination that worked - thanks for your reply...

strMsg = "Detailed summary report for the following docs: " & vbCrLf

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "qryFHCxlsRpt", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Do While Not rs.EOF
strMsg = strMsg & rs![REGULAR-MD].Value & vbCrLf
rs.MoveNext
Loop

 

[Rob Oldfield]

Very strange. I can't see why the DAO version didn't work for you... but
well done on getting it sorted out.

Rob, finally hit on a combination that worked - thanks for your reply...

strMsg = "Detailed summary report for the following docs: " & vbCrLf

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "qryFHCxlsRpt", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Do While Not rs.EOF
strMsg = strMsg & rs![REGULAR-MD].Value & vbCrLf
rs.MoveNext
Loop

I'm trying to populate a string to send in an email from a recordset. I've
tried a couple things. I get the email, but the body of the email only has
the first sentence, not the list I need from the query. Anybody got any
clues? If so, is there a way to insert a vbCrlf between each dimension of
the array as it goes into strMsg?

Dim rs As DAO.Recordset
Dim vArray As Variant
Dim strTo As String
Dim strCC As String
Dim strSubject As String
Dim strMsg As String

Set rs = CurrentDb.OpenRecordset("Select REGULAR-MD from qryFHCxlsRpt")
rs.MoveLast
vArray = rs.GetRows
strMsg = "Detailed summary report for the following docs:" & vbCrLf
strMsg = strMsg & Join(vArray)

strTo = "(e-mail address removed)"
strSubject = "FHC text"
strCC = ""
DoCmd.SendObject acSendReport, "Family Practice Service Detailed
Report", "Snapshot Format", strTo, strCC, , strSubject, strMsg, False

rs.Close

 

[George Nicholson]

Maybe because you're missing:
Set db = CurrentDB

Therefore, since db = Nothing, then rs = Nothing. This causes an error on
the "Do While" because its the first time you try to use rs.

"Set rs = CurrentDB.OpenRecordset(etc)" is *not* the same. You can't use
CurrentDB directly with OpenRecordset. You need to establish your db
reference on a separate line when using OpenRecordset or, when you go to use
that recordset, you will get the same results as if you hadn't set db at
all.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Rob,
Ummm. Well, I commented out everything and then un-commented one step at
a
time. It started hanging when I got to the Do While statement. I
commented
out the rs movement and just tried the field stuff, and the email came
through blank except for the line before the rs. When I execute, I get
the
calculating... in the lower left, so I think something is happening. Put
back the rs movement, and it froze again. I copy/pasted the sql syntax
into
a query to test and I get five records.

Been looking at the url from ms and I'm stumped...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoado.asp


strMsg = "Detailed summary report for the following docs: "

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Set rs = db.OpenRecordset("select [REGULAR-MD] from qryFHCxlsRpt",
dbOpenForwardOnly, dbReadOnly)

Do While Not rs.EOF
'For Each fld In rs.Fields
strMsg = strMsg & fld.Value & "; "
'Next
rs.MoveNext
Loop

You'll need to loop through the records returned, building the string as
you
go. I also don't see any advantage to reading the recordset into an
array
so I'd modify it to something like...

Set rs = CurrentDb.OpenRecordset("Select REGULAR-MD from qryFHCxlsRpt")
strMsg = "Detailed summary report for the following docs:" & vbCrLf
with rs
do until .eof
strmsg=strmsg & !REGULAR-MD & vbcrlf
.movenext
loop
end with

If you did want to use an array to do it then again you'd have to loop
e.g.

dim i as integer
for i = 0 to ubound(varray)
strmsg = strmsg & varray(i) & vbcrlf
next