Base. Do calculation before updating table

Creating Extension - Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This forum is not for asking questions about writing your own macros.

Base. Do calculation before updating table

Postby macnab » Wed Oct 22, 2014 5:41 am

Here is an example of how to use a form to enter some values which are used in a calculation, the results of the calculation being saved to a table.

Code: Select all   Expand viewCollapse view
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
OpenOffice 4.0.1 on Windows 7
Posts: 19
Joined: Tue Sep 06, 2011 8:08 am

Return to Code Snippets

Who is online

Users browsing this forum: No registered users and 1 guest