Going Crazy - Removing from form

[rsearing]

Ok - I am a novice - and am sure this has to do something with recordsets - or how the data is being populated...but I am having the hardest time removing a "parent" from my form. I have 3 others tables that a parent has connections to - a child table, childhours and totalcharges table. I am wanting to delete from all tables, then delete the parent. In the form, I have a parent with a child being the subform. I have tried all sorts of ways of requerying the form - but it doesn't work. My code is probably jumbled up from trying so many different things - but here is my code.

**MY DELETE BUTTON FUNCTION**
Private Sub btn_delete_Click()
If MsgBox("Are you sure you want to delete? This will delete all records associated with this parent including past bills along with all children records.", vbOKCancel, "Confirm Delete") = vbOK Then
Dim myID As Integer
Dim myCN As ADODB.Connection
Dim childRS As ADODB.Recordset
Set childRS = New ADODB.Recordset
Dim strConnection As String
Dim sqlStatement As String
myID = Forms!f_parent!parentId.Value
Dim myForm As Form
sqlStatement = "Select childId from Child WHERE c_parentId = " & Forms!f_parent!parentId.Value
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & CurrentProject.Path & "\GregoryDayCare.mdb;"
Set myCN = New ADODB.Connection
myCN.Open strConnection

With childRS
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.Open sqlStatement, myCN

End With

If childRS.RecordCount > 0 Then
Do Until childRS.EOF
Call DeleteRS("ChildrenHours", "ch_childId", childRS!childId)
Call DeleteRS("Child", "childId", childRS!childId)
'childRS.Delete
'childRS.UpdateBatch
childRS.MoveNext
Loop

End If

childRS.ActiveConnection = Nothing
Call DeleteRS("TotalCharges", "t_parentId", Forms!f_parent!parentId.Value)
Call DeleteRS("Parents", "parentId", myID)
DoCmd.GoToRecord acDataForm, "f_parent", acNewRec
Set myForm = Forms!f_parent
Dim cbo1 As ComboBox
Dim cbo2 As ComboBox
Set cbo1 = Forms!f_parent.Combo40
Set cbo2 = Forms!f_parent.Combo42
cbo1 = vbNullString
cbo2 = vbNullString
Forms!f_parent.Requery

cbo1.Requery
cbo2.Requery
Set cbo1 = Nothing
Set cbo2 = Nothing

Else
MsgBox ("cancel")

End If

End Sub

**MY DELETERS FUNCTION**

Public Sub DeleteRS(myTable As String, myID As String, idNum As Integer)
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & CurrentProject.Path & "\GregoryDayCare.mdb;"
Set cmdCommand = New ADODB.Command
Set cnConnection = New ADODB.Connection
cnConnection.Open strConnection
Dim strSQL As String
strSQL = "DELETE FROM " & myTable & " WHERE " & myID & " = " & idNum
Set cmdCommand.ActiveConnection = cnConnection
cmdCommand.CommandText = strSQL
cmdCommand.Execute
cnConnection.Close
Set cmdCommand = Nothing
Set cnConnection = Nothing
End Sub

This is driving me crazy...as #Deleted is put all over the place after all is deleted.

 

[HiTechCoach]

You might try using Docmd.Echo to turn of the display updates until you have do the requery.

 

[rsearing]

Thank you for the suggestion - however, that didn't work. It displays the #deleted after the requery I believe - just not sure....

 

[3dman]

Please post a picture of the relationship diagram between the tables. I think there may be a flaw there.

 

[rsearing]

Here goes:

 

[3dman]

Try changing the foreign key names to its Primary keys name. e.g. "c_parentId to "parentId". Also change your code accordingly.