Unable to Delete a Record

[Susan Bricker]

I have a command button on a form that is supposed to Delete the record
being displayed. The record is displayed one to a form. The form is
not a Pop-Up nor is it Modal. Tracing the btnDelete event routine shows
that AllowDeletions is TRUE.

When the Delete button is clicked (without TRACE ON), I get a 'beep',
the recordselector (vertical bar on left of form) gets dark in color,
but the record is not deleted. Also, there is no error message that
pops up.

HOWEVER, when I trace the routine ...

Private Sub btnDelete_Click()
On Error GoTo Err_btnDelete_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_btnDelete_Click:
Exit Sub

Err_btnDelete_Click:
Call ShowError("frmPeople", "btnDelete_Click", Err.Number,
Err.Description)
Resume Exit_btnDelete_Click

End Sub

When the first DoCmd is executed I get an error that displays from the
error displaying routine ShowError. The error says:

"The command or action 'Select Record' isn't available now"

I've looked around in the database options to see if I have something
set somewhere to prevent Deletions. I can't find anything.

This error happens for two tables using different forms, but the same
logic structure (delete command button with the same DoCmd
instructions).

HELP. I thought that I was so close to finishing this project. Now I'm
stumped again.

Thanks.

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Allen Browne]

Try replacing your 2 lines with:
If Me.Dirty Then
Me.Undo
End If
If Not Me.NewRecord Then
RunCommand acCmdDeleteRecord
End If

What this does is to undo any edits in progress: an undo or save has to
happen before the delete. If you are at a new record, that's all you need;
otherwise it executes the delete. This assumes the button is in the same
form as the record you want to delete. (If not, you can use the Delete
method on the RecordsetClone of that form, after setting the Bookmark to the
desired record.)

That should work, and generate an error if there is any reason why the
record cannot be deleted.

With your original code, the issue when tracing is probably that the VBA
window has focus when the 2nd DoMenuItem was executed. DoMenuItem applies to
the active window, and it can't delete a record out of the VBA window.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Susan Bricker" <sl*****@verizon.net> wrote in message
news:nH***************@news.uswest.net...

I have a command button on a form that is supposed to Delete the record
being displayed. The record is displayed one to a form. The form is
not a Pop-Up nor is it Modal. Tracing the btnDelete event routine shows
that AllowDeletions is TRUE.

When the Delete button is clicked (without TRACE ON), I get a 'beep',
the recordselector (vertical bar on left of form) gets dark in color,
but the record is not deleted. Also, there is no error message that
pops up.

HOWEVER, when I trace the routine ...

Private Sub btnDelete_Click()
On Error GoTo Err_btnDelete_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_btnDelete_Click:
Exit Sub

Err_btnDelete_Click:
Call ShowError("frmPeople", "btnDelete_Click", Err.Number,
Err.Description)
Resume Exit_btnDelete_Click

End Sub

When the first DoCmd is executed I get an error that displays from the
error displaying routine ShowError. The error says:

"The command or action 'Select Record' isn't available now"

I've looked around in the database options to see if I have something
set somewhere to prevent Deletions. I can't find anything.

This error happens for two tables using different forms, but the same
logic structure (delete command button with the same DoCmd
instructions).

HELP. I thought that I was so close to finishing this project. Now I'm
stumped again.

Thanks.

Regards,
SueB

[Susan Bricker]

Allen,
Thank you for your suggestion. I'm getting closer.

Your suggestion seems to work, but it doesn't. Here's what happens now.

1. Click on Delete button
2. Get "canned" error msg: "You are about to delete 1 record(s). If you
click yes, you won't be able to undo the Delete operation. Are you sure
you want to delete these records?
3. Click Yes button.
4. NO additional pop-up message appears telling me that the record has
been deleted. However, I have some logic (in Form_Current()) to display
which record is being displayed out of how many records total. This
label is updated to reflect the record being deleted and the record
being displayed is changed to the next record (I think it's the next
record - didn't really check this).
5. Ok ... you think it worked. However, I have an unbound combobox used
to search for a record at the top of the screen. I make sure that it
has been requeried (after the delete) and it still has the original
record listed. When I close the form and go the previous form, where
there is a search combobox for this table, it still shows the record).
If I close the database and go back in, and repeat the entire logic
path, the record still exists and gets displayed when selected.

OK ... now the fun ...
When I trace the btnDelete_Click routine ...
on the actual delete command (from your suggestion) I get the following
error (which didn't appear when not tracing):
"The command or action 'DeleteRecord' isn't available now" (error number
2046).

Looks like the same problem as before.

Have another idea?
Thanks.
Regards,
SueB

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Allen Browne]

Here's another way to delete the record in the form:
If Me.Dirty Then
Me.Undo
End If
If Not Me.NewRecord Then
With Me.RecordsetClone
.Bookmark = Me.Bookmark
.Delete
End With
End If

You may want to add a confirmation dialog, as this won't automatically give
one.

It should work even if you trace through it, as it does not depend on the
form having focus.

If that also fails to delete the record, then something else is going on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Susan Bricker" <sl*****@verizon.net> wrote in message
news:0K***************@news.uswest.net...

Allen,
Thank you for your suggestion. I'm getting closer.

Your suggestion seems to work, but it doesn't. Here's what happens now.

1. Click on Delete button
2. Get "canned" error msg: "You are about to delete 1 record(s). If you
click yes, you won't be able to undo the Delete operation. Are you sure
you want to delete these records?
3. Click Yes button.
4. NO additional pop-up message appears telling me that the record has
been deleted. However, I have some logic (in Form_Current()) to display
which record is being displayed out of how many records total. This
label is updated to reflect the record being deleted and the record
being displayed is changed to the next record (I think it's the next
record - didn't really check this).
5. Ok ... you think it worked. However, I have an unbound combobox used
to search for a record at the top of the screen. I make sure that it
has been requeried (after the delete) and it still has the original
record listed. When I close the form and go the previous form, where
there is a search combobox for this table, it still shows the record).
If I close the database and go back in, and repeat the entire logic
path, the record still exists and gets displayed when selected.

OK ... now the fun ...
When I trace the btnDelete_Click routine ...
on the actual delete command (from your suggestion) I get the following
error (which didn't appear when not tracing):
"The command or action 'DeleteRecord' isn't available now" (error number
2046).

Looks like the same problem as before.

Have another idea?
Thanks.
Regards,
SueB

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Susan Bricker]

Allen,

Thanks, again, for trying to help. It didn't do the trick, though.
Looks like I have something else going on. I'll plug away at it and if
I find out I'll respond to this email thread. Boy am I stumped ... and
of course it's at the very worst time - just closed on a new house,
selling current house, trying to finish this project, and my REAL job's
responsibilities are falling to the wayside. ARGHHHH!!!

Regards,
SueB

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***