Problems in deleting a record

[Leif]

I'm having problems deleting a record using code in a form. The delete
command button is on a main form. The record to be deleted is on a datasheet
subform. I've set up a relationship between the parent/child tables to
enforce referential integrity, but to NOT allow cascade deletes. The problem
comes into play when the record to be deleted is a parent record with
children. In this case I do not want the parent deleted, just a message
given.

All I want is what happens when a user selects a record and deletes the
record via the menu Edit/Delete Record. Two things happen:
1. The user is pompted if it is OK to delete the record(s).
2. The user is informed if the record was not deleted, such as a message
that related records exist.

The subform is called sfrm_Profile_Attributes_EditA. First I tried using
the following code for the click event of the delete command button:

On Error GoTo Err_cmdDeleteAttr_Click

DoCmd.GoToControl "sfrm_Profile_Attributes_EditA"
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord

......

This works, except when it is a parent record. In that case no message is
generated, nor is an error generated. That is bad, since the user thinks
they are done, but no message is given that the record cannot be delete.
Also, the program cannot distinguish since no error is generated.

Even though Microsoft recommends using Docmd.RunCommand interesting enough
they do not follow their own advice when it comes to the command button
wizard. The wizard uses DoCmd.DoMenuItem. So, following MS lead, I change
the code to the following:

On Error GoTo Err_cmdDeleteAttr_Click

DoCmd.GoToControl "sfrm_Profile_Attributes_EditA"
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

This works a bit better since it generates an error if it cannot delete due
to a parent record. It generates an error 2501. Unfortuntaley, however,
this is the same number that is generated if the user replies Cancel to the
automatically generated message "Do you want to delete the record?".

I found if I try to delete the record programmatically, I get a better error
number, such as the following:

sfrm_Profile_Attributes_EditA.Form.Recordset.Delete

I get an error if the parent has a child record. Unfortunatley, I do not
get a prompt to confirm the record should be deleted, it just goes ahead and
does it.

So, I had to hobble together the following code, just to get it to do what
the menu Edit/Delete Record does.

On Error GoTo Err_cmdDeleteAttr_Click

DoCmd.GoToControl "sfrm_Profile_Attributes_EditA"
ans = MsgBox("Are you sure you want to delete this record(s)?",
vbExclamation + vbOKCancel, "Position Profile Design")
If ans = vbOK Then
sfrm_Profile_Attributes_EditA.Form.Recordset.Delete
End If

.......
Err_cmdDeleteAttr_Click_Exit:
Exit Sub

Err_cmdDeleteAttr_Click:
If Err = 3200 Then
MsgBox "The record cannot be delete because related records exist"
Else
MsgBox Error$
End If
Resume Err_cmdDeleteAttr_Click_Exit

Is there a better way? I'm surprised the docmd.DoMenuItem does not work
exactly the way the menu works by clicking on it, but that seems to be the
case. I cannot believe it should be this hard.

I have Access 2003 on XP Pro.

Thanks for your help.