RPG wrote:This code is working for me.
Hi. I am 90% sure my syntax is not the problem (for my example below I have made the CASEWHEN line very very simple).
I have attached a file that I think demonstrates the problems I am experiencing.
It's a very simple database, with two tables are related by a common field (CustomerID). I have made two queries, one using non direct SQL mode(Query_NoDirSQL), and the other using direct SQL mode (Query_DirSQL). Query_DirSQL uses CASEWHEN, and you will notice that if you try to change the SQL mode of this query and then either run/save it you will get an error.
There is also 5 forms whose names indicate their type:
1)
Form1-Tb+Tb - a form from table plus subform from table.
Very simple.
2)
Form2-Tb+Qry_NoDirSQL - a form from table plus subform from the Query_NoDirSQL.
Again very simple and all works as it should. The query part of the form can be used to edit data in the table the query is based up fine, with the expected exception of the
"InvoiceAddresseeName" field as it is a construct of the query (FirstName and LastName fields concatenated).
3)
Form3-Tb+Qry_DirSQL - a form a table and subform from the Query_DirSQL.
Here is where trouble strikes. The subform part now does not change records as the main form changes and is completely un-editable.
4)
Form4-Qry_DirSQL - a form bases on the Query_DirSQL (no subform).
This one is quite interesting as in this case the form allows for the table it is based upon to be edited (except of course in the constructed fields which have no corresponding field in the table).
5)
Form5-Qry_DirSQL+Tb - a form based on the Query_DirSQL plus a subform based on a table.
Again similar problems to before. The main form cannot be used edit data in the table it is based upon.
So it seems that once a form is itself based on a direct SQL mode query or has any subforms based on a direct SQL mode query that the form fails to function in terms of either being able to navigate records and the two parts of the form change record according to their linkage, or being able to edit the fields of the part of the form based on the direct mode SQL query. The exception to this was Form (4) where the form was solely based on a direct SQL mode query.
What am I aiming to achieve is to create a form which can:
1) display elements of two separate tables (all of which can be edited and the data saved back to the tables).
2) perform calculations on numbers from one of the tables for display purposes on the form (these do
not have to be edited in any way)
3) perform string manipulation on some fields of the other table for display purposes (would be nice if these fields could be edited because this form is used to print/export a copy for the customer, but obviously this editing does not imply any data being written back to any table).
bobban