> Can't be done. Access commits the selected records to buffer, so I lose
> my
> SelTop and SelHeight values. I need to perform the operation on the
> selected
> records at the same time the user selects them for deletion - hence the
> KeyPreview technique. Even tho' I free up the object with Set RS =
> Nothing -
> somehow the table is still 'locked' and the Delete operationis halted. On
> the
> second attempt, the code works as intended.
>
> Can you figure out how to release the lock on the table immediately after
> DeleteSelectedItems has run (it's badly named, thinking about it, I should
> have called it UpdateFreeItems())
>
> TIA, Simon
>
> "Alex Dybenko" wrote:
>
>> Hi,
>> Just try to use AfterDeleteConfirm instead of keydown event
>>
>> --
>> Best regards,
>> ___________
>> Alex Dybenko (MVP)
>>
http://alexdyb.blogspot.com>>
http://www.PointLtd.com>>
>> "SimonW" <SimonW[ at ]discussions.microsoft.com> wrote in message
>> news:58A6E560-07CC-4706-938E-F43FF36C0E53[ at ]microsoft.com...
>> > Thanks Alex. I want the delete action to go through immediately after
>> > DeleteSelectedItems has enumerated the selected records and UPDATEd the
>> > table. How can I use the AfterDeleteConfirm action to update the table
>> > ?
>> > Currently, the error message appears as soon as Access attempts to
>> > commit
>> > the
>> > records to buffer, so I can't even get to BeforeDeleteConfirm...
>> >
>> > The UPDATE query is setting a flag in the Products table. The table
>> > which
>> > I'm deleting records from is the Order Details table - so there's a one
>> > to
>> > many relationship in force...
>> > TIA, Simon
>> >
>> > "Alex Dybenko" wrote:
>> >
>> >> Hi,
>> >> you have to either cancel delete, setting KeyCode to 0 in case of 46,
>> >> or,
>> >> if you really need to delete current record, you can use AfterDelete
>> >> event
>> >> to update other tables
>> >>
>> >> --
>> >> Best regards,
>> >> ___________
>> >> Alex Dybenko (MVP)
>> >>
http://alexdyb.blogspot.com>> >>
http://www.PointLtd.com>> >>
>> >> "SimonW" <SimonW[ at ]discussions.microsoft.com> wrote in message
>> >> news:71FD00B4-1C66-4172-B17C-D6251B025546[ at ]microsoft.com...
>> >> > Using KeyPreview I am intercepting the user when he/she keys Delete
>> >> > (KeyCode
>> >> > 46) and running some code to enumerate the records in the dynaset in
>> >> > which
>> >> > the records are being deleted. I have to enumerate the records
>> >> > before
>> >> > Access
>> >> > removes & buffers them during the BeforeDelConfirm event.
>> >> >
>> >> > Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
>> >> >
>> >> > 'intercept delete key and enumerate records first
>> >> > Select Case KeyCode
>> >> > Case 46
>> >> > 'If KeyCode is Delete
>> >> > x = DeleteSelectedItems(Form_sfdtls)
>> >> > End Select
>> >> >
>> >> > End Sub
>> >> >
>> >> > 'KB Article 208502
>> >> >
>> >> > Function DeleteSelectedItems(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
>> >> >
>> >> > Trouble I'm having is this. Access displays the following message
>> >> > once
>> >> > when
>> >> > the delete key is pressed having just added a record to the
>> >> > dynaset...
>> >> >
>> >> > "The Microsoft Jet databse engine has stopped the process because
>> >> > you
>> >> > and
>> >> > another user are attempting to change the same data at the same
>> >> > time."
>> >> >
>> >> > When I ok the message and attempt to press delete again the code
>> >> > works
>> >> > and
>> >> > intended and no message is displayed.
>> >> >
>> >> > Can anyone show me how to prevent the message from displaying in the
>> >> > first
>> >> > instance. TIA Simon
>> >>
>> >>
>>
>>