HELP :(

[Guest]

I have a form with three subforms in a tab control. I am having trouble with
giving the user a "clear" button that would erase the record. All the
subforms are linked to the parent form through "Material Transfer Number2." I
want the button to be on the parent form and when they hit it, it deletes the
record on the parent as well as the records in the subforms which may have
been entered (which have the same Material Transfer Number2). Thanks for the
help in advance....

 

[John W. Vinson]

I have a form with three subforms in a tab control. I am having trouble with
giving the user a "clear" button that would erase the record. All the
subforms are linked to the parent form through "Material Transfer Number2." I
want the button to be on the parent form and when they hit it, it deletes the
record on the parent as well as the records in the subforms which may have
been entered (which have the same Material Transfer Number2). Thanks for the
help in advance....

A couple of ways to do this. One (a bit dangerous but reasonable if you do it
right) is to define the Relationships between the mainform's table and the
related tables (the ones used in the subforms) to enforce relational integrity
and turn on Cascade Deletes. This will cause all related records in the child
tables to be deleted (without much warning and with no chance of recovery, I
said it was dangerous!) when you delete a record in the main table.

The other is to use a command button to delete the record; have it (with
appropriate warnings) run Delete queries on the child tables, using [Material
Transfer Number2] as a criterion, and then delete the mainform record.


John W. Vinson [MVP]

 

[Guest]

Okay thanks. I think what I am going to do is just have it delete the parent
form entry. When this happens, the child form entry appears in its table, but
since there is no parent record, it does not show up in the reports, queries,
etc.
--
Michael

I have a form with three subforms in a tab control. I am having trouble with
giving the user a "clear" button that would erase the record. All the
subforms are linked to the parent form through "Material Transfer Number2." I
want the button to be on the parent form and when they hit it, it deletes the
record on the parent as well as the records in the subforms which may have
been entered (which have the same Material Transfer Number2). Thanks for the
help in advance....

A couple of ways to do this. One (a bit dangerous but reasonable if you do it
right) is to define the Relationships between the mainform's table and the
related tables (the ones used in the subforms) to enforce relational integrity
and turn on Cascade Deletes. This will cause all related records in the child
tables to be deleted (without much warning and with no chance of recovery, I
said it was dangerous!) when you delete a record in the main table.

The other is to use a command button to delete the record; have it (with
appropriate warnings) run Delete queries on the child tables, using [Material
Transfer Number2] as a criterion, and then delete the mainform record.


John W. Vinson [MVP]

 

[John W. Vinson]

Okay thanks. I think what I am going to do is just have it delete the parent
form entry. When this happens, the child form entry appears in its table, but
since there is no parent record, it does not show up in the reports, queries,
etc.
--

WHOA... I'm afraid this is a Very Bad Idea! You're using a relational
database; one of the underpinnings of the entire relational model is
"referential integrity", which ensures that there are no "orphan" records. I
don't know, of course, what real-life entities your tables represent; but to
use an analogy, if you had a table of Accounts and Transactions, you really
would not want to have records for Transactions stored in the table which
don't apply to any existing account.

It's easy to set up referential integrity with cascade deletes... and this
will solve your problem, and very likely prevent much worse problems in the
future!

John W. Vinson [MVP]

 

[Guest]

Okay you've convinced me. So all I need to do is go into the relationship
window and check enforce referential integrity and then check cascade delete
related fields?

Will you describe to me what this does. In the past, I have had problems
with messing with relationships and just want to make sure it doesn't happen
again. I assume what will happen is when the user hits the clear button on
the form (because they messed up that record and want to start over), it will
delete that parent record and also the child records that have the same
[Material Transfer Number2] assuming there is a relationship between the two
tables and through [Material Transfer Number2]. Thanks again for your help!

 

[John W. Vinson]

Okay you've convinced me. So all I need to do is go into the relationship
window and check enforce referential integrity and then check cascade delete
related fields?

Will you describe to me what this does. In the past, I have had problems
with messing with relationships and just want to make sure it doesn't happen
again. I assume what will happen is when the user hits the clear button on
the form (because they messed up that record and want to start over), it will
delete that parent record and also the child records that have the same
[Material Transfer Number2] assuming there is a relationship between the two
tables and through [Material Transfer Number2]. Thanks again for your help!

Well... it's not quite that simple, unfortunately. If you have a Form bound to
one table (you haven't described your forms or your tables so I'm guessing
here!) and a Subform bound to a related child table, the main table record
will be saved permanently to disk the moment you set focus to the subform;
each subform record will likewise be written out when you move to a new
subform record or back to the mainform. So it's too late to "clear the form" -
the data isn't IN the form anymore, it's actually been saved to disk.

You can *delete* the record (click in the select bar on the left, or use
Records from the menu, or various other options); this will actually go back
out to the stored records in the table and delete them. You do need to be
careful - some inexperienced users will delete a record thinking "I don't want
this account to show up on this report", not realizing that they are
pemanently deleting the record and all related records from that report and
all other possible future reports! But if it's in fact a record that was
entered erroneously, then yes, cascade deletes will let you delete it.

The Clear button (if you program it yourself) can certainly be set up to
delete the record - and to let cascade deletes delete the related records.

John W. Vinson [MVP]

 

[Guest]

Okay, I'm starting to understand, but let me go over this to make sure. First
off, this is how my form is set up. I have one main form that has info
entered into it that all the subforms (which are in datasheet view) will all
share. Thus, whatever subform is filled out (whether its one, two, or all
three...which are all in seperate tabs), this information in the mainform
applies to them. The mainform writes into one table and the subforms all
write into their own tables as well. All the subforms are linked to the main
form through [Material Transfer Number2]. What I want happening is when a
user messes up in the subforms, they can click a button in the mainform that
deletes what they have done in the subform. I couldn't figure out how to do
this, so I was just going to have the button on the mainform delete the
mainform which worked fine as far as my data collection went, but as you
mentioned, left a orphan record. So, if I use this cascading, I shouldn't
really have any problems. Each time the form is brought up [Material Transfer
Number2] is always a new number. The only way that the mainform would be
deleted (thus deleting the records from the subforms) would be if the user
went into the tables and deleted it (which they won't be able to do) or if
the clear button is hit (in this case, I want that record to be deleted b/c
they messed it up). So, I shouldn't run into any problems right???..I guess I
am assuming that the cascading only occurs if the mainform record is deleted
and not vice versa. Thanks you have been great!
--
Michael

Okay you've convinced me. So all I need to do is go into the relationship
window and check enforce referential integrity and then check cascade delete
related fields?

Will you describe to me what this does. In the past, I have had problems
with messing with relationships and just want to make sure it doesn't happen
again. I assume what will happen is when the user hits the clear button on
the form (because they messed up that record and want to start over), it will
delete that parent record and also the child records that have the same
[Material Transfer Number2] assuming there is a relationship between the two
tables and through [Material Transfer Number2]. Thanks again for your help!

Well... it's not quite that simple, unfortunately. If you have a Form bound to
one table (you haven't described your forms or your tables so I'm guessing
here!) and a Subform bound to a related child table, the main table record
will be saved permanently to disk the moment you set focus to the subform;
each subform record will likewise be written out when you move to a new
subform record or back to the mainform. So it's too late to "clear the form" -
the data isn't IN the form anymore, it's actually been saved to disk.

You can *delete* the record (click in the select bar on the left, or use
Records from the menu, or various other options); this will actually go back
out to the stored records in the table and delete them. You do need to be
careful - some inexperienced users will delete a record thinking "I don't want
this account to show up on this report", not realizing that they are
pemanently deleting the record and all related records from that report and
all other possible future reports! But if it's in fact a record that was
entered erroneously, then yes, cascade deletes will let you delete it.

The Clear button (if you program it yourself) can certainly be set up to
delete the record - and to let cascade deletes delete the related records.

John W. Vinson [MVP]

 

[John W. Vinson]

Okay, I'm starting to understand, but let me go over this to make sure. First
off, this is how my form is set up. I have one main form that has info
entered into it that all the subforms (which are in datasheet view) will all
share. Thus, whatever subform is filled out (whether its one, two, or all
three...which are all in seperate tabs), this information in the mainform
applies to them. The mainform writes into one table and the subforms all
write into their own tables as well. All the subforms are linked to the main
form through [Material Transfer Number2].

Do you mean that you are COPYING fields other than [Material transfer number
2] from the mainform into the subforms? If so, you should not be doing so!
What in fact are the Recordsource properties of these forms?

What I want happening is when a
user messes up in the subforms, they can click a button in the mainform that
deletes what they have done in the subform.

If you mess up a subform, why delete the mainform record!? Just delete the
subform record. It's just data in a table; it can be deleted just like any
other record. The button should be on the subform, though - otherwise how
would Access know WHICH subform was messed up?

I couldn't figure out how to do
this, so I was just going to have the button on the mainform delete the
mainform which worked fine as far as my data collection went, but as you
mentioned, left a orphan record. So, if I use this cascading, I shouldn't
really have any problems. Each time the form is brought up [Material Transfer
Number2] is always a new number. The only way that the mainform would be
deleted (thus deleting the records from the subforms) would be if the user
went into the tables and deleted it (which they won't be able to do) or if
the clear button is hit (in this case, I want that record to be deleted b/c
they messed it up). So, I shouldn't run into any problems right???..I guess I
am assuming that the cascading only occurs if the mainform record is deleted
and not vice versa. Thanks you have been great!

You're right about the dirction of cascades. You can safely delete a record
from a child table without affecting the parent table or any of the other
child tables.

John W. Vinson [MVP]

 

[Guest]

See responses below...
--
Michael

Okay, I'm starting to understand, but let me go over this to make sure. First
off, this is how my form is set up. I have one main form that has info
entered into it that all the subforms (which are in datasheet view) will all
share. Thus, whatever subform is filled out (whether its one, two, or all
three...which are all in seperate tabs), this information in the mainform
applies to them. The mainform writes into one table and the subforms all
write into their own tables as well. All the subforms are linked to the main
form through [Material Transfer Number2].

Do you mean that you are COPYING fields other than [Material transfer number
2] from the mainform into the subforms? If so, you should not be doing so!
What in fact are the Recordsource properties of these forms?

I am just linking the mainform with the subforms through [Material Transfer
Number2], nothing else. Why would it matter if I was transferring data
though? Is that just bad with respect to space usage since if the tables have
a relationship, you don't need them in both?

The record source of the main form is a table called tbl_outbound
materialtransfers. For the the 3 subforms, they are tbl_outboundrods,
tbl_outboundtubes, tbl_outboundother, respecively. With this said though, the
user isn't seeing data from old records, they are simply inputting new
records only.

If you mess up a subform, why delete the mainform record!? Just delete the
subform record. It's just data in a table; it can be deleted just like any
other record. The button should be on the subform, though - otherwise how
would Access know WHICH subform was messed up?

Yah, I would like to do that, but since they are subforms in datasheet view,
I don't know how to make the button show up.

I couldn't figure out how to do
this, so I was just going to have the button on the mainform delete the
mainform which worked fine as far as my data collection went, but as you
mentioned, left a orphan record. So, if I use this cascading, I shouldn't
really have any problems. Each time the form is brought up [Material Transfer
Number2] is always a new number. The only way that the mainform would be
deleted (thus deleting the records from the subforms) would be if the user
went into the tables and deleted it (which they won't be able to do) or if
the clear button is hit (in this case, I want that record to be deleted b/c
they messed it up). So, I shouldn't run into any problems right???..I guess I
am assuming that the cascading only occurs if the mainform record is deleted
and not vice versa. Thanks you have been great!

You're right about the dirction of cascades. You can safely delete a record
from a child table without affecting the parent table or any of the other
child tables.

John W. Vinson [MVP]

Thanks again for all your help, you are very good!

 

[John W. Vinson]

Do you mean that you are COPYING fields other than [Material transfer number
2] from the mainform into the subforms? If so, you should not be doing so!
What in fact are the Recordsource properties of these forms?

I am just linking the mainform with the subforms through [Material Transfer
Number2], nothing else. Why would it matter if I was transferring data
though? Is that just bad with respect to space usage since if the tables have
a relationship, you don't need them in both?

If the only field that exists in both tables is [Material transfer number 2]
you should be fine.

Copying data is bad partly because of the space, but much more so because of
the risk to your data integrity. Suppose you copied information from the
parent table into a child table... and then *changed* the data in one table or
the other. It should be "the same" data but now it's different, and no obvious
way to determine which value is correct!

The record source of the main form is a table called tbl_outbound
materialtransfers. For the the 3 subforms, they are tbl_outboundrods,
tbl_outboundtubes, tbl_outboundother, respecively. With this said though, the
user isn't seeing data from old records, they are simply inputting new
records only.

This is possibly ok, but unless the three tables are different in structure,
you may be causing yourself a problem. If you have three identical tables for
rods, tubes and other you would do better to have a field indicating what type
of material you're using. Again, I don't know your business, so I may be off
base here!

Yah, I would like to do that, but since they are subforms in datasheet view,
I don't know how to make the button show up.

You can use Continuous view and make the record look a lot like a datasheet if
you like the appearance. Even with a datasheet you should be able to select
the "selector tool", the grey bar to the left of the first column, and hit the
Delete key.

Thanks again for all your help, you are very good!

Thanks! Glad to be of help.

John W. Vinson [MVP]