Ask Your Question

Form doesn't show calculated figures from query

asked 2017-05-12 21:49:05 +0200

Avvy65 gravatar image

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"

edit retag flag offensive close merge delete


@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 gravatar imageRatslinger ( 2017-05-12 21:57:10 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2017-05-12 22:21:55 +0200 )edit

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.

librebel gravatar imagelibrebel ( 2017-05-12 23:36:00 +0200 )edit

@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

Avvy65 gravatar imageAvvy65 ( 2017-05-13 00:31:43 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2017-05-13 03:17:51 +0200 )edit

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

librebel gravatar imagelibrebel ( 2017-05-13 04:25:54 +0200 )edit

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

Avvy65 gravatar imageAvvy65 ( 2017-05-13 10:03:05 +0200 )edit

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 gravatar imageAvvy65 ( 2017-05-13 11:57:15 +0200 )edit

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

Avvy65 gravatar imageAvvy65 ( 2017-05-13 18:34:18 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-05-13 11:45:08 +0200

peterwt gravatar image

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.

edit flag offensive delete link more


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

EasyTrieve gravatar imageEasyTrieve ( 2017-05-14 17:33:34 +0200 )edit

answered 2017-05-13 06:12:32 +0200

Ratslinger gravatar image

updated 2017-05-13 06:16:16 +0200

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).

edit flag offensive delete link more


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!

Avvy65 gravatar imageAvvy65 ( 2017-05-13 18:32:25 +0200 )edit

@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.

Ratslinger gravatar imageRatslinger ( 2017-05-13 19:04:57 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-05-12 21:49:05 +0200

Seen: 154 times

Last updated: May 13 '17