Insertion from MultiListbox

  • Thread starter Thread starter spstieng
  • Start date Start date
S

spstieng

Sorry for posting this twice - couldn't find out how to remove the
message in the opther "forum".





I have a multi listbox 'lbCustomerList' which list 'CustomerID' and
customer 'Name' from a table called 'Clients'.

I select multiple customers and press 'Create Invoice' button.



When I press the button, the selected customers will be inserted into a
table called 'Invoice'



I'm kind of on the right track.... just not quite...

Here some of the code:



Set frm = Form!Form_Form1 (have not named the form yet)

Set ctl = frm!lbCustomerList



For Each varItem In ctl.ItemsSelected

strSQL = "INSERT INTO Invoice valuse (CustomerID)"

{here I want to execute the SQL statement}

Next varItem





Any help appreciated
 
Try this - build a list of custids then append the records using a single
insert query:

Private Sub Command2_Click()
Dim varItem As Variant
Dim strSQL As String
Dim strWhere As String
Dim db As DAO.Database
Set db = CurrentDb
strSQL = "INSERT INTO Invoice ( CustomerID) " & _
"SELECT Clients.CustomerID " & _
"FROM Clients " & _
"WHERE Clients.CustomerID In ("
For Each varItem In Me.lbCustomerList.ItemsSelected
strWhere = strWhere & Me.lbCustomerList.ItemData(varItem) & ", "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
db.Execute strSQL & strWhere
MsgBox db.RecordsAffected & " Records were inserted! "
Set db = Nothing
End Sub
 
Back
Top