I wasn’t aware I was using a subquery, and if you could point out where I may find it , that would be great.
@Avvy65, It’s the
(
SELECT "Table1Test"., "Cost" * .2 AS "VAT2" FROM "Table1Test"
)
in your FROM clause. Read about them here.
If I understand your question correctly you have created a Form based on the Query you quoted. This Query was the subject of a previous question. The Query uses a calculated Field in another calculated Field and to do this the two step Query you now have is required. You find that you cannot add a new record through either the Query or Form.
I have done some tests. A Query with two calculated Fields both of which use Fields that exist in the Table and do not use one calculated Field in the other. The Query allows the creation of new records. A Form based on this Query gives error “Error writing to database. Input required in field (name of first calculated field). Please enter a value.” Entering any random numbers in both the calculated fields adds the record and the calculated Fields random numbers are replaced by the correct calculated values. This is due to the Fields Data property - Input required - being set to the default Yes. Setting both the calculated Fields to No results in new records being able to be created.
With one of the Fields calculated values using the other calculated value a new record cannot be created, as you found. The Query has the New Record icon greyed out. It appears that this cannot be done.
The way to achieve what you require is not to use one calculated Field in the other. You can create a simple Query with “Cost” * .2 AS “VAT2” and “Cost” + “Delcost” + “Cost” * .2 AS “TotalCost1”. The vat is calculated again in the TotalCost so you are using only fields that are in the Table. So your query would be -
SELECT “ID”, “BuyDate”, “item”, “Quantity”, “OrderID”, “Cost”, “Vat”, “Delcost”, “TotalCost”, “SupplierPhone”, “SupplierEmail”, “Notes”, “SupplierAddress”, “SupplierName”, “EbaySale”, “Cost” * .2 AS “VAT2”, “Cost” + “Delcost” + “Cost” * .2 AS “TotalCost1” FROM “Table1Test”
Also make sure you set the Input Required Data Property for the calculated Fields on the Form to No.
First, I thank you for your patience and understanding.
Setting the Field Data property to No did not make the fields editable , and I checked and checked again field data property was set to No but still not editable.
Thanks for the SQL statement above, which is quite tidy, but I found that for some unknown reason to me that only around a 3rd of the records were updated with a TotalCost1, that is from 351 records only 124 were given a TotalCost1 figure.