MS Access: RecordSet always returns 1 record…SQL says otherwise!

June 23, 2006

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)

‘Without these two lines, the recordset loop only returned 1 record
RSCheckCurrentReadings.MoveFirst
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.

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: