Currently I have a form with a table as record source. I want users to be able to set filter condition to form depending on values in linked tables. It would be possible, when I replace the table with a query where fields needed for filtering (3 fields from 3 different tables) are added. I don't want to edit contents of those fields - they are there only so I can set filter property for form - but the recordset must contain them.
In https://www.datanumen.com/blogs/15-r...-access-query/ are listed conditions for MS Access query to be updatable. According p.7 the query is updatable when it consist JOIN's only of same direction (LEFT or RIGHT, INNER ?). According this when I use several LEFT JOIN's it must be updatable?
I tested this:
SELECT t1.*, t2.FilterField1
FROM Table1 t1 LEFT JOIN Table2 t2 ON t2.PKField2 = t1.FKField2
--- is updatable (when I run query in query designer mode, I can edit querytable values).
SELECT t1.*, t2.FilterField1
FROM (Table1 t1 LEFT JOIN Table2 t2 ON t2.PKField = t1.FKField) LEFT JOIN Table3 t3 ON t3.PKField3 = t1.FKField3
--- is not updatable (I can't edit querytable values)!
Why? Is there a left-out condition that only one JOIN is allowed - then whole p.7 is moot in link above? Or Access sees <...FROM (Table1 t1 LEFT JOIN Table2 t2 ON t2.PKField = t1.FKField)...> as subquery (p.6 in link above - what makes p.7 moot again, as brackets are a must with several JOIN's in Access), Is there a way around this in Access? (I'm really annoyed with MS Access SQL Query syntax )
When there is no workaround in Access, then as my database uses SQL Server DB as BE, there is another possible solution - to use a SQL Server view instead of table as form's recordsource. SQL Server has less constrictions, but essential is, that only values from one table may be updated. I'm content with this, but before I'm changing my design, can someone clear for me, how will SQL Server and Access handle the case, where those 3 fields are present in form's recordsource, but never really updated from form's point of view (there even will not be any controls linked to them). When the record is updated, do those fields count as updated for SQL Server or not (i.e. are other fields of record updated when I have those 3 fields present in recordsource)?