Code: Select all
Sub DoCalculationsBeforeSaving(oEvent)
' The need was to have the user enter some values
' which are then used in calculations, the results
' of the calculations being stored in a table.
' The Table must be linked to the form.
' These are "system" variables
Dim thisForm
Dim thisControl
Dim sStatement
Dim sSQL As String
' These are the variables for controls whose values must
' be saved in the table.
dim throughVar1 as string
dim throughVar2 as single
' These are the variables for the "input" values.
dim inVar1 as integer
dim inVar2 as integer
' These are the variables which are the result of your calculations.
dim outVar1 as single
dim outVar2 as single
' Get the entered values
' Replace FormName with the name of your form
thisForm = ThisComponent.DrawPage.Forms("FormName")
' These are the variables whose values are stored in the table
' Var1 is a textbox. Take note of the .text to get its value
' Var2 is a Numerical Field. Take note of the .value to get
' its value
' Of course, use the names of your controls, using names
' which are more meaningful
thisControl = thisForm.getByName("txtThroughVar1")
throughVar1 = thisControl.text
thisControl = thisForm.getByName("numThroughVar2")
throughVar2 = thisControl.value
' These are the 2 numbers to be used in the calculations.
thisControl = thisForm.getByName("numInVar1")
inVar1 = thisControl.value
thisControl = thisForm.getByName("numInVar2")
inVar2 = thisControl.value
' You can do checks here for incorrect input values
' What you do will depend on your needs.
if throughVar1 = "" then
MsgBox "You must enter a value for throughVar1."
exit sub
end if
' Can now do calculations
' Write your own code here using yout inVars to
' produce outVars
' Save to table
' Use your own column (field) names. They are between the ( and )
' Here tVar1 is the column (field) for throughVar1.
' oVar1 is the column (field) for outVar1.
' The variables come after VALUES.
' Take careful note of the number and arrangement of quotes,
' both double and single.
' Replace TableName with the name of your table
sStatement = thisForm.ActiveConnection.createStatement()
sSQL = "INSERT INTO ""TableName"" (""tVar1"", ""tVar2"", ""oVar1"", ""oVar2"") VALUES ('" + throughVar1 + "', '" + throughVar2 + "', '" + outVar1 + "', '" + outVar2 + "');"
sStatement.executeUpdate( sSQL )
' Clear the form controls
' You could of course use other default values
thisControl = thisForm.getByName("txtThroughVar1")
thisControl.text = ""
thisControl = thisForm.getByName("numThroughVar2")
thisControl.value = 0
thisControl = thisForm.getByName("numInVar1")
thisControl.value = 0
thisControl = thisForm.getByName("numInar2")
thisControl.value = 0
' Now the values have been inserted into the table
' and the form is ready for the next set of values
End Sub