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.