Results 1 to 6 of 6

Thread: Help with loop

  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Help with loop

    I am trying to write a loop that will insert the number of records equal to the number of items selected from one listbox multiplied by the number selected from a second listbox.

    For example:

    I have a listbox of 10 items. I have selected 3 rows from it. The rows selected had values: AAA, BBB, and CCC
    I have another listbox of 10 items. I have selected 3 rows from it. The rows selected had values: 1, 2, and 3

    I want to insert the following records into a table called ParamsForUpdate:

    AAA, 1
    AAA, 2
    AAA, 3
    BBB, 1
    BBB, 2
    BBB, 3
    CCC, 1
    CCC, 2
    CCC, 3

    Here's my code, but it doesn't work.

    Code:
    Dim i As Integer
    Dim x As Integer
    For i = 0 To DealerListBox.ListCount - 1
             For x = 0 To StockClass1ListBox.ListCount - 1
                 If StockClass1ListBox.Selected(x) Then
                     strIN_2 = StockClass1ListBox.Column(0, x)
          
                 If DealerListBox.Selected(i) Then
                     strIN_1 = DealerListBox.Column(0, i)
    
                     CurrentProject.Connection.Execute "INSERT INTO PARAMSFORUPDATE ([Location],[StockClass1]) " & _
                     "VALUES ('" & strIN_1 & "','" & strIN_2 & "' )"
                   
                 End If
             Next x
          Next i
    Last edited by ssmith001; 06-07-06 at 20:29.

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Try
    PHP Code:
    Dim SelectedItem As Variant
    For Each selectedItem In yourListBox.ItemsSelected
       your INSERT statement
    next 
    Do this twice for the two list boxes

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I do multiselect listboxes a little differently, but this worked in a quick and dirty test:
    Code:
      Dim frm              As Form
      Dim ctl              As Control
      Dim varItem          As Variant
      Dim ctl1             As Control
      Dim varItem1         As Variant
      Dim strSQL           As String
    
      Set frm = Forms!form11
      Set ctl = frm!lstTest
      Set ctl1 = frm!List3
    
      For Each varItem In ctl.ItemsSelected
        For Each varItem1 In ctl1.ItemsSelected
    
          strSQL = "INSERT INTO BillM (FY, FP) " _
                 & "VALUES( '" & ctl.ItemData(varItem) & "', " & ctl1.ItemData(varItem1) & ");"
          CurrentDb.Execute strSQL
        Next varItem1
      Next varItem
    In my test, the second value was numeric thus not surrounded by single quotes; you'd have to correct for that.
    Paul

  4. #4
    Join Date
    Sep 2005
    Posts
    220
    Paul, thanks for the reply. I had mine (shown below) almost working, then I saw your post. Rather than just copying your code, I'm trying to understand how ours are different and why mine doesn't quite work. It's getting the correct dealers from the DealerListBox, but it's not correctly getting the value(s) from the StockClass1ListBox. Can you see a problem with my code?

    Code:
        If Response = vbYes Then
          For i = 0 To DealerListBox.ListCount - 1
             For x = 0 To StockClass1ListBox.ListCount - 1
                If StockClass1ListBox.Selected(x) Then
                    strStkClass1 = StockClass1ListBox.Column(0, x)
                    If DealerListBox.Selected(i) Then
                        strDlr = DealerListBox.Column(0, i)
    
                        CurrentProject.Connection.Execute "INSERT INTO PARAMSFORUPDATE ([Location],[StockClass1]) " & _
                        "VALUES ('" & strDlr & "','" & strStkClass1 & "' )"
                   
                    End If
                End If
             Next x
          Next i
    
        End If

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I copied that into mine, changed the names, and got the result I expected, so maybe we should clarify that first. Here's a picture:
    Attached Thumbnails Attached Thumbnails test.JPG  
    Paul

  6. #6
    Join Date
    Sep 2005
    Posts
    220
    OK, I think I am close. The DealerListBox values are being selected properly, but the values from the StockClass1ListBox are incorrect. The StockClass1ListBox listbox has 2 columns and I want the 1st column. It's grabbing a data value of '900' for example, but neither the 1st or 2nd column values in this listbox contain '900'. I don't know where it's getting this data from. It is also creating one too many rows. The first row is all blanks.

Posting Permissions

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