Batch instead of individual items

[joeserrone]

Hello Everyone,
I have a problem I'm trying to resolve and wanted your opinion on this. I currently have a form that displays a list of items from a table. I then use the code listed below to assign the items highlighted to a specific person from a drop down box. The problem I'm having is that many items can be part of a batch called Advice, instead of highlighting every item in the batch I would like to basically have only the advice numbers showing in the batch and when I run my assign code basically assign all the items in that advice to the person selected from the drop down box.
So for example if Advice# 123456 has 5 items, instead of highlighting all 5 items and assigning them to bob I want to simply highlight the 1 advice 123456 and all 5 items will be assigned to Bob.
I changed my query in the form to show me only the advices by Group but naturally when I try to assign them using the code below it simply doesn't work.

Any ideas how I might be able to overcome this?

Thanks as always for all you help!



'add selected value(s) to table
Set ctl = Me.lstItems
strIN = ""
For Each varItem In ctl.ItemsSelected
strIN = strIN & ctl.ItemData(varItem) & ","
Next varItem

strIN = Left(strIN, Len(strIN) - 1)
CurrentDb.Execute "UPDATE Overdrawn_Chargebacks_Tbl SET EmployeeID = " & Me.EmployeeCbo _
& " WHERE ID In(" & strIN & ")"

 

[pono1]

Use the advice number as criteria to fetch the related items from the database...

SELECT Advice.X 
FROM Advice
WHERE Advice.Y = Advice Number from Listbox

...putting those items into, say, a recordset. (Search on ADO and DAO and loop and recordset for example code.) Then loop through the recordset -- as you loop through the listbox -- stringing the items all together before throwing them into your update query's where clause...

Yes, you will need to make another trip to the DB, but only one additional trip no matter how many items fall under your advice number...

Regards,
Tim

 

[joeserrone]

Thanks pono1 for your advice but I'm still having trouble getting your idea to work correctly. Attached is a sample version of what I did so far with this tool. As you can see all items are displayed in the List box but I would like to have the advice # 537701 which contains 4 items updated once I select a name in the Form called Distributing_Work_Form

Maybe if you can actually show me how you would get it to work using my sample I can better understand what you are proposing.

Thanks for your help

 

[pono1]

Again, I suggest you do the search... If the "solution" I proposed doesn't make sense to you, you may want to search on normalization. If it does make sense, I suggest you step back a bit and, in simple terms, write what you want to do in the code window, step by step...

' Get advice number selected by the user in the listbox

' Get all items related to the advice number from a table in the database

' Find and update the related items in another table in the database

Using the comments above, you can begin writing code to accomplish one broad task at a time... This is sometimes called the "pseudo coding process"...basically you write your code comments before you code...which gives you structure, a mini-design, before any code hits the pavement...

Regards,
Tim