I need to populate some text boxes from a separate table

[CTBarbarin]

Hello all,

I'm a newbie to OOo Base, but have 9+ years experience (some classes, mostly self-taught) in Excel/Excel's VBA...although not much, but some, experience in forms/programming forms.

I have a simple form to add records to a single table...but, I would like to add ~4 text boxes (or other appropriate controls) to pull data from a separate table to use in calculations as sort of a reconciliation. I need these 4 controls to populate/refresh after 2 text boxes have had values entered. I'd also like these 4 controls to update as I am switching from record to record for review.

Any info, especially links to tutorials/walk-throughs would be greatly appreciated.

Thank you for any help anyone can provide,

CTB

[MTP]

I started with the tutorials here: http://sheepdogguides.com/fdb/fdb1main.htm and there are others in a couple of special forum sections: Database examples and Base Tutorials

You can use queries to do calculations (from any combination of tables) and can use queries as a data source for part of a form the same way you can use tables as the data source for all or part of a form.

You can add a pushbutton with property "Action" set to "refresh form" and pressing the button will refresh the form (including doing any recalculations with values that are new or have been changed).

For showing the correctly related information you probably want to do subform linking. This is a really useful method although it takes some space to explain it: [Example #1] Filter/Search with Forms (leveraging SubForms)

[libreofficer]

Hello CTBarbarin,

you could make an SQL statement to lookup a value from another table (for example using the function FindValue() below).

then you can implement the Form's "After record change" event, to refresh your 4 controls
likewise for the "Changed" or "Text modified" event of your controls.

Code: Select all

Function FindValue(strTableName as String, strFieldName as String, strSQL as String)
REM Returns the value of the Field "strFieldName" of the table "strTableName",
REM for the very first Row that satisfies the strSQL criterium.
REM strSQL: make sure that ALL the identifiers are within double quotes.
	Dim Statement, ResultSet, oCon, oColumn
	Dim cmdSQL as String
	FindValue = -1 REM means Not Found.
	oCon = thisComponent.Drawpage.Forms(0).ActiveConnection
	cmdSQL = "SELECT """ & strFieldName & """ FROM """ & strTableName & """ WHERE " & strSQL
	Statement = oCon.createStatement()
	ResultSet = Statement.executeQuery(cmdSQL)
	If ResultSet.Next Then
		oColumn  = ResultSet.Columns.getByName(strFieldName)
		FindValue = getXXX(oColumn)
	End If
	ResultSet.Close()
	Statement.Close()
End Function

Function getXXX(col)
REM Function by Roberto Benitez.
REM col: a Column object.
   Dim ret
   Dim DataType As String
   DataType = col.TypeName
   Select Case DataType
      Case "ARRAY": ret=col.Array
      Case "BLOB": ret=col.Blob
      Case "BOOLEAN": ret=col.Boolean
      Case "BYTE": ret=col.Byte
      Case "BYTES": ret=col.Bytes
      Case "BLOB": ret=col.Clob
      Case "DATE": ret=col.Date
      Case "DOUBLE": ret=col.Double
      Case "INTEGER": ret=col.Int
      Case "LONG": ret=col.Long
      Case "DECIMAL": ret=col.Double
      Case "NULL": ret=col.Null
      Case "OBJECT": ret=col.Object
      Case "REF": ret=col.Ref
      Case "SHORT": ret=col.Short
      Case "VARCHAR": ret=col.String
      Case "TIME": ret=col.Time
      Case "TIMESTAMP": ret=col.TimeStamp
      Case Else: ret=col.String 'GIVE STRING A TRY
   End Select
   getXXX=ret
End Function