calculate a field

[windycity007]

Hello everyone,

I know this topic has been covered in previous threads but I still cannot figure out the answer. (And yes, I am a newbie to base).

I have two fields (Salary Husband and Salary Wife) that I am trying to add together (Total Salary) in a form. I am able to do this in a query very easy. But I want the field itself to be calculated in the form.

I read one of sheepdog's guides on this very subject in which he suggests creating a query first for a calculated field and then creating a form from that query. I must have done something wrong because the query works just fine for adding new data (with the total salary field being calculated). However, the form itself will not update when I add data to Salary Husband and Salary Wife. In fact, it gives me an error saying:

Error Writing Data to Datbase
Input Required in Total Salary field. Please enter a value.

Again, the query itself works just fine and does the calculation. But I need it to work in the form.

I would prefer at all costs to avoid Macros if at all possible. I know nothing about macros and have spent hours trying to learn about macros for this problem but I dont understand it. If the only solution requires a macro, I would greatly appreciate if someone could give a step by step breakdown of how to add a macro to the particular field.

Thanks for reading. I sincerely appeciate you taking the time to consider helping me with this rookie with a problem!

[mobi-doc]

In order to be able to edit or enter new data to your form, the form must be based on the table itself, not on the query, so you'll need some code:

Base your form on the table and put a field control named "total_salary" on the form but not bind it to any database field.

Then put the following function in the form

[windycity007]

Thank you sincerely for taking the time and offering your assistance. I really appreciate it.

Conceptually I think I understand what to do. I tried to implement the solution but have a few questions:

1.) When I try to assign that code to the After Record Action" and "After Record Change" events, it only lets me select macros that have been saved. Very elementary question -but for the life of me I cannot figure out how to save that new code to macro library. I clicked on tools--->macros. But I do not see how to save a new macro?

2.) You stated, "Substitude the x and y values according to the field number of Salary Husband and Salary Wife." What is the "field number" that you are referring to? Where do I find it? Is that the same thing as the field name??

3.) Once I figure out 1) and 2.) above and I am ready to add the Macro, do I add it to the form or to the Total Salary control?

Thanks again for your help. It's really appreciated.

[mobi-doc]

[windycity007]

Thanks again for your help. It's incredibly generous of you and I really do appreciate it.

Well, the good news is that I was able to save the new macro. The bad news is that no calculation is being performed. Here is EXACTLY what the macro is saved as:

Function calcTotal as Boolean
Dim total,oForm
oForm=ThisComponent.DrawPage.Forms(0)
calcTotal=TRUE
total=oForm.getInt(2)+oForm.getInt(3)
On Local Error Goto EH
oForm.getByName("Total Salary").Text=format(total,"####")
On Local Error Goto 0
Exit Function
EH: Resume
End Function


(there is no text before or after the above macro. it is saved exactly as it appears above.)

I tried adding an under score to total salary (e.g. total_salary) even though the label does not have an underscore in it but that didnt work either.

My table consists of 4 fields in the following order as they appear in the table:
ID (Integer)
Salary Husband (Integer)
Salary Wife (Integer)
Total Salary (Integer) (I have not entered any data in this field. its blank)

Table Name: "Salaries"

In the form (Form Name: "Salaries Form"), I was able to add the macro to the events, but no calculation is being performed.

You also mentioned "Base your form on the table and put a field control named "total_salary" on the form but not bind it to any database field. "

I clicked on the Total Salary control on the form and and on the Data tab-->Data Field says "Total Salary". I deleted that so now the Data Field is blank. Not sure that is what you meant by not binding it to a database field? But I tried the macro with and without that field blank and nothing happened.

If you're patience has not run out, please help

[mobi-doc]

the call getByName("Total Salary") is an example. Your form must have a field control named "anything_you_like", and then substitude this "anything_you_like" as a parameter to getByName function.

Then you must enter data on the other fields of the form and commit them in order the Events to get triggered. You commit the data by pushing the diskette button in the navigation toolbar.

[windycity007]

I wasn't very clear. I named the field "Total Salary" (just like you have it in your example.) So the macro will have the same GetByName call as your example since I named it the same.

The calculation still doesnt work. I went into the macro editor and clicked on the little Play button (didnt realize this was there before). Once I click on the Play button, it highlights the line "total=oForm.getInt(2)+oForm.getInt(3)" and says an error has occurred:

BASIC Runtime Error.
An exception has occurred
Type:com.sun.star.sdbc.SQLException
Message: Function Sequence Error

Is there something wrong with that particular line of code that it highlighted?

[mobi-doc]

Now I get it, and found the solution too!
There are times where the calculation it produces error, so the line "On Local Error Goto EH" must up to include the line of error too and change to On Local Error Resume Next. Also remove the EH: label and the Resume line next to it.

[windycity007]

I have never coded in my life so this is a bit over my head

Here is the code I am using for the macro:

Function calcTotal as Boolean
Dim total,oForm
oForm=ThisComponent.DrawPage.Forms(0)
calcTotal=TRUE
total=oForm.getInt(2)+oForm.getInt(3)
On Local Error Goto EH
oForm.getByName("Total Salary").Text=format(total,"####")
On Local Error Goto 0
Exit Function
EH: Resume
End Function


What exactly do I need to change? If its really easy, please just paste the correct code because I wasnt quite sure what you were referring to.

[mobi-doc]

try

[windycity007]

I copied that code exactly into the events and nothing is happening. there are no calculations being performed. any ideas? how do i know if the Total Salary field is not tied to database? (you mentioned it shouldnt be). if you have any other possible reasons please let me know. thanks .....

[mobi-doc]

I give up. The code works for me though.
Try to learn some basic with BASIC and OOo and make your experiments.

[windycity007]

last question:

the line of code "oForm=ThisComponent.DrawPage.Forms(0)" ... am i supposed to replace the "(0)" part with something? or is supposed to be (0)?

thanks for all your help and patience. it was very generous and nice of you.

[QuazzieEvil]

Possibly not. the Forms variable is a collection of forms in your form document. if you only have one form then you can leave it as 0, else use the respective index.

Open your form document in design view, and click on the navigator button in the form design tool bar. this will show you a tree with your forms and their controls.