ADO query EOF problem

"MikeV06" wrote in message
...
The below Sub returns 2 columns and 3 records. The open shows Recs = 3.
The
If statement to move through the recordset returns the name in the first
row but does not move through the next 2 rows. Evidently it thinks it is
EOF and quits. I tried Not .EOF, but it also only returned the first row.
What am I doing wrong. I am using Access 2003.

Thank you, Mike.

Public Sub Retrieve_Test_2()
' ADO - ActiveX Data Objects Architecture directly supported.
' Provides a common set of objects across any data engine including
' SQL Server
Dim cnThisConnect As ADODB.Connection
Dim rstCorpNames As ADODB.Recordset
Dim fldEach As ADODB.Field
Dim strSQL As String
Dim CName As String
Dim Cnt, Recs
Set cnThisConnect = CurrentProject.Connection
Cnt = 1
pstrco = "'1000000001'"
Set rstCorpNames = New ADODB.Recordset
strSQL = "SELECT [tblCorp Name].Corp, " & _
"[tblCorp Name].CorpName FROM [tblCorp Name]" & _
"ORDER BY [tblCorp Name].Corp"
rstCorpNames.Open strSQL, _
cnThisConnect, adOpenKeyset, adLockOptimistic, adCmdText
Recs = rstCorpNames.RecordCount
MsgBox ("Record Count " & Recs)
MsgBox ("rstCorpNames(1) " & rstCorpNames.Fields(1).Name)
For Each fldEach In rstCorpNames.Fields
MsgBox fldEach.Name
Next
rstCorpNames.MoveFirst
If Cnt Recs Then
CName = rstCorpNames!CorpName
MsgBox ("CorpName = " & CName)
rstCorpNames.MoveNext
Cnt = Cnt + 1
MsgBox ("Cnt & Recs = " & Cnt & " " & Recs)
End If
rstCorpNames.Close
Set rstCorpNames = Nothing
End Sub



I believe your problem lies he

rstCorpNames.Open strSQL, _
cnThisConnect, adOpenKeyset, adLockOptimistic, adCmdText
Recs = rstCorpNames.RecordCount

When the recordset is first opened, the RecordCount property is not
reliable. Normally, it only shows the number of records that have been
accessed so far, which is usually 0 (for an empty recordset) or 1 (for the
first record). It may also be -1, if ADO is unable to determine the number
of records at this time.

The RecordCount property only becomes reliable when you have traversed the
entire recordset. So if you need to know *before looping* how many records
there are, you would do this:

With rstCorpNames

.Open strSQL, _
cnThisConnect, adOpenKeyset, adLockOptimistic, adCmdText

If Not .EOF Then
.MoveLast
Recs = .RecordCount
.MoveFirst
End If

End With

More often, though, you don't need to know in advance how many records there
are, and you can just loop until .EOF:

With rstCorpNames

.Open strSQL, _
cnThisConnect, adOpenKeyset, adLockOptimistic, adCmdText

Do Until .EOF Then
Cnt = Cnt + 1
CName = !CorpName
MsgBox ("CorpName = " & CName)
.MoveNext
Loop

.Close

End With


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)