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

OpenRecordset not usable recordset / .edit not working?

P: 1
Working on a database on Access, trying to run a query to find a record with the latest date and a 'where' condition.
Error returned is "Run Timer Error '3027' Cannot update. Database or object is read-only"

Following conditions:
Button is clicked on a form that contains a text field for 'fCheckInFor'.
Database 'ToolTests' fields
"CheckOut" is dates in format of "3/15/2019 5:35:31 PM"
"CheckIn" is dates in format of "3/15/2019 5:35:31 PM"
"CheckInFor" is a text field
"ToolNumber" is a text field

Public CheckInTool as String

Private Sub CheckIn_Click()

CheckInTool = "000"
If Me.fCheckInFor = "" Then
MsgBox "Enter Returning User."

Else
Dim dbsUE As DAO.Database
Dim rstUE As DAO.Recordset
Set dbsUE = CurrentDb
Set rstUE = dbsUE.OpenRecordset("SELECT Max([CheckOut]) FROM [ToolTests] WHERE [ToolNumber]= '" & CheckInTool & "'")

With rstUE
.Edit 'error occurs here
!CheckIn = Now()
!CheckInFor = Me.fCheckInFor
.Update
End With
MsgBox "Checked In"
DoCmd.Close acForm, "CheckIn"
End If

End Sub

So the error throws at the .Edit line, I'm unsure where to go from here. would also be fine with tossing the whole thing and going at it from a different direction, I'm not a programmer by job title...
1 Week Ago #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 997
You should format code like this (use the [CODE/] in the toolbar)!:
Expand|Select|Wrap|Line Numbers
  1. Public CheckInTool as String
  2.  
  3. Private Sub CheckIn_Click()
  4.  
  5. CheckInTool = "000"
  6. If Me.fCheckInFor = "" Then
  7. MsgBox "Enter Returning User."
  8.  
  9. Else
  10. Dim dbsUE As DAO.Database
  11. Dim rstUE As DAO.Recordset
  12. Set dbsUE = CurrentDb
  13. Set rstUE = dbsUE.OpenRecordset("SELECT Max([CheckOut]) FROM [ToolTests] WHERE [ToolNumber]= '" & CheckInTool & "'")
  14.  
  15. With rstUE
  16. .Edit 'error occurs here
  17. !CheckIn = Now()
  18. !CheckInFor = Me.fCheckInFor
  19. .Update
  20. End With
  21. MsgBox "Checked In"
  22. DoCmd.Close acForm, "CheckIn"
  23. End If
  24.  
  25. End Sub
it's much easier to refer to where your error is, at line #16...
6 Days Ago #2

Expert 100+
P: 997
Back to your problem.

Your are trying to '.Edit' the return values of the 'Select MAX(...' statement, and you cannot do that (they are readonly!)

When you change ling 13 to this:
Expand|Select|Wrap|Line Numbers
  1.   Set rstUE = dbsUE.OpenRecordset("SELECT Max([CheckOut]) AS MaxValue FROM [ToolTests] WHERE [ToolNumber]= '" & CheckInTool & "'")
  2.     Set rstUE = dbsUE.OpenRecordset("SELECT * FROM [ToolTests] WHERE [CheckOut]= " & MaxValue )
  3.  
Or, in other word, get the MaxValue,
and after that,
get the record which 'belongs' to that MaxValue .
6 Days Ago #3

twinnyfo
Expert Mod 2.5K+
P: 3,054
Torque1,

Did Luuk’s solution work for you? That would have been my recommendation also.
5 Days Ago #4

Expert 100+
P: 997
@twinnyfo: now i know you are on nr#1 position in this list



Maybe you should also have told @Torque1 to use '[CODE/]' tags when posting code....
Attached Images
File Type: png twinnyfo.png (3.5 KB, 11 views)
5 Days Ago #5

Post your reply

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