Forms & Subforms

[Guest]

I have a relational database that uses a primary form with two subforms
attached. In testing, I noticed that if the user makes a mistake and begins
to enter something in the subform and then deletes it, an error message pops
up because the record in the table corresponding with that subform must have
reference an entry in its parent table. Since there is not a blank or null
value in the parent table, the form gives me the error. Is there a way to
work around this? I added a Undo Command Button and that works, but it
appears I would have to have a separate one for each subform. Is there a
way to have only one command button that works with all three, the primary
form and the two subforms?

 

[Guest]

There is kind of two issues intertwined....

The error message that is happening is probably due to Referential Integrity
being selected in the Relationship between the Tables. It is a powerful
feature - and can unintentionally really kind of lock out some flexibility
the human animal requires.

You can go to Relationships - select the link - right click - and edit
Relationship Properties by unchecking the Referential Integrity enforcement.

If this is your DB and you control everything then I would try this. On the
otherhand if you have inherited a long lived application - the original
designer may have had very good reasons for the Referential Integrity to be
enforced - and maybe un-doing it is not great advice.

Once you do this - the tables are still linked but the cross reference field
between the tables is now a little more forgiving of errors.....

Your second issue I think is a common button command that will Delete Record
of all three Tables at once..... You may not need this if the first issue
relieves you of the error message which locks you up.....

 

[Guest]

This is a new application, but I would rather keep Referential Integrity
between the tables to take advantage of the Cascade Deletes and Cascade
Updates. I tried an 'Undo' command Button, which works, but acts as if it
requires a separate button for the primary for and each subform. I have
already included a button to clear all fields in an entry, but I would like
to give the user another out. The data entry for the subforms is near the
end of the form and I do not want the user to have to start over if this
little mistake is made. Any ideas about how to get one 'Undo' button to work

 

[Guest]

thinking outloud - but not having actually experimented on this.....can you
string together the multiple 'undo' via a Macro?

have a button trigger the Macro

 

[James A. Fortune]

I have a relational database that uses a primary form with two subforms
attached. In testing, I noticed that if the user makes a mistake and begins
to enter something in the subform and then deletes it, an error message pops
up because the record in the table corresponding with that subform must have
reference an entry in its parent table. Since there is not a blank or null
value in the parent table, the form gives me the error. Is there a way to
work around this? I added a Undo Command Button and that works, but it
appears I would have to have a separate one for each subform. Is there a
way to have only one command button that works with all three, the primary
form and the two subforms?

Comment:

In one of the PDC05 sessions, perhaps TLN303, TLN309 or TLN311, the
speaker mentions that the Undo feature in a particular MS product was
implemented by rolling back nested transactions. In TLN303, MS' new
command shell, called Monad, appears to have the capability of enclosing
multiple shell operations within a transaction. The possibilities seem
quite interesting. I doubt that helps you with your problem though.
BTW, Sharepoint has the ability to take snapshots of data state -- one
of the few features of Sharepoint I find compelling for what I use at
the present. You might be stuck with rolling your own Undo in a command
button. I've done that before, but it's not enjoyable. Perhaps check
out Allen Browne's Audit Tracking app and go from there.

James A. Fortune
(e-mail address removed)