
Signature
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(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.
[quoted text clipped - 22 lines]
> > Set rst = Nothing
> > rst.Close
Douglas J. Steele - 30 Sep 2006 18:35 GMT
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!

Signature
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
> Thanks Douglas, here's what I'm doing
>
[quoted text clipped - 91 lines]
>> > Set rst = Nothing
>> > rst.Close
SimonW - 30 Sep 2006 19:07 GMT
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.
>
[quoted text clipped - 97 lines]
> >> > Set rst = Nothing
> >> > rst.Close