Form doesn't show calculated figures from query

[Avvy65]

I realise this may be a silly question, but without me trawling the net for hours, I wanted to ask here.

I am using HSQLDB database, and have a query which has 2 calculated fields, as in 2 x 4 for example, and it works just fine. After I designed the form etc and then added the price of an item and the delivery cost, the vat and total price were not updated or shown on the form.
The fields are VAT and TotalCost, which are updated in the query but not the form. The following SQL works in the query

SELECT “Cost” * .2 AS “VAT1”, “Cost” + “DelCost” + ( “Cost” * .2 ) AS “TOTALCOST1” FROM “tblRecInfo”

[Ratslinger]

@Avvy65 In your previous question I provided two working samples: One to show the calculated fields on the form and a second on how to (using macros is only way) to display & update VAT & TotalCost fields. Was there something missing?

[Ratslinger]

Also, to get current records updated with the calculated data, you would need to preform a one time UPDATE statement to the table. But there is no way to update these table fields with a query. At least not in Base.

[librebel]

Hello Avvy65, please check if your Form is based on the Query that you made.

1) open the "Form Properties" dialog
2) select the tab called "Data",
3) set the field "Content type" to the menuitem "Query",
4) set the field "Content" to the name of your Query.

Now your Form can use the fields from your Query.

[Avvy65]

@ratslinger: Yes I did see those 2 examples, and the 1 without the macro has 2 text boxes to the right with the text greyed out, and I can’t see any reference to them in the properties, unless the fields are duplicated. Also how and why is the text greyed out please.

@lilrebel:Yes the form is based on the Query

[Ratslinger]

Sorry for the confusing additions, deletions. Thought I found a way to do what you wanted through just SQL. But is was not true. While you can display the calculated fields from the query, without macros you cannot update the table with them.

The grayed out text boxes is the Enabled property set to No.

[librebel]

Then perhaps the controls inside your Form are not connected to the proper data fields.

1) Open the properties dialog for your control that should show your VAT1 field,
2) select the tab called "Data",
3) set the field "Data field" to "VAT1"

Simlarly for your TOTALCOST1

[Avvy65]

oops , I do see a reference to the greyed out text boxes: AS VAT1 and AS TotalCost1, Doh!

[Avvy65]

ooops, yes the greyed out fields are referenced in the control properties, Doh!

Yes, I see how it works now! The said 2 calculated fields do not immediately show the required amounts, so I type in any number in the 2 fields, go back a record then forward and the results or figure are there.
@ratslinger: I believe you actually pointed this out to me recently, and this question is now answered.
Thank you once again.

[Avvy65]

This question can now be closed if you wish, as it has been answered now.

[peterwt]

Assuming you have connected the Form to the Query correctly then if you add a new record using the Form the calculated Fields on the Form will not show any data until you save the new record. Click the “Save Record” icon in the Navigation bar (to the right of the “New Record” icon). Alternatively if you navigate to a previous record the new record will be saved. When a record is saved the Query runs again updating the calculated Fields. Until the Query runs again there can be no data shown for a new record in the calculated Fields. Similarly if you edit an existing record changing the Fields used in the calculations the calculated Fields will not update until you save the changes. The same situation occurs if you open the Query and add or edit a record.

[EasyTrieve]

@peterwt, Thanks. This is a very helpful explanation of how this all works.

[Ratslinger]

So, I’m back to your original question. When you create a new field in a query, such as you have with the two calculated fields, it is not associated with any table field no matter what you name it. It cannot update “Vat”, “TotalCost” or anything else. It is an answer to your calculation. Other fields selected may be updatable because they are “as is” from the table.

The generated (calculated) fields can be displayed on a form. They can also, with proper construction, be used to update fields in a table using an UPDATE SQL statement. However I would certainly not recommend this since you need to run the statement over every time information changes.

BTW - to display on form, just make sure control is set to the field name used in the query (Properties → Data).

[Avvy65]

I’m sure I commented earlier today??
Ooops, yes there are references to the 2 greyed out text boxes, Doh!

I was doing some messing around earlier and found that if I entered some random numbers in both the VAT and TotalCost fields, then went back 1 record and then forward again to the last one just entered, the VAT and Totalcost fields are updated correctly.
@ratslinger: I believe you mentioned this in my other question, and now I know what or how it works.
Thank you once again!

[Ratslinger]

@Avvy65 If you wish to Close a question, you can do so by clicking on the X close on the right under your original question.

If this answers your question please click on the (upper left area of answer).

You can of course do this for the previous question also.

Please understand, your questions are always welcome. It would help, however, to be a little more clear on what you are trying to accomplish.