Still not able to UNDO a new record entered

[midnite oil]

Hi,

Thought I had the solution earlier this afternoon using DoCmd.RunCommand
acCmdUndo.

Main form has 2 required fields. Subform has 1 required field. All fields
are bound to tables. I have a form_Unload function to trap if user click
[X] to exit form, if yes, message is displayed to inform user to click the
Return Main Menu button to exit instead. If its a new record,
ValidateAllRequired function will validate all required fields entered, if
not, give the user the option to abort or or continue with the entry.

Test Case 1 : On the Main form I entered both required fields, click [X] to
close form. My form_Unload event gets executed and inform me to click
Return Main Menu to exit which I did and I get the message that Subform
required field is missing - do I want to continue or exit - I choose the
exit option. DoCmd.RunCommand acCmdUndo gets executed and removed the
record that has been created in the Main form and all is good.

Test Case 2 : On the Main form I entered both required fields as well as
required field in subform. Remove the required field entry in the subform.
Click [X], message informs me to click Return Main Menu to exit, click
Continue data entry. Enter the required field in subform. Remove one of
the required fields in Main form. Click Return Main Menu button, receives
message that Main form required field is not entered, abort or continue the
data entry. I selected ABORT - DoCmd.RunCommand acCmdUndo gets executed
but it did not Undo the main form record.

I am lost now. I have to control the form such that the parent must only
exist with a child record. Can someone please, please help me with this !

 

[Dirk Goldgar]

Hi,

Thought I had the solution earlier this afternoon using
DoCmd.RunCommand acCmdUndo.

Main form has 2 required fields. Subform has 1 required field. All
fields are bound to tables. I have a form_Unload function to trap if
user click [X] to exit form, if yes, message is displayed to inform
user to click the Return Main Menu button to exit instead. If its a
new record, ValidateAllRequired function will validate all required
fields entered, if not, give the user the option to abort or or
continue with the entry.

Test Case 1 : On the Main form I entered both required fields, click
[X] to close form. My form_Unload event gets executed and inform me
to click Return Main Menu to exit which I did and I get the message
that Subform required field is missing - do I want to continue or
exit - I choose the exit option. DoCmd.RunCommand acCmdUndo gets
executed and removed the record that has been created in the Main
form and all is good.

Test Case 2 : On the Main form I entered both required fields as
well as required field in subform. Remove the required field entry
in the subform. Click [X], message informs me to click Return Main
Menu to exit, click Continue data entry. Enter the required field in
subform. Remove one of the required fields in Main form. Click
Return Main Menu button, receives message that Main form required
field is not entered, abort or continue the data entry. I selected
ABORT - DoCmd.RunCommand acCmdUndo gets executed but it did not Undo
the main form record.

I am lost now. I have to control the form such that the parent must
only exist with a child record. Can someone please, please help me
with this !

It sounds as though your data design is rather unusual, and may be worth
reexamining. I think the problem you are running into comes from the
fact that it is not possible to enter data on a subform without Access
saving the main form's record. That's because Access assumes that the
subform will be used to add a "child" record that is related to the
"parent" record on the main form, and therefore the parent record must
be saved so as to maintain referential integrity. Access saves the
main-form record as soon as the focus is moved to the subform, and there
is no way you can stop it. So once you've entered a record on the
subform, you can't Undo the main form, because its record has already
been saved. You could delete the record, if knew it didn't previously
exist, or you could restore its values from a backup table if it did,
but you can't just "undo" it.

And then there's your requirement that each main-form record *must* have
a child record. There's no way to enforce this through referential
integrity, because a parent record must be saved before a child record
can be created. The best you can do is put some rather elaborate code
into the form, in several different events, to try to keep the user on
the current main record until a subform record has been added to it. I
wrote such code once, as a test, so it can be made to work, though I
wouldn't care to vouch for its reliability.

But why is it that the parent must not exist without a child? Normally,
relationships that are described as "one-to-many" or "one-to-one", such
as would be represented by a main form and subform, are really more
accurately described as "one-to-zero-or-many", or "one-to-zero-or-one".
One table's record is primary and must exist, while the other table's
record is secondary and may or may not exist. If the child record
*must* exist, would its fields be better placed in the parent record
instead?

 

