The MS Jet DBEngine stopped the process becasue...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
Hi,
Just try to use AfterDeleteConfirm instead of keydown event

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

SimonW said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
Hi,
Just try to use AfterDeleteConfirm instead of keydown event

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

SimonW said:
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 said:
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

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
Click to expand...
Click to expand...
Click to expand...
 
Hi Simon,
in this case I store selected records in some variables, here you can use
KeyDown event

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

SimonW said:
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 said:
Hi,
Just try to use AfterDeleteConfirm instead of keydown event

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

SimonW said:
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

:

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

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top