deleting records from main and child/sub-forms

[Guest]

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?

 

[Graham Mandeno]

Hi Ted

If this code is attached to a button on your main form, then the record
being deleted will be the current record in your *main* form's recordsource.
This will be a record from the IRB table, so *all* records related to that
IRB number will also be deleted.

Perhaps you could try this code in place of "RunCommand acCmdDeleteRecord":

Me.[Evaluations].Form.Recordset.Delete

or, failing that:

With Me.[Evaluations].Form
.RecordsetClone.Bookmark = .Bookmark
.RecordsetClone.Delete
End With

 

[Guest]

hi graham,

Me.[Evaluations].Form.Recordset.Delete

seems to have worked -- what a charm!

i don't know enough to know why, but i won't look a gift horse in the mouth
's they say.

all the best,

-ted

Hi Ted

If this code is attached to a button on your main form, then the record
being deleted will be the current record in your *main* form's recordsource.
This will be a record from the IRB table, so *all* records related to that
IRB number will also be deleted.

Perhaps you could try this code in place of "RunCommand acCmdDeleteRecord":

Me.[Evaluations].Form.Recordset.Delete

or, failing that:

With Me.[Evaluations].Form
.RecordsetClone.Bookmark = .Bookmark
.RecordsetClone.Delete
End With

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

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?

 

[Guest]

hi graham,

in the interest of symmetry, say i were to create three buttons below each
of their 'Delete' counterpar intended to add a new blank record into the
Audit Report, Evaluations or Patients tables...

i tried using the add record feature from the command button wizard after
extinguishing the add records feature from the Audit Report's property sheet

Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click

Me.AllowAdditions = True

DoCmd.GoToRecord , , acNewRec

Me.AllowAdditions = False

Exit_AddRecord_Click:
Exit Sub

Err_AddRecord_Click:
MsgBox Err.description
Resume Exit_AddRecord_Click

End Sub

so, when i click on the 'Add Record' button, the screen sort of flickers for
half a second and no record's been added. where've i gone wrong this time?

-ted

Hi Ted

If this code is attached to a button on your main form, then the record
being deleted will be the current record in your *main* form's recordsource.
This will be a record from the IRB table, so *all* records related to that
IRB number will also be deleted.

Perhaps you could try this code in place of "RunCommand acCmdDeleteRecord":

Me.[Evaluations].Form.Recordset.Delete

or, failing that:

With Me.[Evaluations].Form
.RecordsetClone.Bookmark = .Bookmark
.RecordsetClone.Delete
End With

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

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?

 

[Graham Mandeno]

Hi Ted

Me.[Evaluations].Form.Recordset.Delete

seems to have worked -- what a charm!

i don't know enough to know why, but i won't look a gift horse in the
mouth
's they say.

I'm glad it fixed the problem! The reason it works is that you are
explicitly deleting the current record from the *subform*, not from the form
that currently has the focus (the main form).

 

[Graham Mandeno]

Hi Ted

Turning Me.AllowAdditions back to False disallows the addition of the new
record you have just gone to. What you need to do is turn AllowAdditions
off in your form's Current event procedure, but only if you are not
currently on a new record:

If Me.AllowAdditions and not Me.NewRecord then Me.AllowAdditions = False

As with acCmdDeleteRecord, your GoToRecord will only work on the form that
currently has the focus. Therefore, to add records to the subforms you must
first set focus to the subform. For example:
Me.[Evaluations].SetFocus
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

hi graham,

in the interest of symmetry, say i were to create three buttons below each
of their 'Delete' counterpar intended to add a new blank record into the
Audit Report, Evaluations or Patients tables...

i tried using the add record feature from the command button wizard after
extinguishing the add records feature from the Audit Report's property
sheet

Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click

Me.AllowAdditions = True

DoCmd.GoToRecord , , acNewRec

Me.AllowAdditions = False

Exit_AddRecord_Click:
Exit Sub

Err_AddRecord_Click:
MsgBox Err.description
Resume Exit_AddRecord_Click

End Sub

so, when i click on the 'Add Record' button, the screen sort of flickers
for
half a second and no record's been added. where've i gone wrong this time?

-ted

Hi Ted

If this code is attached to a button on your main form, then the record
being deleted will be the current record in your *main* form's
recordsource.
This will be a record from the IRB table, so *all* records related to
that
IRB number will also be deleted.

Perhaps you could try this code in place of "RunCommand
acCmdDeleteRecord":

Me.[Evaluations].Form.Recordset.Delete

or, failing that:

With Me.[Evaluations].Form
.RecordsetClone.Bookmark = .Bookmark
.RecordsetClone.Delete
End With

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

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?

 

[Guest]

thanks a million for the helpful explanation, graham.

i haven't had the opportunity to implement it yet, but it's bound to be a
winner.

