Deleting parent record from child sub-form.

[KFox]

I have a main form (frmAdd_Mod) where my client enters details on a customer.
On the next window I show the information entered from the previous window
(all these fields are locked and cannot be edited), along with a sub-form
(sfAdd_Mod_1) where the client can enter additional information about the
customer. If the client decides to delete everything and start over, my
delete button only deletes the information on the current sub-form, how do I
get it to also delete the information on the previous form (frmAdd_Mod)? I
have the two tables in the Relationship window with cascde delete and update
checked, but in this case it's kinda deleting backwards, so figured if I
could delete the "parent" first, the rest would follow, but not sure how to
do that.

TIA!

Kellie

 

[TC]

Use the wizard to add a "delete record" button to the /parent/ form.
Then, if you have established a relationship between the parent and
child tables, and set "Cascade Deletes" to True, deleting the parent
should automagically delete the relevant children.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[KFox via AccessMonster.com]

I don't think I used the correct terminology when I referred to parent/child.
The record I want to delete (which I referred to as the parent) is on another
form, I'll call "form1". This is the "one" side of the one-to-many
relationship. Form1 opens form2, which is the "many" side of the one-to-many
relationship. I want to be able to delete the "many" side on form2 and have
if also delete the "one" side on form1.

Does this make sense? =-\

 

[TC]

I don't think I used the correct terminology when I referred to parent/child.
The record I want to delete (which I referred to as the parent) is on another
form, I'll call "form1". This is the "one" side of the one-to-many
relationship. Form1 opens form2, which is the "many" side of the one-to-many
relationship.

If form1's base table has a 1-to-many relationship with form2's base
table, then those two tables have a parent/child relationship, no? The
"one" side record (or parent), has many corresponding records in the
"many" (child) side table.

I want to be able to delete the "many" side on form2 and have if also
delete the "one" side on form1. Does this make sense? =-\

Not quite as it is written there.

If you delete *one* of the child records in form2, it does not make
sense (and in fact, will not be possible) to delete the correspnding
parent record in form1. Only if you delete *all* the child records (or
the last remaining or only child record) in form2, would it make sense
(and be possible) to delete the corresponding parent record in form1.

I guess you probably mean, "If the user deletes the only or last
remaning child record, can this automatically delete the parent
record?" Yes?

If so, there are various ways to achieve this. One would be, to trap
the delete action in form2, and pre-check to see if that child record
is the only or last remaining one for that parent. If so, the code
could delete the *parent* - which will automatically delete that child
(if the relationships have been defined correctly).

But how would you expect that to look on the screen of form1? Would you
expect form1 to go blank (to reflect the fact that the parent record
has been deleted), or go to the next parent record in the current sort
order, or what?

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[TC]

PS. Throughout the above, when I refer to deleting one or all child
records, I really mean, deleting one or all child records *for the
current parent record*.

TC

 

[KFox via AccessMonster.com]

Yes, I'm referring to deleting the only or last child record. I would like
the parent form to appear blank after the delete function is performed. And
actually, there would be no need to check to see if there are any other child
records. In this particular situation, there would never be more than one.

Can you help me with what code I'd use to do this?

Kellie

 

[TC]

Ok, but you will have to wait for 48 hours. It will take me some coding
& testing, which I do not normally like to do when answering questions


One more question: how do you *know* that there is only one child
record? The fundamental nature of a parent/child structure, is that a
parent record can have many children. If you have a parent/child
structure where a parent can only *ever* have one child, then, the
child table could be deleted, and its data moved to the parent table.

Cheers,
TC (MVP Access)
http://tc2.atspace.com

 

[KFox via AccessMonster.com]

Well, the only reason I need this code is for that instance where the client
goes into the child window by accident. I have a primary key set-up on the
child form and because that primary key gets created as soon as the window
opens, I now need to delete the record-- Cancel doesn't do it. So, my button
on the child form will be labeled "Cancel", but technically it's going to
delete the child (and the parent) so the client can start from scratch.

Kellie

 

[KFox via AccessMonster.com]

Not sure if this helps, but wondering if I couldn't use code that deletes
all the records in both tables (parent and child) where the primary key =
[the value in the primary key field]. That appears to be the common
denominator between both forms. (???)

Kellie

Well, the only reason I need this code is for that instance where the client
goes into the child window by accident. I have a primary key set-up on the
child form and because that primary key gets created as soon as the window
opens, I now need to delete the record-- Cancel doesn't do it. So, my button
on the child form will be labeled "Cancel", but technically it's going to
delete the child (and the parent) so the client can start from scratch.

Kellie

Ok, but you will have to wait for 48 hours. It will take me some coding
& testing, which I do not normally like to do when answering questions

[quoted text clipped - 9 lines]

TC (MVP Access)
http://tc2.atspace.com