Update Temporary Table with selected items from a listbox

[shylock]

I create a temporary table "TempServicesTbl" and populate a listbox with the data. In creating the temp table I added a new field titled "IsSelected" and set it to False for every row. I want to select 1 or more items from the list box and then seet the "IsSelected" field , in the Temp table, to True for that row corresponding to the selected item in the listbox. I will then set the rowsource of a second list box to select the rows from the temp table where the "IsSelcted" field equals True. My question is: Do I have to use a loop to update each row in the temp table using the lsitbox.itemsselected collection? if so, this sounds as if it will be rather slow. Can anyone think of a better way to do this?

[orange]

What are you trying to do in simple, plain English?
Why do you think the loop processing would be "rather slow"?
How many records involved?

[shylock]

Im trying to populatethe second listbox with selected services from the first listbox. I will, then, update a table containing a list of servers paired with the services that each server contains. The orgininal listbox contains all the possible services and this list keeps growing as new services are added, so the number of services is indeterminate. I don't know that a loop will be too slow, it just seems rather clumsy. All of the example I have seen involve and outer loop with an inner loop to get the selected items from the first listbox to the second listbox. Am I mistaken here? If so, what do you suggest?

[Gicu]

Hi shylock,
As I believe I was the one that suggested this approach I would like to offer some more info. You actually loop through the ItemsSelected collection of the listbox(es)(if multiselect is enabled on the list box), not the temp table. Please have a look at the code included and adapt for your table;

Private Sub cmdAddAll_Click()
Dim varItem, lJoinID As Long, sSQL As String


For Each varItem In Me.lstAvailable.ItemsSelected
    lJoinID = Me.lstAvailable.Column(0, varItem)
    sSQL = "UPDATE tmpIncomingJobHoldUpdater SET [IsSelected]=True WHERE [IncomingJobJoinID] = " & lJoinID & ";" 'select the row
    
    CurrentDb.Execute sSQL ', dbFailOnError
Next varItem


Me.lstAvailable.Requery
Me.lstSelected.Requery


End Sub


Private Sub cmdAddOne_Click()
Dim sSQL As String, lJobId As Long
lJobId = Me.lstAvailable.Column(0)
sSQL = "UPDATE tmpIncomingJobHoldUpdater SET [IsSelected]=True WHERE [IncomingJobJoinID] = " & lJobId & ";" 'select the row
CurrentDb.Execute sSQL, dbFailOnError
Me.lstAvailable.Requery
Me.lstSelected.Requery
End Sub


Private Sub cmdRemoveOne_Click()
Dim sSQL As String, lJobId As Long
lJobId = Me.lstSelected.Column(0)
sSQL = "UPDATE tmpIncomingJobHoldUpdater SET [IsSelected]=False WHERE [IncomingJobJoinID] = " & lJobId & ";" 'deselect the row
CurrentDb.Execute sSQL, dbFailOnError
Me.lstAvailable.Requery
Me.lstSelected.Requery
End Sub


Private Sub cmdRemoveAll_Click()
Dim varItem, lJoinID As Long, sSQL As String


For Each varItem In Me.lstSelected.ItemsSelected
    lJoinID = Me.lstSelected.Column(0, varItem)
    sSQL = "UPDATE tmpIncomingJobHoldUpdater SET [IsSelected]=False WHERE [IncomingJobJoinID] = " & lJoinID & ";" 'select the row
    
    CurrentDb.Execute sSQL ', dbFailOnError
Next varItem


Me.lstAvailable.Requery
Me.lstSelected.Requery
End Sub
Private Sub lstAvailable_DblClick(Cancel As Integer)
Call cmdAddOne_Click
End Sub


Private Sub lstSelected_DblClick(Cancel As Integer)
Call cmdRemoveOne_Click
End Sub
Private Sub cmdCancel_Click()
DoCmd.Close
End Sub

Hope it helps.

Cheers,
Vlad

[orange]

If I understand correctly, you have table set up along this:

AllServers---->ThisServerHasTheseServices<--AllServices

[Gicu]

Hi Orange, I believe this post is related to OPs previous one here: https://www.accessforums.net/showthread.php?t=75070

Cheers,
Vlad

[shylock]

Vlad:

Yes, it is. However I'm a bit further on than I was on the previous thread. I feel Like an idiot, of course I should be looping through the listbox items NOT the temporary table. I should have seen this right away! I guess it's a case of not being able to see the trees because of the forest.

BTW I've used some of the code you provided, but it only updates one item in the temp table even though I chose more than one item in the listbox. I do have the Multi Select property set to extended. Same thing if it is set to simple. I think I can eventually figure it out.

Thank you very much for your assistance.

[Gicu]

In my code you have examples for both options - one item select vs. multiple, just use the code from one of the "all" buttons. I usually use extended for the multiple property.

Cheers,
Vlad

[shylock]

Vlad:

I solved my problem of only getting one item to update in the temp table. I had varItm as a variant and in the loop spelled it as varItem for the sub-script in the loop. Sometime I just get in a hurry and type the correct spelling. Thanks again for you patience and help.

[Gicu]

Glad to help! Good luck!
Vlad

[moke123]

I had varItm as a variant and in the loop spelled it as varItem for the sub-script in the loop.

Do you have Option Explicit declared at the top of all yor modules? It probably would have saved you a lot of time.