By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,720 Members | 2,095 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,720 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."

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
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...
Mar 15 '19 #1
Share this Question
Share on Google+
4 Replies

Expert 100+
P: 1,035
You should format code like this (use the [CODE/] in the toolbar)!:
Expand|Select|Wrap|Line Numbers
  1. Public CheckInTool as String
  3. Private Sub CheckIn_Click()
  5. CheckInTool = "000"
  6. If Me.fCheckInFor = "" Then
  7. MsgBox "Enter Returning User."
  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 & "'")
  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
  25. End Sub
it's much easier to refer to where your error is, at line #16...
Mar 16 '19 #2

Expert 100+
P: 1,035
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 )
Or, in other word, get the MaxValue,
and after that,
get the record which 'belongs' to that MaxValue .
Mar 16 '19 #3

Expert Mod 2.5K+
P: 3,283

Did Luuk’s solution work for you? That would have been my recommendation also.
Mar 17 '19 #4

Expert 100+
P: 1,035
@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, 82 views)
Mar 17 '19 #5

Post your reply

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