Multi Select listbox and ComboBox to populate field 1

[bryn30]

I have a list of orders say 2000, and a few reps, about 12.
I am using a multiselect ComboBox (Extended) 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

 

[jlitondo]

Send me an email jlitondo@gatecitysteel.com

 

[jlitondo]

This is how the logic for this works. You have three tables; Reps,Orders and Order Information. The Reps and Orders need to have their own Primary Keys e.g. RepID and OrderID. The Order Infor table will store these two Id's as its PK.
1-The user selects a Rep, and then selects several orders to assign to this rep.
2-When they click the [Assign] command button, the code behind this grabs the RepID from the combo box and then loops through the list box to grab all the OrderIDs of the selected Orders , then appends these to your OrderInformation table.
3-You will need to store the OrderId's from you list box need to be stored in an array.
Here is how your code will look like.

Private Sub AssignOrders_Click()

Dim cnn1 As ADODB.Connection 'connection string
Dim rs As ADODB.Recordset 'your recordset variable
Dim varitem As Variant 'list box loop counter

Dim OrdersArray(0 To 100) As String 'not sure how to make this array dynamic yet

Dim arraystr As String

'Assign List box values to array
For Each varitem In OrdersListBox.ItemsSelected
OrdersArray(varitem) = OrdersListBox.Column(1, [varitem])
Next varitem

'open the connection
Set cnn1 = CurrentProject.Connection

'open OrderInfo table
Set rs = New ADODB.Recordset
rs.Open "OrderInfo", cnn1, adOpenDynamic, adLockOptimistic

For Each varitem In OrdersListBox.ItemsSelected
rs.AddNew
rs![RepId] = Me.RepCombo
rs![OrdersId] = OrdersListBox.ItemData(varitem)
rs.Update
Next varitem

varitem = 0

'clear list box
For Each varitem In OrdersListBox.ItemsSelected
OrdersListBox.Selected(varitem) = False
Next varitem

'clear recordset
set rs = nothing

'clear array elements
Dim c as integer
For c = 0 to 100
OrdersArray(c) = vbnullstring
Next c


End Sub

jlitondo@gatecitysteel.com

 

[bryn30]

From the looks of this code, it is really good. however, I only have two table from which to pull the data. a Rep table and an Orders Table. the listbox pulls from the orders and the combo pulls from the Rep. The code looks like it is going to add orders to the orders table?

It got all the way through to the .addnew but gave me a run time error saying it will cause dups in the table.

is there a way to update it instead?

 

[jlitondo]

The code above is designed to store assigned Orders in another table, the "OrdersInfo" table.The primary keys of the Reps and Orders table are stored in this table as unique(pk).
On second thoughts,and depending on what information you are storing in one Order(regardless of whether its assigned or not) or in an OrderInfo table(only assigned Orders are stored here)you could alternatively store the RepId in the Orders table and bypass having to create the OrderInfo table. Make the RepId a foreign key in the Orders table.
jlitondo@gatecitysteel.com

 

[bryn30]

please forgive my ingorance at this. How is makeing the repid a forien key in the orders table going to eliminate the need for a 2nd table? the code still uses the .addnew code which duplicated orders in the table. is there a whay to update the existing table with the persons name from the combo box into the table of select orders?

 

[jlitondo]

I don't understand what you mean by eliminate the need for a 2nd table.You already have two tables.
Let me roll back a little.You have Rep and Orders tables. Each one of these has a primary key, say RepId and OrdersId, which uniquely identifies each record on those tables.You want to assign one or more Orders to a single rep.
One way of doing this is to create another column in your Orders table Called RepId. This would allow you to associate a rep with one or more Orders in that table.This RepId is the foreign key in the Orders table and therefore duplicates are allowed.
So far that is the setup.Now do you have these two tables set up in a one to many relationship?
jlitondo@gatecitysteel.com

 

[bryn30]

hey thanks for the clarification on this. I went back and did some tweeking to the code to fit the database, couple of snags... though
first here is the code so far

Private Sub Command5_Click()

Dim cnn1 As ADODB.Connection 'connection string
Dim rs As ADODB.Recordset 'your recordset variable
Dim varitem As Variant 'list box loop counter

Dim OrdersArray(0 To 100) As String 'not sure how to make this array dynamic yet

Dim arraystr As String

'Assign List box values to array
For Each varitem In Me.WorkMang_Listbox.ItemsSelected
OrdersArray(varitem) = Me.WorkMang_Listbox.Column(1)
Next varitem

'open the connection
Set cnn1 = CurrentProject.Connection

'open OrderInfo table
Set rs = New ADODB.Recordset
rs.Open "Production", cnn1, adOpenDynamic, adLockOptimistic

For Each varitem In Me.WorkMang_Listbox.ItemsSelected
rs.AddNew
rs![OC] = Me.OCcombo
rs![ID] = Me.WorkMang_Listbox.ItemData(varitem)
rs.Update
Next varitem

varitem = 0

'clear list box
For Each varitem In Me.WorkMang_Listbox.ItemsSelected
Me.WorkMang_Listbox.Selected(1) = False
Next varitem

