OpenRecordset not returning usable recordset / .edit not working?

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.

728x90

1 Answers OpenRecordset not returning usable recordset / .edit not working?

If I get you right, you want to update a record that has a specific ToolNumber. So, if you want to use a subquery, you will either need to use that criterion in both the main query and the subquery, or link the subquery with the main query using that field:

Set rstUE = dbsUE.OpenRecordset( _ &
  "SELECT CheckIn, CheckinFor FROM ToolTests WHERE" & _
  " CheckOut IN (SELECT Max(CheckOut) FROM ToolTests AS tmp WHERE ToolNumber = ToolTests.ToolNumber)" & _
  " And ToolNumber = '" & CheckInTool & "'")

Alternatively, you could simply sort the records in the correct order and update the first one:

Set rstUE = dbsUE.OpenRecordset( _ &
  "SELECT CheckIn, CheckinFor FROM ToolTests WHERE" & _
  " ToolNumber = '" & CheckInTool & "'" & _
  " ORDER BY CheckOut DESC")

1 weeks ago