By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,537 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Execute the sql inside the loop.

P: 6
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.


Expand|Select|Wrap|Line Numbers
  1.   Dim i As Variant
  2.     Dim x As Variant
  3.     Dim ParkValue As String, ConceptValue As String
  4.     Dim sqlApprove As String
  5.  
  6.  
  7. With Me.ConceptList
  8. For Each i In Me.ConceptList.ItemsSelected
  9. 'ConceptValue = ConceptValue & ", " & Me.ConceptList.ItemData(i)
  10. ConceptValue = Me.ConceptList.ItemData(i)
  11. Next i
  12.  
  13.  
  14.    With Me.ParkList
  15.     For Each x In Me.ParkList.ItemsSelected
  16.    ' ParkValue = ParkValue & ", " & Me.ParkList.ItemData(x)
  17.     ParkValue = Me.ParkList.ItemData(x)
  18.     Next x
  19.  
  20.     sqlApprove = "INSERT INTO TblFinal ( ParkID, ConceptID )  " & "VALUES('" & ParkValue & "','" & ConceptValue & "');"
  21.  
  22.     DoCmd.RunSQL sqlApprove
  23.  
  24. End With
  25.     End With
  26.  
  27. End Sub
Apr 19 '17 #1

✓ answered by 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.

Share this Question
Share on Google+
2 Replies


PhilOfWalton
Expert 100+
P: 1,430
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
Apr 19 '17 #2

NeoPa
Expert Mod 15k+
P: 31,186
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.
Apr 19 '17 #3

Post your reply

Sign in to post your reply or Sign up for a free account.