Listbox Columns INSERT into Table, HALP!

Hello Scripts gurus

I am Filling a Listbox with combo box values
the listbox is showing multiple values
what I am trying to do is get these values to
INSERT into a table!! this code only inserts one.


Thank you in advance,

Expand|Select|Wrap|Line Numbers

1. Private Sub cmdAddProd_Click()
2. If IsNull(Me.lstAddedProd) And Not IsNull(Me.lstProduct) Then
3.  
4.  
5. Dim db As DAO.Database
6. Dim rs As DAO.Recordset
7.  
8. Set rs = Me.RecordsetClone
9.  
10.   With rs
11.  
12.     .AddNew
13.  
14.     !productName = Me.lstProduct.Column(1)
15.     !UnitPrice = Me.lstProduct.Column(2)
16.  
17.     .Update
18.  
19.   End With
20.  
21.    Me.lstAddedProd.Requery
22.    Me.lstProduct.Requery
23.  
24.    Me.lstAddedProd = Null
25.    Me.lstProduct = Null
26.  
27. Set rs = Nothing
28.  
29.   End If
30.  
31. End Sub
32.  

Have a look here for a brief discussion about getting all the selected data out of a multiselect listbox.

Have fun

Jim

Have a look here for a brief discussion about getting all the selected data out of a multiselect listbox.
Have fun
Jim

I will have FUN when this is working! ;-)
I see, thank you for that, it is telling me my method is not the best..

is this close?
I also found this method on this brilliant forum

I will have FUN when this is working! ;-)
I see, thank you for that, it is telling me my method is not the best..

is this close?

Expand|Select|Wrap|Line Numbers

1. Dim valSelect As Variant
2. Dim strValue As String ' just used for the demonstration
3.  
4.     For Each valSelect In Me.lstProduct.ItemsSelected
5.         strValue = strValue & "'" & Me.lstProduct.ItemData(valSelect) & "', "
6.     Next valSelect
7.  
8. End Sub

I also found this method on this brilliant forum

Expand|Select|Wrap|Line Numbers

1.         Private Sub cmdSubmit_Click()
2.         Dim valSelect As Variant
3.         Dim strSQL As String
4.  
5.            For Each valSelect In Me.listboxName.ItemsSelected
6.               strSQL = "INSERT INTO Interest (applicantID, officeID) VALUES (" & _
7.               Me.applicantID & ", " & Me.listboxName.ItemData(valSelect) & ")"
8.            Next valSelect
9.  
10.            Me.SubformObjectName.Requery
11.  
12.         End Sub

The first method builds up a string made up of all the selected values in the multi select list box (don't forget to get rid of the trailing comma) and you could use this string to add data to your table.

The second method builds up a string of SQL code that would run an append query to add data to your table.

The choice is yours - I'm not competent to comment on the efficiency of either method.

Jim

The first method builds up a string made up of all the selected values in the multi select list box (don't forget to get rid of the trailing comma) and you could use this string to add data to your table.

The second method builds up a string of SQL code that would run an append query to add data to your table.

The choice is yours - I'm not competent to comment on the efficiency of either method.

Jim

Thank You Jim,

Where would I insert the INSERT?

INSERT into tblINVOICEDETAILS

Thank You Jim,

Where would I insert the INSERT?

INSERT into tblINVOICEDETAILS

Put a command button near the multiselect list box so that your users can click on it after they have made their selections. Put the code that builds up the SQL INSERT INTO string in the On-Click event of that button. You will also need the RunSQL method of the DoCmd object.

Jim

Put a command button near the multiselect list box so that your users can click on it after they have made their selections. Put the code that builds up the SQL INSERT INTO string in the On-Click event of that button. You will also need the RunSQL method of the DoCmd object.

Jim

Thank you Jim, that really helped me. you guys are the best!