Why are Recordsets in Forms Read-Only when there are Unions involved?

[llyal]

Why are Recordsets in Forms Read-Only when there are Unions involved?

Through some tests, I have found that when a form is based on one table, the recordset can be updated through the form; however, when a form is based on a recordset with two or more tables, the form will treat the recordset as read-only;

Is this the best Access can do, or am i missing something? I would like to have a form be based on a recordset with two or more tables AND be able to do updates to this recordset through the form;

What is the how-to?

Thank you!
Llyal

 

[Carol]

Refer to answer in todays posting question "Recordset not updatable"

Good luck

 

[Pat Hartman]

This is not an Access restriction. It applies to all SQL implementations that I have used. In fact, Access has the MOST flexible rules for creating updateable recordsets based on joined tables!

The reason Union queries don't produce updateable recordsets is there is no way to identify which table any particular row came from.

 

[MikeAngelastro]

I don't think Llyal is using a Union query.

 

[MHM]

Some recordsets are read-only by definition - as UNIONs. For expl. all "GROUP BY"-SQLs dont't make sense to be editable, because if you edited data in there, more than one data-field needs to be changed - and you won't know before which one or how many.

Try this:
Use UNION and add any ID-field.
Then do Recordset2.FindFirst "ID=" & Recordset.ID at another Recordset.

 

[llyal]

Just to close this thread; the solution is to set RecordSetType to Dynaset(inconsistent updates), not just Dynaset; this is little documented and i cannot explain why Microsoft created two Dynasets to be used;

I apologize if my wording mislead people; i was creating joins, not unions;