deleting records from main and child/sub-forms

T

Ted

in my a2k app, i have three tables:

IRB, Evaluations and Patients.

the PKs are as follows:

IRB = IRB Number
Evaluations = IRB Number, Date of Audit
Patients = IRB Number, MedRecNum, Date of Audit

in my relationships window, i have

IRB linked with Evaluations via a 1-to-many relationship on IRB Number
Evaluations linked with Patients via a 1-to-many relationship on IRB Number
and Date of Audit.

i have established referential integrity and enabled cascade delete and
update and my join type is the default.

i have a main form (parent) called 'Audit Report' that uses the IRB table
and two child/sub-forms on 'Audit Report' called 'Evaluations' and 'Patients'
which use their namesake tables.

i have placed three DELETE buttons on the form called 'Delete IRB', 'Delete
Audit' and 'Delete Patient'.

the code for the 2nd one (audit) is as follows:


Private Sub DeleteAudit_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteAudit_DblClick
If vbOK = MsgBox("Be advised you are about to IRREVERSIBLY DELETE *ALL*
INFORMATION CONCERNING THIS AUDIT DATE (" & Forms![Audit
Report]![Evaluations].Form![Date of Audit] & ") FROM THE DATABASE!",
vbCritical + vbOKCancel + vbDefaultButton2, "SUPER HYPER ULTRA CRITICAL!!!")
Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If

Exit_DeleteAudit_DblClick:
Me.AllowDeletions = False
Exit Sub

Err_DeleteAudit_DblClick:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteAudit_DblClick

End Sub