[midnite oil]

Hi Dirk,

Thank you for your response. Your explanations make all the sense now why
my form is not behaving the way I would like it to.

The Access forms were written originally by the client themselves who are
not developers but understands enough Access to create forms for data entry.
I got involved in the middle of this project. The fellow who started this
took the originals and top it up with additional client's requirements.

You are right about Access assuming that the subform to be used to add a
"child" record that is related to the "parent" record on the main form. The
client specifically does not want any parent record to exist if it does not
have any child record(s).

Appreciate your explanations. I was hitting my head against the wall trying
to get the main form record UNDO, thinking surely there must be a way to do
so, even if I have moved the focus into the subform. What I will do now is
to write codes to check if a child exist for the parent, if not, delete the
parent manually.

Once again, big thank you for your help. Now, I can go get some sleep.

ym

Hi,

Thought I had the solution earlier this afternoon using
DoCmd.RunCommand acCmdUndo.

Main form has 2 required fields. Subform has 1 required field. All
fields are bound to tables. I have a form_Unload function to trap if
user click [X] to exit form, if yes, message is displayed to inform
user to click the Return Main Menu button to exit instead. If its a
new record, ValidateAllRequired function will validate all required
fields entered, if not, give the user the option to abort or or
continue with the entry.

Test Case 1 : On the Main form I entered both required fields, click
[X] to close form. My form_Unload event gets executed and inform me
to click Return Main Menu to exit which I did and I get the message
that Subform required field is missing - do I want to continue or
exit - I choose the exit option. DoCmd.RunCommand acCmdUndo gets
executed and removed the record that has been created in the Main
form and all is good.

Test Case 2 : On the Main form I entered both required fields as
well as required field in subform. Remove the required field entry
in the subform. Click [X], message informs me to click Return Main
Menu to exit, click Continue data entry. Enter the required field in
subform. Remove one of the required fields in Main form. Click
Return Main Menu button, receives message that Main form required
field is not entered, abort or continue the data entry. I selected
ABORT - DoCmd.RunCommand acCmdUndo gets executed but it did not Undo
the main form record.

I am lost now. I have to control the form such that the parent must
only exist with a child record. Can someone please, please help me
with this !

It sounds as though your data design is rather unusual, and may be worth
reexamining. I think the problem you are running into comes from the
fact that it is not possible to enter data on a subform without Access
saving the main form's record. That's because Access assumes that the
subform will be used to add a "child" record that is related to the
"parent" record on the main form, and therefore the parent record must
be saved so as to maintain referential integrity. Access saves the
main-form record as soon as the focus is moved to the subform, and there
is no way you can stop it. So once you've entered a record on the
subform, you can't Undo the main form, because its record has already
been saved. You could delete the record, if knew it didn't previously
exist, or you could restore its values from a backup table if it did,
but you can't just "undo" it.

And then there's your requirement that each main-form record *must* have
a child record. There's no way to enforce this through referential
integrity, because a parent record must be saved before a child record
can be created. The best you can do is put some rather elaborate code
into the form, in several different events, to try to keep the user on
the current main record until a subform record has been added to it. I
wrote such code once, as a test, so it can be made to work, though I
wouldn't care to vouch for its reliability.

But why is it that the parent must not exist without a child? Normally,
relationships that are described as "one-to-many" or "one-to-one", such
as would be represented by a main form and subform, are really more
accurately described as "one-to-zero-or-many", or "one-to-zero-or-one".
One table's record is primary and must exist, while the other table's
record is secondary and may or may not exist. If the child record
*must* exist, would its fields be better placed in the parent record
instead?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Hi Dirk,

Thank you for your response. Your explanations make all the sense
now why my form is not behaving the way I would like it to.

Glad to help.

You are right about Access assuming that the subform to be used to
add a "child" record that is related to the "parent" record on the
main form. The client specifically does not want any parent record
to exist if it does not have any child record(s).

One quick and dirty way to make this happen is to use the form's Close
event (or the Unload event, if you want to display a prompt and
possibly cancel the event to give the user a chance to fix the problem
manually) to run a delete query that deletes all parent records that
don't have children.

 

[midnite oil]

Wow..now I am sailing !

Thank you for the suggestion.

ym