removing record 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.

[RuralGuy]

You will see #Deleted until the RecordSet is Requeried. If you turn on Cascade Deletes in Referential Integrity and your relationships are set up correctly, all you need to do is delete the Parent record and all of the Children go away. Way too powerful for me to use frankly.

[rsearing]

Well - trust me - I have it set up so that the user can actually archive - but after x amount of years they want to remove completely from DB.

So - essentially - I'm trying to do the cascading myself. Because I'm a stickler for trying to understand things - where, in my code, would you suggest me changing how I requery? Or - what code am I missing.

I have literally been working on this for two days - trying a variety of different things. I *know* this is because I don't understand how the data is binding - and how the recordset is working. Basically - I deleting all the stuff programatically via SQL commands directly to the tables - then I just want to refresh the control - but it's not working with the way I have it coded.

Again - thank you!

[RuralGuy]

I do not know what your forms look like but wherever you see #Deleted needs that form Requeried after all of the deleting is complete.

[rsearing]

Well - I thought that's what I did - if you look at the code above - you will see about 4 calls to DeleteRS - a Sub I created to delete the Parent, Child, Childhours and TotalCharges (4 related tables)...then I do a requery on the f_parent form - the main form.

See attached for a look at the form. This is what I get after I click delete - it actually just sits there - then as soon as I click the drop down on the left - I get the attached pic:

[RuralGuy]

Any chance you can post the db so we can look at it?

[rsearing]

Sure thing. It's uploaded to www.kofc1913.org/gregorydaycare.mdb

I have a couple functions in a global file - and then most of the code is in the f_parent class and the global function DeleteRS - PLEASE don't critisize - this is my *first* attempt at writing an Access app - and I don't fully understand Resultsets - and writing code to interact with the DB.....



I really appreciate the help/advice!

Sincerely,
Rob

[RuralGuy]

You can add attachments directly to your posts on this forum. I could not read the db you posted. Is your db in 2000 format? Try and post as an attachment here.