Multi Select listbox and ComboBox to populate field

[bryn30]

I have a list of orders say 2000, and a few reps, about 12.
I am using a multiselect ComboBox to select the order to be assigned. The ComboBox is a list of the reps. I am thinking a command button to execute the magic wand. How do I get the button to copy the ComboBox value (rep name) to the selected orders in the Multi-Select ListBox to the table that holds order information?
Loosing hair fast on this one. Please any help will be appreciated.
Thanks

 

[DougP]

to get multi-list info you need a loop
and look for the selected property.
I just grabbed this out of my goody bag of tricks

Dim A As Integer, SQL, Criteria As String
Dim ctlList As Control, varItem As Variant
SQL = "Select * From Employees Where EmployeeID = "
' Return Control object variable pointing to list box.
Set ctlList = Forms!form1!List1
' Enumerate through selected items.
For Each varItem In ctlList.ItemsSelected
' Print value of bound column.
Debug.Print ctlList.ItemData(varItem)
Criteria = Criteria & ctlList.ItemData(varItem) & " AND "
Next varItem
Criteria = Left(Criteria, Len(Criteria) - 4)
SQL = SQL & Criteria

then its up to you to figure out waht to do with it.

DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[bryn30]

We Did it!!!!!
Thank you jlitondo !!!!
it was small change in the single quotes being in the expression.

here is the final code that did it

Private Sub Command5_Click()


Dim rst As DAO.Recordset
Dim dbs As Database
Dim RepIdVar As String 'Variable to store the Rep ID
Dim OrderIdVar As String 'Variable to store the OrderID
Dim strSql As String
Dim varitem As Variant 'list box loop counter
Dim strCriteria As String

Set dbs = CurrentDb
Set rst = CurrentDb.OpenRecordset("Titan", dbOpenDynaset)

RepIdVar = Me.OCcombo

strCriteria = "[OC] = '" & RepIdVar & "'"

For Each varitem In WorkMang_Listbox.ItemsSelected

OrderIdVar = Me.WorkMang_Listbox.ItemData(varitem)

'Capture Criteria variables
strCriteria = strCriteria & " AND [ID] = " & OrderIdVar

'see if assignment already exists
rst.FindFirst strCriteria

If rst.NoMatch Then
'the combination of RepID and OrderId does not exist, build sql string to make assignment
strSql = "Update Titan Set [OC] = '" & RepIdVar & "' Where [ID] = " & OrderIdVar
dbs.Execute strSql
End If

Next varitem

rst.Close


'Debug.Print dbs.RecordsAffected
Set dbs = Nothing
End Sub


Thanks sooo much for sticking with me on this. you have been a tremendouse Help!

jlitondo Rocks!!!!