OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

calculate a field

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
windycity007
General User
General User


Joined: 18 Nov 2007
Posts: 10

PostPosted: Fri Nov 23, 2007 9:50 am    Post subject: calculate a field Reply with quote

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!
Back to top
View user's profile Send private message
mobi-doc
OOo Enthusiast
OOo Enthusiast


Joined: 30 Jan 2007
Posts: 130
Location: Thessaloniki

PostPosted: Fri Nov 23, 2007 1:18 pm    Post subject: Reply with quote

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
Code:
Function calcTotal as Boolean
Dim total,oForm
        oForm=ThisComponent.DrawPage.Forms(0)
   calcTotal=TRUE
   total=oForm.getInt(x)+oForm.getInt(y)
   On Local Error Goto EH
      oForm.getByName("total_salary").Text=format(total,"####")
   On Local Error Goto 0
   Exit Function
EH: Resume
End Function


Substitude the x and y values according to the field number of Salary Husband and Salary Wife.

Now go to the properties window of the root form and assign the above function to the "After Record Action" and "After Record Change" events.

I hope it will work. It does for me.
Back to top
View user's profile Send private message Send e-mail Visit poster's website
windycity007
General User
General User


Joined: 18 Nov 2007
Posts: 10

PostPosted: Fri Nov 23, 2007 2:33 pm    Post subject: calculate a field Reply with quote

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.
Back to top
View user's profile Send private message
mobi-doc
OOo Enthusiast
OOo Enthusiast


Joined: 30 Jan 2007
Posts: 130
Location: Thessaloniki

PostPosted: Fri Nov 23, 2007 11:42 pm    Post subject: Re: calculate a field Reply with quote

windycity007 wrote:
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?

Tools/Macros/Organize macros/OpenOffice.org Basic
Then select your document/standard and click new
it will create a new module "Module1" and open an edit window. There you can add your macro.
Quote:
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??

According to the table or query your form is based on, the first field in order of the definition of table or query is 1, the second is 2 etc.
Quote:
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?

The macros ara belonging to the form. Controls can not have macros (is there a Paradox background here?). The controls can bound to events. In your case you bound this macro to the form events. Right click any control in your form and choose Form to open the form properties. The third tab is Events, there you can find the events "After Record Change" and "After Record Action".
Back to top
View user's profile Send private message Send e-mail Visit poster's website
windycity007
General User
General User


Joined: 18 Nov 2007
Posts: 10

PostPosted: Sat Nov 24, 2007 9:36 am    Post subject: Reply with quote

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 Sad
Back to top
View user's profile Send private message
mobi-doc
OOo Enthusiast
OOo Enthusiast


Joined: 30 Jan 2007
Posts: 130
Location: Thessaloniki

PostPosted: Sat Nov 24, 2007 10:42 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message Send e-mail Visit poster's website
windycity007
General User
General User


Joined: 18 Nov 2007
Posts: 10

PostPosted: Sat Nov 24, 2007 11:41 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
mobi-doc
OOo Enthusiast
OOo Enthusiast


Joined: 30 Jan 2007
Posts: 130
Location: Thessaloniki

PostPosted: Sat Nov 24, 2007 1:18 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message Send e-mail Visit poster's website
windycity007
General User
General User


Joined: 18 Nov 2007
Posts: 10

PostPosted: Sat Nov 24, 2007 2:01 pm    Post subject: Reply with quote

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

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. Sad
Back to top
View user's profile Send private message
mobi-doc
OOo Enthusiast
OOo Enthusiast


Joined: 30 Jan 2007
Posts: 130
Location: Thessaloniki

PostPosted: Sat Nov 24, 2007 2:45 pm    Post subject: Reply with quote

try
Code:
Function calcTotal as Boolean
Dim total,oForm
oForm=ThisComponent.DrawPage.Forms(0)
calcTotal=TRUE
On Local Error Resume Next
total=oForm.getInt(2)+oForm.getInt(3)
oForm.getByName("Total Salary").Text=format(total,"####")
On Local Error Goto 0
End Function
Back to top
View user's profile Send private message Send e-mail Visit poster's website
windycity007
General User
General User


Joined: 18 Nov 2007
Posts: 10

PostPosted: Sun Nov 25, 2007 8:10 am    Post subject: Reply with quote

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 .....
Back to top
View user's profile Send private message
mobi-doc
OOo Enthusiast
OOo Enthusiast


Joined: 30 Jan 2007
Posts: 130
Location: Thessaloniki

PostPosted: Mon Nov 26, 2007 7:54 am    Post subject: Reply with quote

I give up. The code works for me though.
Try to learn some basic with BASIC and OOo and make your experiments.
Back to top
View user's profile Send private message Send e-mail Visit poster's website
windycity007
General User
General User


Joined: 18 Nov 2007
Posts: 10

PostPosted: Mon Nov 26, 2007 8:24 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
QuazzieEvil
Super User
Super User


Joined: 17 Jan 2007
Posts: 599
Location: Houston, TX

PostPosted: Mon Nov 26, 2007 9:03 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group