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

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11963-solution-recordset-always-returns-1-record.html
http://groups.google.co.uk/group/mic...b0de72b12ed5fc

But...I still dont understand why I need to movelast AND movefirst. (Move
first before a loop I can understand).
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11963

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!

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

Actually, only the MoveLast should be required.

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Simon Harris" wrote in message
news:e52gW1rlGHA.3632@TK2MSFTNGP03.phx.gbl...
> 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)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11963
>
> 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/mic...b0de72b12ed5fc
>
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11963
> * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
>
>

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.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Simon Harris" wrote in message
news:e52gW1rlGHA.3632@TK2MSFTNGP03.phx.gbl...
> 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11963
>
> 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/mic...b0de72b12ed5fc
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11963
>
> 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!
>