Computation on Base Form

[senior]

Hi
I would like to compute new value on form using values typed to numecic fields from keyboard. Computed value is described on another numecic field. I wish to save after that all values from the form into associated table. But in new record in the table are only values which are taped from the keyboard. The result is the same, if for saving is used icon in form navigator, button with saving action or macro. Used macro for this action is this:
REM ***** BASIC *****
REM Computation with values described in a form
REM A nuber is taped from keyboard into field fNumA
Sub ComputeInsideForm
dim oForm as object
dim oShape as object
dim oName as string
dim vNumA as single, vNumB as single
dim i as integer, iA as integer, iB as integer

oForm = ThisComponent.getDrawPage()
For i = 0 To oForm.getCount() - 1
oShape = oForm.getByIndex(i)
oName = oShape.control.name REM Control names in oForm
select case oName
case "fNumA" REM Name of the numeric field
vNumA = oShape.control.value
iA = i REM Index of this field
case "fNumB"
iB = i REM Index of fNumB field
end select
next
oShape = oForm.getByIndex(iB) REM Select the field fNumB
oShape.control.value = vNumA * 2 REM Describe result in fNumB
REM Up to this it works. Next statements do not.
REM dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
REM dispatcher.executeDispatch(Document, ".uno:RecSave", "", 0, Array())
REM dispatcher.executeDispatch(document, ".uno:NewRecord", "", 0, Array())
end sub
Does somebody knows how to do it?

[Villeroy]

Demo how to get calculated from stored values http://user.services.openoffice.org/en/ ... hp?id=1266
I can not think of any case where storage of calculated values is required since you can calculate those values from other values at any time.

[senior]

Many thanks for quick reply. But mentioned example (file.php?id=1266 - cubois) does not solve my requirement. Field volume is not a field of the table and if it is added to the table, it does not obtain computed value. So the efect in this example is the same as in my macro: new value is only described in the form and it is not shift into table. I try also to use SQL statement INSERT, but BASE does not accepted it.

[Ant]

I'm trying to do a similar thing, but I want the computation of 2 fields to be put into a 3rd field in the same record.
e.g. field1VAL * field2VAL = field3VAL

I want to do this because all 3 fields are used on a Report that adds records together (using Accumulate).

I can update a record using SQL.

Code: Select all

strSQL = "UPDATE " + Chr(34) + tblNAME + Chr(34) + " SET " + Chr(34) + fldNAME + Chr(34) + " = " + CvaMarginAmount + " WHERE ID = " + IDno
'rem e.g. strSQL="UPDATE Variations SET CVAmarginAmt = 100 WHERE ID = 29"
Statement=Conn.createStatement()
Statement.executeUpdate(strSQL)

The Chr(34) is to embed quote marks.

I can also get data from records:

Code: Select all

Statement = Connection.createStatement()
ResultSet = Statement.executeQuery("SELECT CVAamt FROM Variations WHERE ID =  " + IDno)
'rem show results
If Not IsNull(ResultSet) Then
ResultSet.next()
CvaAmount = CSng(ResultSet.getString(1))
End If

Strangely this didn't require Chr(34)s. As I'm only getting one value I don't bother stepping through ResultSet.

My problem is identifying the actual record from within the form I want to trigger this code from (i.e. what is IDno?)
Presumably the form knows which record it is looking at, but how do I get that into my Basic Macro?

[Villeroy]

Have a look at "Form1" in the attached file.

[Ant]

Your solution creates the value, but I also want to save that value back to a table record.
Basically I want the computer to calculate a value for a new record entry.
I need to do this so I can have a total (using accumulate) in a report.

[Villeroy]

Your solution creates the value, but I also want to save that value back to a table record.
Basically I want the computer to calculate a value for a new record entry.
I need to do this so I can have a total (using accumulate) in a report.

My form simply stores input in a database. This way I preserve all the information, keep it editable and reserve the ability to perform other calculations based on the same input data. The results will be traceable.
If you really want a calculator to store final results only, write your own program (macros).

I need to do this so I can have a total (using accumulate) in a report.

For running totals (sum of precedents) google for "Accumulate +SQL".
This looks quite promising: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52965

I'd prefer "poor-man's-report" with a sorted query, dumped into a spreadsheet's import range and adjacent formulas for the accumulation. This is what spreadsheets can do more easily with database data than with spreadsheet data since the import range is always sorted while having clear dimensions to insert and remove adjacent formulas automatically.

[Ant]

If you really want a calculator to store final results only, write your own program (macros).

I'm trying to do this. I've found some code that can read and write from fields in a form, except none will write to a field that uses data from a record.

Infact I have found 2 methods using completely different code.

One uses

Form=Event.Source.Model.Parent

the other uses

oThisPage = ThisComponent.getDrawPage()

The first can read field data but not write to a field, (it falls over on

Form.Columns.GetByName("formfieldname").updateDouble(NEWVALUE)

), the second can do both but has to step through all field names to find the right field.

There is very little information available on oOO Basic. Some things work, some don't, which makes me think it is a little flaky.

Am I wasting my time?

[Villeroy]

It has absolutely nothing to do with Basic. 95% of that language is documented in the F1-help. Basic is just an old-fashioned extra language to embed simple batches of API-calls into office documents more easily.
You would face exactly the same problems in any other language (C++, Java, Python, JavaScript and others). You've got to use the API reference and a tool to inspect UNO objects in order to access the right objects, properties and methods for your task.
Download the OOo-SDK[*] plus the MRI extension or XRay and you are on the right track, IMHO. Plus one useful goody for the firefox broser. https://addons.mozilla.org/de/firefox/addon/4102

[*] the SDK is needed for compiled languages, if you want some advanced examples or if you just want the official html-reference on your disk. For instance, XRay can refer to the locally stored reference.