DAO SQL Recordset RecordCount Incorrect When Compared to Query? 2

[kjv1611]

I have a DAO recordset I am using to loop through a subset of table records by building the recordset off of a SQL string.

If I put the SQL string in a new query, then I get 28 records. However, the .RecordCount of the recordset is only showing 1 with the same SQL code.

Can anyone give me any hints as to what might be going on?

Here's the code where this occurs:

Private Sub ClickMe_Click()
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim strSQL As String
  Dim QU As String

  QU = Chr(34) [green]'Double Quote[/green]

  strSQL = "SELECT i.* FROM tblImports i " & Chr(13) & Chr(9) & _
             "WHERE (i.ObjectType = " & QU & "ws" & QU & ") AND " & Chr(13) & Chr(9) & _
             "(i.Approved = True)"
 
  Set db = CurrentDb
  Set rs = db.OpenRecordset(strSQL)

  If rs.RecordCount > 0 Then
    Do While Not rs.EOF
      'Other Code here...
      rs.MoveNext
    Loop
  End If

  rs.Close
  Set rs = Nothing
  db.Close
  Set db = Nothing
End Sub

And the table structure it is querying from is this:[tt]
Field DataType
ImportID AutoNumber
DateImported Text
FileName Text
ObjectName Text
ObjectType Text
ImportedBy Text
Approved Yes/No
[/tt]

The full table at the moment has 60 records in it.

Here is some sample data (well, I'll leave off irrelavent fields from examples, to make them fit on screen):
[tt]FileName ObjectName ObjectType Approved
FullPath.xls Sheet1 ws Yes
FullPath.xls Sheet2 ws Yes
FullPath1.xls Summary ws No
[/tt]
The field, "ObjectType", was one I came up with to specify whether the file was an Excel, Access, or text file.

The Yes/No field could be the problem, I suppose, but I was under the impression that you can relate to Yes/No the same as True/False. Is this not the case? Should I be using "Yes" instead of True?

I'll get back to trying that method in an hour or two, hopefully...

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

You may try this:

...
  End If
[!]MsgBox rs.RecordCount[/!]
  rs.Close
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sxschech]

...
...
 Set rs = db.OpenRecordset(strSQL)

[red]
 rs.movelast
 rs.movefirst
[/red]
  If rs.RecordCount > 0 Then
    Do While Not rs.EOF
...
...

 

[kjv1611]

Well, sxschech's method worked.

Can anyone tell me why? Shouldn't it work correctly just be being instantiated?

Either way, pinky to sxschech!

--

"If to err is human, then I must be some kind of human!" -Me

 

[LarrySteele]

Good question, because the answer is not intuitive. The reason you have to navigate the recordset is that recordcount gives you the count of records that have been access, not the count of records in the recordset.

For more info: How to: Count the Number of Records in a DAO Recordset

HTH,
Larry

 

[kjv1611]

Thanks for the background and reference - I had just recently looked up the same reference, but didn't read it in depth, merely looked at the examples at the time.

--

"If to err is human, then I must be some kind of human!" -Me