'clear recordset
Set rs = Nothing

'clear array elements
Dim c As Integer
For c = 0 To 100
OrdersArray(c) = vbNullString
Next c

Me.WorkMang_Listbox.Requery

End Sub

ok there are about 3000 records. after the first 100 or so, I get run time error "Sub script out of range"
also the table is not updated with the rep name...
when the code is ran the listbox requeries and it looks the same as before.

 

[jlitondo]

The subscript out of range error occurs when the pointer on the array false beyond the assigned number of elements. Your array is set to hold 101 elements but your code is attempting to add more values than that (through the For loop) thus the error.I am going to check into making that array dynamic(so that it grows or shrinks according to the number of records selected in your list box) as opposed to having it static/fixed as we do now.
Secondly, your list box will not all clear out because you are only unchecking the first selected item.In your code you have
- Me.WorkMang_Listbox.Selected(1)=False
instead of
- Me.WorkMang_Listbox.Selected(varitem) = False

jlitondo@gatecitysteel.com

 

[bryn30]

ok made that change but the list box still does not clear, and no changes are being made to the table.

 

[jlitondo]

You have another problem in your code.Where you have
- OrdersArray(varitem) = Me.WorkMang_Listbox.Column(1)
you should have
- OrdersArray(varitem) = Me.WorkMang_Listbox.Column(1,varitem])
Note: Column("this would be the column where your pk OrdersID is stored/appears on your list box",[varitem])
jlitondo@gatecitysteel.com

 

[bryn30]

ok here is the update...
it works as far as placing these orders to a assigned order table. I have to do some more tweeking to get the order over but here it is.
ok here is the process flow prespective
order is placed into a table called titan, orders is then assigned and placed in a table called WorkOrders, then orders are sent to another table called Archive after they have been worked. Orders not assigned are archived automatically. All orders in the archive table are then proccesed for rootcause.
I am going to have to do some massive changes to other queries, forms, reports, and a lot of other objects due to the new WorkOrder table. which explains why I wanted to keep all the data in the orignal titan table. Hey I am not complaing this will work and I am happy.. I do thank you for bearing with me.
I have an idea.. can we delete the data from the titan table after we transfer the info over to the WorkOrder table. then send the information back to the Titan Table thus solving the duplicate order issue?

 

[jlitondo]

That's okay. Do you have a table where you store all your reps? I don't think it is good database design to store all your rep information alongside your Orders info in one table, Titans since this would create alot of data redundancy.Also, you are looking at 2 different themes, Reps and Orders, which generally should fall into separate tables.
I don't see why you need to have both the WorkOrders table and the Titans table to store your orders information. From within one table, say Titans, you can set a flag to determine whether that order has been assigned or not, or, you can simply tell that it has been assigned just by the fact that a specific order in your Titans table has a RepId.If there is no RepId in that record then we know that Order has not been assigned. jlitondo@gatecitysteel.com

 

[bryn30]

yes I do have the rep info in it's own table. Yes you are correct, that is how we determine is an order is assgned or not. but the code does not populate the titan table with the repid, it gives a duplicate error everytime.

 

[bryn30]

ok maybe I am just not getting this. here is another update. The from was bound to the titan table (table with all the listboxes and comboboxes) unbound the form, not I do not get the duplicate error. However, the code does add another ID and a rep id to the titan table. the information is getting into the table just have to get it to the correct record now...

 

[jlitondo]

By the way, you are referencing the wrong table: rs.Open "Production" ....
since it is the Titans table that you want to update.You are getting the dup error message because the [ID] you adding already exists in the production table. jlitondo@gatecitysteel.com

 

[bryn30]

yep figured that one out and pointed it to the titan table. I an going to try and get rid of the production table alltogether. and keeping all the order info in one table Titan.

 

[jlitondo]

Since we are only updating the Titans table with RepID(correct me if I'm wrong) for each Order the rep gets, we are going to use a completely different set of code.The above code only adds a new record to the table while we are seeking to update existing records.Also, before you eliminate your Producion table, determine that all the information contained in it is exclusively Production, and OrderInfo table is exclusively that.Do not mix combine the two.I shall post the code in a few. jlitondo@gatecitysteel.com

 

[jlitondo]

note: i hope the reversion to DAO won't be too confusing

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("Titans", dbOpenDynaset)

RepIdVar = Me.YourComboBoxName

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

For Each varitem In WorkMang_ListBox.ItemsSelected

OrderIdVar = Me.WorkMang_ListBox.ItemData(varitem)

'Capture Criteria variables
strCriteria = strCriteria & " AND [OrderID] = '" & 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 Titans Set [RepID] = '" & RepIdVar & "' Where [OrderID] = '" & OrderIdVar & "';"
dbs.Execute strSql
End If

Next varitem

rst.Close


'Debug.Print dbs.RecordsAffected
Set dbs = Nothing

You may run into a few syntactical errors at first but logically this works.
jlitondo@gatecitysteel.com

 

[bryn30]

I really think we have it here... I am hitting and error in the code here
"rst.FindFirst strCriteria"
the error is 'data type mismatch in criteria expression'