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 list the orders and 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

 

[rbowes]

Using a multi select listbox almost requires that you use VBA to use the values. I assume a few things here

1. That you understand how to code.

2. that your combo boxes and list boxes have hidden values in the first column that are the IDs.

3. That you are using Access 2000 and ADO. You can use earlier versions like 97 and the DAO technology, I just didn't create it in this answer.

on the command button's OnClick event place this

Private Sub Command4_Click()
Dim con As ADODB.Connection
Dim strsql As String
Dim strWhere As String
Dim var As Variant

'Initialize strWhere
strWhere = "("
'Loop through the List Box
For Each var In Me.List2.ItemsSelected
strWhere = strWhere & Me.List2.ItemData(var) & ","
Next
'Now delete the last two characters and add a
closing parantheses
strWhere = Left(strWhere, Len(strWhere) - 1) & &quot"

'set the connection to the current database, change
as needed
Set con = CurrentProject.Connection

'Set the SQL statement and execute
strsql = "UPDATE Orders SET Orders.EmployeeID =" & Me.Combo0 & _
" WHERE Orders.OrderID IN " & strWhere
con.Execute strsql

Set con = Nothing
End Sub

This code works well but you will have to put single quotes around the criteria values in the StrWhere if you use non-numerical data types for your IDs

Hope that helps.

BB

 

[bryn30]

Rbowes
Thanks for the help on this. I am not an expert at coding, I have been doing it for about six months and what I do know has been from books and experience as well as this wonderful site.
I am using access 2000
I have not been using ADO in this database. or at least not to my knowledge.

I tried the code you gave me and it gets to the line;

strWhere = Left(strWhere, Len(strWhere) - 1) & &quot"

and it gives me an error "Can't find project or Library"
could you help me out?
Thanks

 

[rbowes]

Classic problem! First, if you are using Access 2000 ADO is the default data access technology. To use DAO you need to set a Reference to it. In the Visual Basic Editor, go to Tools > References. Choose DAO 3.6 (I think that is the latest version, by the way you should convert to ADO, it really is easier to use and that's the way Microsoft is heading anyway).

I had this problem about a year ago. It can't find the library because your references are broken or changed or something. You might see a reference checked for MISSING:... That might very well be your problem. Deselect it and try to compile. Also, try to compile after you have set the DAO reference. You might also check your Jet reference it should be Microsoft Jet 4.0 OLEDB. Your ADO reference should be 2.1 or higher. There is another solution, you could put VBA. infront of all of your string function references -- a real pain.

Let me know if anything works. To be quite honest, sometimes it kind of fixes itself when you start fiddling with the references.

Bob

 

[THoey]

Bryn30,

I notice that you posted this across a couple of different forums, but are only getting responses here. When you get this solved, Bob seems to be pointing you in the right direction, please make sure you post in the other forum to let everyone know. That way someone doesn't spend time trying to solve something that is already fixed...

Thanks and good luck... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[bryn30]

Not a problem Terry... I will. still running into issues. though. same problems though.

 

[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!!!!