List box Items Selected (Acc2K)

Topic

New Reply

I’m missing something here. I want to update a recordset with a value from a text box. By going through the listbox, I want to update only those records that are selected. Here is the code:
For Each varItem In lstItems.ItemsSelected
rst!CheckNo = Me.txtCheckNo
rst.Update
Next

This only updates the first selection.
What am I missing?
Thanks!!!

Reply | Quote

Replies

My code for doing something similar looks somewhat the same. The big difference is that the Next statement has the loop element, as in:

Next varItem

Reply | Quote

I may be missing something obvious, but it appears that you’re telling Access to loop through the items selected in a listbox, but you are updating the same recordset record each time. How are you telling Access to get the particular record you need?

Reply | Quote

That is my problem. I need to know how to update each record that is selected in a list. I appreciate any light you could shed on this.

I want a user to select items in a listbox on a form, then update a field for each record with one value from a text box on the same form.

Thanks for any ideas!

Reply | Quote

I presume you are using DAO based on the code snippet you provided. In any event, you need to step through the recordset one record at a time, using the rst.movenext command inside your loop. You will also need to have an IF statement that checks to see if the record has been selected, and if it has then you will want to do the update process. I also made the assumption that the form is not bound to the recordset – it seems to me however that it may be difficult to keep the listbox synchronized with the recordset unless they are exactly in the same order. Hope this gives you some ideas.

Reply | Quote

You want to know how to update records. But you haven’t indicated how the items in the list box relate to the records in the recordset. There is nothing in the code in the first post in this thread that gives a clue. We don’t know what the row source of the list box is, which is the bound column, etc.

So the following is based on guesswork.

Let’s assume that the recordset has a unique key field and that the bound column of the list box corresponds to this field. Since you haven’t given us information on this, I’ll name it UniqueID.

One way to do what you want is to loop through the selected items of the list box, look up the record corresponding to each and edit that record:

For Each varItem In lstItems.ItemsSelected
‘ Look for record
rst.FindFirst “UniqueID=” & lstItems.ItemData(varItem)
If rst.NoMatch = False Then
‘ If found, put record in edit mode, modify and update
rst.Edit
rst!CheckNo = Me.txtCheckNo
rst.Update
End If
Next

Another way is to create a SQL string to update the records. The code is longer, but more efficient in theory, because the appropriate records are updated in one action.

Dim strSQL As String

If lstItems.ItemsSelected.Count = 0 Then
‘ Issue warning, get out, …
Exit Sub
End If

For Each varItem In lstItems.ItemsSelected
‘ Append comma, space and unique ID to string
strSQL = “, ” & lstItems.ItemData(varItem)
Next

‘ Get rid of first “, ”
strSQL = Mid(strSQL, 3)

‘ Make update statement
strSQL = “UPDATE tblSomething SET CheckNo = ” & Me.txtCheckNo & _
” WHERE UniqueID IN (” & strSQL & “)”
‘ Execute it
CurrentDb.Execute strSQL

Reply | Quote

That is exactly what I needed! Thanks to all who replied, as I can now continue to develop this database.

So much to learn……

Reply | Quote