Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2006
    Posts
    37

    Unanswered: Select row from Recordset

    I have opened a query in some VBA code using the OpenRecordSet command and am trying to pull data out of it for use in the VBA code.

    This particular query has 1 row and 5 columns.

    I can get the data from the first row and any of the clumns using rs.Fields(#) for any particular column I want 1-5 by replacing # with the column.

    However I do not know how to specfy that I want something from a row other than row 1. For example the data I need is in column five of each Row.

    Can anyone tell me how to get to the other rows of data?

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    can't
    This particular query has 1 row and 5 columns.
    Can anyone tell me how to get to the other rows of data?


    ...don't blame me - that's what you said in your post.

    some possibilities:

    loop through your recordset with the .movenext method, collecting what you need as you go.

    feed a list from your query: you can address the list as an array
    someValue = myList.column(intColumn, intRow)

    if you are in ADO, i believe it has a .getrows method which sticks a rst into an array: try VBA help.

    can you do it direct in your query: if you are trying to sum "column 5" you are by far better off doing this in the query rather than messing around in a recordset

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Since we're gonna pile on ... A query has no rows ever ... A RESULT set does ... As Izy noted, use your fav looping mechanism using the MoveNext method ...

    Hey Izy: DAO recordsets don't start with a column # of zero? ADO does ... That can also cause confusion ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Mike,

    i saw your (i think your) post a few days ago, about using the index rather than the names... i wasn't convinced since names haven't bitten me in the bum so far...

    ...so i have absolutely no idea at all whether a DAO rst column index is 0 or 1 based.

    gut tells me 0-based, but you could very well be right.

    how about that ADO rst.getrows method tho? i'm not aware of an equiv in DAO. i'm not an ADO person, but i bumped into it a few days ago looking for something else ...looks pretty cute if you have to mess around in a random-access fashion with row/col in a modest-sized rst. does it work as advertised?

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by izyrider
    gut tells me 0-based
    Correctamundo. Only library I know that is 1 based is Excel. Oh - and Outlook
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by izyrider
    Mike,

    i saw your (i think your) post a few days ago, about using the index rather than the names... i wasn't convinced since names haven't bitten me in the bum so far...

    ...so i have absolutely no idea at all whether a DAO rst column index is 0 or 1 based.

    gut tells me 0-based, but you could very well be right.

    how about that ADO rst.getrows method tho? i'm not aware of an equiv in DAO. i'm not an ADO person, but i bumped into it a few days ago looking for something else ...looks pretty cute if you have to mess around in a random-access fashion with row/col in a modest-sized rst. does it work as advertised?

    izy
    About the only time that that can happen is if you have X cols of the same name from separate tables pulled in a query WHERE the same name columns are not aliased.

    GetRows: Not random access ... Pulls the specified # of rows (or entire) recordset into a 2 dim array ... Does work. Used it a long time ago ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •