Control Form Saving

[Guest]

Hi
I have 2 tables, TblOrg and TblQty joined in a one to many.
I have a main form "FrmSignin" with a subform "FrmSignIn subform" displaying
related records. From the main form's "Add New Record" how can I not save
the main form until data is entered into the subform. I've looked at
AllowAdditions & Cycle Properties but no success. Today when I tab into the
subform, Access automatically saves the main form. Thanks

 

[Dirk Goldgar]

Hi
I have 2 tables, TblOrg and TblQty joined in a one to many.
I have a main form "FrmSignin" with a subform "FrmSignIn subform"
displaying related records. From the main form's "Add New Record"
how can I not save the main form until data is entered into the
subform. I've looked at AllowAdditions & Cycle Properties but no
success. Today when I tab into the subform, Access automatically
saves the main form. Thanks

If your main form is bound and is "dirty" -- i.e., has been modified but
not yet saved -- you can't keep Access from saving that record when the
focus goes to the subform. This is a mechanism built into Access to
ensure that no "child" record can be created without a "parent". This
makes sense: after all, you can't create a related record unless
there's a record for it to be related to.

Maybe if you'll explain why you want to circumvent this behavior, we can
come up with a workaround.

 

[tina]

AFAIK, you can't. once you enter data into bound controls in the main form,
that data will be saved when you move into the subform. this makes sense,
because the general use of a subform is to display records in a "child"
table that's linked to the "parent" table displayed in the main form. to
preserve referential integrity, you can't create a child record until the
parent record exists.

you might try making the controls on the main form *unbound*, while keeping
the form itself bound to the table. then you could enter data in the unbound
controls on the main form, next enter data in the subform, and then use the
subform's BeforeUpdate event to write the unbound data in the main form into
the underlying table. hopefully that will generate a primary key value in
the parent table, and populate the foreign key field in the subform before
the subform record is actually saved. i've done something somewhat similar,
but using the subform's BeforeInsert event - so i'm not sure how it will
work; you'd have to test it and probably fiddle with it to make it work.

hth

 

[Guest]

Thanks for your response. The problem I'm trying to avoid is when the focus
shifts to the subform, but for whatever reason no data is then entered into
the subform, I end up with a "parent" & no "child" record. Is there a way
when focus shifts to the subform [main form (parent) is already saved at this
point as its bound & dirty] to not allow the user to leave the subform until
data is entered, thus ensuring a "child" ? Hope that makes sense, thanks
again. Nigel

 

[Guest]

Nigel,

You could also set a value in one of the fields in the code and only allow a
save of the record. You could put logic in the subform to not allow a save
of a record if a certain field is blank. I am no means an expert, but I go
back to the other person's question of why wouldn't you want to allow a 1:*
on the parent to child? You could also do a lookup or validation table if it
just a few fields.

Not sure if any of the above helps. I've run into similar issues, but never
the requirement of both main form AND subform requiring data. If it is just
a couple fields in the subform, I think a combo box would meet your needs.

Leslie

 

[Guest]

Thanks for the help. The subform has 4 fields with validation rules & a combo
box, but the issue is once someone tabs from the main form to the sub form &
then does nothing, the main form is saved. I don't think Access should save
the main form until the indicator in the subform shows "data entry" - the
pencil instead of the pointer, until that point there is no chance of an
orphan., or at least make it an option. I'll live with it. Thanks

Nigel,

You could also set a value in one of the fields in the code and only allow a
save of the record. You could put logic in the subform to not allow a save
of a record if a certain field is blank. I am no means an expert, but I go
back to the other person's question of why wouldn't you want to allow a 1:*
on the parent to child? You could also do a lookup or validation table if it
just a few fields.

Not sure if any of the above helps. I've run into similar issues, but never
the requirement of both main form AND subform requiring data. If it is just
a couple fields in the subform, I think a combo box would meet your needs.

Leslie

Thanks for your response. The problem I'm trying to avoid is when the focus
shifts to the subform, but for whatever reason no data is then entered into
the subform, I end up with a "parent" & no "child" record. Is there a way
when focus shifts to the subform [main form (parent) is already saved at this
point as its bound & dirty] to not allow the user to leave the subform until
data is entered, thus ensuring a "child" ? Hope that makes sense, thanks
again. Nigel