using alist box for updating multiple records at once

[hfs]

Hi,
I have a list box populated with record ID's all of which need a date field updated. I have been succesful at using the list box to update single records, but am not sure how to transfer this idea to work with multiple records simultaneously.

The code i am using is:

Private Sub Command13_Click()
Dim i As Integer
    Dim strSQL As String
    Dim sMessage As String
    Set db = CurrentDb()
    Dim sTitle As String
    For i = List10.ListCount - 1 To 0 Step -1
    If List10.Selected(i) Then
    sSQL = "UPDATE test0 SET test0.date2 = now() " & " WHERE test0.itemno = '" & i & "'"
 
    sMessage = "Discontinue "
    sTitle = "Continue..."
    If MsgBox(sMessage, vbYesNo, sTitle) = vbYes Then
        MsgBox sSQL
        db.Execute (sSQL)
        
    End If
  
    End If
      Next i
End Sub

 

[pbaldy]

You haven't said what the problem is. Not the loop I'd use, but it could work. Use this to check the SQL:

http://www.baldyweb.com/ImmediateWindow.htm

I suspect the variable i isn't returning what you expect. See the loop here, which is more efficient, and how the value is referred to:

http://www.baldyweb.com/MultiselectAppend.htm

 

[hfs]

Thankyou so much for your help,the actual problem was to define record set in my code ,the code you send was very helpul and it worked .Thanks alot

 

[pbaldy]

You weren't using a recordset, but glad you got it working.