tags:

views:

26

answers:

1

I can store records in the DB by using combobox with the following code. Here single part number is selected and partnumber related data is stored in the DB table.

But I want the code for Listbox...When I select multiple partnumbers ..how can I store in the DB table?

Case "Pn ADDED to Wrapper", _
            "Pn REMOVED from Wrapper"
            If Me!PartNumber <> "All" And Me!PartNumber <> "Select" Then ' a proper part number has been selected in combo box
                strNewSq5 = _
                    "INSERT INTO tblTmpEventLog (TrackingNumber,PartNumber,PartNumberChgLvl,EnteredBy,EventTypeSelected,EventDate)"
                strNewSq5 = strNewSq5 & " VALUES ('" & tempTrackingNumber & "','" & _
                    tempPartNumber & "','" & _
                    tempPartNumberChgLvl & "','" & _
                    tempEnteredBy & "','" & _
                    tempEventTypeSelected & "'," & _
                    "#" & Forms!frmEventLog_Input.EventDate & "#)"
                dbs.Execute strNewSq5, dbFailOnError

                TrnsfTmpEventToEventLog
                Else
                        displayMsgBox = MsgBox("A single part number must be specified. Please correct.", vbCritical, "System Error")
                Exit Sub
                End If
A: 

You need to iterate over the selected items and store them one by one:

http://stackoverflow.com/questions/3310904/ms-access-2007-cycling-through-values-in-a-list-box-to-grab-ids-for-a-sql-stat/3311022#3311022

EDIT re comment

You do not provide sufficient information for a detailed answer, but here are some notes that may help.

For Each itm In Me.NameOfListBox.ItemsSelected
      If Instr("All,Select",Me.NameOfListBox.Column(0, itm) )=0 Then 
           '' a proper part number has been selected in list box

           '' Me.NameOfListBox.Column(0, itm) is the column (zero in this example
           '' and row (itm) of the selected item in the list box. If it is the 
           '' part number, then you might like to say:

           '' tempPartNumber = Me.NameOfListBox.Column(0, itm)

           strNewSq5 = "INSERT INTO tblTmpEventLog " & _ 
                    "(TrackingNumber,PartNumber,PartNumberChgLvl,EnteredBy," & _
                    "EventTypeSelected,EventDate)"
           strNewSq5 = strNewSq5 & " VALUES ('" & tempTrackingNumber & "','" & _
                    tempPartNumber & "','" & _
                    tempPartNumberChgLvl & "','" & _
                    tempEnteredBy & "','" & _
                    tempEventTypeSelected & "'," & _
                    "#" & Forms!frmEventLog_Input.EventDate & "#)"
          dbs.Execute strNewSq5, dbFailOnError

          TrnsfTmpEventToEventLog
    Else
         ''Do not insert
    End If
Next
Remou
I do not understand the link, can you please explain me with my code
I have added some notes.
Remou
It works, thanks Remou!