SQL Update only working on 1st 2 rows of List Box

[markmcgookin]

This is a strange one!

One minute, everything worked fine, then suddenly I don't know if i did anything, but this started happening.

I have a listbox, populated with 3 values (UserID, userName As Name, status AS AR_Status)

I have a button that (amongst other things) will update the status field to "Inactive" Now.... here's the weird thing, it only works on the top two rows of the list box?!?

Private Sub btnARLogOff_Click()
 
Dim rs As DAO.Recordset
Dim sqlStr As String
Dim sqlStr2 As String
Dim sqlStr3 As String
Dim username As String
Dim Tvalue As String
Dim LDate As String
Dim status As String
Dim stDocName As String
 
Tvalue = Time
LDate = Date
Booking = "Inactive"
Istatus = "Inactive"
 
username = Me.List14.Column(0)
 
sqlStr = "SELECT bookingStatus " & _
         "FROM tblBooking " & _
         "WHERE userID= '" & username & "'"
 
Set rs = CurrentDb.OpenRecordset(sqlStr)
 
Do While Not rs.EOF
 
sqlStr2 = "UPDATE tblBooking SET finishTime =#" & Tvalue & "#, finishDate =#" & LDate & "#, bookingStatus ='" & Booking & "' "
sqlStr3 = "UPDATE tblUser SET Status = '" & Istatus & "' WHERE UserID = '" & username & "'   "
 
DoCmd.RunSQL sqlStr2
DoCmd.RunSQL sqlStr3
rs.MoveNext
 
Loop
 
MsgBox "All your jobs have been Closed"
 
stDocName = "ARRefresh"
DoCmd.RunMacro stDocName
 
End Sub
 

I can not find any reason why it would work perfectly, repeatidly for two rows... and not the others. If I add new rows exactly the same as the top two (except for the PK) it still wont work, I'm absoloutly stumped!

I'll upload the db as a .rar for anyone who wants to see this!

www.cmdclan.co.uk/Temp/jr_system.rar

Thanks for any help or even an idea why! hehe.

*EDIT* Sorry, it is on the ARsim Form

[markmcgookin]

Got it fixed! Sorry!

Just moved the update outside the loop. Still can't figure out why it worked for 2 and not the others though.

Private Sub btnARLogOff_Click()
 
Dim rs As DAO.Recordset
Dim sqlStr As String
Dim sqlStr2 As String
Dim sqlStr3 As String
Dim username As String
Dim Tvalue As String
Dim LDate As String
Dim status As String
Dim stDocName As String
 
Tvalue = Time
LDate = Date
Booking = "Inactive"
Istatus = "Inactive"
 
username = Me.List14.Column(0)
 
sqlStr = "SELECT bookingStatus " & _
         "FROM tblBooking " & _
         "WHERE userID= '" & username & "'"
 
Set rs = CurrentDb.OpenRecordset(sqlStr)
 
Do While Not rs.EOF
 
sqlStr2 = "UPDATE tblBooking SET finishTime =#" & Tvalue & "#, finishDate =#" & LDate & "#, bookingStatus ='" & Booking & "' "
 
DoCmd.RunSQL sqlStr2
 
rs.MoveNext
 
Loop
 
sqlStr3 = "UPDATE tblUser SET Status = '" & Istatus & "' WHERE UserID = '" & username & "'   "
 
DoCmd.RunSQL sqlStr3
 
MsgBox "All your jobs have been Closed"
 
stDocName = "ARRefresh"
DoCmd.RunMacro stDocName
 
End Sub