data entry in subform before mainform causes huge hassle, HELP!

[DawnTreader]

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.

 

[John W. Vinson]

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

 

[DawnTreader]

Hello John

i have found a way around it, i cause a new record when the user sets the
focus to the subform. but this is a hack and slash fix.

what i want is for the subform to be the first place the user enters data. i
need a solution that doesnt involve a lot of work arounds.

is there a way to capture the new record for the main form and insert it
back to the subform?