to my (newbie) surprise, when i agreed to the 2ndary message reminding me
that 1 record in this table and all related records would be deleted, i found
that all records from all three tables were now deleted :-( what i had
expected to find was that the record having the Audit Date from the
Evaluations table and all records having the same Audit Date from the
Patients table would be deleted.

can anyone spot the achilees heel in all of this?
 
M

MacDermott

Is the IRB table included in the query underlying the subform?
Is the button on the main form?
 
T

Ted

hi, i don't just now have the app'n in front of me but i wanted to answer the
first part conditionally since i'll know definitively tomorrow am. so, that
said, let's assume that there is a query under one or more of the child
sub-forms. actually, next, in response to the button question: there are
three buttons and each is situated on the parent/main form (Audit Report) ---
take a look at the way the "Date of Audit" control is referenced for example.
lastly, though you didn't ask, there is a "Select...." bit of SQL code that
is getting the data for one of the combo boxes on the main ("Audit Report")
form. what did you happen to have in mind?

best,

-ted


MacDermott said:
Is the IRB table included in the query underlying the subform?
Is the button on the main form?

Ted said:
in my a2k app, i have three tables:

IRB, Evaluations and Patients.

the PKs are as follows:

IRB = IRB Number
Evaluations = IRB Number, Date of Audit
Patients = IRB Number, MedRecNum, Date of Audit

in my relationships window, i have

IRB linked with Evaluations via a 1-to-many relationship on IRB Number
Evaluations linked with Patients via a 1-to-many relationship on IRB Number
and Date of Audit.

i have established referential integrity and enabled cascade delete and
update and my join type is the default.

i have a main form (parent) called 'Audit Report' that uses the IRB table
and two child/sub-forms on 'Audit Report' called 'Evaluations' and 'Patients'
which use their namesake tables.

i have placed three DELETE buttons on the form called 'Delete IRB', 'Delete
Audit' and 'Delete Patient'.

the code for the 2nd one (audit) is as follows:


Private Sub DeleteAudit_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteAudit_DblClick
If vbOK = MsgBox("Be advised you are about to IRREVERSIBLY DELETE *ALL*
INFORMATION CONCERNING THIS AUDIT DATE (" & Forms![Audit
Report]![Evaluations].Form![Date of Audit] & ") FROM THE DATABASE!",
vbCritical + vbOKCancel + vbDefaultButton2, "SUPER HYPER ULTRA CRITICAL!!!")
Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If

Exit_DeleteAudit_DblClick:
Me.AllowDeletions = False
Exit Sub

Err_DeleteAudit_DblClick:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteAudit_DblClick

End Sub

to my (newbie) surprise, when i agreed to the 2ndary message reminding me
that 1 record in this table and all related records would be deleted, i found
that all records from all three tables were now deleted :-( what i had
expected to find was that the record having the Audit Date from the
Evaluations table and all records having the same Audit Date from the
Patients table would be deleted.

can anyone spot the achilees heel in all of this?
Click to expand...
Click to expand...
 
T

Ted

.....i forgot to mention that when i disable the buttons and their associated
events from the form, using the file menu 'Delete Record' command from both
the 'Audit Report' form and 'Evaluations' sub-form works as i would expect,
i.e. the parent and child records related to the parent are removed. it was
only when using the button actuated event vba code that this anomaly
materializes. arrrgh :-(

MacDermott said:
Is the IRB table included in the query underlying the subform?
Is the button on the main form?

Ted said:
in my a2k app, i have three tables:

IRB, Evaluations and Patients.

the PKs are as follows:

IRB = IRB Number
Evaluations = IRB Number, Date of Audit
Patients = IRB Number, MedRecNum, Date of Audit

in my relationships window, i have

IRB linked with Evaluations via a 1-to-many relationship on IRB Number
Evaluations linked with Patients via a 1-to-many relationship on IRB Number
and Date of Audit.

i have established referential integrity and enabled cascade delete and
update and my join type is the default.

i have a main form (parent) called 'Audit Report' that uses the IRB table
and two child/sub-forms on 'Audit Report' called 'Evaluations' and 'Patients'
which use their namesake tables.

i have placed three DELETE buttons on the form called 'Delete IRB', 'Delete
Audit' and 'Delete Patient'.

the code for the 2nd one (audit) is as follows:


Private Sub DeleteAudit_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteAudit_DblClick
If vbOK = MsgBox("Be advised you are about to IRREVERSIBLY DELETE *ALL*
INFORMATION CONCERNING THIS AUDIT DATE (" & Forms![Audit
Report]![Evaluations].Form![Date of Audit] & ") FROM THE DATABASE!",
vbCritical + vbOKCancel + vbDefaultButton2, "SUPER HYPER ULTRA CRITICAL!!!")
Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If

Exit_DeleteAudit_DblClick:
Me.AllowDeletions = False
Exit Sub

Err_DeleteAudit_DblClick:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteAudit_DblClick

End Sub

to my (newbie) surprise, when i agreed to the 2ndary message reminding me
that 1 record in this table and all related records would be deleted, i found
that all records from all three tables were now deleted :-( what i had
expected to find was that the record having the Audit Date from the
Evaluations table and all records having the same Audit Date from the
Patients table would be deleted.

can anyone spot the achilees heel in all of this?
Click to expand...
Click to expand...
 
M

MacDermott

If the button is on the main form, then Me refers to the main form, and that
is the context in which the delete is performed, deleting the current record
in the main form, and through the cascade delete, all associated ones.
You could try setting focus to the subform (not just the control that
contains it) before executing your RunCommand.

HTH

Ted said:
....i forgot to mention that when i disable the buttons and their associated
events from the form, using the file menu 'Delete Record' command from both
the 'Audit Report' form and 'Evaluations' sub-form works as i would expect,
i.e. the parent and child records related to the parent are removed. it was
only when using the button actuated event vba code that this anomaly
materializes. arrrgh :-(

MacDermott said:
Is the IRB table included in the query underlying the subform?
Is the button on the main form?

Ted said:
in my a2k app, i have three tables:

IRB, Evaluations and Patients.

the PKs are as follows:

IRB = IRB Number
Evaluations = IRB Number, Date of Audit
Patients = IRB Number, MedRecNum, Date of Audit

in my relationships window, i have

IRB linked with Evaluations via a 1-to-many relationship on IRB Number
Evaluations linked with Patients via a 1-to-many relationship on IRB Number
and Date of Audit.

i have established referential integrity and enabled cascade delete and
update and my join type is the default.

i have a main form (parent) called 'Audit Report' that uses the IRB table
and two child/sub-forms on 'Audit Report' called 'Evaluations' and 'Patients'
which use their namesake tables.

i have placed three DELETE buttons on the form called 'Delete IRB', 'Delete
Audit' and 'Delete Patient'.

the code for the 2nd one (audit) is as follows:


Private Sub DeleteAudit_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteAudit_DblClick
If vbOK = MsgBox("Be advised you are about to IRREVERSIBLY DELETE *ALL*
INFORMATION CONCERNING THIS AUDIT DATE (" & Forms![Audit
Report]![Evaluations].Form![Date of Audit] & ") FROM THE DATABASE!",
vbCritical + vbOKCancel + vbDefaultButton2, "SUPER HYPER ULTRA CRITICAL!!!")
Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If

Exit_DeleteAudit_DblClick:
Me.AllowDeletions = False
Exit Sub

Err_DeleteAudit_DblClick:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteAudit_DblClick

End Sub

to my (newbie) surprise, when i agreed to the 2ndary message reminding me
that 1 record in this table and all related records would be deleted,
Click to expand...
i
found
that all records from all three tables were now deleted :-( what i had
expected to find was that the record having the Audit Date from the
Evaluations table and all records having the same Audit Date from the
Patients table would be deleted.

can anyone spot the achilees heel in all of this?
Click to expand...
Click to expand...
Click to expand...
 
T

Ted

hi,

ah so, you're saying that somehow the vba should incorporate some command
that sets the focus to the sub-form itself; can you describe what that
command would look like.

-ted

MacDermott said:
If the button is on the main form, then Me refers to the main form, and that
is the context in which the delete is performed, deleting the current record
in the main form, and through the cascade delete, all associated ones.
You could try setting focus to the subform (not just the control that
contains it) before executing your RunCommand.

HTH

Ted said:
....i forgot to mention that when i disable the buttons and their associated
events from the form, using the file menu 'Delete Record' command from both
the 'Audit Report' form and 'Evaluations' sub-form works as i would expect,
i.e. the parent and child records related to the parent are removed. it was
only when using the button actuated event vba code that this anomaly
materializes. arrrgh :-(

MacDermott said:
Is the IRB table included in the query underlying the subform?
Is the button on the main form?

in my a2k app, i have three tables:

IRB, Evaluations and Patients.

the PKs are as follows:

IRB = IRB Number
Evaluations = IRB Number, Date of Audit
Patients = IRB Number, MedRecNum, Date of Audit

in my relationships window, i have

IRB linked with Evaluations via a 1-to-many relationship on IRB Number
Evaluations linked with Patients via a 1-to-many relationship on IRB
Number
and Date of Audit.

i have established referential integrity and enabled cascade delete and
update and my join type is the default.

i have a main form (parent) called 'Audit Report' that uses the IRB table
and two child/sub-forms on 'Audit Report' called 'Evaluations' and
'Patients'
which use their namesake tables.

i have placed three DELETE buttons on the form called 'Delete IRB',
'Delete
Audit' and 'Delete Patient'.

the code for the 2nd one (audit) is as follows:


Private Sub DeleteAudit_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteAudit_DblClick
If vbOK = MsgBox("Be advised you are about to IRREVERSIBLY DELETE *ALL*
INFORMATION CONCERNING THIS AUDIT DATE (" & Forms![Audit
Report]![Evaluations].Form![Date of Audit] & ") FROM THE DATABASE!",
vbCritical + vbOKCancel + vbDefaultButton2, "SUPER HYPER ULTRA
CRITICAL!!!")
Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If

Exit_DeleteAudit_DblClick:
Me.AllowDeletions = False
Exit Sub

Err_DeleteAudit_DblClick:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteAudit_DblClick

End Sub

to my (newbie) surprise, when i agreed to the 2ndary message reminding me
that 1 record in this table and all related records would be deleted, i
found
that all records from all three tables were now deleted :-( what i had
expected to find was that the record having the Audit Date from the
Evaluations table and all records having the same Audit Date from the
Patients table would be deleted.

can anyone spot the achilees heel in all of this?
Click to expand...
Click to expand...
Click to expand...
 
M

MacDermott

Me.MySubformControl.Form.SetFocus

Put the name of your subform control in place of MySubformControl.

HTH

Ted said:
hi,

ah so, you're saying that somehow the vba should incorporate some command
that sets the focus to the sub-form itself; can you describe what that
command would look like.

-ted

MacDermott said:
If the button is on the main form, then Me refers to the main form, and that
is the context in which the delete is performed, deleting the current record
in the main form, and through the cascade delete, all associated ones.
You could try setting focus to the subform (not just the control that
contains it) before executing your RunCommand.

HTH

Ted said:
....i forgot to mention that when i disable the buttons and their associated
events from the form, using the file menu 'Delete Record' command from both
the 'Audit Report' form and 'Evaluations' sub-form works as i would expect,
i.e. the parent and child records related to the parent are removed.
Click to expand...
it
was
only when using the button actuated event vba code that this anomaly
materializes. arrrgh :-(

:

Is the IRB table included in the query underlying the subform?
Is the button on the main form?

in my a2k app, i have three tables:

IRB, Evaluations and Patients.

the PKs are as follows:

IRB = IRB Number
Evaluations = IRB Number, Date of Audit
Patients = IRB Number, MedRecNum, Date of Audit

in my relationships window, i have

IRB linked with Evaluations via a 1-to-many relationship on IRB Number
Evaluations linked with Patients via a 1-to-many relationship on IRB
Number
and Date of Audit.

i have established referential integrity and enabled cascade
Click to expand...
delete
and
update and my join type is the default.

i have a main form (parent) called 'Audit Report' that uses the
Click to expand...
IRB
table
and two child/sub-forms on 'Audit Report' called 'Evaluations' and
'Patients'
which use their namesake tables.

i have placed three DELETE buttons on the form called 'Delete IRB',
'Delete
Audit' and 'Delete Patient'.

the code for the 2nd one (audit) is as follows:


Private Sub DeleteAudit_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteAudit_DblClick
If vbOK = MsgBox("Be advised you are about to IRREVERSIBLY DELETE *ALL*
INFORMATION CONCERNING THIS AUDIT DATE (" & Forms![Audit
Report]![Evaluations].Form![Date of Audit] & ") FROM THE DATABASE!",
vbCritical + vbOKCancel + vbDefaultButton2, "SUPER HYPER ULTRA
CRITICAL!!!")
Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If

Exit_DeleteAudit_DblClick:
Me.AllowDeletions = False
Exit Sub

Err_DeleteAudit_DblClick:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteAudit_DblClick

End Sub

to my (newbie) surprise, when i agreed to the 2ndary message
Click to expand...
reminding
me
that 1 record in this table and all related records would be
Click to expand...
deleted,
i
found
that all records from all three tables were now deleted :-( what i had
expected to find was that the record having the Audit Date from the
Evaluations table and all records having the same Audit Date from the
Patients table would be deleted.

can anyone spot the achilees heel in all of this?
Click to expand...
Click to expand...
Click to expand...
 
T

Ted

seems really simple and it's bound to work.

many thanks for all the great input(s)!!

best wishes,

-ted

MacDermott said:
Me.MySubformControl.Form.SetFocus

Put the name of your subform control in place of MySubformControl.

HTH

Ted said:
hi,

ah so, you're saying that somehow the vba should incorporate some command
that sets the focus to the sub-form itself; can you describe what that
command would look like.

-ted

MacDermott said:
If the button is on the main form, then Me refers to the main form, and that
is the context in which the delete is performed, deleting the current record
in the main form, and through the cascade delete, all associated ones.
You could try setting focus to the subform (not just the control that
contains it) before executing your RunCommand.

HTH

....i forgot to mention that when i disable the buttons and their
associated
events from the form, using the file menu 'Delete Record' command from
both
the 'Audit Report' form and 'Evaluations' sub-form works as i would
expect,
i.e. the parent and child records related to the parent are removed. it
was
only when using the button actuated event vba code that this anomaly
materializes. arrrgh :-(

:

Is the IRB table included in the query underlying the subform?
Is the button on the main form?

in my a2k app, i have three tables:

IRB, Evaluations and Patients.

the PKs are as follows:

IRB = IRB Number
Evaluations = IRB Number, Date of Audit
Patients = IRB Number, MedRecNum, Date of Audit

in my relationships window, i have

IRB linked with Evaluations via a 1-to-many relationship on IRB Number
Evaluations linked with Patients via a 1-to-many relationship on IRB
Number
and Date of Audit.

i have established referential integrity and enabled cascade delete
and
update and my join type is the default.

i have a main form (parent) called 'Audit Report' that uses the IRB
table
and two child/sub-forms on 'Audit Report' called 'Evaluations' and
'Patients'
which use their namesake tables.

i have placed three DELETE buttons on the form called 'Delete IRB',
'Delete
Audit' and 'Delete Patient'.

the code for the 2nd one (audit) is as follows:


Private Sub DeleteAudit_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteAudit_DblClick
If vbOK = MsgBox("Be advised you are about to IRREVERSIBLY DELETE
*ALL*
INFORMATION CONCERNING THIS AUDIT DATE (" & Forms![Audit
Report]![Evaluations].Form![Date of Audit] & ") FROM THE DATABASE!",
vbCritical + vbOKCancel + vbDefaultButton2, "SUPER HYPER ULTRA
CRITICAL!!!")
Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If

Exit_DeleteAudit_DblClick:
Me.AllowDeletions = False
Exit Sub

Err_DeleteAudit_DblClick:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteAudit_DblClick

End Sub

to my (newbie) surprise, when i agreed to the 2ndary message reminding
me
that 1 record in this table and all related records would be deleted,
i
found
that all records from all three tables were now deleted :-( what i had
expected to find was that the record having the Audit Date from the
Evaluations table and all records having the same Audit Date from the
Patients table would be deleted.

can anyone spot the achilees heel in all of this?
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

regarding main and sub-form relationships 3
regarding relationships and sub-forms 7
adding new (blank) record to child/sub-form on main form 5
DoCmd.GoToRecord , , acNewRec is not working 8
Appending data from two tables to two tables in Access 2000. 1
Appending Records to Another Table When Certain Values Get Updates 1
sql queries: need identify patient not in a table 1
Insert Into 2 tables from a form & subform 1

Top