how to ensure delete child records with MySQL back end Access front end?

[AccessIdiot]

Back with another question.

I have a form with a subform that also has a subform. Tables in MySQL:

dpr = dpr_id (pk), etc.
dpr_survey = survey_id (pk), dpr_id, etc.
dpr_cr = cr_id (pk), survey_id, etc.

On each of the three forms is a delete button to delete the current record. It works well for the parent record but not the child record(s). I have relationships set up for dpr_survey and dpr_cr but only in the access relationships tool.

Here is the code on my delete button:

If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
      DoCmd.RunCommand acCmdDeleteRecord
End If

What do I need to include to make sure the child records get delete too?

Thanks for any help!

[Mariostg]

There are some considerations here you might want to look at.
It could be a disaster to blindly delete childrens when you delete a parent unless you are fully aware of what you are doing. I always set up my databases to prevent the deletion of a parent if a child record exists. The user would get a warning saying the record cannot be deleted because a related record exists. This is particularly true if you have many people using your database.
The most expedient way, and if you have access to the design of the MySQL database, you could create a constraint to cascade delete. So when you have a query action to delete a parent, MySQL will delete the childrens too.
If there is no such constraint, you would need to get the primary key of the parent then run a query in the children table that would delete the records that have that parent primary key.

[AccessIdiot]

I'm fairly new to MySQL. How do you set up cascading? I noticed that option was not available on the Access side of the relationship builder.

Thanks again for your help. :-)

[Mariostg]

That does not surprise me. I doubt you can use Access to design MySQL database. You will probably have to turn to MySQL Designer or alter the structure through the MySQL command prompt. How were relationship set up initially, because you mention you have relationship established on your original post?

[AccessIdiot]

phpmyadmin was used to create the tables initially. In Access I used the relationship diagrammer to simply set connections between tables but the cascading options are greyed out. I'm not familiar enough with phpmyadmin or MySQL to define those relationships. Is there a better interface (has to be free, too) to do that in?

[Mariostg]

It has been a while since I used mySQL intensively. That would be a post for the MySQL forum. But I believe there are free open source softwares and I think MySQL Designer is free.

[AccessIdiot]

Thanks I'll look into it. So do you think that if I set those relationships through MySQL that the code I have above will work? Or does it need to be augmented?

[Mariostg]

Technically, that should do. Maybe you want to test it though by importing the tables in a test database that would contain the forms. There, because tables are in Access, you could create the relationship and see if deleting the parent will delete the children as expected.

[AccessIdiot]

Okay, so here is what I came up with. This is on the main form and will delete a grandchild, a child, and the parent (that sounds so morbid).

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click
Dim strSQL As String

DoCmd.SetWarnings (False)
If MsgBox("You are about to delete a daily progress report dated " & Me.dpr_date & " and all its associated survey areas and cultural resources. Do you wish to continue and delete the record?", vbYesNo) = vbNo Then
Else

strSQL = "delete from DPR_CR_ID where survey_id = " & Me!frm_DPRSurvey.Form!survey_id
DoCmd.RunSQL strSQL
strSQL = "delete from DPR_SURVEY_ID where dpr_id = " & Me.dpr_id
DoCmd.RunSQL strSQL
strSQL = "delete from DPR where dpr_id = " & Me.dpr_id
DoCmd.RunSQL strSQL

DoCmd.RunCommand acCmdDeleteRecord

End If

DoCmd.GoToRecord , , acFirst

DoCmd.SetWarnings (True)

Exit_cmdDelete_Click:
Exit Sub

Err_cmdDelete_Click:
'MsgBox Err.Description
Resume Exit_cmdDelete_Click

Me.Requery

End Sub

It works GREAT. The only problem is that the requery doesn't do anything. Do I have it in the wrong place? As soon as the record is deleted the record count still shows "1 of 2" when it should be "1 of 1" and the deleted record shows "#deleted".

Seems like a simple refresh problem but I have no idea how to make it happen.

[AccessIdiot]

Got it. Had to set focus to a field on the main form first, then requery.

[Mariostg]

There you go, sounds like you can go back to you GIS stuff now :)
You can delete line 17 in your routine: DoCmd.RunComman d acCmdDeleteReco rd. I think (without seeing the rest of your processes) the three docmd.runsql above do the trick.