OpenRecordset not usable recordset / .edit not working?

[Torque1]

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...

[Luuk]

You should format code like this (use the [CODE/] in the toolbar)!:

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

it's much easier to refer to where your error is, at line #16...

[Luuk]

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:

  Set rstUE = dbsUE.OpenRecordset("SELECT Max([CheckOut]) AS MaxValue FROM [ToolTests] WHERE [ToolNumber]= '" & CheckInTool & "'")
    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 .

[twinnyfo]

Torque1,

Did Luuk’s solution work for you? That would have been my recommendation also.

[Luuk]

@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....