Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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!
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page