By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

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

markmcgookin
Expert 100+
P: 648
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?!?

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnARLogOff_Click()
  2.  
  3. Dim rs As DAO.Recordset
  4. Dim sqlStr As String
  5. Dim sqlStr2 As String
  6. Dim sqlStr3 As String
  7. Dim username As String
  8. Dim Tvalue As String
  9. Dim LDate As String
  10. Dim status As String
  11. Dim stDocName As String
  12.  
  13. Tvalue = Time
  14. LDate = Date
  15. Booking = "Inactive"
  16. Istatus = "Inactive"
  17.  
  18. username = Me.List14.Column(0)
  19.  
  20. sqlStr = "SELECT bookingStatus " & _
  21.          "FROM tblBooking " & _
  22.          "WHERE userID= '" & username & "'"
  23.  
  24. Set rs = CurrentDb.OpenRecordset(sqlStr)
  25.  
  26. Do While Not rs.EOF
  27.  
  28. sqlStr2 = "UPDATE tblBooking SET finishTime =#" & Tvalue & "#, finishDate =#" & LDate & "#, bookingStatus ='" & Booking & "' "
  29. sqlStr3 = "UPDATE tblUser SET Status = '" & Istatus & "' WHERE UserID = '" & username & "'   "
  30.  
  31. DoCmd.RunSQL sqlStr2
  32. DoCmd.RunSQL sqlStr3
  33. rs.MoveNext
  34.  
  35. Loop
  36.  
  37. MsgBox "All your jobs have been Closed"
  38.  
  39. stDocName = "ARRefresh"
  40. DoCmd.RunMacro stDocName
  41.  
  42. End Sub
  43.  
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
Jan 11 '07 #1
Share this Question
Share on Google+
1 Reply


markmcgookin
Expert 100+
P: 648
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnARLogOff_Click()
  2.  
  3. Dim rs As DAO.Recordset
  4. Dim sqlStr As String
  5. Dim sqlStr2 As String
  6. Dim sqlStr3 As String
  7. Dim username As String
  8. Dim Tvalue As String
  9. Dim LDate As String
  10. Dim status As String
  11. Dim stDocName As String
  12.  
  13. Tvalue = Time
  14. LDate = Date
  15. Booking = "Inactive"
  16. Istatus = "Inactive"
  17.  
  18. username = Me.List14.Column(0)
  19.  
  20. sqlStr = "SELECT bookingStatus " & _
  21.          "FROM tblBooking " & _
  22.          "WHERE userID= '" & username & "'"
  23.  
  24. Set rs = CurrentDb.OpenRecordset(sqlStr)
  25.  
  26. Do While Not rs.EOF
  27.  
  28. sqlStr2 = "UPDATE tblBooking SET finishTime =#" & Tvalue & "#, finishDate =#" & LDate & "#, bookingStatus ='" & Booking & "' "
  29.  
  30. DoCmd.RunSQL sqlStr2
  31.  
  32. rs.MoveNext
  33.  
  34. Loop
  35.  
  36. sqlStr3 = "UPDATE tblUser SET Status = '" & Istatus & "' WHERE UserID = '" & username & "'   "
  37.  
  38. DoCmd.RunSQL sqlStr3
  39.  
  40. MsgBox "All your jobs have been Closed"
  41.  
  42. stDocName = "ARRefresh"
  43. DoCmd.RunMacro stDocName
  44.  
  45. End Sub
  46.  
Jan 11 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.