Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Dec 2000
    Location
    Hellertown, Pennsylvania, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List box Items Selected (Acc2K)

    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!!!

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List box Items Selected (Acc2K)

    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

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: List box Items Selected (Acc2K)

    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?
    Charlotte

  4. #4
    Star Lounger
    Join Date
    Dec 2000
    Location
    Hellertown, Pennsylvania, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List box Items Selected (Acc2K)

    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!

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: List box Items Selected (Acc2K)

    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.
    Wendell

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List box Items Selected (Acc2K)

    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

  7. #7
    Star Lounger
    Join Date
    Dec 2000
    Location
    Hellertown, Pennsylvania, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List box Items Selected (Acc2K)

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

    So much to learn......

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •