I have a subform based on a query with data from one table. Is working but does not let me edit or add data.
Looking up forums and websites and experimenting I did not find a clear answer to the question of
“is it actually possible to edit data in a form based on a query”
If yes - what setting to change?
Have a look at the query first. If it is possible to add data in query directly the form properties are set wrong for changing data.
A query needs the primary key from every table the query depends on. Only one table could be assigned by an alias. Then it should be possible to add data.
Yes! It is possible.
.
The most common error for beginners was named by @RobertG You have to include the primary key in your query.
.
Another point to note: Some datasources (especially spreadsheets) are read only. And if you didn’t define a primary key for your table, this has the same effect.
Thank you for the clear “Yes it is possible”.
However, I thought I had included the primary key in my SQL by ‘SELECT “SCcalendar”.*’
“SELECT “SCcalendar”.* FROM “SCcalendar”, “SCcfilter” WHERE UPPER ( “SCcalendar”.“SCclocation” ) LIKE UPPER ( “SCcfilter”.“Location” || ‘%’ ) OR UPPER ( “SCcalendar”.“SCctype” ) LIKE UPPER ( “SCcfilter”.“Purpose” || ‘%’ ) OR UPPER ( “SCcalendar”.“SCcstartdate” ) LIKE UPPER ( “SCcfilter”.“StartDate” || ‘%’ ) OR UPPER ( “SCcalendar”.“SCclocation” ) LIKE UPPER ( “SCcfilter”.“Location” || ‘%’ ) AND UPPER ( “SCcalendar”.“SCcstartdate” ) LIKE UPPER ( “SCcfilter”.“StartDate” || ‘%’ )”
Experimenting showed that the problem is the second table SCcfilter. I had followed an example that let me into the wrong direction.
Now I have to find another way to create a form with search fields and editable results!
No. This/your query can be “repaired”. And you should find examples in the guide for Base.
.
Without testing I guess the/one problem is the missing Id of the filter-table. You may try a sub-select like
SELECT SCcalendar.* FROM SCcalendar WHERE UPPER ( SCcalendar.SCclocation) LIKE UPPER ( (SELECT Location FROM SCcfilter LIMIT 1) || ‘%’ )
.
Additional hints:
Please use backticks ` to enclose source-code, as this site will alter your text otherwise. For smaller stuff like SELECT * use single backticks, while my example above is started and ended with a line with three backticks: ```
.
And don’t use “suggest solution” unless you wish to present an solution. Prefer the little speech-bubble (rightmost icon) for comments instead.