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

INSERT INTO tbl values from Unbound ListBox

P: 28
Good Afternoon Pro's,

I am attempting to append values to a table that are loaded into a listbox. I have found NUMEROUS resources for this topic, however it's all regarding the .ItemsSelected property.

Question: Is there a way to Insert the values of a listbox without actually having the user select the items? Seems like a very simple task but I'm left dumbfounded. The below code works perfectly but I'd like to swap out the .ItemsSelected property with something like ".Items" Hope this makes sense. Thanks again for your time.

Paul



Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim varValue        As Variant
  3. Dim varitem         As Variant
  4.  
  5.  
  6.  With Me.List
  7.             For Each varValue In List.ItemsSelected
  8.                  CurrentDb.Execute "INSERT INTO [Team_Proj] ([ID], [Team_Member], ion_ID) Values " & _
  9.                             "('" & Me.Text1 & "', '" & Me.List.Column(0, varValue) & "', " & Me.Text2 & ")"
  10.             Next
  11.         End With
  12.  
Feb 21 '14 #1

✓ answered by ADezii

Assuming you have a 3-Column List Box named lstTest, consisting of the following Fields: [ID], [Last Name], and [First Name], the following Code will display each Row of the List Box in a Comma-Delimited Format:
Expand|Select|Wrap|Line Numbers
  1. Dim intCount As Integer
  2. Dim intRowCtr As Integer
  3. Dim lst As ListBox
  4.  
  5. Set lst = Me![lstTest]
  6.  
  7. intCount = Me![lstTest].ListCount       'Number of Items
  8.  
  9. For intRowCtr = 0 To lst.ListCount - 1
  10.   Debug.Print lst.Column(0, intRowCtr) & "," & _
  11.               lst.Column(1, intRowCtr) & "," & _
  12.               lst.Column(2, intRowCtr)
  13. Next
  14.  
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. 2,Cencini,Andrew
  2. 1,Freehafer,Nancy
  3. 8,Giussani,Laura
  4. 9,Hellung-Larsen,Anne
  5. 3,Kotas,Jan
  6. 6,Neipper,Michael
  7. 4,Sergienko,Mariya
  8. 5,Thorpe,Steven
  9. 7,Zare,Robert
P.S. - The rest you should be able to figure out.

Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,701
Assuming you have a 3-Column List Box named lstTest, consisting of the following Fields: [ID], [Last Name], and [First Name], the following Code will display each Row of the List Box in a Comma-Delimited Format:
Expand|Select|Wrap|Line Numbers
  1. Dim intCount As Integer
  2. Dim intRowCtr As Integer
  3. Dim lst As ListBox
  4.  
  5. Set lst = Me![lstTest]
  6.  
  7. intCount = Me![lstTest].ListCount       'Number of Items
  8.  
  9. For intRowCtr = 0 To lst.ListCount - 1
  10.   Debug.Print lst.Column(0, intRowCtr) & "," & _
  11.               lst.Column(1, intRowCtr) & "," & _
  12.               lst.Column(2, intRowCtr)
  13. Next
  14.  
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. 2,Cencini,Andrew
  2. 1,Freehafer,Nancy
  3. 8,Giussani,Laura
  4. 9,Hellung-Larsen,Anne
  5. 3,Kotas,Jan
  6. 6,Neipper,Michael
  7. 4,Sergienko,Mariya
  8. 5,Thorpe,Steven
  9. 7,Zare,Robert
P.S. - The rest you should be able to figure out.
Feb 22 '14 #2

P: 28
ADezii,

Worked like a charm! Great job.

One last question about this code. I've always been curious about this structure of the FOR NEXT loop.

This Line:
Expand|Select|Wrap|Line Numbers
  1. For intRowCtr = 0 To lst.ListCount - 1
  2.  
I understand this is looping through the index and recording each item accordingly, but how does "0" to "-1" work? Is each iteration subtracting 1 from the index until it reaches:
Expand|Select|Wrap|Line Numbers
  1. intCount = Me![lstTest].ListCount
  2.  
?

Thanks again!
Paul
Feb 22 '14 #3

ADezii
Expert 5K+
P: 8,701
The Column Property of the ListBox Control is Zero based and is an Integer that can range from 0 to the setting of the ListCount property minus 1. Each Iteration of the Loop is adding/not subtracting 1. There are 9 Items in the List Box so the actual Loop would be:
Expand|Select|Wrap|Line Numbers
  1. For intRowCtr = 0 to 8     '9 Iterations
  2.   'Loop processing Code here
  3. Next
Hope this explains matters well enough.
Feb 22 '14 #4

Post your reply

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