Run SQL for each listbox item

[steve1111]

Hello,

bare with me on this as I am still new to running recordset in VBA, which is where I suspect this will end up....

Quick Setup: I have a Work Order maintenance process where when a work van needs repairs we generate a work order and list all the issues wrong. Then I go to another form that pulls in just the issues for that work order into a listbox so we can assign a shop to handle them. Sometimes all the issues can be worked out at one shop others we need to send to multiple shops so one work order can have several ServiceID shops.

So from the lbx I select the first issue, and then data entry the shop I want, appointment time, shuttle information etc. Then I save, it updates the second column in my lbx as "Assigned" and I can select the next unassigned issue on that work order from the lbx and repeat.

Since most of the time we send to one shop I have put a chkbox to "Save Settings for all issues" on my form so that I do not have to type redundant info.

So my question is what is the best way to handle automating the same data onto a new ServiceID record for each reported issue from the lbx?

Keep in mind that once I select an issue from the lbx, it looks up if a shop is assigned, if so, it filters that form to that record so changes can be made, if no shop is assigned with the on_click of the lbx, then the form goes to a new record. I want to be able to cycle through the all the listbox lines and create a new service record with the data from the first round of input.

I hope I am explaining this well enough and sorry I cannot attach a copy of the DB, I work for a medical company that is pretty strict on that sort of thing.

Thanks for any tips on the direction I need to go.

 

[Ranman256]

i have 2 ways,
sometimes I use a pick list screen , where I dbl-click items from an Available box to what I need into the Picked list. (tPicked table)
then I can assign these to the Assignment by dbl-clicking the picked list item. (see image bottom)

another:
I have the same list of items, then click a button to run thru and add each to the Data table via append queries and calculations or just email. (see code below)

'make reports

sub btnSend_click()
dim vTo, vSubj, vBody
dim i as integer

for i = 0 to lstBox.listcount -1
    vTo = lstBox.itemdata(i)    'get next item in listbox
    lstBox = vTo		'set the listbox to this item

        'get rest of info from form
    vSub = txtSubj
    vBody = txtBody

   docmd.SendObject acSendReport ,txtReport,acFormatPDF,vTo,,,vSubj,vBody
next
end sub

 

[steve1111]

Thanks Rainman, I think this definitely sets me on a path to incorporate and update query that runs on each iteration of the listbox.