Great Difficulty Deleting Parent Record

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?
 
D

David Lloyd

One suggestion, and this is meant more as a simplifying suggestion, would be
to consider implementing cascading referential integrity on your tblPupils.
If you do this, then you only need one query.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


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?
 
H

Huw Davies

David,

Many thanks for your note. I've got cascade updates on, but not cascade
deletes - it's something that I've always avoided for no better reason than
when I first started playing with Access, I read in a book that deletes
should be always be under my control, and assuming that if someone's smart
enough to write a book, then they're to be taken seriously, and what started
as naive policy has become a habit. Having said that anyone smart enough to
get an MCSD is also to be taken seriously, so I'll follow your idea and let
you know how it goes. The only thing I'd say is that it works, I suppose I'll
be even more worried, because as far as I can tell, I'm effectively doing a
cascade delete in (logically) the same way as Access would do it. I'll let
you know how I get on.

Regards,

Huw.
 
T

Tim Ferguson

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?

Interesting: it may be that the RunSQL is happening asynchronously. Since
it calls the UI code it may be returning before the update has happened
and therefore the tables are not cleared before doing the next one.

I have never met this, but then again, I use db.Execute for everything
anyway. You might find it helps because if there is a problem with any of
the SQL you can trap it. With SetWarnings False (I assume) you wouldn't
see any problems at all if there were any. The .Execute method definitely
runs before the code returns.

An alternative would be to put a delay before the final RunSQL, say using
the Sleep command.

As a final note, I too don't like using Cascade Deletes. In general, if a
user requests a delete that causes a lot of destruction, I query the db
first to see how much and then put up a MsgBox to say something like "You
are about to delete 281 records. Are you sure?". At least then you know
it's not your fault if they wreck their database!

All the best


Tim F
 
H

Huw Davies

Tim, David, Many thanks for the ideas - I'm still stuck though. Following on
from David's suggestions, I did turn on the cascade deletes across all the
tables and limited my self to the one SQL command, but still no joy. I turned
the SetWarnings back to TRUE and found that the system was refusing to do the
delete due to a lock violation - very confusing as I'm the only one on the
machine. Interestingly, when I went direct to the tables and deleted the
record manually - it went without a single word of complaint.

Following Tim's suggestions, I have converted to the currentDb.Execute
method (both with and without the Sleep function), but again no joy - with
exactly the same symptoms.

I have also remarked out the subHandleCurrentMe so the only things now going
on are the SQL executes and the Requery.

Regards,

Huw.
 
H

Huw Davies

Tim, David, I've managed to crack the problem at long last. The Sleep
function didn't seem to make any difference, but by putting a Me.Refresh in
before the last SQL statement (the one that deletes the parent record) it
works a treat.

Many thanks for your help, you helped me focus in the right direction.
 
T

Tim Ferguson

but by putting a Me.Refresh in
before the last SQL statement (the one that deletes the parent record)
it works a treat.

Curious. I might have expected that solution if you were calling a
DoCmd.DeleteCurrentRecord or whatever it's called, but I would not have
suspected a form refresh to affect a direct call to the db engine.

Glad it worked out for you in the end, though.

All the best


Tim F
 
L

Linda Brown

Hello, gentlemen. Just so you know, I too have been getting a violoation.
And there is no reason for it. I had an update query that refused to run, no
matter what.

I removed the referential integretity from the single table I was trying to
update. It still would not run. I created a function and called it. Still,
I was getting the locks violation message. No one other than myself was
using this database (at home, no less).

I am having to create a message so that the user can be notified when to go
to the Modules window and which Function to run by clicking the carrot....

Originally the update query was supposed to change a Yes field to No (there
would be only one record among 8022 records involved), and change the Notes
field if not Null to an empty string (""). The function, which cannot be
called within the VBA code because of receiving messages on lock violations,
is run manually and goes through the Yes field, changing a Yes to No, and
then goes through the Notes field to make any text entries empty strings.

Clearly there is a bug in something. (-:
Regards, Linda Brown
 
T

tina

if you're hoping that someone at Microsoft will look at your comments and
follow up, it would help if they knew what operating system you're using -
such as Windows 2000, Windows XP, etc - and what version of Access you're
using. (this is a public *user* forum, so i have no idea whether or not
anyone at MS monitors it with any regularity.)

hth
 
L

Linda Brown

Windows XP, Access 2000

tina said:
if you're hoping that someone at Microsoft will look at your comments and
follow up, it would help if they knew what operating system you're using -
such as Windows 2000, Windows XP, etc - and what version of Access you're
using. (this is a public *user* forum, so i have no idea whether or not
anyone at MS monitors it with any regularity.)

hth
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top