Getrows to string

[janetb99]

trying to send file with email body populated by one field. Get the email with attachment fine, but can't get the fields to display in the body.

I've tried: the following three and nothing seems to populate?

1.
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 = "janetb@mtn.ncahec.org"
strSubject = "FHC text"
strCC = ""
DoCmd.SendObject acSendReport, "Family Practice Service Detailed Report", "Snapshot Format", strTo, strCC, , strSubject, strMsg, False

rs.Close

2. Dim db As Database
Dim rs As Recordset
Dim data As Variant
Dim strTo As String
Dim strCC As String
Dim strSubject As String
Dim strMsg As String
'Dim myArray As String
'myArray = Recordset.GetRows(rsDocs)

strMsg = "Detailed summary report for the following docs:" & vbCrLf
Set db = OpenDatabase("f:\users\janetb\listXP.mdb")
Set rs = db.OpenRecordset("Select REGULAR-MD from qryFHCxlsRpt")
Set data = rs.GetRows(0)
strMsg = strMsg & data

strTo = "janetb@mtn.ncahec.org"
strSubject = "FHC text"
strCC = ""
DoCmd.SendObject acSendReport, "Family Practice Service Detailed Report", "Snapshot Format", strTo, strCC, , strSubject, strMsg, False

rs.Close
db.Close

3. 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
Dim intI As Integer, intJ As Integer

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

' Find upper bound of second dimension.
For intI = 0 To UBound(vArray, 1)
strMsg = strMsg & vArray(intI)
Next intI


strTo = "janetb@mtn.ncahec.org"
strSubject = "FHC text"
strCC = ""
DoCmd.SendObject acSendReport, "Family Practice Service Detailed Report", "Snapshot Format", strTo, strCC, , strSubject, strMsg, False

rs.Close


Any help appreciated....

 

[PHV]

Why using MoveLast before GetRows ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Eupher]

Hmm... Never used the GetRows method before, but I poked around in the help files a bit and I think I may see where you're getting snagged. In your first example, you haven't specified the number of rows that GetRows is supposed to get. Further, GetRows returns a two-dimensional array; the Join command requires a one-dimensional array as its first argument.

In example 2, you told GetRows to get 0 rows - I know this is confusing, because array subscripts are zero-based and GetRows returns an array - but the argument specifying the number of rows to return is not zero-based.

In example 3, I think you are getting closer - you have discovered you can't avoid looping through the recordset to build your string. But once again, you didn't tell GetRows how many rows to get.

It strikes me that this is a bit at cross-purposes with the point of the GetRows method. Certainly you can use GetRows to get all the rows of a recordset and assign it to an array, but why in this case? You've already got the recordset open - all the rows are already gotten. I think I would just use the EOF property and the MoveNext method to step through the records and build the text string:

(I agree with PHV, I think the MoveLast method is unnecessary in this context - if you are going to keep it, use MoveFirst to reposition the cursor to the first record in the recordset before using this code)

Do While Not Rs.EOF
     strMsg = strMsg & Rs![REGULAR-MD] & vbCrLf
     Rs.MoveNext
Loop
'optional code to strip trailing vbCrLf
strMsg = Left(strMsg, Len(strMsg) - 1)

HTH...

Ken S.

 

[janetb99]

Eupher,
Thanks much for the explanation and response. What you're saying does make sense. I tried your approach, but now Access hangs until I kill it with Task Manager. Would appreciate more insight:

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

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select REGULAR-MD from qryFHCxlsRpt")
Do While Not rs.EOF
strMsg = strMsg & rs![REGULAR-MD] & vbCrLf
rs.MoveNext
Loop

 

[Eupher]

janetb99 -

Where is your code hanging? Have you tried setting a break and stepping through the code one line at a time?

Ken S.

 

[janetb99]

Eupher Ken,
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

 

[PHV]

And this ?
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
Set db = CurrenDB
Set rs = db.OpenRecordset("select [REGULAR-MD] from qryFHCxlsRpt", dbOpenForwardOnly, dbReadOnly)
strMsg = "Detailed summary report for the following docs: "
Do While Not (rs.EOF Or rs.BOF)
strMsg = strMsg & rs![REGULAR-MD] & "; "
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing
MsgBox strMsg

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[janetb99]

Finally hit on a combination that worked. Many thanks to all for replying. Here's the one that worked:

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