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

    For Each LOOP statement

    'For Each' Loop on a multi-select listbox

    ----------------------------------------------------
    For Each Item In Me.MyListbox.ItemsSelected

    var_Item = Me.
    MyListbox.Column(0)

    DoCmd.RunSQL "insert into tbl_InvoiceLink (Invoice_ID, Inv_DP_ID)
    values (" & int_NewInvoice & ", " & var_Item & ")"



    Next Item


    -----------------------------------------------------

    The listbox is set at as multiselect.
    The FOR loop correctly identifies the item selected - and the SQL statement works.

    BUT - it does not go to the next item in the list..

    Do I need to create an Array? The help isnt very helpful in this..

    thanks in advance

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I believe in order for your For Each Item loop to be useful, you would need to use Item inside the loop somewhere.

  3. #3
    CGM3 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Atlanta, GA
    Posts
    49
    Just a thought, could the DoCmd operation be clearing the list box's "selection flags"? You might want to put some breakpoints before and after that line to check the list's status.

    If that is the problem, the simplest solution would be to make var_Item a string array, store each checked item in that array in the loop you have (with a counter variable, of course, incremented for each one), then have a second loop that runs if the counter is greater than zero to execute the DoCmd for each item in var_Item.

    On the other hand, RuralGuy may have a point; one of the examples for the ItemsSelected property is:

    Code:
    Sub AllSelectedData()
        Dim frm As Form, ctl As Control
        Dim varItm As Variant, intI As Integer
     
        Set frm = Forms!Contacts
        Set ctl = frm!Names
        For Each varItm In ctl.ItemsSelected
            For intI = 0 To ctl.ColumnCount - 1
                Debug.Print ctl.Column(intI, varItm)
            Next intI
            Debug.Print
        Next varItm
    End Sub
    --which seems to indicate you might need to make that code something like:

    Code:
    For Each vItem In Me.MyListbox.ItemsSelected
     
       var_Item = Me.MyListbox.Column(0, vItem)
     
     DoCmd.RunSQL "insert into tbl_InvoiceLink (Invoice_ID, Inv_DP_ID)
    values (" & int_NewInvoice & ", " & var_Item & ")"
     
    Next vItem
    (I'd avoid variable names like Item that could be reserved VBA words.)

  4. #4
    zambam737 is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    6
    var_Item = Me.MyListbox.Column(0, vItem)


    adding the
    vItem to the Column function fixed it..

    thanks a million CGM3 !!!

    zambam

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

Similar Threads

  1. update table in loop based on IF statement
    By LALouw in forum Access
    Replies: 0
    Last Post: 07-26-2009, 08:46 PM
  2. IIF statement
    By james1982 in forum Access
    Replies: 1
    Last Post: 07-20-2009, 09:38 AM
  3. how to use IF then statement
    By ronnie4 in forum Access
    Replies: 1
    Last Post: 03-17-2009, 11:32 PM
  4. concatenate string using loop
    By nengster in forum Programming
    Replies: 0
    Last Post: 02-23-2009, 08:05 PM
  5. Loop a table to use field in query
    By jdubp in forum Programming
    Replies: 0
    Last Post: 03-04-2008, 11:48 AM

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