OO-Snippets: Setting Datefields to empty value

Commons
Keywords datefield, empty, not empty
Language OOBasic
Application Database
Authors Tom Schindl (initial)
Supported
Versions
Supported OS
How do I create an empty datefield in database forms, normally it is always filled
Question
with actual date
At the moment this is only possible by registering a OO-Macro onto the "after reset"-
event of the field's parentform and resetting the value of the field when form.isNew
Answer
evaluates to true.

Code-Snippet-Listing (snippet-source)
' Set the date field of the form to an empty value
Sub _SetDateFieldToNull( Form As Object, FieldParent As Object, ColName As String )

' only reset the date field if the record is new

if Form.isNew Then
FieldParent.GetByName(ColName).BoundField.updateNull()
end if

End Sub

' When the date field is part of a sub form the case will
' have to additionally check some more things
Sub _SetSubFormDateFieldToNull( ParentForm As Object, ChildForm As Object, FieldParent As Obj

Dim bIsEmpty

' Let's see whether the parent form holds any values
' else we are running into troubles
if not ParentForm.isBeforeFirst And ParentForm.isAfterLast Then
bIsEmpty = true
else
bIsEmpty = false
end if

' If the parentform is new the subform does not hold any values
' the same situation arises if the parent form does not hold any
' records
if not ParentForm.isNew And not bIsEmpty Then
 _SetDateFieldToNull( ChildForm, FieldParent, ColName )
end if

End Sub

OO-Snippets: Querying databases

Commons
Keywords database, access
Language OOBasic
Application Database
Authors Tom Schindl (initial)
Supported
Versions
Supported OS
Question How do I query a database in OpenOffice using StarBASIC?
Fairly simple check this out. You'll have to register an DataSource named
Answer "my_resource" using Tools -> Datasources.

Code-Snippet-Listing (snippet-source)
Sub executeSQLOne
Dim RowSet

' Create a row-set to query the database

RowSet = createUnoService("com.sun.star.sdb.RowSet")
RowSet.DataSourceName = "my_resource"
RowSet.CommandType = com.sun.star.sdb.CommandType.COMMAND
RowSet.Command = "SELECT max(m_id) FROM member WHERE delmark = 0"
RowSet.execute()

' it only returns 1 row

' so we need no loop
RowSet.next()

MsgBoxg "The MaxId is " + rowSet.getString(1)

End Sub

Sub executeSQLMore
' Create a row-set to query the database
RowSet = createUnoService("com.sun.star.sdb.RowSet")
 RowSet.DataSourceName = "my_resource"
RowSet.CommandType = com.sun.star.sdb.CommandType.COMMAND
RowSet.Command = "SELECT m_id, m_surname FROM member WHERE delmark = 0"
RowSet.execute()

' loop through the resultset until no record is left

while RowSet.next()
MsgBoxg "The Person " + rowSet.getString(2) + " has the ID " + rowSet.getString(1);
wend
End Sub

OO-Snippets: setting an filter using a macro

Commons
Keywords filter, database, database forms
Language OOBasic
Application Database
Authors Tom Schindl (initial)
Supported
Versions
Supported
OS
How do I access a SubSubForm and set an filter

I have a form document to view a datasource. The document has a form, a subform,
Question and a sub-subform, all of them linked with one-to-many relationship. I need to filter
the last subform on the fly, pushing a button. I need to write a StarBasic macro that
can do that on the fly, without switching to design mode. Is this possible?

Yes given the sturcture looks like the following

+ Form1
Answer
+ Form1_1

+ Form1_1_1

Code-Snippet-Listing (snippet-source)
Sub setFilter

' reserve some variables

Dim TopForm As Object
Dim SubForm As Object
Dim SubSubForm As Object

' Fetch the appropiate form

TopForm = ThisComponent.DrawPage.Forms.GetByName("Form1")
SubForm = TopForm.GetByName("Form1_1")
SubSubForm = SubForm.GetByName("Form1_1_1")

' Now we set the filter attribute

SubSubForm.Filter = "p_surname LIKE 'Sch%'"
' and don't forget to reload
SubSubForm.reload()

End Sub

OO-Snippets: Cancle Database Action

Commons
Keywords cancle db action, set column value via macro
Language OOBasic
Application Database
Authors Tom Schindl (initial)
Supported
Versions
Supported OS
How can I cancle a database action

I have to problem that I want to cancle the delete opertion of an user and
overwrite it with
Question
an update statement which sets a delmark. The column which is set to one if the
entry is deleted

is named delmark

First of all make sure that you are filtering the data by setting the filter property to
"delmark = 0".

Please note that you have to use the filter property when directly appending the
Answer
delmark = 0 in

the where clause of the SQL-Statement will result in an error.

Code-Snippet-Listing (snippet-source)
' event handler which cancles the event
Function MainFormBeforeDeleteHandler( Event As Object )

Dim FormObj
Dim DelmarkFieldObj

FormObj = ThisComponent.DrawPage.Forms.GetByIndex(0)

DelmarkFieldObj = FormObj.GetbyName("delmark")

_SetDelmarkInsteadOfDelete( FormObj, DelmarkFieldObj )

MainFormBeforeDeleteHandler = false

End Function

' Event handler which cancles the event

Function SubFormDebitBeforeDeleteHandler( Event As Object )

_SetDelmarkInsteadOfDelete4SubForms( "DebitDirectly", "delmark" )

SubFormDebitBeforeDeleteHandler = false

End Function

' Set the delmark-column to a 1

Sub _SetDelmarkInsteadOfDelete( Form As Object, DelmarkField As Object )

DelmarkField.BoundField.updateShort( 1 )

Form.UpdateRow()

Form.reload()

End Sub

' This sub is working onto table control which are often used
' in sub-forms
Sub _SetDelmarkInsteadOfDelete4SubForms( Form As String, DelmarkField As String )
Dim FormObj As Object
Dim DelmarkFieldObj As Object

' Get the $Form-form

FormObj = ThisComponent.DrawPage.Forms.GetByIndex(0).GetByName(Form)

' Get the $DelmarkField

DelmarkFieldObj = FormObj.GetbyName("TableControl").GetbyName(DelmarkField)

_SetDelmarkInsteadOfDelete( FormObj, DelmarkFieldObj )

End Sub
OO-Snippets: Call a method from a specific interface

Commons
Keywords DataBrowser, interface, beamer, grid, selection
Language OOBasic
Application Database
Bernard Marcelly (initial)
Marc Santhoff (initial)
Authors Frank Schönheit (initial)
Paolo Mantovani (initial)
Fernand (initial)
Supported
Versions
Supported OS
Question In Basic, how to call a method offered by a specific interface of an object ?
An object may support the same method name from several interfaces. In this case
Basic uses the first method it finds. This snippet shows how to specify which method
to use.
Answer
Based on Developer's Guide chapter 6.2.6.

Code-Snippet-Listing (snippet-source)
Option Explicit

Sub RowSelectionInDataBrowser
' finds which rows in the DataSourceBrowser are selected by the user
Dim frame1 As Object, frame2 As Object
Dim oModel As Object, oResults As Object
Dim oDoc As Object, oGrid As Object
Dim oDataSourceBrowser as Object
Dim oSelection as Variant, ii As Long

oDoc = thiscomponent
frame1 = oDoc.CurrentController.Frame
frame2 = frame1.findFrame("_beamer",4) ' get DataBrowser frame
if IsNull(frame2) then
MsgBox("Beamer not found !", 16)
Exit Sub
end if
oDataSourcebrowser = frame2.Controller
' the DataSourceBrowser object has several getModel methods
' choose getModel from interface com.sun.star.awt.XTabController
' to get the form used by DataSourceBrowser
oModel = oDataSourcebrowser.com_sun_star_awt_XTabController_getModel
' creating a "clone" of the form
oResults = oModel.createResultSet
oGrid = oDataSourceBrowser.CurrentControl
oSelection = oGrid.Selection
if UBound(oSelection) >= 0 then ' we get a list of Bookmarks
For ii= 0 To UBound(oSelection)
if oResults.moveToBookmark(oSelection(ii)) then
MsgBox( oResults.Columns(0).String & " : " & oResults.Columns(4).String )
else
MsgBox("Bookmark not found : " & oSelection(ii), 16)
end if
Next
else
MsgBox("No line selected in the DataBrowser", 16)
end if
End Sub

OO-Snippets: copy record to new

Commons
Keywords record, template, form, copy
Language OOBasic
Application Database
Authors Marc Santhoff (initial)
Supported
1.1.x 2.2.0
Versions
Supported OS All
How can I copy an existing record to a new one as template?

When typing in data into a database form sometimes many records only differ in a
small number of fields.
Question
In this case it is desirable to copy one record to a new, empty one and only change
the minor differences by hand.

Answer

Code-Snippet-Listing (snippet-source)
' Copies the value currently shown in the form
' as a template to a new record. This new record only
' is displayed for editing, not stored yet.

' Only some control types are checked here, please

' add missing types needing special treatment.
' The programmer or user has to take care of adding a
' new primary key into the corresponding field control.

' event binding sub

sub copyToNewEvent(evt as object)
copyRecordToNewRecord(thisComponent)
end sub

sub copyRecordToNewRecord(oDoc as object, optional sKeyfieldname as string)

dim aVal as Variant
dim ccount as integer
dim i as integer

if IsMissing(sKeyfieldname) then sKeyfieldname = "ID"

oForm = oDoc.Drawpage.Forms(0)
ccount = oForm.count
redim aVal(ccount)

' Step 1 --> get the current fields content (excluding key field for auto values)

' loop over all controls

for i=0 to ccount-1
aControl = oForm.getByIndex(i)
' only get controls storing database values
if HasUNOInterfaces(aControl, "com.sun.star.form.XBoundComponent") then
' read currently shown value
n = aControl.name
' exclude primary key field
if (InStr(sKeyfieldname, n)=0) then
if aControl.supportsService("com.sun.star.awt.UnoControlDateF
aVal(i) = aControl.Date
elseif aControl.supportsService("com.sun.star.awt.UnoControlT
aVal(i) = aControl.Time
elseif aControl.supportsService("com.sun.star.awt.UnoControlL
aVal(i) = oDoc.currentController.getControl(aControl)
elseif aControl.supportsService("com.sun.star.awt.UnoControlF
aVal(i) = aControl.EffectiveValue
else
aVal(i) = aControl.Text
end if
end if
end if
next i

' Step 2 --> make a new record in the form (only)

oForm.moveToInsertRow()

' Step 3 --> copy in saved values

' loop over controls again

for i=0 to ccount-1
aControl = oForm.getByIndex(i)
if HasUNOInterfaces(aControl, "com.sun.star.form.XBoundComponent") then
' get the value to set
n = aControl.name
' exclude primary key field
if (InStr(sKeyfieldname, n)=0) then
if aControl.supportsService("com.sun.star.awt.UnoControlDateF
 aControl.Date = aVal(i)
elseif aControl.supportsService("com.sun.star.awt.UnoControlT
aControl.Time = aVal(i)
elseif aControl.supportsService("com.sun.star.awt.UnoControlL
oDoc.currentController.getControl(aControl).SelectedI
elseif aControl.supportsService("com.sun.star.awt.UnoControlF
oDoc.currentController.getControl(aControl).setText(a
else ' an EditField
aControl.Text = aVal(i)
end if
' let the control store it's value into the bound field model
aControl.commit()
end if
end if
next i

end sub

OO-Snippets: change listbox source

Commons
Keywords listbox, form, sql, list source
Language OOBasic
Application Database
Authors Marc Santhoff (initial)
Supported
1.1.x 2.2.0
Versions
Supported OS All
How can I change the SQL list source of a ListBox?

A query (SQL) based listbox has a fixed SQL statement for it's source.
Question
Sometimes it is useful to change this statement by code, e.g. when using one form
with different queries.

Answer

Code-Snippet-Listing (snippet-source)
Sub changeLBSource
' caution: if you change like this and then save the form
' the SQL statement filled in when designing the form is
' overwritten with the new one, your code should hold both of them!
dim oDoc as object, oForm as object
 dim oListboxModel as object
dim ssql(0) as string

oDoc = ThisComponent
oForm = oDoc.DrawPage.Forms(0)
oListboxModel = oForm.getByName("ListBox")
ssql(0) = "select ""name"", ""ID"" from ""color"""
oListboxModel.ListSource() = ssql()
oListboxModel.refresh()
End Sub