Re: Still not able to UNDO a new record entered

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Dirk Goldgar (dg_at_NOdataSPAMgnostics.com)
Date: 11/02/04 

Date: Mon, 1 Nov 2004 22:37:53 -0500

"midnite oil" <midnite@msn.com> wrote in message
news:%23JtBXlHwEHA.3872@TK2MSFTNGP11.phx.gbl
> 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)

Relevant Pages

  • Re: Still not able to UNDO a new record entered
    ... "child" record that is related to the "parent" record on the main form. ... even if I have moved the focus into the subform. ... >> well as required field in subform. ... >> only exist with a child record. ...
    (microsoft.public.access.formscoding)
  • Re: assuring values
    ... identifies a child record as "belonging" to a parent record. ... Does the subform control specify anything in the Link ... Master/Chiid Fields properties? ...
    (microsoft.public.access.formscoding)
  • check subform for records
    ... I'm trying to sketch together a code to check a subform for records. ... child record is required in the subform before the parent record can ... they've forgotten to enter a child record. ... Dim rst As Recordset ...
    (microsoft.public.access.formscoding)
  • Re: Tables wont link correctly
    ... The subform in connected to the subtable. ... key fieldname, and the parent table's primary key fieldname, respectively. ... child record, the foreign key field in the child record will automatically ... populate with the primary key value from the parent record. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Store data from form in table associated with subform
    ... but how can I store this data in the table associated with my subform? ... I need this information for reference on the subform table to keep the ... you can get to it from any child record by using the ... understand why you would ever want to copy data from the parent record ...
    (microsoft.public.access.forms)