Solution: RecordSet always returns 1 record...SQL says otherwise!

[Simon Harris]

Hi All,

I've just spent 3 hours working on getting a simple recordset loop to go
through all the records that the SQL returned.

Heres my code, commented with how I eventually fixed it:

Dim conn As DAO.Database
Set RSCheckCurrentReadings = conn.OpenRecordset(strSQL)

RSCheckCurrentReadings.MoveFirst <<< Without these two lines, the
recordset loop only returned 1 record
RSCheckCurrentReadings.MoveLast <<<
Do While Not RSCheckCurrentReadings.EOF
MsgBox (CInt(RSCheckCurrentReadings("idInvoice")))
Loop

Hopefully that will be helpful to someone in the future, took me a lot of
Googling to work it out.

Thanks to Graham Mandeno and John Vinson for giving me some very useful
pointers in solving this.
http://groups.google.co.uk/group/mi...b12ed5fc?lnk=st&rnum=5&hl=en#59b0de72b12ed5fc

But...I still dont understand why I need to movelast AND movefirst. (Move
first before a loop I can understand).

Cheers all!
Simon.

--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!

 

[Douglas J Steele]

Actually, only the MoveLast should be required.

As well, your code as presented is going to constantly pop up the message
box.

 

[Sylvain Lafontaine]

Unless you want the total count number of rows, making a MoveLast followed
by a MoveFirst should be useless in your case and doing a MoveFirst right
after the OpenRecordset should always be useless.

You don't give us neither the value of strSQL nor the description of the
table(s); so it's hard to tell you why you have hitting this strange bug.
BTW, you don't even give us the value of the ID returned when you have the
bug.