Results 1 to 5 of 5
  1. #1
    zambam737 is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    6

    passing the results of an SQL Select statement through a FOR loop

    hi all,



    I wish to run the results of a query through a FOR loop.

    I have tried using recordset controls and some other methods i found in the help files.

    The only way i can get this to work is this:

    I have a form with two listboxes. The user selects one item from the first listbox (to trap an ID used in the query for the other one). The other listbox is a multi select - extended.

    If i manually select the items returned in the second listbox, my loop code works fine. - does exactly what it is supposed to.

    What I would like to know is: is there a way of using code to select all the items in the second listbox to save having to manually select them...?

    OR - a better way - to run the results of a select statement through a FOR loop.

    I hope that makes sense...

    Thanks in advance

    mark

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I would say it depends on what you're trying to accomplish (ie what are you doing with the selected items?). If it's to filter a query, selecting all is the same as not including that field in the criteria at all. You can do this type of loop to either act on the items directly or select/deselect all:

    Code:
      For i = 0 To Me.ListBoxName.ListCount - 1
        Me.ListBoxName.Selected(i) = True
      Next i
    I'm not clear on what you mean by "run the results of a select statement through a FOR loop". From the sound of it, you'd open a recordset on the SQL and then run a

    Do While Not rs.EOF

    loop on it which would access every record returned.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Paul,

    the OP sent me a private message two hours before this thread was started and I gave a good google search with numerous results.

    I think what he means is he wants a SELECT ALL option. Allen Browne has a page that does it, but that 3-liner you wrote will do it too.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I got a PM as well, but suggested a public thread was more appropriate.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by pbaldy View Post
    I got a PM as well, but suggested a public thread was more appropriate.
    in my PM, I got ... "you seem to know your stuff". Love those!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. select statement
    By jellybeannn in forum Access
    Replies: 5
    Last Post: 08-13-2010, 05:21 AM
  2. Select statement syntax?
    By ksmith in forum Programming
    Replies: 3
    Last Post: 06-24-2010, 09:21 AM
  3. For Each LOOP statement
    By zambam737 in forum Programming
    Replies: 3
    Last Post: 10-26-2009, 09:59 PM
  4. Passing Criteria to IN statement from Form
    By stephenaa5 in forum Queries
    Replies: 1
    Last Post: 10-09-2009, 04:02 AM
  5. update table in loop based on IF statement
    By LALouw in forum Access
    Replies: 0
    Last Post: 07-26-2009, 08:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums