ErrMsg: The MS Jet DBEngine stopped the process because you and an

[Guest]

Hello
I am intercepting the Delete action with KeyPreview and running a process on
the selected records. How can I release an object (table) having Set the
object to a variable and enumerated the selected records to memory, I have
Set the variable to Nothing at the end of my code but I still receive the
above message once when I hand back to the BeforeDelConfirm event. TIA Simon

PS. I have to do it this way because Access removes the selection
immediately OnDelete.

i.e. If I were using DAO (which I'm not) I would use:
Set rst = Nothing
rst.Close

 

[Douglas J. Steele]

How did you set the reference in the first place? Presumably you've got a
Set statement somewhere: you need to set that same object to nothing.

BTW, what you've got is incorrect for DAO. You're setting the reference to
nothing and then you're trying to close it. You have to do it in the
opposite order:

rst.Close
Set rst = Nothing

 

[Guest]

Thanks Douglas, here's what I'm doing

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

'intercept delete action and run some code to update the selected records

Select Case KeyCode
Case 46
'If KeyCode is Delete
x = UpdateSelectedRecs(Form_sfdtls)
End Select

End Sub

Private Function UpdateSelectedRecs(f As Form)
Dim i As Long
Dim RS As Object
Dim Criteria As String

'Get the form and its recordset.

Set RS = f.RecordsetClone

If RS.RecordCount = 0 Then
Set RS = Nothing
Exit Function
End If

' Move to the first record in the recordset.
RS.MoveFirst

'Move to the first selected record.
RS.Move f.SelTop - 1

' Build the string
For i = 1 To f.SelHeight

If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[itemid]=" & RS.itemid
RS.MoveNext
Next i

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE items SET items.[free] = True WHERE " &
Criteria, 0
DoCmd.SetWarnings True

Set RS = Nothing

End Function

Funny thing is, when the UpdateSelectedRecs has run and the Delete action
resumes, the records are locked and I receive the "you and another user are
attempting to change the same records at the same time" message. When I OK
the message and hit Delete again, all works fine. I have found another
solution but would be interested to learn if there is some fix that would
free the object.

TIA, Simon

 

[Douglas J. Steele]

To be honest, I think it's just timing.

Try putting in a DoEvents after you set RS to nothing.

BTW, despite what you said in your first post, that's DAO code you're using!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Douglas, here's what I'm doing

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

'intercept delete action and run some code to update the selected
records

Select Case KeyCode
Case 46
'If KeyCode is Delete
x = UpdateSelectedRecs(Form_sfdtls)
End Select

End Sub

Private Function UpdateSelectedRecs(f As Form)
Dim i As Long
Dim RS As Object
Dim Criteria As String

'Get the form and its recordset.

Set RS = f.RecordsetClone

If RS.RecordCount = 0 Then
Set RS = Nothing
Exit Function
End If

' Move to the first record in the recordset.
RS.MoveFirst

'Move to the first selected record.
RS.Move f.SelTop - 1

' Build the string
For i = 1 To f.SelHeight

If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[itemid]=" & RS.itemid
RS.MoveNext
Next i

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE items SET items.[free] = True WHERE " &
Criteria, 0
DoCmd.SetWarnings True

Set RS = Nothing

End Function

Funny thing is, when the UpdateSelectedRecs has run and the Delete action
resumes, the records are locked and I receive the "you and another user
are
attempting to change the same records at the same time" message. When I
OK
the message and hit Delete again, all works fine. I have found another
solution but would be interested to learn if there is some fix that would
free the object.

TIA, Simon

How did you set the reference in the first place? Presumably you've got a
Set statement somewhere: you need to set that same object to nothing.

BTW, what you've got is incorrect for DAO. You're setting the reference
to
nothing and then you're trying to close it. You have to do it in the
opposite order:

rst.Close
Set rst = Nothing

 

[Guest]

Thanks again. A DoEvents didn't change the behaviour. I think I'll switch
KeyCode 46 to 0 and delete the records with a DELETE query instead...

Thanks again, Simon

To be honest, I think it's just timing.

Try putting in a DoEvents after you set RS to nothing.

BTW, despite what you said in your first post, that's DAO code you're using!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Douglas, here's what I'm doing

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

'intercept delete action and run some code to update the selected
records

Select Case KeyCode
Case 46
'If KeyCode is Delete
x = UpdateSelectedRecs(Form_sfdtls)
End Select

End Sub

Private Function UpdateSelectedRecs(f As Form)
Dim i As Long
Dim RS As Object
Dim Criteria As String

'Get the form and its recordset.

Set RS = f.RecordsetClone

If RS.RecordCount = 0 Then
Set RS = Nothing
Exit Function
End If

' Move to the first record in the recordset.
RS.MoveFirst

'Move to the first selected record.
RS.Move f.SelTop - 1

' Build the string
For i = 1 To f.SelHeight

If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[itemid]=" & RS.itemid
RS.MoveNext
Next i

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE items SET items.[free] = True WHERE " &
Criteria, 0
DoCmd.SetWarnings True

Set RS = Nothing

End Function

Funny thing is, when the UpdateSelectedRecs has run and the Delete action
resumes, the records are locked and I receive the "you and another user
are
attempting to change the same records at the same time" message. When I
OK
the message and hit Delete again, all works fine. I have found another
solution but would be interested to learn if there is some fix that would
free the object.

TIA, Simon

How did you set the reference in the first place? Presumably you've got a
Set statement somewhere: you need to set that same object to nothing.

BTW, what you've got is incorrect for DAO. You're setting the reference
to
nothing and then you're trying to close it. You have to do it in the
opposite order:

rst.Close
Set rst = Nothing

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello
I am intercepting the Delete action with KeyPreview and running a
process
on
the selected records. How can I release an object (table) having Set
the
object to a variable and enumerated the selected records to memory, I
have
Set the variable to Nothing at the end of my code but I still receive
the
above message once when I hand back to the BeforeDelConfirm event. TIA
Simon

PS. I have to do it this way because Access removes the selection
immediately OnDelete.

i.e. If I were using DAO (which I'm not) I would use:
Set rst = Nothing
rst.Close