H
Huw Davies
I have a table (tblPupils) that has a series of 1-many relationships with 5
other tables. Within these 5 other tables, one (tblReferrals) has a series of
1-many relationships with a further 6 tables. Data from tblPupils is
displayed on frmPupils , and the data from 4 of it's "child" tables are
displayed on sub-forms to that form. Data from tblReferrals is displayed on
it's own form (frmReferrals), which then has a corresponding series of
subforms to display the data from the final 6 tables. frmReferrals is opened
via a command button on frmPupils.
There is also a DELETE command button on frmPupils, which when pressed kicks
off the following code..
.....
.....
.....
Dim strSQL As String
Dim rst As DAO.Recordset
Dim intMsgReturn As Integer
Dim intPupilID As Integer
Dim intReferralID As Integer
intPupilID = Me.txtPupilID
If intPupilID = 0 Then
Exit Sub
End If
intMsgReturn = MsgBox("Are you sure you want to delete this Pupil from the
database?", vbYesNo, "Record Deletion")
If intMsgReturn = vbYes Then
strSQL = "DELETE * FROM tblTestResults WHERE PupilID = " & intPupilID
DoCmd.RunSQL strSQL
strSQL = "DELETE * FROM tblLetters WHERE PupilID = " & intPupilID
DoCmd.RunSQL strSQL
strSQL = "DELETE * FROM tblReports WHERE PupilID = " & intPupilID
DoCmd.RunSQL strSQL
strSQL = "DELETE * FROM tblParents WHERE PupilID = " & intPupilID
DoCmd.RunSQL strSQL
strSQL = "SELECT ReferralID FROM tblReferrals WHERE PupilID = " &
intPupilID
Set rst = currentDB.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
intReferralID = rst.Fields("ReferralID")
strSQL = "DELETE * FROM tblDates WHERE ReferralID = " &
intReferralID
DoCmd.RunSQL strSQL
strSQL = "DELETE * FROM tblLessons WHERE ReferralID = " &
intReferralID
DoCmd.RunSQL strSQL
strSQL = "DELETE * FROM tblIPPDetails WHERE ReferralID = " &
intReferralID
DoCmd.RunSQL strSQL
strSQL = "DELETE * FROM tblPriorityRecs WHERE ReferralID = " &
intReferralID
DoCmd.RunSQL strSQL
strSQL = "DELETE * FROM tblTermActions WHERE ReferralID = " &
intReferralID
DoCmd.RunSQL strSQL
strSQL = "DELETE * FROM tblTermSessions WHERE ReferralID = " &
intReferralID
DoCmd.RunSQL strSQL
End If
strSQL = "DELETE * FROM tblReferrals WHERE PupilID = " & intPupilID
DoCmd.RunSQL strSQL
strSQL = "DELETE * FROM tblPupils WHERE PupilID = " & intPupilID
DoCmd.RunSQL strSQL
Me.Requery
subHandleCurrent Me
End If
.......
.......
.......
subHandleCurrentMe is a sub that handles the display characteristics of the
navigation buttons I use on most forms.
Now we get to the strange bit...
When I hit the DELETE button, every single one of the child records gets
deleted, but the parent record, (the one in tblPupils) stays exactly where it
is. No error messages are generated - it just stays there.
Now the even stranger strange bit....
If at this point, I open frmReferrals (where there is no data to display),
and immediately close it again so that I return to frmPupils - when I now hit
the DELETE button, the parent record does get deleted.
I have tried replacing the final DoCmd.RunSQL with a) CurrentDb.Execute b)
RunCommand acCmdDeleteRecord and c) DoCmd.DoMenuItem....., but none have done
the trick. Has anyone got any ideas as to where I'm going wrong?
other tables. Within these 5 other tables, one (tblReferrals) has a series of
1-many relationships with a further 6 tables. Data from tblPupils is
displayed on frmPupils , and the data from 4 of it's "child" tables are
displayed on sub-forms to that form. Data from tblReferrals is displayed on
it's own form (frmReferrals), which then has a corresponding series of
subforms to display the data from the final 6 tables. frmReferrals is opened
via a command button on frmPupils.
There is also a DELETE command button on frmPupils, which when pressed kicks
off the following code..
.....
.....
.....
Dim strSQL As String
Dim rst As DAO.Recordset
Dim intMsgReturn As Integer
Dim intPupilID As Integer
Dim intReferralID As Integer
intPupilID = Me.txtPupilID
If intPupilID = 0 Then
Exit Sub
End If
intMsgReturn = MsgBox("Are you sure you want to delete this Pupil from the
database?", vbYesNo, "Record Deletion")
If intMsgReturn = vbYes Then
strSQL = "DELETE * FROM tblTestResults WHERE PupilID = " & intPupilID
DoCmd.RunSQL strSQL
strSQL = "DELETE * FROM tblLetters WHERE PupilID = " & intPupilID
DoCmd.RunSQL strSQL
strSQL = "DELETE * FROM tblReports WHERE PupilID = " & intPupilID
DoCmd.RunSQL strSQL
strSQL = "DELETE * FROM tblParents WHERE PupilID = " & intPupilID
DoCmd.RunSQL strSQL
strSQL = "SELECT ReferralID FROM tblReferrals WHERE PupilID = " &
intPupilID
Set rst = currentDB.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
intReferralID = rst.Fields("ReferralID")
strSQL = "DELETE * FROM tblDates WHERE ReferralID = " &
intReferralID
DoCmd.RunSQL strSQL
strSQL = "DELETE * FROM tblLessons WHERE ReferralID = " &
intReferralID
DoCmd.RunSQL strSQL
strSQL = "DELETE * FROM tblIPPDetails WHERE ReferralID = " &
intReferralID
DoCmd.RunSQL strSQL
strSQL = "DELETE * FROM tblPriorityRecs WHERE ReferralID = " &
intReferralID
DoCmd.RunSQL strSQL
strSQL = "DELETE * FROM tblTermActions WHERE ReferralID = " &
intReferralID
DoCmd.RunSQL strSQL
strSQL = "DELETE * FROM tblTermSessions WHERE ReferralID = " &
intReferralID
DoCmd.RunSQL strSQL
End If
strSQL = "DELETE * FROM tblReferrals WHERE PupilID = " & intPupilID
DoCmd.RunSQL strSQL
strSQL = "DELETE * FROM tblPupils WHERE PupilID = " & intPupilID
DoCmd.RunSQL strSQL
Me.Requery
subHandleCurrent Me
End If
.......
.......
.......
subHandleCurrentMe is a sub that handles the display characteristics of the
navigation buttons I use on most forms.
Now we get to the strange bit...
When I hit the DELETE button, every single one of the child records gets
deleted, but the parent record, (the one in tblPupils) stays exactly where it
is. No error messages are generated - it just stays there.
Now the even stranger strange bit....
If at this point, I open frmReferrals (where there is no data to display),
and immediately close it again so that I return to frmPupils - when I now hit
the DELETE button, the parent record does get deleted.
I have tried replacing the final DoCmd.RunSQL with a) CurrentDb.Execute b)
RunCommand acCmdDeleteRecord and c) DoCmd.DoMenuItem....., but none have done
the trick. Has anyone got any ideas as to where I'm going wrong?