all the best,

-ted

Hi Ted

Turning Me.AllowAdditions back to False disallows the addition of the new
record you have just gone to. What you need to do is turn AllowAdditions
off in your form's Current event procedure, but only if you are not
currently on a new record:

If Me.AllowAdditions and not Me.NewRecord then Me.AllowAdditions = False

As with acCmdDeleteRecord, your GoToRecord will only work on the form that
currently has the focus. Therefore, to add records to the subforms you must
first set focus to the subform. For example:
Me.[Evaluations].SetFocus
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

hi graham,

in the interest of symmetry, say i were to create three buttons below each
of their 'Delete' counterpar intended to add a new blank record into the
Audit Report, Evaluations or Patients tables...

i tried using the add record feature from the command button wizard after
extinguishing the add records feature from the Audit Report's property
sheet

Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click

Me.AllowAdditions = True

DoCmd.GoToRecord , , acNewRec

Me.AllowAdditions = False

Exit_AddRecord_Click:
Exit Sub

Err_AddRecord_Click:
MsgBox Err.description
Resume Exit_AddRecord_Click

End Sub

so, when i click on the 'Add Record' button, the screen sort of flickers
for
half a second and no record's been added. where've i gone wrong this time?

-ted

Hi Ted

If this code is attached to a button on your main form, then the record
being deleted will be the current record in your *main* form's
recordsource.
This will be a record from the IRB table, so *all* records related to
that
IRB number will also be deleted.

Perhaps you could try this code in place of "RunCommand
acCmdDeleteRecord":

Me.[Evaluations].Form.Recordset.Delete

or, failing that:

With Me.[Evaluations].Form
.RecordsetClone.Bookmark = .Bookmark
.RecordsetClone.Delete
End With

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

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?

 

[Guest]

Hi y'all,

Access 2002 (XP):
I also a similar suggestion, under a different heading, but encounter a
couple of problems, which may occur with your suggestion also. Perhaps you
can give me a different spin on this. right now, I just need to delete a rec
in a subform, from a button on the main form. I also want to identify (and
present) the ID's that the user is about to delete, as you will see from my
code below.

Ultimately, I will need to transfer the deleted rec to a duplicate table to
analyze who and what records are being deleted, for audit purposes. I do
capture the logged in user already; only certain people will be able to
delete when I am done.

1) if I delete one record and then try to delete another, I get 'no current
record'.
Is there some kinda refresh I have to do first. Also, I saw you set a
bookmark, but I didn't see where you reposition the cursor, if that record is
gone.

2) In my particular code, I want to show the user which ParentProjectID,
ProjectID, and Item (the unique lssue number), they are -about- to delete, so
they can confirm. However, with my cursor on Item 2, my code presents Item 1
(all the time). It does not seem to know which record I -am- on.

Code is below.

Thanks.

Private Sub cmdDeleteTRGIssue_Click()
Dim frm As Form
Dim nParentProjectID
Dim nProjectID
Dim nItem
Dim Response

Set frm = Me.[Testing Results Grid].Form
DoCmd.SetWarnings False

If Not frm.NewRecord Then
With frm.RecordsetClone

nParentProjectID = .ParentProjectID
nProjectID = .ProjectId
nItem = .Item

Response = MsgBox("This function will DELETE the CURRENT
ISSUE LINE, " & vbCrLf & _
"AND its associated CYCLE LINES!" &
vbCrLf & vbCrLf & _
"CONFIRM?" & vbCrLf & _
"Parent Project ID: " & nParentProjectID
& ", Project ID: " & nProjectID & ", CURRENT ITEM: " & nItem & vbCrLf &
vbCrLf & _
"Do you want to DELETE THIS ITEM? There
is *NO UNDO*.", vbQuestion + vbYesNo, "Are You Sure?")

If Response = vbYes Then
.Bookmark = frm.Bookmark
.Delete
End If

End With

Else
MsgBox "The Current Issue is a New line or being edited and
cannot be deleted." & vbCrLf & vbCrLf & _
"Please click on the 'row header button' to select the
row for deletion, " & crlf & _
"and then press Delete again.", , "Cannot Delete a New
Line"

End If

Set frm = Nothing
DoCmd.SetWarnings True

End Sub

--
MichaelM
--
MichaelM

Hi Ted

If this code is attached to a button on your main form, then the record
being deleted will be the current record in your *main* form's recordsource.
This will be a record from the IRB table, so *all* records related to that
IRB number will also be deleted.

Perhaps you could try this code in place of "RunCommand acCmdDeleteRecord":

Me.[Evaluations].Form.Recordset.Delete

or, failing that:

With Me.[Evaluations].Form
.RecordsetClone.Bookmark = .Bookmark
.RecordsetClone.Delete
End With

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

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?