Recordset Loop Unexpected Behaviour

#1 (**permalink**) 28-Jul-2006, 08:23 AM

Hi All,

The following code is only showing me one message box with one idInvoice.
Yet the SQL behind it returns 4 distinct rows, each with a different
idInvoice.

Set conn = CurrentDb
Set RSCheckCurrentReadings = conn.OpenRecordset(strSQL)
Do While Not RSCheckCurrentReadings.EOF
MsgBox (CInt(RSCheckCurrentReadings("idInvoice")))
RSCheckCurrentReadings.MoveNext
Loop

Any ideas will be much appreciated!

Thanks!
Simon.

--
-
* Please reply to group for the benefit of all
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11718-recordset-loop-unexpected-behaviour.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11718
* 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!

#2 (**permalink**) 28-Jul-2006, 08:23 AM

On Mon, 19 Jun 2006 21

03 +0100, "Simon Harris"
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11718

wrote:

>Hi All,
>
>The following code is only showing me one message box with one idInvoice.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11718
>Yet the SQL behind it returns 4 distinct rows, each with a different
>idInvoice.
>
>Set conn = CurrentDb
>Set RSCheckCurrentReadings = conn.OpenRecordset(strSQL)
>Do While Not RSCheckCurrentReadings.EOF
> MsgBox (CInt(RSCheckCurrentReadings("idInvoice")))
> RSCheckCurrentReadings.MoveNext
>Loop
>
>Any ideas will be much appreciated!
>
>Thanks!
>Simon.

It may be helpful to put a line

RSCheckCurrentReadings.MoveFirst

before the DO WHILE loop.

John W. Vinson[MVP]

#3 (**permalink**) 28-Jul-2006, 08:23 AM

Hi Simon

No idea. But have you declared your object variables correctly?

Dim conn as DAO.Database
Dim RSCheckCurrentReadings as DAO.Recordset

I'm suspicious that your variable name "conn" suggests an ADODB.Connection,
while it should be a DAO.Database.

Also, have you tried?
RSCheckCurrentReadings.MoveLast
MsgBox RSCheckCurrentReadings.RecordCount

Of course, this should give 4.

If you're still in trouble, post some more code including variable
declarations and the value of strSQL.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Simon Harris" wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11718
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11718
news:u74RFz9kGHA.4888@TK2MSFTNGP02.phx.gbl...
> Hi All,
>
> The following code is only showing me one message box with one idInvoice.
> Yet the SQL behind it returns 4 distinct rows, each with a different
> idInvoice.
>
> Set conn = CurrentDb
> Set RSCheckCurrentReadings = conn.OpenRecordset(strSQL)
> Do While Not RSCheckCurrentReadings.EOF
> MsgBox (CInt(RSCheckCurrentReadings("idInvoice")))
> RSCheckCurrentReadings.MoveNext
> Loop
>
> Any ideas will be much appreciated!
>
> Thanks!
> 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!
>