Data input form with 3 source tables issues (1 Viewer)

[Chris SL]

Hello

I created a data input form with 2 tables: "tableA" and "tableB"
Both tables have different fields 1_a, 2_a, 3_a .... for tableA and 1_b, 2_b, 3_b .... for tableB
Both tables are linked with field 1: 1_a are unique records, 1_b are multiples, both tables are linked with first link option

Like that it works perfectly, when I open my form, I have all the different fields according with my design, all are empty, and I can input values on each of them

Now I add a third table "tableC" in the form source. tableC is linked with tableA as tableA is linked with tableB
No links between tableB and tableC

Now when I run the form all the fields have datas and I can not remove or modify them.

I can not understand what is the problem.

Anybody can help me ?

Thank you in advance
Chris

 

[CJ_London]

Basic rule is one table per form

sometimes you can use two or more tables but it is likely to make the query unupdateable. it is also an indicator that your tables are not designed properly if all three tables are being updated at the same time

you can look at the data properties of the form and change from dynaset to dynaset inconsistent updates, it sometimes works

usual solution is to use subforms

 

[Chris SL]

Basic rule is one table per form

sometimes you can use two or more tables but it is likely to make the query unupdateable. it is also an indicator that your tables are not designed properly if all three tables are being updated at the same time

you can look at the data properties of the form and change from dynaset to dynaset inconsistent updates, it sometimes works

usual solution is to use subforms

Thanks a lot for your answer.

It looks like that dynaset inconsistent solves my problem.

Anyway, I would like to take the opportunity to understand more.
1 table per form is the rule or use subforms.
Ok, well noted.

Same for query, 1 query for 1 form ?

You said that my issue is an indicator that my tables are nod designed properly, what do you mean ?

Thanks by advance for your explanations

Chris

 

[CJ_London]

Same for query, 1 query for 1 form ?

No - if using a query, then it is the number of tables in the query

Forms should always be based on a query. If only to limit the number of records brought through -once you have a significant amount of data, forms based on tables will have a real drag on performance because all records have to be brought across which takes longer and longer as the number of records increase. With a query you can limit that number by using criteria. Note that filters (and the WHERE parameter in docmd.openform is a filter) work on the recordset brought across.

You said that my issue is an indicator that my tables are nod designed properly, what do you mean ?

It is just an indicator, perhaps the tables should be combined or constructed differently. Without seeing your tables and relationships, can't be any clearer than that.

 

[The_Doc_Man]

It is possible to have queries that join more than two tables. However, it is not recommended for updating.

In your second exhibit in the first post, the two dependent tables are joined to the independent table with one/many relationships. This indicates the possibility that you expect multiple entries with the same addresse_revision (in each of Reevaluations and Revisions_loyers) and there is where things get tricky. In fact, even with ONE dependent table, you can have issues.

Forms are usually meant for working with individual records. So the question is, when you have one "parent" record linked to a table of "child" records, how many child records are involved? (This is a rhetorical question.) Access will perform updates through a bound form when the bound record target is unique and unambiguous. When the record appears ambiguous because more than one combination of joining records contains the same specified key value, the form doesn't know which value to target for its update. And that ambiguity kills the process by making the query non-updateable. Access HATES ambiguity.

The way USUALLY employed for this kind of case is to keep the independent record "pure" - although CJ's suggestion of using a query is good. I have used single-table queries many times for various reasons. What you do to deal with the dependent tables is to put them in sub-forms. Even though they APPEAR in the same form, that isolation caused by using a sub-form control and expressing linkage supported by sub-forms means that the sub-forms, even if ambiguously chosen, don't interfere with the independent records. Note that this moves part of the problem to the sub-forms. If there are multiple records in the sub-form matching the key, it is still possible to be unable to update the child data.

The solution for THAT problem is to remember that Access wants unique targets. If there is a way to force selection of unique child records, that would help. And I want to clarify that this is because you are using a form. If you did an UPDATE query on a 2-way JOIN then there would no problem. All records with that combination would be updated. However, for a 3-way JOIN, the ambiguity problem CAN rear its head again since a poorly specified three-way JOIN can lead to what is called a Cartesian operation. Oh, it is still possible - but Access gets "picky" in such cases.

 

[Chris SL]

thank you for your answer

 

[Pat Hartman]

The problem is that you have two parallel relationships. I'm surprised you are not complaining about "duplicates".

Customer --> OrderHeader --> OrderDetails = a hierichical relationship. You won't have any trouble with a form based on this query. Doesn't mean you should do it but you can.

However using my favorite silly example:
Student --> Subjects
Student --> Pets
these are two parallel relationships. Subjects is related to students and pets is related to students but pets and subjects have nothing to do with each other. In a query that joins these three tables, you end up with a partial Cartesian Product. Subjects are multiplied by Pets. So, if Suzy has 3 subjects and 2 pets and John has 4 subjects and 3 pets, the query will return 5 records (3 * 2) for Suzy and 12 records (4 * 3) for John.