Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by Simon Harris, Jul 28, 2006.

  1. Simon Harris

    Simon Harris
    Expand Collapse
    Guest

    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!
     
  2. Loading...


  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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" <too-much-spam@makes-you-fat.com> 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)
    >
    > 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!
    >
    >
     
  4. Sylvain Lafontaine

    Sylvain Lafontaine
    Expand Collapse
    Guest

    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" <too-much-spam@makes-you-fat.com> 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)
    >
    > 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!
    >
     

Share This Page