Execute the sql inside the loop.

[DetrieZ]

This code is supposed to insert all selected items from two list boxes into a table.
It only inserts one record regardless of how any are selected.

I've been told three times that I have to execute the SQL inside the loop. I have tried everything I can think of but I just don't get how to do it.

  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
 
    sqlApprove = "INSERT INTO TblFinal ( ParkID, ConceptID )  " & "VALUES('" & ParkValue & "','" & ConceptValue & "');"
 
    DoCmd.RunSQL sqlApprove
 
End With
    End With
 
End Sub

[PhilOfWalton]

Several questions spring to mind.

Where is the data coming from that populates your list box? presumably already from one or more tables?

Are you trying to add a "parallel" item from each list box to your new table?

What is supposed to happen if a different number of items is picked from each list box?

Have you thought of having a Yes/No field like ConceptSelected and ParkSelected in your tables, using the list boxes to set those values to true, and just using a query to display the results?

Phil

[NeoPa]

DetrieZ:
I've been told three times that I have to execute the SQL inside the loop.

As there are two loops this explanation is a little short of helpful.

It seems to me, from your initial explanation sentence, that you'd need to have both loops and each would need some SQL executed within it to add all the items separately.

Unfortunately, Jet/ACE, which is what Access uses in DAO, doesn't have a syntax where you can add multiple records from values. Therefore each needs to be run separately.

At this point I'll stop as, looking at your SQL code it seems your question doesn't match what you're trying to do. Your code indicates that the two separate ListBoxes are actually connected in some way and one of each should end up in the destination table record. As nothing you've said explains, or even indicates that, we'll have to wait for you to fix the question to make proper sense before we can continue.