Recordset Loop Unexpected Behaviour

[Simon Harris]

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!

 

[John Vinson]

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.

It may be helpful to put a line

RSCheckCurrentReadings.MoveFirst

before the DO WHILE loop.

John W. Vinson[MVP]

 

[Graham Mandeno]

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.