Please help me with recordset!

[skiminok65]

I opened the recordset by all the rules. There should be a few fields, and it turns out, only one !!!! Head already broke .....

Code:

dim rst as DAO.Recordset
 
dim strSQL as String
 
strSQL = "SELECT table.Name FROM table WHERE ID > 3"
set str = CurrentDB.OpenRecordset(strSQL, dbOpenDynaset)
Debug.Print rst.Recordcount
set rst = Nothing

Resalt of Debug.Print rst.Recordset always =1

[jforbes]

Fields are not Rows. You can think of Fields as columns in your dataset. Since you are selecting:

SELECT table.Name FROM table WHERE ID > 3]

you are only asking for one field to be returned.

But, I don't think that is what you are asking about. It appears that you are debug.printing the RecordCount and are confused as to why you are only getting one record in the RecordCount. There are a couple reasons you would get this. The first is that there is only one record that meets your Where Clause:

SELECT table.Name FROM table WHERE ID > 3

Make sure there is more than one record with an ID over 3.

The second is that the RecordCount property is unreliable until all the records have been viewed/accessed This link explains it fairly well: How to: Count the Number of Records in a DAO Recordset ... There is a big note in the middle of the article specific to what you are running into.

If you put in a

rst.MoveLast

before you attempt to use the RecordCount Property, and you actually have more than one record, you should get the correct results.

lastly, I think str is a typo, and should be rst:

set str = CurrentDB.OpenRecordset(strSQL, dbOpenDynaset)

If you put Option Explicit at the top of your code modules, typos like this will be pointed out by Access: Option Explicit Statement

[NeoPa]

Require Variable Declaration should help with that last part.

You can also find a link to it in Before Posting (VBA or SQL) Code.

People will generally be very hesitant about working with somebody who posts code they've typed in directly - rather than copy/pasted from their project.