Prev: Combo boxes like an option group?
Next: jumping data
From: DawnTreader on 16 Apr 2010 11:39
Hello all

i have an application where there are forms which have subforms where it is
entirely possible for the user to start entering data in the subform before
the main form. this is proving to be a real pain.

as soon as a user enters something in the subform and then moves from it to
the main form and types (cause the on dirty state), the entered data in the
subform is saved and disappears. basically because the link between the
mainform and the subform is an autonumber field on the main form, which
doesnt have a value until the user types in the main form, the main form
doesnt get a record and the subform has nothing to put in the field that
links it to the mainform.

this causes orphan records in the subforms table. this is not a good
situation because the user has to retype the record that they had in the
subform after wondering where the record went.

how do i stop this from happening? i have tried placing code on the form to
put a value in the mainform on enter of the subform, this doesnt help and
makes using the combo boxes in the subform to chose something an exercise in
frustration.

i have been working on this app for more than 2 years and just found this
problem today. you would think that i would be getting complaints from the
users on a regular basis.
--
As always, any and all help appreciated! :)
From: John W. Vinson on 16 Apr 2010 22:46
On Fri, 16 Apr 2010 08:39:01 -0700, DawnTreader
<DawnTreader(a)discussions.microsoft.com> wrote:

>Hello all
>
>i have an application where there are forms which have subforms where it is
>entirely possible for the user to start entering data in the subform before
>the main form. this is proving to be a real pain.

I can imagine. Since the only link from the subform's table to the mainform's
table is the foreign key field, and it's not being filled with the
Master/Child Link Field, you would indeed lose data.

>as soon as a user enters something in the subform and then moves from it to
>the main form and types (cause the on dirty state), the entered data in the
>subform is saved and disappears. basically because the link between the
>mainform and the subform is an autonumber field on the main form, which
>doesnt have a value until the user types in the main form, the main form
>doesnt get a record and the subform has nothing to put in the field that
>links it to the mainform.
>
>this causes orphan records in the subforms table. this is not a good
>situation because the user has to retype the record that they had in the
>subform after wondering where the record went.

It's worse than you think! You now have BOTH records in the child table, one
with a foreign key link to the mainform, one with a null foreign key.

>how do i stop this from happening? i have tried placing code on the form to
>put a value in the mainform on enter of the subform, this doesnt help and
>makes using the combo boxes in the subform to chose something an exercise in
>frustration.

I'd suggest having the subform's Enabled property set to No in form design;
set it to Yes in the mainform's On Dirty event, and also put code in the
mainform's current event to enable the subform if there is a mainform record:

Private Sub Form_Current()
Me!subformname.Enabled = Not Me.NewRecord
End Sub

--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Combo boxes like an option group?
Next: jumping data