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

  • Thread starter Thread starter Simon Harris
  • Start date Start date
S

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!
 
Actually, only the MoveLast should be required.

As well, your code as presented is going to constantly pop up the message
box.
 
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top