adding 2 fields in a form

Bucky10 · General User Joined: 30 Apr 2008 Posts: 30

I have a form based on a table. Relevant fields are Expense, GST, PST an Total. The first 3 fields are from the table, the Total field is not tied to a table. The first 3 fields are Formatted fields, the Total field is a numeric field.
I have a macro specified in Form Events, When Loading, but the Total field does not show the total.
Thanks for your help
Bart

REM ***** BASIC *****

Sub calcTotal(event As Object)
On Error Goto HandleErr

Dim Form As Object
Dim Ctrl1 As Double
Dim Ctrl2 As Double
Dim Ctrl3 As Double
rem Double
Form=event.Source.getModel().getParent()
Ctrl1=Form.getByName("Expense").Text
Ctrl2=Form.getByName("PST").Text
Ctrl3=Form.getByName("GST").Text
Form.getByName("Total").Text=Ctrl1+Ctrl2+Ctrl3
HandleErr:
Exit Sub
End Sub

marc_ly · General User Joined: 11 Oct 2008 Posts: 5

I threw together a quick database with the most generic of what you were talking about: Expense, PST, GST, Total each as Doubles in the table and then generating a form from the wizard

Then in the form I put this Sub in each box I was entering info into so it would update after each entry.

'#######################################################
'# calcTotal Adding up text fields
'#######################################################
'# This is placed in the When Losing focus event in each
'# numeric box so it would update after i enter each amount

Sub calcTotal(event As Object)

Dim ExpAmt as Double, PSTAmt as Double, GSTAmt as Double, TtlAmt as Double
Dim Form_model
Form_model=Event.Source.Model.Parent

'# This fmt part is an addition Base puts on form boxes
ExpAmt=Form_model.getByName("fmtExpense").getCurrentValue
PSTAmt=Form_model.getByName("fmtPST").getCurrentValue
GSTAmt=Form_model.getByName("fmtGST").getCurrentValue
TtlAmt=ExpAmt+PSTAmt+GSTAmt

Form_model.getByName("fmtTOTAL").BoundField.updateDouble(TtlAmt)
End Sub

It seemed to work perfectly for me. Hope it works for you too.

Marc

MSPhobe · Posted: Sat Oct 11, 2008 2:03 pm Post subject:

Another... simpler?.... way to get a form to display the sum (or other mathematical combination) of the data in some of the other fields displayed on the form is explained at....

http://sheepdogguides.com/fdb/fdb1calcf1.htm

(I hope I am right in inferring that you only want the sum of the figures showing on the form, not some kind of total involving values from multiple RECORDS? (If not, the tutorial mentioned is of No Use!)

Bucky10 · General User Joined: 30 Apr 2008 Posts: 30

Thanks Marc & Msphobe

Marc, your solution is based on control events.
I want the total to show when the form is opened, and each time the user goes to a new record. So I am looking for a form based macro solution.

Msphobe,
the reference features a query. I know how to do the math in a query, but now I am very confused. The author goes on to create a form based on the query and SAYS changes you make to the data "percolate" back to the table. THIS HAS BEEN MY STRUGGLE FROM THE BEGINNING, forms I create based on a query do not allow me to edit the data. Posts to this forum for a solution result in answers like "base your form on a table". But the only way to add two fields with a form based on a table is with a macro.
Msphobe am I going insane?

ooBase designers listen... in my old dos based db program, all I had to do was put "=abc+xzy" into the source of a form control, and bingo!

Bart

RPG · Posted: Sun Oct 12, 2008 8:00 am Post subject:

Hello
Maybe this code is wahat you need. You have to adjust it for an event. Is it not what you need describe it better

MSPhobe · Posted: Mon Oct 13, 2008 7:25 am Post subject:

(Reply to question further up page....)

Are you going insane? Probably... if you are trying to get ANY computer to do ANY job! (I don't think ooBase is particularly dementia-genic, though.)

Thank you for making what you want to do so clear. It seems there OUGHT to be a way to do it. Apologies for not reading your initial post more clearly, nor checking that the "solution" offered was exactly applicable...

I'll have to see if I can do what you want, by a simple means... but it will have to be a challenge for another day... Sorry again.

Bucky10 · General User Joined: 30 Apr 2008 Posts: 30

Thanks Romke
I have tried this code:

RPG · Posted: Sat Oct 18, 2008 3:09 pm Post subject:

Hello

Did you try the others event ?

Romke

Bucky10 · General User Joined: 30 Apr 2008 Posts: 30

I tried When Reloading.
This works when I press the Refresh button, BUT, jumps to the first record of the table.

I tried Before Submitting, After Resetting, & others bu did not get the desired behaviour. I tried Before Record Action which of course only works if I change a record.

In my form, the Total is to be displayed when the form is first opened, and for every record you view. To me, When Loading is the proper event to use (but does not work)

Any advice for me?

Bart
Toronto

RPG · Posted: Sun Oct 19, 2008 8:40 am Post subject:

Hello

you say

Bucky10 · General User Joined: 30 Apr 2008 Posts: 30

Romke, I bow to your wisdom Smile

Your suggestion has worked. I would like to blame (just a little bit) the oo Help, which is not too helpful.

There is a subform in my form, but it only displays one row.
My form content is based on my table Payables

The subform Content Type is Query
Content: Query_Payables
Query_Payables has a column with Field: "GST" + "Expense" + "PST", with Alias "Total"
Link master field is PayableID of the table, Link slave fields is "PayableID" of the query

Anyway, the subform only displays one row, how do I get it to display ALL rows?

Thx
Bart
Toronto

RPG · Posted: Sat Oct 25, 2008 12:13 pm Post subject:

Hello

The sub form can only display those fields who belong to the query or table who is the base of the subform.

If you change the query and add more fields, you can also display more fields.

You can make the query in the grapich query designer and copy the text to the good place.

I hope this helps you

Romke

Bucky10 · General User Joined: 30 Apr 2008 Posts: 30

In my experience with other db front ends, the subform could be based on a different table or query based on a different table, as long as the proper ID filed can be linked.
For instance, a sales order: the form has the name, address etc, and the subform has the order items. A form should be able to show a one-to-many relationship such as this.

Are you sure about this Romke? Then why does the subform have settings for linked field? If the subform is simply a table view of the parent form, why the need to specify any linked fields?

Bart Confused
Toronto

RPG · Posted: Sun Oct 26, 2008 1:55 pm Post subject:

Hello

Bucky10 · General User Joined: 30 Apr 2008 Posts: 30

Thanks Romke
I think we are on the same page.

My form is based on my table: Payables

The subform is based on my query: Query_Payables

The subform correctly displays the query data, BUT, it only displays one row.

Let me explain the use of my subform.
I want the subform to display ALL records of the Query_Payables. If select a record from the subform, I expect that record to be displayed in the main form.

Just like the accounting application we use here at work, it has a table view of records at the bottom of the window, and when I select one of the rows, it is displayed in the top of the window.

Bucky
Toronto