dao recordset error

[Janet Barnett via AccessMonster.com]

Any ideas on the following? Everything works great except for the field
contents I'm trying to retrieve from the recordset. If I run with the rs
movement commented out, I get the file and email but no docs in the body.
Seems like I should get the first one? I do get the file save, then
calculating... in the lower left info bar, then the email. If I put rs
movement back in, it hangs the application until I have to terminate in
task list. If I take the sql syntax and put into a query and execute, I get
five records, none are nulls. Libraries are: VB for apps, Access 10 object
library, DAO 3.6, and ole automation. Really been struggling. Tried to
switch it to ADO, but even when I added in what I thought was the correct
library, still wouldn't run.

Been trying to figure it out through:
http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/dndao/html/daotoado.asp Any ideas?

Dim strFileName As String
Dim strTo As String
Dim strCC As String
Dim strSubject As String
Dim strMsg As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field

'''create excel file for triage nurse
strFileName = "f:\users\janetb\fhc" & Format$(Date, "mm-dd-yy") & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
"qryFHCxlsRpt", strFileName, True, ""

strMsg = "Detailed summary report for the following docs: " & vbCrLf
strTo = "(e-mail address removed)"
strSubject = "FHC text"
strCC = ""

'''get list of docs
Set rs = db.OpenRecordset("select [REGULAR-MD] from qryFHCxlsRpt",
dbOpenForwardOnly, dbReadOnly)

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

''''send email to docs listing applicable docs in email body
DoCmd.SendObject acSendReport, "Family Practice Service Detailed Report",
"Snapshot Format", strTo, strCC, , strSubject, strMsg, False

rs.Close

 

[Ken Snell [MVP]]

Change this line:
strMsg = strMsg & rs!fld.Value & vbCrLf

to this:
strMsg = strMsg & fld.Value & vbCrLf

 

[Janet Barnett via AccessMonster.com]

Thanks for the reply. Actually, that was one of my iterations, and it
didn't work. But, I finally got the right combination in ADO and this
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

 

[Ken Snell [MVP]]

I didn't try to move you away from using the fld object, as that was what
you were already using. But either of these approaches will do what you
seek, using DAO recordset (and are the ways that I would use -- using the
fld object approach is unnecessary overhead):

Do While Not rs.EOF
strMsg = strMsg & rs.Fields(0).Value & vbCrLf
rs.MoveNext
Loop

or

Do While Not rs.EOF
strMsg = strMsg & rs![REGULAR-MD].Value & vbCrLf
rs.MoveNext
Loop

or

Do While Not rs.EOF
strMsg = strMsg & rs.Fields("REGULAR-MD").Value & vbCrLf
rs.MoveNext
Loop