Form vs sub-Form for data integrity

[johngalt47]

I was reading somewhere that it is not good to update two tables from a single form. Something to do with data integrity. On the other hand it seems to me that using an update query for the two tables is exactly what is required to populate the form.

I'm confused...again....

 

[sneuberg]

The form is populated automatically when it is bound to a table or query via the forms record source. The tables that are bound to the form are updated whenever the current record of the form is changed, e.g., when you move to another record, move focus to a subform or close the form. If you have two tables that are joined in a query and use that query as a record source for the form the tables aren't always updated nicely. If fact I'd say Access gets rather rude with obnoxious and unclear error messages about key values. It best just to avoid this hassle and stick to one table per form. Use subforms within the form for related tables.

 

[CJ_London]

the rule is one table one form for editable forms - a subform is treated as a separate form.

The main form would have the parent table as its recordsource and the subform form the child table.

the main form is usually a single form, the subform usually a datasheet of continuous form.

You can have the main form as a continuous form, but if so, the subform needs to be located in the main form footer.

If the form is for viewing data only (no adds or edits) then you can have multiple tables for its recordsource - effectively you are creating a report.

The reason why a multi table form is not editable is because for new entries you would need to update both sides of a relationship and usually these fields are not completed by the user. You might be able to change the form recordset type to 'dynaset inconstant updates' to overcome this, but be aware you will probably need additional code to maintain relationships. Also be aware that, depending on your tables and what the form does, a change in one record could affect other records