Insert vales from 2 multiselect listbox into a table

[detrie]

The attached samples is is a modified version of what i found here.

I need to create new records into TblFinal that will will include IDs from selections in both listboxes for everything selected

If I select "Park 1", Park 3, Concept 1
tblFinal will have 2 new records

FinalID= 1 ParkID= 1 Concept= 1
FinalID= 2 ParkID= 3 Concept= 1

Note. The Name address and Telephone fields have no value to my project

 

[pbaldy]

Perhaps a more relevant example is what do you want if 2 parks amd 2 concepts are selected? 4 records, with all the combinations?

 

[detrie]

Yes.. Exactly. 4 records would be created. All combinations

 

[pbaldy]

Then your code would look more like this (with another loop variable):

For Each i In Me.ConceptList.ItemsSelected
  For Each x In Me.ParkList.ItemsSelected
    'build and execute an SQL string that uses the i and x values
  Next x
Next i

I'd probably test first to make sure at least one selection was made in each.

 

[detrie]

Hi Paul,
Thanks for your response.
I modified the code as follows but it only tries to insert one record and that record is empty except for the Autonumber.

Dim i As Variant
Dim x As Variant
Dim sqlApprove As String
    
For Each i In Me.ConceptList.ItemsSelected
i = [Forms]![createQueryForm]![ConceptList]
  For Each x In Me.ParkList.ItemsSelected
  x = [Forms]![createQueryForm]![ParkList]
        
  Next x
Next i

    
    sqlApprove = "INSERT INTO TblFinal ( ParkID, ConceptID ) SELECT [Forms]![createQueryForm]![ParkList] AS x, [Forms]![createQueryForm]![ConceptList] AS i;"
    DoCmd.RunSQL sqlApprove

End Sub

 

[pbaldy]

You didn't follow my example. Perhaps this is clearer?

For Each i In Me.ConceptList.ItemsSelected
  For Each x In Me.ParkList.ItemsSelected
    sqlApprove = "INSERT INTO ..."
    DoCmd.RunSQL sqlApprove
  Next x
Next i

But you have to concatenate the listbox values into the string, and you need to use the same reference you did originally, like:

Me.ConceptList.ItemData(i)

 

[pbaldy]

Oh, and you may find this helpful:

http://www.baldyweb.com/SQLWarnings.htm

 

[MarkK]

cross posted: http://www.utteraccess.com/forum/index.php?showtopic=2043353&st=0&gopid=2642491&#entry2642491

 

[detrie]

(Updated Example attached)

I think I'm getting close but I'm not there yet.
If I select 4 values in each of the listboxes, I expect to have 12 new records in my table.
This will create 12 records that are empty except for the autonumber.

If I comment out the sql statement the Debug statement correctly prints the ConceptValues and ParkValues delimited by commas.

I tried to position my lines of code according to the example by pbaldy but I kept getting Next without For errors.. This is the only way I could get past it

My guess is there is something wrong with my SQL Statement as well as my syntax

Thank you

Dim i As Variant
    Dim x As Variant
    Dim ParkValue As String, ConceptValue As String
    Dim sqlApprove As String
    
    
With Me.ConceptList
For Each i In Me.ConceptList.ItemsSelected
ConceptValue = ConceptValue & ", " & Me.ConceptList.ItemData(i)
Next i

    With Me.ParkList
    For Each x In Me.ParkList.ItemsSelected
    ParkValue = ParkValue & ", " & Me.ParkList.ItemData(x)
   
     sqlApprove = "INSERT INTO TblFinal ( ConceptID, ParkID ) VALUES ((ConceptValue),(ParkValue))"
    DoCmd.RunSQL sqlApprove
  

    Next x
    End With

End With
 Debug.Print "ConceptValue =  "; ConceptValue & "   -   " & "ParkValue= "; ParkValue

 

[MarkK]

Your loops are still not nested. I would expect...

start concept loop
   start park loop
      perform update using current concept, current park
   end park loop
end concept loop

You code does...

start concept loop
end concept loop
start park loop
   perform update, but concept loop data not available because loop has ended
end park loop

 

[pbaldy]

Like I said:

But you have to concatenate the listbox values into the string)

like you did in your original SQL. Also, you don't want to concatenate the listbox values, just use the current one.

 

[detrie]

I'm sorry to be so thick.
Ok, I think there is still something wrong with my loop.
This returns only returns the last item selection of each list

  Dim i As Variant
    Dim x As Variant
    Dim ParkValue As String, ConceptValue As String
    Dim sqlApprove As String
    
    
With Me.ConceptList
For Each i In Me.ConceptList.ItemsSelected
'ConceptValue = ConceptValue & ", " & Me.ConceptList.ItemData(i)
ConceptValue = Me.ConceptList.ItemData(i)
Next i


   With Me.ParkList
    For Each x In Me.ParkList.ItemsSelected
   ' ParkValue = ParkValue & ", " & Me.ParkList.ItemData(x)
    ParkValue = Me.ParkList.ItemData(x)
    Next x
    
'Removes leading comma
'    ConceptValue = Right(ConceptValue, Len(ConceptValue) - 1)
'    ParkValue = Right(ParkValue, Len(ParkValue) - 1)
    
    sqlApprove = "INSERT INTO TblFinal ( ParkID, ConceptID )  " & "VALUES('" & ParkValue & "','" & ConceptValue & "');"

    DoCmd.RunSQL sqlApprove

End With
    End With

End Sub

 

[pbaldy]

For the third time, you have to execute the sql inside the loop.

 

[MarkK]

Detrie, so now you are posting back at www.utteraccess.com/forum/index.php?showtopic=2043353 without indication???

I thought we had an understanding. Remember this?

Hey Mark,
That makes perfect sense to me. I always worry about 'cross posting' for that very reason.
Great advice, thanks for the heads up

 

[detrie]

That thread already had indication. Should every post include indication?

 

[MarkK]

Do as you wish. Maybe someone will jump in,

Cheers,