LibreOfficeLogo

Base Guide 7.3

Chapter 9
Macros

Copyright

This document is Copyright © 2022 by the LibreOffice Documentation Team. Contributors are listed below. You may distribute it and/or modify it under the terms of either the GNU General Public License (https://www.gnu.org/licenses/gpl.html), version 3 or later, or the Creative Commons Attribution License (https://creativecommons.org/licenses/by/4.0/), version 4.0 or later.

All trademarks within this guide belong to their legitimate owners.

Contributors

To this edition

flywire

Jean-Pierre Ledure

Jean Hollis Weber

Steve Fanning

Olivier Hallot

 

To previous editions

Robert Großkopf

Pulkit Krishna

Jost Lange

Jean-Pierre Ledure

Andrew Pitonyak

Alain Romedenne

Hazel Russman

Jochen Schiffers

Jean Hollis Weber

Feedback

Please direct any comments or suggestions about this document to the Documentation Team’s forum at LibreOffice Guides - The Document Foundation Community (registration is required) or send a mail to loguides@community.documentfoundation.org

Note

Everything you post to a forum, including your email address and any other personal information that is written in the message, is publicly archived and cannot be deleted. E-mails sent to the forum are moderated.

Publication date and software version

Published August 2022. Based on LibreOffice 7.3 Community.
Other versions of LibreOffice may differ in appearance and functionality.

Using LibreOffice on macOS

Some keystrokes and menu items are different on macOS from those used in Windows and Linux. The table below gives some common substitutions for the instructions in this book. For a more detailed list, see the application Help.

Windows or Linux

macOS equivalent

Effect

Tools > Options menu selection

LibreOffice > Preferences

Access setup options

Right-click

Control+click and/or right-click depending on computer setup

Open a context menu

Ctrl (Control)

⌘ (Command)

Used with other keys

Alt

⌥ (Option)

Used with other keys

Ctrl+Q

⌘+Q

Exit / quit LibreOffice

General remarks on macros

In principle a database in Base can be managed without macros. At times, however, they may become necessary for:

You must decide for yourself how intensively you wish to use macros in Base. Macros can improve usability but are always associated with small reductions in the speed of the program, and sometimes with larger ones (when coded poorly). It may also pose issues of code maintenance in the long term. It is always better to start off by fully utilizing the possibilities of the database and the provisions for configuring forms before trying to provide additional functionality with macros. Macros should always be tested on larger databases to determine their effect on performance.

Macros are created using Tools > Macros > Organize Macros > Basic. A window appears which provides access to all macros. For Base, the important area corresponds to the filename of the Base file.

Figure 1: New Module dialog

graphics1

The New button in the LibreOffice Basic Macros dialog opens the New Module dialog, which asks for the module name (the folder in which the macro will be filed). The name can be altered later if desired.

As soon as this is given, the macro editor appears. Its input area already contains the Start and the End for a subroutine:

REM ***** BASIC *****

Sub Main

End Sub

If macros are to be used, the following steps are necessary:

Some basic principles for the use of Basic code in LibreOffice:

End Sub

For further details, see Chapter 13, Getting Started with Macros, in the Getting Started Guide.

Note

Macros in the PDF and ODT versions of this chapter are colored according to the rules of the LibreOffice macro editor:

Macro designation
Macro comment
Macro operator
Macro reserved expression
Macro number
Macro character string

Note

There are several references to the LibreOffice API Reference throughout this chapter. In electronic versions of the chapter, such references will be hyperlinks. For those reading a hard copy, the API Reference can be found at https://api.libreoffice.org/docs/idl/ref/.

Macros in Base

Using macros

The “direct way”, using Tools > Macros > Run Macro is possible, but not usual for Base macros. A macro is normally assigned to an event and launched when that event occurs. Macros are used for:

The “direct way” is not possible – not even for testing – when one of the objects thisComponent (see “Accessing forms” on page 1) or oEvent (see “Accessing form elements” on page 1) is to be used.

Assigning macros

If a macro is to be launched by an event, it must first be defined. Then it can be assigned to an event. Such events can be accessed through two locations.

Events that occur in a form when the window is opened or closed

Actions that take place when a form is opened or closed are registered as follows:

Figure 2: Customize dialog, Events tab

Grafik2

1)  While designing the form, open the Events tab in Tools > Customize.

2)  Choose the appropriate event. Some macros can only be launched when the View created event is chosen. Other macros, for example to create a full-screen form, should be launched by Open document.

3)  Use the Macro button to find the macro you want and confirm your choice.

4)  Under Save in, give the name of the form.

5)  Confirm with OK.

Events in a form in an open window

Once the window is opened to show the overall content of the form, individual elements of the form can be accessed. This includes the elements you have assigned to the form.

The form elements can be accessed using the Form Navigator, as shown in the illustration below. They can equally well be accessed by using the contextual menus of individual controls within the form interface.

The events listed under Form Properties > Events all take place while the form window is open. The can be set separately for each form or subform in the form window.

Figure 3: Form Properties dialog, Events tab

Bild11

Note

Base uses the word “form” both for a window that is opened for the input of data, and for elements within this window that are bound to a specific data source (table or query).

A single form window might well contain several forms with different data sources. In the Form Navigator, you always see first the term Forms, which in the case of a simple form contains only one subordinate entry.

Events within a form

All other macros are registered using the properties of subforms and controls through the Events tab.

1)  Open the window for the properties of the control (if you have not already done so).

2)  Choose a suitable event in the Events tab.

3)  To edit the data source, use events that refer to Record or Update or Reset.

4)  Click the ... button to the right to open the Assign action dialog.

5)  Click the Macro button to choose the macro defined for the action.

6)  Click OK to confirm the assignment.

Figure 4: Assign Action dialog

Grafik3

Components of macros

This section explains some of the macro language that is commonly used in Base, especially within forms. As far as is possible (and reasonable), examples are given in all the following sections.

The “Framework” of a macro

The definition of a macro begins with its type – Sub or Function – and ends with End Sub or End Function. A macro that is assigned to an event can receive arguments (values); the only useful one is the oEvent argument. All other routines that might be called by such a macro can be defined with or without a return value, depending on their purpose, and provided with arguments if necessary.

Sub update_loan

End Sub

Sub from_Form_to_Form(oEvent As Object)

End Sub

Function confirm_delete(oEvent As Object) As Boolean

confirm_delete = False

End Function

It is helpful to write out this framework immediately and put in the content afterwards. Do not forget to add comments to explain the macro, remembering the rule “As many as necessary, as few as possible”. In addition, Basic does not distinguish between upper and lower case. Usually fixed terms like SUB are written preferably in upper case, other concepts in mixed case.

Defining variables

In the next step, at the beginning of the routine, the Dim command is used to define the variables that will occur within the routine, each with its appropriate data type. Basic itself does not require this; it accepts any new variables that occur within the program. However the program code is “safer” if the variables, especially their data types, are declared. Many programmers make this a requirement, using Basic’s Explicit option when they begin to write a module. This means “Do not recognize any old variable, but only those I have declared beforehand”.

Dim oDoc As Object

Dim oDrawpage As Object

Dim oForm As Object

Dim sName As String

Dim bOKEnabled As Boolean

Dim iCounter As Integer

Dim dBirthday As Date

Only alphabetic characters (A-Z or a-z), numbers and the underline character ‘_’ may be used in variable names. No special characters are allowed. Spaces are allowed under some conditions, but are best avoided. The first character must be alphabetic.

It is common practice to specify the data type in the first character. Then it can be recognized wherever the variable occurs in the code. Also recommended are “expressive names”, so that the meaning of the variable is obvious from its name.

Note

You should make this more specific where necessary, as only one letter does not allow you to distinguish between the data types “Double” and “Date” or “Single” and “String”.

A list of possible data types in Star Basic can be found in Appendix A in this book. They differ in various places from the types in the database and in the LibreOffice API (Application Programming Interface). Such changes are made clear in the examples.

Defining arrays

For databases in particular, the assembly of several variables into a record is important. If several variables are stored together in a single common location, this is called an array. An array must be defined before data can be written into it.

Dim arData()

creates an empty array.

arData = Array("Lisa","Schmidt")

creates an array of a specific size (2 elements) and provides it with values.

Using

Print arData(0), arData(1)

causes the two defined elements to be displayed onscreen. The element count begins with 0.

Dim arData(2)

arData(0) = "Lisa"

arData(1) = "Schmidt"

arData(2) = "Cologne"

This creates an array in which three elements of any type can be stored, for example a record for "Lisa""Schmidt""Cologne". You cannot put more than three elements into this array. If you want to store more elements, you must make the array larger. However if the size of an array is redefined while a macro is running, the array is initially empty, just like a new array.

ReDim Preserve arData(3)

arData(3) = "18.07.2003"

Adding Preserve keeps the preceding data so that the array is truly extended by the entry of the date (here in the form of text).

The array shown above can store only one record. If you want to store several records, as a table in a database does, you need to define a two-dimensional array.

Dim arData(2,1)

arData(0,0) = "Lisa"

arData(1,0) = "Schmidt"

arData(2,0) = "Cologne"

arData(0,1) = "Egon"

arData(1,1) = "Müller"

arData(2,1) = "Hamburg"

Here too it is possible to extend the previously defined array and preserve the existing contents by using Preserve.

Accessing forms

The form lies in the currently active document. The region which is represented here is called drawpage. The container in which all forms are kept is called forms; in the Form Navigator this shows up as the primary heading with all the individual forms attached. The variables named above receive their values like this:

oDoc = thisComponent

oDrawpage = oDoc.drawpage

oForm = oDrawpage.forms.getByName("Filter")

The form to be accessed is called Filter. This is the name that is visible in the top level of the Form Navigator (by default the first form is called MainForm). Subforms lie in hierarchical order within the main form and can be reached step by step:

Dim oSubForm As Object

Dim oSubSubForm As Object

oSubForm = oForm.getByName("Readerselect")

oSubSubForm = oSubForm.getByName("Readerdisplay")

Instead of using intermediate varables, you can go straight to a particular form. An intermediate object, which can be used more than once, needs to be declared and assigned a separate value. In the following example, oSubForm is no longer used.

oForm = thisComponent.drawpage.forms.getByName("Filter")

oSubSubForm = oForm.getByName("readerselect").getByName("readerdisplay")

Note

If a name consists solely of ascii letters and numbers with no spaces or special characters, the name can be used directly in an assignment statement.

oForm = thisComponent.drawpage.forms.Filter
oSubSubForm = oForm.readerselect.readerdisplay

Contrary to normal Basic usage, such names must be written with the correct case.

A different mode of access to the form is provided by the event that triggers the macro.

If a macro is launched from a form event such as Form Properties > Before record action, the form itself can be reached as follows:

Sub MacroexampleCalc(oEvent As Object)

oForm = oEvent.Source

...

End Sub

If the macro is launched from an event in a form control, such as Text box > When losing focus, both the form and the field become accessible:

Sub MacroexampleCalc(oEvent As Object)

oField = oEvent.Source.Model

oForm = oField.Parent

...

End Sub

Access to events has the advantage that you need not bother about whether you are dealing with a main form or a subform. Also the name of the form is of no importance to the functioning of the macro.

Accessing form elements

Elements within forms are accessed in a similar way: declare a suitable variable as object and search for the appropriate control within the form:

Dim btnOK As Object ' Button »OK»

btnOK = oSubSubForm.getByName("button 1") ' from the form readerdisplay

This method always works when you know which element the macro is supposed to work with. However when the first step is to determine which event launched the macro, the oEvent method shown above becomes useful. The variable is declared within the macro “framework” and gets assigned a value when the macro is launched. The Source property always yields the element that launched the macro, while the Model property describes the control in detail:

Sub confirm_choice(oEvent As Object)

Dim btnOK As Object

btnOK = oEvent.Source.Model

End Sub

If you want, further actions can be carried out with the object obtained by this method.

Please note that subforms count as components of a form.

Access to the database

Normally access to the database is controlled by forms, queries, reports or the mailmerge function, as described in previous chapters. If these possibilities prove insufficient, a macro can specifically access the database in several ways.

Connecting to the database

The simplest method uses the same connection as the form. oForm is determined as shown above.

Dim oConnection As Object

oConnection = oForm.activeConnection()

Or you can fetch the data source (i.e. the database) through the document and use its existing connection for the macro:

Dim oDatasource As Object

Dim oConnection As Object

oDatasource = thisComponent.Parent.dataSource

oConnection = oDatasource.getConnection("","")

A further way allows the connection to the database to be created on the fly:

Dim oDatasource As Object

Dim oConnection As Object

oDatasource = thisComponent.Parent.CurrentController

If Not (oDatasource.isConnected()) Then oDatasource.connect()

oConnection = oDatasource.ActiveConnection()

The If condition controls only one line so End If is not required.

If the macro is to be launched through the user interface and not from an event in a form, the following variant is suitable:

Dim oDatasource As Object

Dim oConnection As Object

oDatasource = thisDatabaseDocument.CurrentController

If Not (oDatasource.isConnected()) Then oDatasource.connect()

oConnection = oDatasource.ActiveConnection()

Access to databases outside the current database is possible as follows:

Dim oDatabaseContext As Object

Dim oDatasource As Object

Dim oConnection As Object

oDatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")

oDatasource = oDatabaseContext.getByName("registered name of Database in LO")

oConnection = oDatasource.GetConnection("","")

Connections to databases not registered with LibreOffice are also possible. In such cases, instead of the registered name, the path to the database must be given as file:///……/database.odb.

Expanded instructions on database connections are given in “Making a connection to a database” (page 1).

SQL commands

You work with the database using SQL commands. These need to be created and sent to the database; the result is determined according to the type of command and the results can be further processed. The createStatement directive creates a suitable object for this purpose.

Dim oSQL_Statement As Object ' the object that will carry out the SQL-command

Dim stSql As String ' Text of the actual SQL-command

Dim oResult As Object ' result of executeQuery

Dim iResult As Integer ' result of executeUpdate

oSQL_Statement = oConnection.createStatement()

To query data, you call the executeQuery method; the result is then evaluated. Table and field names are usually double-quoted. The macro must mask these with additional double quotes to ensure that they appear in the command.

stSql = "SELECT * FROM ""Table1"""

oResult = oSQL_Statement.executeQuery(stSql)

To modify data – that is to INSERT, UPDATE or DELETE – or to influence the database structure, you call the executeUpdate method. Depending on the command and the database, this yields either nothing useful (a zero) or the number of records modified.

stSql = "DROP TABLE ""Suchtmp"" IF EXISTS"

iResult = oSQL_Statement.executeUpdate(stSql)

For the sake of completeness, there is one more special case to be mentioned: if the oSQL_Statement is to be used in different ways for SELECT or for other purposes, there is another method available, namely execute. We will not be using it here. For further information, see the API Reference.

Pre-prepared SQL commands with parameters

In all cases where manual entry by a user needs to be transferred into a SQL statement, it is easier and safer not to create the command as a long character string but to prepare it in advance and use it with parameters. This makes the formatting of numbers, dates, and strings easier (the constant double quotes disappear) and prevents malicious input from causing data loss.

To use this method, the object for a particular SQL command is created and prepared:

Dim oSQL_Statement As Object ' the object, that executes the SQL-command

Dim stSql As String ' Text of the actual SQL-command

stSql = "UPDATE author " _

& "SET lastname = ?, firstname = ?" _

& "WHERE ID = ?"

oSQL_Statement = oConnection.prepareStatement(stSql)

The object is created with prepareStatement so that the SQL command is known in advance. Each question mark indicates a position which later – before the command is executed – will receive an actual value. Because the command is prepared in advance, the database knows what type of entry – in this case two strings and a number – is expected. The various positions are distinguished by number (counting from 1).

Then the values are transferred with suitable statements and the SQL command is carried out. Here the values are taken from form controls, but they could also originate from other macros or be given as plain text:

oSQL_Statement.setString(1, oTextfeld1.Text) ' Text for the surname

oSQL_Statement.setString(2, oTextfeld2.Text) ' Text for the first name

oSQL_Statement.setLong(3, oZahlenfeld1.Value) ' value for the appropriate ID

iResult = oSQL_Statement.executeUpdate

The complete list of assignments is in “Parameters for prepared SQL commands" (page 1).

For further information on the advantages of this method, see below (external links):

Reading and using records

There are several ways, depending on requirements, to transfer information out of a database into a macro so that it can be processed further.

Please note: references to a form include subforms. What is intended is that form or part of a form that is bound to a particular data source.

Using forms

The current record and its data are always available through the form that shows the relevant data (table, query, SELECT). There are several getdata_ type methods, such as:

Dim ID As Long

Dim sName As String

Dim dValue AS Currency

Dim dEntry As New com.sun.star.util.Date

ID = oForm.getLong(1)

sName = oForm.getString(2)

dValue = oForm.getDouble(4)

dEntry = oForm.getDate(7)

All these methods require a column number from the data source; the count starts at 1.

Note

For all methods that work with databases, counting starts at 1. This applies to both columns and rows.

If you prefer to use column names instead of column numbers to work with the underlying data source (table, query, view), the column number can be determined using findColumn. Here is an example for finding the column called Name.

Dim sName As String

nName = oForm.findColumn("Name")

sName = oForm.getString(nName)

The type of value returned always matches the method type, but the following special cases should be noted:

The complete list of these methods is to be found in “Editing rows of data" (page 1).

Tip

If values from a form are to be used directly for further processing in SQL (for example for input into another table), it is much simpler not to have to query the field type.

The following macro, which is bound to Properties: Button > Events > Execute action reads the first field in the form independently of the type necessary for future processing in Basic.

SUB ReadValues(oEvent As Object)
Dim oForm As Object
Dim stFeld1 As String
oForm = oEvent.Source.Model.Parent
 stFeld1 = oForm.getString(1)
End Sub

If fields are read using getString(), all formatting necessary for further SQL processing is preserved. A date that is displayed as 08.03.19 is read out in the format 2019-03-08 and can be used directly in SQL.

Reading out in a format corresponding to the type is only mandatory if the value is to be further processed within the macro, for example in a calculation.

Result of a query

The set of results from a query can be used in the same way. In the SQL commands section, you will find the variable oResult for this result set, which is usually read out something like this:

While oResult.next ' one record after another
REM transfer the result into variables
stVar = oResult.getString(1)
inVar = oResult.getLong(2)
boVar = oResult.getBoolean(3)
REM do something with these values

Wend

According to the type of SQL command, the expected result and its purpose, the WHILE loop can be shortened or dropped altogether. But basically a result set can always evaluated in this way.

If only the first record is to be evaluated

oResult.next

accesses the row for this record and with

stVar = oResult.getString(1)

reads the content of the first field. The loop ends here.

The query for the above example has text in the first column, an integer number in the second (Integer in the database corresponds to Long in Basic), and a Yes/No field in the third. The fields are accessed through a field index which, unlike an array index, starts from 1.

Navigation through such a result is not possible. Only single steps to the next record are allowed. To navigate within the record, the ResultSetType must be known when the query is created. This is accessed using

oSQL_Result.ResultSetType = 1004

or

oSQL_Result.ResultSetType = 1005

Type 1004 – SCROLL_INTENSIVE allows you to navigate freely but does not pick up changes in the original data. Type 1005 – SCROLL_SENSITIVE recognizes changes in the original data which might affect the query result.

The total number of rows in the result set can be determined only after a numeric type for the result has been specified. It is carried out as follows:

Dim iResult As Long

If oResult.last ' go to the last record if possible

iResult = oResult.getRow ' the running number is the sum

Else

iResult = 0

End If

Using a control

If a control is bound to a data source, the value can be read out directly, as described in the next section. However this can lead to problems. It is safer to use the procedure described in “Using forms” (page 1) or else the following method, which is shown for several different types of control:

sValue = oTextField.BoundField.Text ' example for a Text field

nValue = oNumericField.BoundField.Value ' example for a numeric field

dValue = oDateField.BoundField.Date ' example for a date field

BoundField represents the link between the visible control and the actual content of the data set.

Navigating in a data set

In the last but one example the Next method was used to move from one row of the result set to the next. There are further similar methods and tests that can be used both for the data in a form – represented by the variable oForm – and for a result set. For example, using the method described in “Automatic updating of forms” (page 1), the previous record can be selected again:

Dim loRow As Long

loRow = oForm.getRow() ' save the current row number

oForm.reload() ' reload the record set

oForm.absolute(loRow) ' go back to the same rowthe

The section “Automatic updating of forms” shows all the methods that are suitable for this.

Note

There is a bug which affects forms. It sets the current row number when data is altered within a form to ‘0’. See https://bugs.documentfoundation.org/show_bug.cgi?id=82591. To get the correct current row number, bind the following macro to the event Form > Properties > Events > After record change.

Global loRow As Long
Sub RowCounter(oEvent As Object)
loRow = oEvent.Source.Row
End Sub

The new row number is read out and assigned to the global variable loRow. This variable is to be placed at the start of all modules and will retain its content until you exit Base or change the value by calling RowCounter again.

Editing records – adding, modifying, deleting

In order to edit records, several things have to work together:

When you are doing this through a macro, these partial steps must all be considered. If any one of them is lacking or is carried out wrongly, changes will be lost and will not end up in the database. First of all the change must not be in the control’s displayed value but in the data set itself. This makes it pointless to change the Text property of a control.

Please note that tables are the only data sets that can be altered without causing problems. For other data sets, editing is possible only under special circumstances.

Changing the content of a control

If you wish to change only a single value, the BoundField property of the control can be used with an appropriate method. Then the change must be transmitted to the database. Here is an example for a date field into which the actual date is to be entered:

Dim unoDate As New com.sun.star.util.Date

unoDate.Year = Year(Date)

unoDate.Month = Month(Date)

unoDate.Day = Day(Date)

oDateField.BoundField.updateDate( unoDate )

oForm.updateRow() ' the change is transmitted to the databset

For BoundField you use the updateXxx method that matches the data type of the field. In this example the field is a Date field. The new value is passed as the argument – in this case the current date, converted into the format which the macro requires.

Altering rows in a data set

The previous method is unsuitable when several values in a row need to be changed, For one thing, a control would have to exist on the form for every field, which is often not desired and not useful. Also, an object must be fetched for each field. The simple and direct way uses the form like this:

Dim unoDate As New com.sun.star.util.Date

unoDate.Year = Year(Date)

unoDate.Month = Month(Date)

unoDate.Day = Day(Date)

oForm.updateDate(3, unoDate )

oForm.updateString(4, "ein Text")

oForm.updateDouble(6, 3.14)

oForm.updateInt(7, 16)

oForm.updateRow()

For each column in the data set, the updateXxx method appropriate to its type is called. The arguments are the column number (counting from 1) and the desired value. Then the alterations are passed on to the database.

Creating, modifying, and deleting rows

The named changes refer to the current row of the data set underlying the form. Under some circumstances it is necessary to call a method from “Navigating in a data set” (page 1). The following steps are necessary:

1)  Choose the current record.

2)  Change the values as described in the previous section.

3)  Confirm the change with the following command:
oForm.updateRow()

4)  In special cases it is possible to cancel and return to the previous state:
oForm.cancelRowUpdates()

For a new record there is a special method, comparable with changing to a new row in a table control. This is done as follows:

1)  Prepare for a new record:
oForm.moveToInsertRow()

2)  Enter all wanted/required values. This is done using the updateXxx methods as shown in the previous section.

3)  Confirm the new data with the following command:
oForm.insertRow()

4)  The new entry cannot be easily reversed. Instead you will have to delete the new record.

There is a simple command to delete a record; proceed as follows:

1)  Choose the desired record and make it current, as for a modification.

2)  Use the following command to delete it:
oForm.deleteRow()

Tip

To ensure that changes are carried over into the database, they must be confirmed explicitly with updateRow or insertRow as appropriate. While pressing the Save button will automatically use the appropriate function, with a macro you must determine before saving if the record is new (Insert) or a modification of an existing one (Update).

If oForm.isNew Then
 oForm.insertRow()
Else
oForm.updateRow()
End If

Testing and changing controls

Apart from the content of the data set, a lot more information can be read out of a control, edited and modified. This is particularly true of properties, as described in Chapter 4, Forms.

Several examples in “Improving usability” (page 1) use the additional information in the field:

Dim stTag As String

stTag = oEvent.Source.Model.Tag

As mentioned in the previous section, the Text property can only be modified usefully if the control is not bound to a data set. However there are other properties which are determined as part of the form definition but can be adapted at run time. For example, a label could be given a different text color if it represented a warning rather than information:

Sub showWarning(oField As Object, iType As Integer)

Select Case iType

Case 1

oField.TextColor = RGB(0,0,255) ' 1 = blue

Case 2

oField.TextColor = RGB(255,0,0) ' 2 = red

Case Else

oField.TextColor = RGB(0,255,0) ' 0 = green (neither 1 nor 2)

End Select

End Sub

English names in macros

Whereas the designer of a form can use native language designations for properties and data access, only English names can be used in Basic. These are set out in the following synopsis.

Properties that are normally only set in the form definition are not included here. Nor are methods (functions and/or procedures) which are only rarely used or only required for more complex declarations.

The synopsis includes the following:

Further information can be found in the API Reference by searching for the English name of the control. There is a useful tool called Xray for finding out which properties and methods are available for an element.

Sub Main(oEvent)

Xray(oEvent)

End Sub

This launches the Xray extension for the argument.

Properties of forms and controls

The model of a control describes its properties. According to the situation, the value of a property can be accessed read-only or write-only. The order follows that in the lists of “Properties of Control Fields” in Chapter 4, Forms.

Font

In every control that shows text, the font properties can be customized.

Table 1: Font properties

Name

Data type

R/W

Property

FontName

string

R+W

Name of the font

FontHeight

single

R+W

Size of the font

FontWeight

single

R+W

Whether bold or normal

FontSlant

integer

R+W

Whether italic or roman

FontUnderline

integer

R+W

Whether underlined

FontStrikeout

integer

R+W

Whether struck through

Formula

English term: Form

Table 2: Formula properties

Name

Data type

R/W

Property

ApplyFilter

boolean

R+W

Filter applied

Filter

string

R+W

Current filter for the record

FetchSize

long

R+W

Number of records loaded at once

Row

long

R

Current row number

RowCount

long

R

Number of records

These properties apply to all controls

Control – see also FormComponent

Table 3: Properties applicable to all controls

Name

Data type

R/W

Property

Name

string

R+(W)

Name of the field

Enabled

boolean

R+W

Active: Field can be selected

EnableVisible

boolean

R+W

Field is displayed

ReadOnly

boolean

R+W

Field content cannot be changed

TabStop

boolean

R+W

Field can be reached through the Tab key

Align

integer

R+W

Horizontal alignment:
0 = left, 1 = centered, 2 = right

BackgroundColor

long

R+W

Background color

Tag

string

R+W

Additional information

HelpText

string

R+W

Help text as a Tooltip

These apply to many types of control

Table 4: Properties applicable to many controls

Name

Data type

R/W

Property

Text

string

(R+W)

Displayed content of the field. In text fields, this can be read and further processed, but that does not usually work for other types.

Spin

boolean

R+W

Spinbox incorporated in a formatted field.

TextColor

long

R+W

Text (foreground) color.

DataField

string

R

Name of the field in the Data set.

BoundField

object

R

Object representing the connection to the data set and providing access to the field content.

Text field – further properties (TextField)

Table 5: Further text field properties

Name

Data type

R/W

Property

String

string

R+W

Displayed field content.

MaxTextLen

integer

R+W

Maximum text length.

DefaultText

string

R+W

Default text.

MultiLine

boolean

R+W

Indicates if there is more than one line.

EchoChar

(integer)

R+W

Character displayed during password entry.

Numeric Field (NumericField)

Table 6: Numeric field properties

Name

Data type

R/W

Property

ValueMin

double

R+W

Minimum acceptable input value

ValueMax

double

R+W

Maximum acceptable input value

Value

double

R+(W)

Current value
(Do not use for values from the data set).

ValueStep

double

R+W

Interval corresponding to one click for the mouse wheel or spinbox.

DefaultValue

double

R+W

Default value.

DecimalAccuracy

integer

R+W

Number of decimal places.

ShowThousandsSeparator

boolean

R+W

Show the locale separator for thousands.

Date field (DateField)

Date values are defined by the data type long and are displayed in ISO format: YYYYMMDD, for example 20190304 for 04 March 2019. To use this type with getDate and updateDate, and with the type com.sun.star.util.Date, see the examples.

Table 7: Date field properties

Name

Data type

Datatype since LO 4.1.1

R/W

Property

DateMin

long

com.sun.star.util.Date

R+W

Minimum acceptable entry date.

DateMax

long

com.sun.star.util.Date

R+W

Maximum acceptable entry date.

Date

long

com.sun.star.util.Date

R+(W)

Current value
(Do not use for values from the data set).

DateFormat

integer

 

R+W

OS-specific date format:
0 = short Date (simple)
1 = short Date dd.mm.yy (2-digit year)
2 = short Date dd.mm.yyyy (4-digit year)
3 = long Date (includes day of the week and month name)
Further possibilities can be found in the form definition or in the API Reference.

DefaultDate

long

com.sun.star.util.Date

R+W

Default value.

DropDown

boolean

 

R+W

Show a drop-down monthly calendar.

Time field (TimeField)

Time values are also of the type long.

Table 8: Time field properties

Name

Data type

Data type from LO 4.1.1

R/W

Property

TimeMin

long

com.sun.star.util.Time

R+W

Minimum acceptable entry value.

TimeMax

long

com.sun.star.util.Time

R+W

Maximum acceptable entry value.

Time

long

com.sun.star.util.Time

R+(W)

Current value
(Do not use for values from the data set).

TimeFormat

integer

 

R+W

Time format:
0 = short as hh:mm (hours, minutes, 24 hour clock)
1 = long as hh:mm:ss (same thing with Seconds, 24 hour clock)
2 = short as hh:mm (12 hour clock with AM/PM)
3 = long as hh:mm:ss (12 hour clock with AM/PM)
4 = short entry for a time duration
5 = long entry for a time duration

DefaultTime

long

com.sun.star.util.Time

R+W

Default value.

Currency field (CurrencyField)

A currency field is a numeric field with the following additional possibilities.

Table 9: Currency field properties

Name

Datatype

R/W

Property

CurrencySymbol

string

R+W

Currency symbol for display only.

PrependCurrencySymbol

boolean

R+W

Symbol is displayed before the number.

Formated field (FormattedControl)

A formatted control can be used as desired for numbers, currency or date/time. Very many of the properties already described apply here to but with different names.

Table 10: Formatted field properties

Name

Data type

L/S

Property

CurrentValue

variant

R

Current value of the contents. The actual data type depends on the contents and format.

EffectiveValue

R+(W)

EffectiveMin

double

R+W

Minimum acceptable entry value.

EffectiveMax

double

R+W

Maximum acceptable entry value.

EffectiveDefault

variant

R+W

Default value.

FormatKey

long

R+(W)

Format for display and entry. There is no easy way to alter this using a macro.

EnforceFormat

boolean

R+W

Format is tested during entry. Only certain characters and combinations are allowed.

Listbox (ListBox)

Read and write access to the value lying behind the selected line is somewhat complicated but possible.

Table 11: List box properties

Name

Data type

R/W

Property

ListSource

array of string

R+W

Data source: Source of the list contents or name of the data set that provides the visible entry.

ListSourceType

integer

R+W

Type of data source:
0 = Value list
1 = Table
2 = Query
3 = Result set from a SQL command
4 = Result of a database command
5 = Field names from a database-table

StringItemList

array of string

R

List entries available for selection.

ItemCount

integer

R

Number of available list entries

ValueItemList

array of string

R

List of values to be passed from the form to the table.

DropDown

boolean

R+W

Drop-down list.

LineCount

integer

R+W

Total displayed lines when fully dropped down.

MultiSelection

boolean

R+W

Multiple selection intended.

SelectedItems

array of integer

R+W

List of selected entries as a list of positions in the overall entry list.

The first selected element from the list field is obtained like this:

oControl = oForm.getByName("Name of the Listbox")

sEintrag = oControl.ValueItemList( oControl.SelectedItems(0) )

Note

Since LibreOffice 4.1, the value passed to the database can be determined directly.

oControl = oForm.getByName("Name of the Listbox")
iD = oControl.getCurrentValue()

getCurrentValue() returns the value that will be stored in the database table. In listboxes this depends on the field to which they are bound ( BoundField ).

Up to and including LibreOffice 4.0, this function returned the displayed content, not the underlying value in the table.

Please note that the entry is an “array of string”, should the query for a list field be exchanged to restrict a selection option:

Sub Listenfeldfilter

Dim stSql(0) As String

Dim oDoc As Object

Dim oDrawpage As Object

Dim oForm As Object

Dim oFeld As Object

oDoc = thisComponent

oDrawpage = oDoc.drawpage

oForm = oDrawpage.forms.getByName("MainForm")

oFeld = oForm.getByname("Listenfeld")

stSql(0) = "SELECT ""Name"", ""ID"" FROM ""Filter_Name"" ORDER BY ""Name"""

oFeld.ListSource = stSql

oFeld.refresh

End Sub

Combo boxes (ComboBox)

In spite of having similar functionality as listboxes, the properties of comboboxes are somewhat different. See the example “Comboboxes as listboxes with an entry option” on page 1.

Table 12: Combo box properties

Name

Data type

R/W

Property

Autocomplete

boolean

R+W

Fill automatically.

StringItemList

array of string

R+W

List entries available for use.

ItemCount

integer

R

Number of available list entries.

DropDown

boolean

R+W

Drop-down list.

LineCount

integer

R+W

Number of rows shown when dropped down.

Text

string

R+W

Currently displayed text.

DefaultText

string

R+W

Default entry.

ListSource

string

R+W

Name of the data source that provides the list entries.

ListSourceType

integer

R+W

Type of data source. Same possibilities as for listboxes (only the choice of Value list is ignored).

Checkboxes (CheckBox) and radio buttons (RadioButton)

Option Buttons can also be used.

Table 13: Check box and radio button properties

Name

Data type

R/W

Property

Label

string

R+W

Title (label)

State

short

R+W

Status
0 = not selected
1 = selected
2 = undefined

MultiLine

boolean

R+W

Line breaks for long text.

Pattern Field (PatternField)

In addition to the properties for simple text, the following are of interest:

Table 14: Pattern field properties

Name

Data type

R/W

Property

EditMask

string

R+W

Input mask.

LiteralMask

string

R+W

Character mask.

StrictFormat

boolean

R+W

Format testing during input.

Table control (GridControl)

Table 15: Table control properties

Name

Data type

R/W

Property

Count

long

R

Number of columns.

ElementNames

array of string

R

List of column names.

HasNavigationBar

boolean

R+W

Navigation bar available.

RowHeight

long

R+W

Row height.

FixedText – also called Label

Table 16: Fixed text / label properties

Name

Data type

R/W

Property

Label

string

R+W

Text displayed.

MultiLine

boolean

R+W

Line breaks for long text.

Group Boxes (GroupBox)

There are no properties for group boxes that are normally processed using macros. It is the status of the individual option fields that matters.

Buttons

CommandButton or ImageButton

Table 17: Command and image button properties

Name

Data type

R/W

Property

Label

string

R+W

Title – Label text.

State

short

R+W

Default state selected for toggling.

MultiLine

boolean

R+W

Line breaks for long text.

DefaultButton

boolean

R+W

Whether this is a default button.

Navigation bar (NavigationBar)

Further properties and methods associated with navigation – for example filters and changing the record pointer – are controlled using the form.

Table 18: Navigation bar properties

Name

Data type

R/W

Property

IconSize

short

R+W

Size of icons.

ShowPosition

boolean

R+W

Position can be entered and is displayed.

ShowNavigation

boolean

R+W

Allows navigation.

ShowRecordActions

boolean

R+W

Allows record actions.

ShowFilterSort

boolean

R+W

Allows filter sorting.

Methods for forms and controls

The data type of the parameter is indicated by an abbreviation:

Navigating in a data set

These methods work both in forms and in the results set from a query.

“Cursor” in the description means the record pointer.

Table 19: Navigation methods: Testing for the position of the cursor

Name

Data type

Description

isBeforeFirst

boolean

The cursor is before the first record. This is the case if it has not yet been reset after entry.

isFirst

boolean

Shows if the cursor is on the first entry.

isLast

boolean

Shows if the cursor is on the last entry.

isAfterLast

boolean

The cursor is after the last row when it is moved on with next.

getRow

long

Current row number.

Table 20: Navigation methods: Setting the cursor

For boolean data types, True means that the navigation was successful.

Name

Data type

Description

beforeFirst

–

Moves before the first row.

first

boolean

Moves to the first row.

previous

boolean

Goes back one row.

next

boolean

Goes forward one row.

last

boolean

Goes to the last record.

afterLast

–

Goes after the last record.

absolute(n)

boolean

Goes to the row with the given row number.

relative(n)

boolean

Goes backwards or forwards by the given amount: forwards for positive and backwards for negative arguments.

Table 21: Navigation methods: Methods affecting the current record status

Name

Data type

Description

refreshRow

–

Reads the original values for the row back in.

rowInserted

boolean

Indicates if this is a new row.

rowUpdated

boolean

Indicates if the current row has been altered.

rowDeleted

boolean

Indicates if the current row has been deleted.

Editing rows of data

The methods used for reading are available for any form or data set. Methods for alteration and storage can be used only for editable data sets (usually tables, not queries).

Table 22: Methods for editing rows of data: Methods for the whole row

Name

Data type

Description

insertRow

–

Saves a new row.

updateRow

–

Confirms alteration of the current row.

deleteRow

–

Deletes the current row.

cancelRowUpdates

–

Reverses changes in the current row.

moveToInsertRow

–

Moves the cursor into a row corresponding to a new record.

moveToCurrentRow

–

After the entry of a new record, returns the cursor to its previous position.

Table 23: Methods for editing rows of data: Reading values

Name

Data type

Description

getString(c)

string

Gives the content of the column as a character string.

getBoolean(c)

boolean

Gives the content of the column as a boolean value.

getByte(c)

byte

Gives the content of the column as a single byte.

getShort(c)

short

Gives the content of the column as an integer.

getInt(c)

integer

Gives the content of the column as an integer.

getLong(c)

long

Gives the content of the column as an integer.

getFloat(c)

float

Gives the content of the column as a single precision decimal number.

getDouble(c)

double

Gives the content of the column as a double precision decimal number. The automatic conversions carried out by Basic makes this a suitable type for decimal and currency fields.

getBytes(c)

array of bytes

Gives the content of the column as an array of single bytes.

getDate(c)

Date

Gives the content of the column as a date.

getTime(c)

Time

Gives the content of the column as a time value.

getTimestamp(c)

DateTime

Gives the content of the column as a timestamp (date and time).

wasNull

boolean

Indicates if the value of the most recently read column was NULL.

Note

In Basic itself, date and time values are both given the type DATE. To access dates in data sets, various types are available: com.sun.star.util.Date for a date, com.sun.star.util.Time for a time, and com.sun.star.util.DateTime for a timestamp.

Table 24: Methods for saving values

Name

Data type

Description

updateNull(c)

–

Sets the column content to NULL.

updateBoolean(c,b)

–

Changes the content of column c to the logical value b.

updateByte(c,x)

–

Stores byte x in column c.

updateShort(c,n)

–

Stores the integer n in column c.

updateInt(c,n)

–

Stores the integer n in column c.

updateLong(c,n)

–

Stores the integer n in column c.

updateFloat(c,n)

–

Stores the decimal number n in column c.

updateDouble(c,n)

–

Stores the decimal number n in column c.

updateString(c,s)

–

Stores the string s in column c.

updateBytes(c,x)

–

Stores the byte array x in column c.

updateDate(c,d)

–

Stores the date d in column c.

updateTime(c,d)

–

Stores the time d in column c.

updateTimestamp(c,d)

–

Stores the timestamp d in column c.

Editing individual values

This method uses the BoundField property of a control to read or modify the content of the corresponding column. It corresponds almost exactly to the method described in the previous section, except that the column number is not given.

Table 25: Methods for editing individual values: reading values

Name

Data type

Description

getString

string

Gives the content of the field as a character string.

getBoolean

boolean

Gives the content of the field as a logical value.

getByte

byte

Gives the content of the field as a single byte.

getShort

short

Gives the content of the field as an integer.

getInt

integer

Gives the content of the field as an integer.

getLong

long

Gives the content of the field as an integer.

getFloat

float

Gives the content of the field as a single-precision decimal value.

getDouble

double

Gives the content of the field as a double-precision decimal number. The automatic conversions carried out by Basic makes this a suitable type for decimal and currency fields.

getBytes

array of bytes

Gives the content of the field as an array of bytes.

getDate

Date

Gives the content of the field as a date.

getTime

Time

Gives the content of the field as a time.

getTimestamp

DateTime

Gives the content of the field as a timestamp.

wasNull

boolean

Indicates if the value of the most recently read column was NULL.

Note

In Basic itself, date and time values are both given the type DATE. To access dates in data sets, various types are available: com.sun.star.util.Date for a date, com.sun.star.util.Time for a time, and com.sun.star.util.DateTime for a timestamp.

Table 26: Methods for editing individual values: storing values

Name

Data type

Description

updateNull

–

Sets the content of the column to NULL.

updateBoolean(b)

–

Sets the content of the column to the logical value b.

updateByte(x)

–

Stores the byte x in the column.

updateShort(n)

–

Stores the integer n in the column.

updateInt(n)

–

Stores the integer n in the column.

updateLong(n)

–

Stores the integer n in the column.

updateFloat(n)

–

Stores the decimal number n in the column.

updateDouble(n)

–

Stores the decimal number n in the column.

updateString(s)

–

Stores the character string s in the column.

updateBytes(x)

–

Stores the byte array x in the column.

updateDate(d)

–

Stores the date d in the column.

updateTime(d)

–

Stores the time d in the column.

updateTimestamp(d)

–

Stores the timestamp d in the column.

Parameters for prepared SQL commands

The methods which transfer the value of a pre-prepared SQL command (see “Pre-prepared SQL commands with parameters” on page 1) are similar to those in the previous section. The first parameter (denoted by I) is a numbered position within the SQL command.

Table 27: Methods for transferring the value of a pre-prepared SQL command

Name

Data type

Description

setNull(i, n)

–

Sets the content of the column to NULL. N is the SQL data type as given in the API Reference.

setBoolean(i, b)

–

Puts the given logical value b into the SQL command.

setByte(i, x)

–

Puts the given byte x into the SQL command.

setShort(i, n)

–

Puts the given integer n into the SQL command.

setInt(i, n)

–

Puts the given integer n into the SQL command.

setLong(i, n)

–

Puts the given integer n into the SQL command.

setFloat(i, n)

–

Puts the given decimal number into the SQL command.

setDouble(i, n)

setString(i, s)

–

Puts the given character string into the SQL command.

setBytes(i, x)

–

Puts the given byte array x into the SQL command.

setDate(i, d)

–

Puts the given date d into the SQL command.

setTime(i, d)

–

Puts the given time d into the SQL command.

setTimestamp(i, d)

–

Puts the given timestamp d into the SQL command.

clearParameters

–

Removes the previous values of all parameters from a SQL command.

Improving usability

For this first category of macro use, we show various possibilities for improving the usability of Base forms.

Automatic updating of forms

Often something is altered in a form and this alteration is required to appear in a second form on the same page. The following code snippet calls the reload method on the second form, causing it to refresh.

Sub Update

First the macro is named. The default designation for a macro is Sub. This may be written in upper or lower case. Sub allows a subroutine to run without returning a value. Further down by contrast a function is described, which does return a value.

The macro has the name Update. You do not need to declare variables because LibreOffice Basic automatically creates variables when they are used. If you misspell a variable, LibreOffice Basic silently creates a new variable without complaint. Use Option Explicit To prevent LibreOffice Basic from automatically creating variables; this is recommended by most programmers.

Therefore we usually start by declaring variables. All the variables declared here are objects (not numbers or text), so we add As Object to the end of the declaration. To remind us later of the type of the variables, we preface their names with an "o". In principle, though, you can choose almost any variable names you like.

Dim oDoc As Object

Dim oDrawpage As Object

Dim oForm As Object

The form lies in the currently active document. The container, in which all forms are stored, is named drawpage. In the form navigator this is the top-level concept, to which all the forms are subsidiary.

In this example, the form to be accessed is named Display. Display is the name visible in the form navigator. So, for example, the first form by default is called Form1.

oDoc = thisComponent

oDrawpage = oDoc.drawpage

oForm = oDrawpage.forms.getByName("Display")

Since the form has now been made accessible and the point at which it can be accessed is saved in the variable oForm, it is now reloaded (refreshed) with the reload() command.

oForm.reload()

End Sub

The subroutine begins with SUB so it must end with End Sub.

This macro can now be selected to run when another form is saved. For example, on a cash register (till), if the total number of items sold and their stock numbers (read by a barcode scanner) are entered into one form, another form in the same open window can show the names of all the items, and the total cost, as soon as the form is saved.

Filtering records

The filter itself can function perfectly well in the form described in Chapter 8, Database Tasks. The variant shown below replaces the Save button and reads the listboxes again, so that a chosen filter from one listbox can restrict the choices available in the other listbox.

Note

See also the database Example_Search_and_Filter.odb associated with this book.

Sub Filter

Dim oDoc As Object

Dim oDrawpage As Object

Dim oForm1 As Object

Dim oForm2 As Object

Dim oFieldList1 As Object

Dim oFieldList2 As Object

oDoc = thisComponent

oDrawpage = oDoc.drawpage

First the variables are defined and set to access the set of forms. This set comprises the two forms "Filter" and "Display". The listboxes are in the "Filter" form and have the names "List_1" and "List_2".

oForm1 = oDrawpage.forms.getByName("filter")

oForm2 = oDrawpage.forms.getByName("display")

oFieldList1 = oForm1.getByName("listbox1")

oFieldList2 = oForm1.getByName("listbox2")

First the contents of the listboxes are transferred to the underlying form using commit(). The transfer is necessary, because otherwise the change in a listbox will not be recognized when saving. The commit() instruction need only be applied to the listbox that has just been accessed. After that the record is saved using updateRow(). In principle, our filter table contains only one record, which is written once at the beginning. This record is therefore overwritten continuously using an update command.

oFieldList1.commit()

oFieldList2.commit()

oForm1.updateRow()

The listboxes are meant to influence each other. For example, if one listbox is used to restrict displayed media to CDs, the other listbox should not include all the writers of books in its list of authors. A selection in the second listbox would then all too often result in an empty filter. That is why the listboxes must be read again. Strictly speaking, the refresh() command only needs to be carried out on the listbox that has not been accessed.

After this, form2, which should display the filtered content, is read in again.

oFieldList1.refresh()

oFieldList2.refresh()

oForm2.reload()

End Sub

Listboxes that are to be influenced using this method can be supplied with content using various queries.

The simplest variant is to have the listbox take its content from the filter results. Then a single filter determines which data content will be further filtered.

SELECT "Field_1" || ' - ' || "Count" AS "Display", "Field_1"

FROM ( SELECT COUNT( "ID" ) AS "Count", "Field_1" FROM "searchtable" GROUP BY "Field_1" )

ORDER BY "Field_1"

The field content and the number of hits is displayed. To get the number of hits, a sub-query is used. This is necessary as otherwise only the number of hits, without further information from the field, will be shown in the listbox.

The macro creates listboxes quite quickly by this action; they are filled with only one value. If a listbox is not NULL, it is taken into account by the filtering. After activation of the second listbox, only the empty fields and one displayed value are available to both listboxes. That may seem practical for a limited search. But what if a library catalog shows clearly the classification for an item, but does not show uniquely if this is a book, a CD or a DVD? If the classification is chosen first and the second listbox is then set to "CD", it must be reset to NULL in order to carry out a subsequent search that includes books. It would be more practical if the second listbox showed directly the various media types available, with the corresponding hit counts.

To achieve this aim, the following query is constructed, which is no longer fed directly from the filter results. The number of hits must be obtained in a different way.

SELECT

IFNULL( "Field_1" || ' - ' || "Count", 'empty - ' || "Count" ) AS "Display",

"Field_1"

FROM

( SELECT COUNT( "ID" ) AS "Count", "Field_1" FROM "Table"

WHERE "ID" IN

( SELECT "Table"."ID" FROM "Filter", "Table"

WHERE "Table"."Field_2" = IFNULL( "Filter"."Filter_2",

"Table"."Field_2" ) )

GROUP BY "Field_1" )

ORDER BY "Field_1"

This very complex query can be broken down. In practice it is common to use a VIEW for the sub-query. The listbox receives its content from a query relating to this VIEW.

The query in detail: The query presents two columns. The first column contains the view seen by a person who has the form open. This view shows the content of the field and, separated by a hyphen, the hits for this field content. The second column transfers its content to the underlying table of the form. Here we have only the content of the field. The listboxes thus draw their content from the query, which is presented as the filter result in the form. Only these fields are available for further filtering.

The table from which this information is drawn is actually a query. In this query the primary key fields are counted (SELECT COUNT( "ID" ) AS "Count"). This is then grouped by the search term in the field (GROUP BY "Field_1"). This query presents the term in the field itself as the second column. This query in turn is based on a further sub-query:

SELECT "Table"."ID" FROM "Filter", "Table"

WHERE "Table"."Field_2" =

IFNULL( "Filter"."Filter_2", "Table"."Field_2" )

This sub-query deals with the other field to be filtered. In principle, this other field must also match the primary key. If there are further filters, this query can be extended:

SELECT "Table"."ID" FROM "Filter", "Table" WHERE

"Table"."Field_2" = IFNULL( "Filter"."Filter_2", "Table"."Field_2" )

AND

"Table"."Field_3" = IFNULL( "Filter"."Filter_3", "Table"."Field_3" )

This allows any further fields that are to be filtered to control what finally appears in the listbox of the first field, "Field_1".

Finally the whole query is sorted by the underlying field.

What the final query underlying the displayed form, actually looks like, can be seen from Chapter 8, Database Tasks.

The following macro can control through a listbox which listboxes must be saved and which must be read in again.

The following subroutine assumes that the Additional Information property for each listbox contains a comma-separated list of all listbox names with no spaces. The first name in the list must be the name of that listbox.

Sub Filter_more_info(oEvent As Object)

Dim oDoc As Object

Dim oDrawpage As Object

Dim oForm1 As Object

Dim oForm2 As Object

Dim sTag As String

sTag = oEvent.Source.Model.Tag

An array (a collection of data accessible via an index number) is established and filled with the field names of the listboxes. The first name in the list is the name of the listbox linked to the event.

aList() = Split(sTag, ",")

oDoc = thisComponent

oDrawpage = oDoc.drawpage

oForm1 = oDrawpage.forms.getByName("filter")

oForm2 = oDrawpage.forms.getByName("display")

The array is run through from its lower bound ('Lbound()') to its upper bound ('Ubound()') in a single loop. All values which were separated by commas in the additional information, are now transferred successively.

For i = LBound(aList()) To UBound(aList())

If i = 0 Then

The listbox that triggered the macro must be saved. It is found in the variable aList(0). First the information for the listbox is carried across to the underlying table, and then the record is saved.

oForm1.getByName(aList(i)).commit()

oForm1.updateRow()

Else

The other listboxes must be refreshed, as they now contain different values depending on the first listbox.

oForm1.getByName(aList(i)).refresh()

End If

Next

oForm2.reload()

End Sub

The queries for this more usable macro are naturally the same as those already presented in the previous section.

Preparing data from text fields to fit SQL conventions

When data is stored in a SQL command, apostrophes in names such as “O’Connor” can cause problems. This is because single quotes ('') are used to enclose text that is to be entered into records. In such cases, we need an intermediate function to prepare the data appropriately.

Function String_to_SQL(st As StringString)

If InStr(st,"'") Then

st = Join(Split(st,"'"),"''")

End If

String_to_SQL = st

End Function

Note that this is a function, not a sub. A function takes a value as argument and then returns a value.

The text to be transferred is first searched to see if it contains an apostrophe. If this is the case, the text is split at this point – the apostrophe itself is the delimiter for the split – and joined together again with two apostrophes. This masks the SQL code. The function yields its result through the following call:

stTextnew = String_to_SQL(stTextold)

This simply means that the variable stTextold is reworked and the result stored in stTextnew. The two variables do not actually need to have different names. The call can be done with:

stText = String_to_SQL(stText)

This function is used repeatedly in the following macros so that apostrophes can also be stored using SQL commands.

Calculating values in a form in advance

Values which can be calculated using database functions are not stored separately in the database. The calculation takes place not during the entry into the form but after the record has been saved. If the form consists only of a single table control, this makes little difference. The calculated value can be read out immediately after data entry. But when forms have a set of different individual fields, the previous record may not be visible. In such cases it makes sense for the values that are otherwise calculated inside the database to be shown in the appropriate fields.

Note

See the database Example_direct_Calculation_Form.odb associated with this book.

The following three macros show how such a thing can be done in principle. Both macros are linked to the exit from the particular field. This also allows for the fact that the value in an existing field might subsequently be changed.

Sub Calculation_without_Tax(oEvent As Object)

Dim oForm As Object

Dim oField As Object

Dim oField2 As Object

oField = oEvent.Source.Model

oForm = oField.Parent

oField2 = oForm.getByName("price_without_tax")

oField2.BoundField.UpdateDouble(oField.getCurrentValue / 1.19)

If Not IsEmpty(oForm.getByName("quantity").getCurrentValue()) Then

total_calc2(oForm.getByName("quantity"))

End If

End Sub

If a value is entered into the price field, the macro is launched on leaving that field. In the same form as the price field is a field called price_without_tax. For this field BoundField.UpdateDouble is used to calculate the price without VAT. The data field is derived from a query which in principle carries out the same calculation but using saved data. In this way the calculated value is visible during data entry and also later during navigation through the record without being stored.

If the quantity field contains a value, a further calculation is carried out for the fields bound to it.

Sub Calculation_Total(oEvent As Object)

oField = oEvent.Source.Model

Calculation_Total2(oField)

End Sub

This short procedure serves only to transmit the solution of the following procedure when leaving the quantity field on the form.

Sub Calculation_Total2(oFeld As Object)

Dim oForm As Object

Dim oField2 As Object

Dim oField3 As Object

Dim oField4 As Object

oForm = oFeld.Parent

oField2 = oForm.getByName("price")

oField3 = oForm.getByName("total")

oField4 = oForm.getByName("tax_total")

oField3.BoundField.UpdateDouble(oField.getCurrentValue * oField2.getCurrentValue)

oField4.BoundField.UpdateDouble(oField.getCurrentValue * oField2.getCurrentValue -

oField.getCurrentValue * oField2.getCurrentValue / 1.19)

End Sub

This procedure is merely a way of affecting several fields at once. The procedure is launched from one field quantity, which contains the number of items bought. Using this field and the price field, the total and tax_total are calculated and transferred to the appropriate fields.

These procedures and queries have one shortcoming: the rate of VAT is effectively hard-coded into the program. It would be better to use an argument for this, related to the price, since VAT might vary and not be the same for all products. In such cases the appropriate value for VAT would need to read out of a form field.

Providing the current LibreOffice version

LibreOffice version 4.1 brought some changes to listfields and date values that make it necessary to determine the current version when executing macros in these areas. The following code serves this purpose:

Function OfficeVersion()

Dim aSettings, aConfigProvider

Dim aParams2(0) As New com.sun.star.beans.PropertyValue

Dim sProvider$, sAccess$

sProvider = "com.sun.star.configuration.ConfigurationProvider"

sAccess = "com.sun.star.configuration.ConfigurationAccess"

aConfigProvider = createUnoService(sProvider)

aParams2(0).Name = "nodepath"

aParams2(0).Value = "/org.openoffice.Setup/Product"

aSettings = aConfigProvider.createInstanceWithArguments(sAccess, aParams2())

OfficeVersion() = Array(aSettings.ooName,aSettings.ooSetupVersionAboutBox)

End Function

This function returns an array in which the first element is LibreOffice and the second is the full version number, for example 4.1.5.2.

Returning the value of listfields

Since LibreOffice 4.1, the value returned by a listbox to the database is stored in CurrentValue. This was not the case in previous versions, nor in OpenOffice or Apache OpenOffice. The following function will do the calculation. The LibreOffice version must be checked to see if it is later than LibreOffice 4.0.

Function ID_Determination(oField As Object) As Integer

a() = OfficeVersion()

If a(0) = "LibreOffice" And (LEFT(a(1),1) = 4 And RIGHT(LEFT(a(1),3),1) > 0) Or LEFT(a(1),1) > 4 Then

stContent = oField.currentValue

Else

Before LibreOffice 4.1, the value that was passed on was read out of the listbox’s value list. The visibly chosen record is SelectedItems(0). '0' because several additional values could be selected in a listbox.

stContent = oField.ValueItemList(oField.SelectedItems(0))

End If

If IsEmpty(stContent) Then

-1 is a value that is not used as an AutoValue and therefore will not exist in most tables as a foreign key.

ID_Determination = -1

Else

ID_Determination = Cint(stContent)

Convert to integer

End If

End Function

The function transmits the value as an integer. Most primary keys are automatically incrementing integers. When a foreign key does not satisfy this criterion, the return value must be adjusted to the appropriate type.

The displayed value of a listfield can be further determined using the field’s view property.

Sub Listfielddisplay

Dim oDoc As Object

Dim oForm As Object

Dim oListbox As Object

Dim oController As Object

Dim oView As Object

oDoc = thisComponent

oForm = oDoc.Drawpage.Forms(0)

oListbox = oForm.getByName("Listbox")

oController = oDoc.getCurrentController()

oView = oController.getControl(oListbox)

print "Displayed content: " & oView.SelectedItem

End Sub

The controller is used to access the view of the form. This determines what appears in the visual interface. The selected value is SelectedItem.

Limiting listboxes by entering initial letters

It can sometimes happen that the content of listboxes grows too big to handle. To make searching faster in such cases, it is useful to limit the content of the listbox to the values indicated by entering one or more initial characters. The listbox itself is provided with a SQL command that serves as a placeholder. This could be:

SELECT "Name", "ID" FROM "Table" ORDER BY "Name" LIMIT 5

This prevents Base from having to read a huge list of values when the form is opened.

The following macro is linked to Properties: Listbox > Events > Key released.

Global stListStart As String

Global lTime As Long

First, global variables are created. These variables are necessary to enable searching not only for a single letter but also, after further keys have been pressed, for combinations of letters.

The letters entered are stored sequentially in the global variable stListStart.

The global variable lTime is used to store the current time in seconds. If there is a long pause between keystrokes, the stListStart variable should be reset. For this reason, the time difference between successive entries is queried.

Sub ListFilter(oEvent As Object)

oField = oEvent.Source.Model

If oEvent.KeyCode < 538 Then

The macro is launched by a keystroke. Within the API, each key has a numeric code which can be looked up in the com::sun::star::awt::Key constant group reference section of the API Reference. Special characters like ä, ö, and ü have the KeyCode 0. All other letters and numbers have a KeyCode less than 538.

It is important to check the KeyCode because hitting the Tab key to move to another field will also launch the macro. The KeyCode for the Tab key is 1282, so any further code in the macro will not be executed.

Dim stSql(0) As String

The SQL code for the listbox is stored in an array. However, SQL commands count as single data elements, so the array is dimensioned as stSql(0).

When reading SQL code out of the listbox, please note that the SQL code is not directly accessible as text. Instead the code is available as a single array element: oField.ListSource(0).

After declaring variables for future use, the SQL command is split up. To get the field which is to be filtered, we split the code at the first comma. The field must therefore be placed first in the command. Then this code is split again at the first double quote character, which introduces the fieldname. Here this is done using simple arrays. The stField variable needs to have the quotation marks put back at the beginning. In addition Rtrim is used to prevent any space from occurring at the end of the expression.

Dim stText As String

Dim stField As String

Dim stQuery As String

Dim ar0()

Dim ar1()

ar0() = Split(oField.ListSource(0),",", 2)

ar1() = Split(ar0(0),"""", 2)

stFeld = """" & Rtrim(ar1(1))

A sort instruction is expected next in the SQL code. However commands in SQL can be in upper, lower or mixed case, so the inStr function is used instead of Split to find the ORDER character string. The last parameter for this function is 1, indicating that the search should be case-insensitive. Everything to the left of the ORDER string is to be used for constructing the new SQL code. This ensures that the code can also serve listfields which come from different tables or have been defined in SQL code using further conditions.

stQuery = Left(oField.ListSource(0), inStr(1,oField.ListSource(0), "ORDER",1)-1)

If inStr(stQuery, "LOWER") > 0 Then

stQuery = Left(stQuery, inStr(stQuery, "LOWER")-1)

ElseIf inStr(1,stQuery, "WHERE",1) > 0 Then

stQuery = stQuery & " AND "

Else

stQuery = stQuery & " WHERE "

End If

If the query contains the term LOWER, it means that it was created using this ListFilter procedure. Therefore in constructing the new query, we need go only as far as this position.

If this is not the case, and the query already contains the term WHERE (in upper or lower case), any further conditions to the query need to be prepended with AND.

If neither condition is fulfilled, a WHERE is attached to the existing code.

If lTime > 0 And Timer() - lTime < 5 Then

stListStart = stListStart & oEvent.KeyChar

Else

stListStart = oEvent.KeyChar

End If

lTime = Timer()

If a time value has been stored in the global variable, and the difference between this and the current time is less than 5 seconds, the entered letter is joined onto the previous one. Otherwise the letter is treated as a new single-letter entry. The listfield will then be re-filtered according to this entry. After this, the current time is stored in lTime.

stText = LCase( stListStart & "%")

stSql(0) = stQuery + "LOWER("+stField+") LIKE '"+stText+"' ORDER BY "+stField+""

oFeld.ListSource = stSql

oField.refresh

End If

End Sub

The SQL code is finally put together. The lower-case version of the field content is compared with the lower-case version of the entered letter(s). The code is inserted into the listbox and the field updated so that only the filtered content can be looked up.

Converting dates from a form into a date variable

Function DateValue(oField As Object) As Date

a() = OfficeVersion()

If a(0) = "LibreOffice" And (LEFT(a(1),1) = 4 And RIGHT(LEFT(a(1),3),1) > 0)

Or LEFT(a(1),1) > 4 Then

Here all LibreOffice versions from 4.1 onward are intercepted. For this purpose, the version number is split into its individual elements, and the major and minor release numbers are checked. This will work up to LibreOffice 9.

Dim stMonth As String

Dim stDay As String

stMonth = Right(Str(0) & Str(oField.CurrentValue.Month),2)

stDay = Right(Str(0) & Str(oField.CurrentValue.Day),2)

Datumswert = CDateFromIso(oField.CurrentValue.Year & stMonth & stDay)

Else

DateValue = CDateFromIso(oField.CurrentValue)

End If

End Function

Since LibreOffice 4.1.2, dates have been stored as arrays within form controls. This means that the current value of the control cannot be used to access the date itself. The date needs to be recreated from the day, month and year if it is to be used further in macros.

Searching data records

You can search database records without using a macro. However, the corresponding query that must be set up can be very complicated. A macro can solve this problem with a loop.

The following subroutine reads the fields in a table, creates a query internally, and finally writes a list of primary key numbers of records in the table that are retrieved by this search term. In the following description, there is a table called Searchtmp, which consists of an auto-incrementing primary key field (ID) and a field called Nr. that contains all the primary keys retrieved from the table being searched. The table name is supplied initially to the subroutine as a variable.

To get a correct result, the table must contain the content you are searching for as text and not as foreign keys. If necessary, you can create a VIEW for the macro to use.

Note

See the database Example_Search_and_Filter.odb associated with this book.

Sub Searching(stTable As String)

Dim oDataSource As Object

Dim oConnection As Object

Dim oSQL_Command As Object

Dim stSql As String

Dim oResult As Object

Dim oDoc As Object

Dim oDrawpage As Object

Dim oForm As Object

Dim oForm2 As Object

Dim oField As Object

Dim stContent As String

Dim arContent() As String

Dim inI As Integer

Dim inK As Integer

oDoc = thisComponent

oDrawpage = oDoc.drawpage

oForm = oDrawpage.forms.getByName("searchform")

oField = oForm.getByName("searchtext")

stContent = oField.getCurrentValue()

stContent = LCase(stContent)

The content of the search text field is initially converted into lower case so that the subsequent search function need only compare lower case spellings.

oDataSource = ThisComponent.Parent.DataSource

oConnection = oDataSource.GetConnection("","")

oSQL_Command = oConnection.createStatement()

First it must be determined if a search term has actually been entered. If the field is empty, it will be assumed that no search is required. All records will be displayed without further searching.

If a search term has been entered, the column names are read from the table being searched, so that the query can access the fields.

If stContent <> "" Then

stSql = "SELECT ""COLUMN_NAME"" FROM ""INFORMATION_SCHEMA"".""SYSTEM_COLUMNS"" WHERE ""TABLE_NAME"" = '" + stTable + "' ORDER BY ""ORDINAL_POSITION"""

oResult = oSQL_Statement.executeQuery(stSql)

Note

SQL formulas in macros must first be placed in double quotes like normal character strings. Field names and table names are already in double quotes inside the SQL formula. To create final code that transmits the double quotes properly, field names and table names must be given two sets of these quotes.

stSql = "SELECT ""Name"" FROM ""Table"";"

becomes, when displayed with the command MsgBox stSql,
SELECT "Name" FROM "Table";

The index of the array, in which the field names are written is initially set to 0. Then the query begins to be read out. As the size of the array is unknown, it must be adjusted continuously. That is why the loop begins with 'ReDim Preserve arContent(inI)' to set the size of the array and at the same time to preserve its existing contents. Next the fields are read and the array index incremented by 1. Then the array is dimensioned again and a further value can be stored.

InI = 0

While oResult.next

ReDim Preserve arContent(inI)

arContent(inI) = oResult.getString(1)

inI = inI + 1

Wend

stSql = "DROP TABLE ""searchtmp"" IF EXISTS"

oSQL_Command.executeUpdate (stSql)

Now the query is put together within a loop and subsequently applied to the table defined at the beginning. All case combinations are allowed for, since the content of the field in the query is converted to lower case.

The query is constructed such that the results end up in the "searchtmp" table. It is assumed that the primary key is the first field in the table (arContent(0)).

stSql = "SELECT """+arContent(0)+""" INTO ""searchtmp"" FROM """ + stTable
+ """ WHERE "

For inK = 0 To (inI - 1)

stSql = stSql+"LCase("""+arContent(inK)+""") LIKE '%"+stContent+"%'"

If inK < (inI - 1) Then

stSql = stSql+" OR "

End If

Next

oSQL_Command.executeQuery(stSql)

Else

stSql = "DELETE FROM ""searchtmp"""

oSQL_Command.executeUpdate (stSql)

End If

The display form must be reloaded. Its data source is a query, in this example Searchquery.

oForm2 = oDrawpage.forms.getByName("display")

oForm2.reload()

End Sub

This creates a table that is to be evaluated by the query. As far as possible, the query should be constructed so that it can subsequently be edited. A sample query is shown:

SELECT * FROM "searchtable" WHERE "Nr." IN ( SELECT "Nr." FROM "searchtmp" ) OR "Nr." = CASE WHEN ( SELECT COUNT( "Nr." ) FROM "searchtmp" ) > 0 THEN '0' ELSE "Nr." END

All elements of the searchtable are included, including the primary key. No other table appears in the direct query; therefore no primary key from another table is needed and the query result remains editable.

The primary key is saved in this example under the name Nr. The macro reads precisely this field. There is an initial check to see if the content of the Nr. field appears in the searchtmp table. The IN operator is compatible with multiple values. The sub-query can also yield several records.

For larger amounts of data, value matching by using the IN operator quickly slows down. Therefore it is not a good idea to use an empty search field simply to transfer all primary key fields from searchtable into the searchtmp table and then view the data in the same way. Instead an empty search field creates an empty searchtmp table, so that no records are available. This is the purpose of the second half of the condition:

OR "Nr." = CASE WHEN ( SELECT COUNT( "Nr." ) FROM "searchtmp" ) > 0 THEN '-1' ELSE "Nr." END

If a record is found in the Searchtmp table, it means that the result of the first query is greater than 0. In this case: "Nr." = '-1' (here we need a number which cannot occur as a primary key, so '-1'is a good value). If the query yields precisely 0 (which will be the case if no records are present), then "Nr." = "Nr.". This will list every record which has a Nr. As Nr. is the primary key, this means all records.

Highlighting search terms in forms and results

With a large text field, it is often unclear where matches to a search term occur. It would be nice if the form could highlight the matches. It should look something like that shown in Figure 5.

Figure 5: Form to highlight search matches

Bild2

To get a form to work like this, we need a couple of extra items in our box of tricks.

Note

See the database Example_Autotext_Searchmarkin_Spelling.odb associated with this book.

The operation of a search field like this has already been explained. A filter table is created and a form is used to write the current values of a single record into this table. The main form is provided with its content using a query which looks like this:

SELECT "ID", "memo"

FROM "table"

WHERE LOWER ( "memo" ) LIKE '%' || LOWER (

( SELECT "searchtext" FROM "filter" WHERE "ID" = TRUE ) ) || '%'

When search text is entered, all records in the table “Table” that have the search text in the “memo” field are displayed. The search is not case-sensitive.

If no search text is entered, all the records in the table are displayed. As the primary key of this table is included in the query, the latter can be edited.

Figure 6: Setting the text type to multi-line with formatting

Bild6

In the form, in addition to the ID field for the primary key, there is a field called MemoFormat which has been configured (using Properties > General > Text type > Multi-line with formatting) to show colored as well as black text. Careful consideration of the properties of the text field reveals that the Data tab has now disappeared. This is because data cannot be entered into a field that has additional formatting which the database itself cannot store. Nevertheless, it is still possible to get text into this field, to mark it up, and to transfer it out after an update by using a macro.

The ContentRead procedure serves to transfer the content of the database field “memo” into the formatted text field MemoFormat, and to format it so that any text corresponding to that in the search field will be highlighted.

The procedure is bound to Form > Events > After record change.

Sub ContentRead(oEvent As Object)

Dim inMemo As Integer

Dim oField As Object

Dim stSearchtext As String

Dim oCursor As Object

Dim inSearch As Integer

Dim inSearchOld As Integer

Dim inLen As Integer

oForm = oEvent.Source

inMemo = oForm.findColumn("memo")

oField = oForm.getByName("MemoFormat")

oField.Text = oForm.getString(inMemo)

First the variables are defined. Then the table field “memo” is searched from the form and the getString() function is used to read the text from the numbered column. This is transferred into the field which can be formatted but which has no link to the database: MemoFormat.

Initial tests showed that the form opened but the form toolbar at the bottom was no longer created. Therefore a very short wait of 5/1000 seconds was built in. After this the displayed content is read out of the FormFilter (which is parallel to the Form in the forms hierarchy).

Wait 5

stSearchtext = oForm.Parent.getByName("FormFilter").getByName("Search").Text

To be able to format text, an (invisible) TextCursor must be created in the field that contains the text. The default display of the text uses a 12-point serif font which may not occur in other parts of the form and cannot be directly customized using the form control properties. In this procedure, the text is set to the desired appearance right at the beginning. If this is not done, differences in formatting can cause the upper boundary of the text in the field to be cut off. In early tests, only 2/3 of the first line was legible.

In order for the invisible cursor to mark the text, It is set initially to the beginning of the field and then to the end. The argument in both cases is true. Next come the specifications for font size, font face, color, and weight. Then the cursor is set back to the beginning again.

oCursor = oField.createTextCursor()

oCursor.gotoStart(true)

oCursor.gotoEnd(true)

oCursor.CharHeight = 10

oCursor.CharFontName = "Arial, Helvetica, Tahoma"

oCursor.CharColor = RGB(0,0,0)

oCursor.CharWeight = 100.000000 'com::sun::star::awt::FontWeight

oCursor.gotoStart(false)

If there is text in the field and an entry has been made requesting a search, this text is now searched to find the search string. The outer loop asks first if these conditions are met; the inner one establishes if the search string is really in the text in the MemoFormat field. These settings could actually be omitted, since the query on which the form is based only displays text that fulfills these conditions.

If oField.Text <> "" And stSearchtext <> "" Then

If inStr(oField.Text, stSearchtext) Then

inSearch = 1

inSearchOld = 0

inLen = Len(stSearchtext)

The text is searched for the search string. This takes place in a loop which ends when no further matches are displayed. InStr() returns the location of the first character of the search string in the specified display format, independent of case. The loop is controlled by the requirement that at the end of each cycle, the start of inSearch has been incremented by 1 (-1 in the first line of the loop and +2 in the last line). For each cycle, the cursor is moved to the initial position without marking using oCursor.goRight(Position, false), and then to the right with marking by the length of the search string. Then the desired formatting (blue and somewhat bolder) is applied and the cursor moved back to its next starting point for the next run.

Do While inStr(inSearch, oField.Text, stSearchtext) > 0

inSearch = inStr(inSearch, oField.Text, stSearchtext) - 1

oCursor.goRight(inSearch-inSearchOld,false)

oCursor.goRight(inLen,true)

oCursor.CharColor = RGB(102,102,255)

oCursor.CharWeight = 110.000000

oCursor.goLeft(inLen,false)

inSearchOld = inSearch

inSearch = inSearch + 2

Loop

End If

End If

End Sub

The ContentWrite procedure serves to transfer the content of the formattable text field MemoFormat into the database. This proceeds independently of whether any alteration takes place.

The procedure is bound to Form > Events > Before record change.

Sub ContentWrite(oEvent As Object)

Dim oForm As Object

Dim inMemo As Integer

Dim loID As Long

Dim oField As Object

Dim stMemo As String

oForm = oEvent.Source

If InStr(oForm.ImplementationName, "ODatabaseForm") Then

The trigger event is implemented twice. Only the implementation name which ends with OdatabaseForm gives the correct access to the record (implementations are explained on page 1).

If Not oForm.isBeforeFirst() And Not oForm.isAfterLast() Then

When the form is read or reloaded, the cursor stands before the current record. Then if an attempt is made, you get the message “Invalid cursor status”.

inMemo = oForm.findColumn("memo")

loID = oForm.findColumn("ID")

oField = oForm.getByName("MemoFormat")

stMemo = oField.Text

If stMemo <> "" Then

oForm.updateString(inMemo,stMemo)

End If

If stMemo <> "" And oForm.getString(loID) <> "" Then

oForm.UpdateRow()

End If

End If

End If

End Sub

The “memo” table field is located from the data source of the form, along with that for “ID”. If the field MemoFormat contains text, it is transferred into the Memo field of the data source using oForm.updateString(). Only if there is an entry in the ID field (in other words a primary key has been set) does an update follow. Otherwise a new record is inserted through the normal working of the form; the form recognizes the change and stores it independently.

Checking spelling during data entry

This macro can be used for multi-line formatted text fields. As in the previous chapter, the content of each record must first be written and then the new record can be loaded into the form control. The procedures TransferContent and WriteContent differ only in the point at which the search function can be bracketed out.

Figure 7: Form to check spelling

Bild7

The spelling checker is launched in the above form whenever a space or a return is hit within the form control. In other words, it runs at the end of each word. It could also be linked to the control losing focus to ensure that the last word is checked.

The procedure is bound to Form > Events > Key released.

SUB MarkWrongWordsDirect(oEvent As Object)

GlobalScope.BasicLibraries.LoadLibrary("Tools")

The RTrimStr function is used to remove any punctuation mark at the end of the string. Otherwise all words which ended with a comma, full stop or other punctuation mark would show up as spelling mistakes. In addition, LTrimChar is used to remove brackets at the beginning of words.

Dim aProp() As New com.sun.star.beans.PropertyValue

Dim oLinuSvcMgr As Object

Dim oSpellChk As Object

Dim oField As Object

Dim arText()

Dim stWord As String

Dim inlenWord As Integer

Dim ink As Integer

Dim i As Integer

Dim oCursor As Object

Dim stText As Object

oLinguSvcMgr = createUnoService("com.sun.star.linguistic2.LinguServiceManager")

If Not IsNull(oLinguSvcMgr) Then

oSpellChk = oLinguSvcMgr.getSpellChecker()

End If

First all variables are declared. Then the Basic spell-checking module SpellChecker is accessed. It will be this module that will actually check individual words for correctness.

oField = oEvent.Source.Model

ink = 0

If oEvent.KeyCode = 1280 Or oEvent.KeyCode = 1284 Then

The event that launches the macro is a keystroke. This event includes a code, the KeyCode, for each individual key. The KeyCode for the Return key is 1280, the one for the space is 1284. Like many other pieces of information, these items are retrieved through the Xray tool. If space or return is pressed, the spelling is checked. It is launched, in other words, at the end of each word. Only the test for the last word does not occur automatically.

Each time the macro runs, all words in the text are checked. Checking individual words might also be possible but would take a lot more work.

The text is split up into single words. The delimiter is the space character. Before that, words split by line breaks must be joined together again, or the pieces might be mistaken for complete words.

stText = Join(Split(oField.Text,CHR(10))," ")

stText = Join(Split(stText,CHR(13))," ")

arText = Split(RTrim(stText)," ")

For i = LBound(arText) To Ubound(arText)

stWord = arText(i)

inlenWord = len(stWord)

stWord = Trim( RtrimStr( RtrimStr( RtrimStr( RtrimStr( RtrimStr(
RtrimStr(stWord,","), "."),"?"),"!"),"."),")"))

stWord = LTrimChar(stWord,"(")

The individual words are read out. Their untrimmed length is needed for the following editing step. Only so can the position of the word within the whole text (which is necessary for the specific highlighting of spelling mistakes) be determined.

Trim is used to remove spaces, while RTrimStr removes commas and full stops at the end of the text and LTrimChar any punctuation marks at the beginning.

If stWord <> "" Then

oCursor = oField.createTextCursor()

oCursor.gotoStart(false)

oCursor.goRight(ink,false)

oCursor.goRight(inLenWord,true)

If Not oSpellChk.isValid(stWord, "en", aProp()) Then

oCursor.CharUnderline = 9

oCursor.CharUnderlineHasColor = True

oCursor.CharUnderlineColor = RGB(255,51,51)

Else

oCursor.CharUnderline = 0

End If

End If

ink = ink + inLenWord + 1

Next

End If

End Sub

If the word is not null, a text cursor is created. This cursor is moved without highlighting to the beginning of the text in the entry field. Then it jumps forward to the right, still without highlighting, to the term stored in the variable ink. This variable starts as 0, but after the first loop has run, it is equal to the length of the word (+1 for the following space). Then the cursor is moved to the right by the length of the current word. The font properties are modified to create the highlighted region.

The spellchecker is launched. It requires the word and the country code as arguments; without a country code everything counts as correct. The array argument is usually empty.

If the word is not in the dictionary, it is marked with a red wavy line. This type of underlining is represented here by '9'. If the word is found, there is no underline ('0'). This step is necessary because otherwise a word recognized as false and then corrected would continue to be shown with the red wavy line. It would never be removed because no conflicting format was given.

Comboboxes as listboxes with an entry option

A table with a single record can be directly created by using comboboxes and invisible numeric fields and the corresponding primary key entered into another table.

Tip

For the use of combo boxes instead of list boxes, see the database Example_Combobox_Listfield.odb associated with this book.

The Combobox control treats form fields for combined entry and choice of values (comboboxes) as listboxes with an entry option. For this purpose, in addition to the comboboxes in the form, the key field values which are to be transferred to the underlying table are stored in separate numeric fields. Fields can be declared as invisible. The keys from these fields are read in when the form is loaded and the combobox is set to show the corresponding content. If the content of the combobox is changed, it is saved and the new primary key is transferred into the corresponding numeric field to be stored in the main table.

If editable queries are used instead of tables, the text to be displayed in the combination fields can be directly determined from the query. A macro is then not required for this step.

An assumption for the functioning of the macro is that the primary key of the table which is the data source for the combination field is an automatically incrementing integer. It is also assumed that the field name for the primary key is ID.

Text display in comboboxes

This subroutine is to show text in the combobox according to the value of the invisible foreign key fields from the main form. It can also be used for listboxes which refer to two different tables. This might happen if, for example, the postcode in a postal address is stored separately from the town. In that case the postcode might be read from a table that contains only a foreign key for the town. The listbox should show postcode and town together.

Sub ShowText(oEvent As Object)

This macro should be bound to the following form event: 'After record change'.

The macro is called directly from the form. The trigger event is the source for all the variables the macro needs. Some variables have already been declared globally in a separate module and are not declared here again.

Dim oForm As Object

Dim oFieldList As Object

Dim stFieldValue As String

Dim inCom As Integer

Dim stQuery As String

oForm = oEvent.Source

In the form there is a hidden control from which the names of all the different comboboxes can be obtained. One by one, these comboboxes are processed by the macro.

aComboboxes() = Split(oForm.getByName("combofields").Tag,",")

For inCom = LBound(aComboboxes) TO Ubound(aComboboxes)

...

Next inCom

The additional information (Tag) attached to the hidden control contains this list of combobox names, separated by commas The names are written into an array and then processed within a loop. The loop ends with the NEXT term.

The combobox, which has replaced a listbox, is called oFieldList. To get the foreign key, we need the correct column in the table that underlies the form. This is accessible using the name of the table field, which is stored in the combobox’s additional information.

oFieldList = oForm.getByName(Trim(aComboboxes(inCom)))

stFieldID = oForm.getString(oForm.findColumn(oFieldList.Tag))

oFieldList.Refresh()

The combobox is read in again using Refresh()in case the content of the field has been changed by the entry of new data.

The query needed to provide the visible content of the combobox is based on the field underlying the control and the value determined for the foreign key. To make the SQL code usable, any sort operation that might be present is removed. Then a check is made for any relationship definitions (which will begin with the word WHERE). By default the InStr() function does not distinguish between upper and lower case, so all case combinations are covered. If there is a relationship, it means that the query contains fields from two different tables. We need to find the table that provides the foreign key for the link. The macro depends here on the fact that the primary key in every table is called ID.

If there is no relationship defined, the query accesses only one table. The table information can be discarded and the condition formulated directly using the foreign key value.

If stFieldID <> "" Then

stQuery = oFieldList.ListSource

If InStr(stQuery,"order by") > 0 Then

stSql = Left(stQuery, InStr(stQuery,"order by")-1)

Else

stSql = stQuery

End If

If InStr(stSql,"where") Then

st = Right(stSql, Len(stSql)-InStr(stSql,"where")-4)

If InStr(Left(st, InStr(st,"=")),".""ID""") Then

a() = Split(Right(st, Len(st)-InStr(st,"=")-1),".")

Else

a() = Split(Left(st, InStr(st,"=")-1),".")

End If

stSql = stSql + "AND "+a(0)+".""ID"" = "+stFieldID

Else

stSql = stSql + "WHERE ""ID"" = "+stFieldID

End If

Each field and table name must be entered into the SQL command with two sets of quotation marks. Quotation marks are normally interpreted by Basic as text string delimiters, so they no longer appear when the code is passed on to SQL. Doubling the quotation marks ensures that one set are passed on. ""ID"" signifies that the field "ID" will be accessed in the query, with the single set of quotes that SQL requires.

The query stored in the stSql variable is now carried out and its result saved in oResult.

oDatasource = ThisComponent.Parent.CurrentController

If Not (oDatasource.isConnected()) Then

oDatasource.connect()

End If

oConnection = oDatasource.ActiveConnection()

oSQL_Command = oConnection.createStatement()

oResult = oSQL_Command.executeQuery(stSql)

The result of the query is read in a loop. As with a query in the GUI, several fields and records could be shown. But the construction of this query requires only one result, which will be found in the first column (1) of the query result set. It is the record which provides the displayed content of the combobox. The content is text (getString()), hence the command oResult.getString(1).

While oResult.next

stFieldValue = oResult.getString(1)

Wend

The combobox must now be set to the text value retrieved by the query.

oFieldList.Text = stFieldValue

Else

If there is no value in the field for the foreign key oField, the query has failed and the combobox is set to an empty string.

oFieldList.Text = ""

End If

Next inCom

End Sub

This procedure manages the contact between the combobox and the foreign key available in a field of the form’s data source. This should be enough to show the correct values in comboboxes. Storage of new values would require a further procedure.

Transfering a foreign key value from a combobox to a numeric field

If a new value is entered into the combobox (and this after all is the purpose for which this macro was constructed), the corresponding primary key must be entered into the form's underlying table as a Foreign key.

Sub TextSelectionSaveValue(oEvent As Object)

This macro should be bound to the following form event: 'Before record action'.

After the variables have been declared (not shown here), we must first determine exactly which event should launch the macro. Before record action, two implementations are called in succession. It is important for the macro itself to retrieve the form object. This can be done in both implementations but in different ways. Here the implementation called OdatabaseForm is filtered out.

If InStr(oEvent.Source.ImplementationName,"ODatabaseForm") Then

...

End If

End Sub

This loop builds in the same start as the Display_text procedure:

oForm = oEvent.Source

aComboboxes() = Split(oForm.getByName("combofields").Tag,",")

For inCom = LBound(aComboboxes) To Ubound(aComboboxes)

...

Next inCom

The field oFieldList shows the text. It might lie inside a table control, in which case it is not possible to access it directly from the form. In such cases, the additional information for the hidden control comboboxes should contain the path to the field using “tablecontrol” combobox. Splitting this entry up will reveal how the combobox is to be accessed.

a() = Split(Trim(aComboboxen(inCom)),">")

If Ubound(a) > 0 Then

oFieldList = oForm.getByName(a(0)).getByName(a(1))

Else

oFieldList = oForm.getByName(a(0))

End If

Next the query is read from the combobox and split up into its individual parts. For simle comboboxes, the necessary items of information are the field name and table name:

SELECT "Field" FROM "Table"

This could in some cases be augmented by a sort instruction. Whenever two fields are to be put together in the combobox, more work will be required to separate them.

SELECT "Field1"||' '||"Field2" FROM "Table"

This query puts two fields together with a space between them. As the separator is a space, the macro will search for it and split the text into two parts accordingly. Naturally this will only work reliably if Field1 does not already contain text in which spaces are permitted. Otherwise, if the first name is “Anne Marie” and the surname “Müller”, the macro will treat “Anne” as the first name and “Marie Müller” as the surname. In such cases a more suitable separator should be used, which can then be found by the macro. In the case of names, this could be “Surname, Given name”.

Things get even more complicated if the two fields come from different tables:

SELECT "Table1"."Field1"||' > '||"Table2"."Field2"

FROM "Table1", "Table2"

WHERE "Table1"."ID" = "Table2"."ForeignID"

ORDER BY "Table1"."Field1"||' > '||"Table2"."Field2" ASC

Here the fields must be separated from one another, the table to which each field belongs must be established and the corresponding foreign keys determined.

stQuery = oFieldList.ListSource

aFields() = Split(stQuery, """")

stContent = ""

For i=LBound(aFields)+1 To UBound(aFields)

The content of the query is stripped of unnecessary ballast. The parts are reassembled into an array with an unusual character combination as separator. FROM separates the visible field display from the table names. WHERE separates the condition from the table names. Joins are not supported.

If Trim(UCASE(aFields(i))) = "ORDER BY" Then

Exit For

ElseIf Trim(UCASE(aFields(i))) = "FROM" Then

stContent = stcontent+" §§ "

ElseIf Trim(UCASE(aFields(i))) = "WHERE" Then

stContent = stcontent+" §§ "

Else

stContent = stContent+Trim(aFields(i))

End If

Next i

aContent() = Split(stContent, " §§ ")

In some cases the content of the visible field display comes from different fields:

aFirst() = Split(aContent(0),"||")

If UBound(aFirst) > 0 Then

If UBound(aContent) > 1 Then

The first part contains at least two fields. The fields begin with a table name. The second part contains two table names, which can be determined from the first part. The third part contains a relationship with a foreign key, separated by =:

aTest() = Split(aFirst(0),".")

NameTable1 = aTest(0)

NameTableField1 = aTest(1)

Erase aTest

stFieldSeperator = Join(Split(aFirst(1),"'"),"")

aTest() = Split(aFirst(2),".")

NameTable2 = aTest(0)

NameTableField2 = aTest(1)

Erase aTest

aTest() = Split(aContent(2),"=")

aTest1() = Split(aTest(0),".")

If aTest1(1) <> "ID" Then

NameTab12ID = aTest1(1)

IF aTest1(0) = NameTable1 Then

Position = 2

Else

Position = 1

End If

Else

Erase aTest1

aTest1() = Split(aTest(1),".")

NameTab12ID = aTest1(1)

If aTest1(0) = NameTable1 Then

Position = 2

Else

Position = 1

End If

End If

Else

The first part contains two field names without table names, possibly with separators. The second part contains the table names. There is no third part:

If UBound(aFirst) > 1 Then

NameTableField1 = aFirst(0)

stFieldSeperator = Join(Split(aFirst(1),"'"),"")

NameTableField2 = aFirst(2)

Else

NameTableField1 = aFirst(0)

NameTableField2 = aFirst(1)

End If

NameTable1 = aContent(1)

End If

Else

There is only one field from one table:

NameTableField1 = aFirst(0)

NameTable1 = aContent(1)

End If

The maximum character length that an entry can have is given by the ColumnSize function. The combobox cannot be used to limit the size as it may need to contain two fields at the same time.

LengthField1 = ColumnSize(NameTable1,NameTableField1)

If NameTableField2 <> "" Then

If NameTable2 <> "" Then

LengthField2 = ColumnSize(NameTable2,NameTableField2)

Else

LengthField2 = ColumnSize(NameTable1,NameTableField2)

End If

Else

LengthField2 = 0

End If

The content of the combobox is read out:

stContent = oFieldList.getCurrentValue()

Leading and trailing spaces and non-printing characters are removed if necessary.

stContent = Trim(stContent)

If stContent <> "" Then

If NameTableField2 <> "" Then

If a second table field exists, the content of the combobox must be split. To determine where the split is to occur, we use the field separator provided to the function as an argument.

a_stParts = Split(stContent, FieldSeparator, 2)

The last parameter signifies that the maximum number of parts is 2.

Depending on which entry corresponds to field 1 and which to field 2, the content of the combobox is now allocated to the individual variables. "Position = 2" serves here as a sign that the second part of the content stands for Field 2.

If Position = 2 Then

stContent = Trim(a_stParts(0))

If UBound(a_stParts()) > 0 Then

stContentField2 = Trim(a_stParts(1))

Else

stContentField2 = ""

End If

stContentField2 = Trim(a_stParts(1))

Else

stContentField2 = Trim(a_stParts(0))

If UBound(a_stParts()) > 0 Then

stContent = Trim(a_stParts(1))

Else

stContent = ""

End If

stContent = Trim(a_stParts(1))

End If

End If

It can happen that with two separable contents, the installed size of the combobox (text length) does not fit the table fields to be saved. For comboboxes that represent a single field, this is normally handled by suitably configuring the form control. Here by contrast, we need some way of catching such errors. The maximum permissible length of the relevant field is checked.

If (LengthField1 > 0 And Len(stContent) > LengthField1) Or (LengthField2 > 0 And Len(stContentField2) > LengthField2) Then

If the field length of the first or second part is too big, a default string is stored in one of the variables. The character Chr(13) is used to put in a line break.

stmsgbox1 = "The field " + NameTableField1 + " must not exceed " + Field1Length + "characters in length." + Chr(13)

stmsgbox2 = "The field " + NameTableField2 + " must not exceed " + Field2Length + "characters in length." + Chr(13)

If both field contents are too long, both texts are displayed.

If (LengthField1 > 0 And Len(stContent) > LengthField1) And (LengthField2 > 0 And Len(stContentField2) > LengthField2) Then

MsgBox("The entered text is too long." + Chr(13) + stmsgbox1 + stmsgbox2 + "Please shorten it.",64,"Invalid entry")

The display uses the MsgBox() function. This expects as its first argument a text string, then optionally a number (which determines the type of message box displayed), and finally an optional text string as a title for the window. The window will therefore have the title "Invalid entry" and the number '64' provides a box containing the Information symbol.

The following code covers any further cases of excessively long text that might arise.

ElseIf (Field1Length > 0 And Len(stContent) > Field1Length) Then

MsgBox("The entered text is too long." + Chr(13) + stmsgbox1 + "Please shorten it.",64,"Invalid entry")

Else

MsgBox("The entered text is too long." + Chr(13) + stmsgbox2 + "Please shorten it.",64,"Invalid entry")

End If

Else

If there is no excessively long text, the function can proceed. Otherwise it exits here.

Now the entries are masked so that any quotes that may be present will not generate an error.

stContent = String_to_SQL(stContent)

If stContentField2 <> "" Then

stContentField2 = String_to_SQL(stContentField2)

End If

First variables are preallocated which can subsequently be altered by the query. The variables inID1 and inID2 store the content of the primary key fields of the two tables. If a query yields no results, Basic assigns these integer variable a value of 0. However this value could also indicate a successful query returning a primary key value of 0; therefore the variable is preset to -1. HSQLDB cannot set this value for an autovalue field.

Next the database connection is set up, if it does not already exist.

inID1 = -1

inID2 = -1

oDatasource = ThisComponent.Parent.CurrentController

If Not (oDatasource.isConnected()) Then

oDatasource.connect()

End If

oConnection = oDatasource.ActiveConnection()

oSQL_Command = oConnection.createStatement()

If NameTableField2 <> "" And Not IsEmpty(stContentField2) And NameTable2 <> "" Then

If a second table field exists, a second dependency must first be declared.

stSql = "SELECT ""ID"" FROM """ + NameTable2 + """ WHERE """ + NameTableField2 + """='" + stContentField2 + "'"

oResult = oSQL_Command.executeQuery(stSql)

While oResult.next

inID2 = oResult.getInt(1)

Wend

If inID2 = -1 Then

stSql = "INSERT INTO """ + NameTable2 + """ (""" + NameTableField2 + """) VALUES ('" + stContentField2 + "') "

oSQL_Command.executeUpdate(stSql)

stSql = "CALL IDENTITY()"

If the content within the combobox is not present in the corresponding table, it is inserted there. The primary key value which results is then read. If it is present, the existing primary key is read in the same way. The function uses the automatically generated primary key fields (IDENTITY).

oResult = oSQL_Command.executeQuery(stSql)

While oResult.next

inID2 = oResult.getInt(1)

Wend

End If

The primary key for the second value is temporarily stored in the variable inID2 and then written as a foreign key into the table corresponding to the first value. According to whether the record from the first table was already available, the content is freshly saved (INSERT) or altered (UPDATE):

If inID1 = -1 Then

stSql = "INSERT INTO """ + NameTable1 + """ (""" + NameTableField1 + """,""" + NameTab12ID + """) VALUES ('" + stContent + "','" + inID2 + "') "

oSQL_Command.executeUpdate(stSql)

And the corresponding ID directly read out:

stSql = "CALL IDENTITY()"

oResult = oSQL_Command.executeQuery(stSql)

While oResult.next

inID1 = oResult.getInt(1)

Wend

The primary key for the first table must finally be read again so that it can be transferred to the form's underlying table.

Else

stSql = "UPDATE """ + NameTable1 + """ SET """ + NameTab12ID + """='" + inID2 + "' WHERE """ + NameTableField1 + """ = '" + stContent + "'"

oSQL_Command.executeUpdate(stSql)

End If

End If

In the case where both the fields underlying the combobox are in the same table (for example Surname and Firstname in the Name table), a different query is needed:

If NameTableField2 <> "" And NameTable2 = "" Then

stSql = "SELECT ""ID"" FROM """ + NameTable1 + """ WHERE """ + NameTableField1 + """='" + stContent + "' AND """ + NameTableField2 + """='" + stContentField2 + "'"

oResult = oSQL_Command.executeQuery(stSql)

While oResult.next

inID1 = oResult.getInt(1)

Wend

If inID1 = -1 Then

... and a second table does not exist:

stSql = "INSERT INTO """ + NameTable1 + """ (""" + NameTableField1 + """,""" + NameTableField2 + """) VALUES ('" + stContent + "','" + stContentField2 + "') "

oSQL_Command.executeUpdate(stSql)

Then the primary key is read again.

stSql = "CALL IDENTITY()"

oResult = oSQL_Command.executeQuery(stSql)

While oResult.next

inID1 = oResult.getInt(1)

Wend

End If

End If

IF NameTableField2 = "" Then

Now we consider the simplest case: The second table field does not exist and the entry is not yet present in the table. In other words, a single new value has been entered into the combobox.

stSql = "SELECT ""ID"" FROM """ + NameTable1 + """ WHERE """ + NameTableField1 + """='" + stContent + "'"

oResult = oSQL_Command.executeQuery(stSql)

While oResult.next

inID1 = oResult.getInt(1)

Wend

If inID1 = -1 Then

If there is no second field, the content of the box is inserted as a new record.

stSql = "INSERT INTO """ + NameTable1 + """ (""" + NameTableField1 + """) VALUES ('" + stContent + "') "

oSQL_Command.executeUpdate(stSql)

… and the resulting ID directly read out.

stSql = "CALL IDENTITY()"

oResult = oSQL_Command.executeQuery(stSql)

While oResult.next

inID1 = oResult.getInt(1)

Wend

End If

End If

The value of the primary key field must be determined, so that it can be transferred to the main part of the form.

Next the primary key value that has resulted from all these loops is transferred to the invisible field in the main table and the underlying database. The table field linked to the form field is reached by using 'BoundField'. 'updateInt' places an integer (see under numerical type definitions) in this field.

oForm.updateLong(oForm.findColumn(oFeldList.Tag),inID1)

End If

ELSE

If no primary key is to be entered, because there was no entry in the combobox or that entry was deleted, the content of the invisible field must also be deleted. updateNull() is used to fill the field with the database-specific expression for an empty field, NULL.

oForm.updateNULL(oForm.findColumn(oFeldList.Tag),NULL)

End If

NEXT inCom

End If

End Sub

Function to measure the length of the combobox entry

The following function gives the number of characters in the respective table column, so that entries that are too long do not just get truncated. A Function is chosen here to provide return values. A SUB has no return value that can be passed on and processed elsewhere.

Function ColumnSize(Tablename As String, Fieldname As String) As Integer

oDatasource = ThisComponent.Parent.CurrentController

If Not (oDatasource.isConnected()) Then

oDatasource.connect()

End If

oConnection = oDataSource.ActiveConnection()

oSQL_Command = oConnection.createStatement()

stSql = "SELECT ""COLUMN_SIZE"" FROM ""INFORMATION_SCHEMA"".""SYSTEM_COLUMNS"" WHERE ""TABLE_NAME"" = '" + Tablename + "' AND ""COLUMN_NAME"" = '" + Fieldname + "'"

oResult = oSQL_Command.executeQuery(stSql)

While oResult.next

i = oResult.getInt(1)

Wend

ColumnSize = i

End Function

Generating database actions

Sub GenerateRecordAction(oEvent As Object)

This macro should be bound to the When receiving focus event of the listbox. It is necessary that in all cases where the listbox is changed, the change is stored. Without this macro, there would be no change in the actual table that Base could recognize, since the combobox is not bound to the form.

This macro directly alters the form properties:

Dim oForm As Object

oForm = oEvent.Source.Model.Parent

oForm.IsModified = TRUE

End Sub

This macro is not necessary for forms that use queries for the content of comboboxes. Changes in comboboxes are registered directly.

Navigation from one form to another

A form is to be opened when a particular event occurs.

In the form control properties, on the line "Additional information" (tag), enter the name of the form. Further information can also be entered here, and subsequently separated out by using the Split() function.

Sub From_form_to_form(oEvent As Object)

Dim stTag As String

stTag = oEvent.Source.Model.Tag

aForm() = Split(stTag, ",")

The array is declared and filled with the form names, first the form to be opened and secondly the current form, which will be closed after the other has been opened.

ThisDatabaseDocument.FormDocuments.getByName( Trim(aForm(0)) ).open

ThisDatabaseDocument.FormDocuments.getByName( Trim(aForm(1)) ).close

End Sub

If instead, the other form is only to be opened when the current one is closed, for example where a main form exists and all other forms are controlled from it using buttons, the following macro should be bound to the form with Tools > Customize > Events > Document closed:

Sub Mainform_open

ThisDatabaseDocument.FormDocuments.getByName( "Mainform" ).open

End Sub

If the form documents are sorted within the ODB file into directories, the macro for changing the form needs to be more extensive:

Sub From_form_to_form_with_folders(oEvent As Object)

REM The form to be opened is given first.

REM If a form is in a folder, use "/" to define the relationship

REM so that the subfolder can be found.

Dim stTag As String

stTag = oEvent.Source.Model.Tag 'Tag is entered in the additional information

aForms() = Split(stTag, ",") 'Here the form name for the new form comes first, then the one for the old form

aForms1() = Split(aForms(0),"/")

aForms2() = Split(aForms(1),"/")

If UBound(aForms1()) = 0 Then

ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms1(0)) ).open

Else

ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms1(0)) ).getByName( Trim(aForms1(1)) ).open

End If

If UBound(aForms2()) = 0 Then

ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms2(0)) ).close

Else

ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms2(0)) ).getByName( Trim(aForms2(1)) ).close

End If

End Sub

Form documents that lie in a directory are entered into the Additional Information field as directory/form. This must be converted to:

...getByName("Directory").getByName("Form").

Hierarchical listboxes

Settings in one listfield are intended to influence directly the settings of another. For simple cases, this has already been described above in the section on record filtering. But supposing that the first listbox is meant to affect the content of the second listbox, which then affects the content of a third listbox, and so on.

Figure 8: Example list boxes for a hierarchical ordering of list boxes

Image1

In this example the first listbox (Jahrgang = Years) contains all school years. The Klasse (Classes) in each year are represented by letters. The Names are those of the class members.

Under normal circumstances, the Years listbox would show all 13 years, the Classes listbox all class letters and the Names listbox all pupils at the school.

If these are to be hierarchical listboxes, the choice of classes is restricted once a year has been selected. Only those class letters are shown that are actually present in that year. This might vary because, if pupil numbers are increasing, the number of classes in a year might also increase. The last listbox, Names, is very restricted. Instead of more than 1000 pupils, it would show only 30.

At the beginning, only the year can be selected. Once this has been done, the (restricted) list of classes is made available. Only at the end is the list of names given.

If the Years listbox is altered, the sequence must start again. If only the Classes listbox is altered, the year number for the last listbox remains valid

To create such a function, the form must be able to store an intermediate variable. This takes place in a hidden control.

The macro is bound to a change in the content of a listbox: Properties Listbox > Events > Changed. The necessary variables are stored in the additional information of the listbox.

Here is an example of the additional information provided:
MainForm,Year,hidden control,Listbox_2

The form is called MainForm. The current listbox is called Listbox1. This listbox shows the content of the table field Year and the following listboxes must be filtered according to this entry. The hidden control is designated by hidden_control and the existence of a second listbox (Listbox_2) is passed on to the filtering procedure.

Sub Hierarchical_control(oEvent As Object)

Dim oDoc As Object

Dim oDrawpage As Object

Dim oForm As Object

Dim oFieldHidden As Object

Dim oField As Object

Dim oField1 As Object

Dim stSql As String

Dim acontent()

Dim stTag As String

oField = oEvent.Source.Model

stTag = oField.Tag

oForm = oField.Parent

REM Tag goes into the Additional information field

REM It contains:

REM 0. Field name of field to be filtered in the table

REM 1. Field name of the hidden control that will store the filtered value

REM 2. Possible further listbox

REM The tag is read from the element that launches the macro. The variable is

REM passed to the procedure, and if necessary to all further listboxes

aFilter() = Split(stTag, ",")

stFilter = ""

After the variables have been declared, the content of the tag is passed to an array, so that individual elements can be accessed. Then the access to the various fields in the form is declared.

The listbox that called the macro is determined and its value read. Only if this value is not NULL will it be combined with the name of the field to be filtered, in our example Year, to make a SQL command. Otherwise the filter will stay empty. If the listboxes are meant for filtering a form, no hidden control is available. In this case, the filter value is stored directly in the form.

If Trim(aFilter(1)) = "" Then

If oField.getCurrentValue <> "" Then

stFilter = """"+Trim(aFilter(0))+"""='"+oField.getCurrentValue()+"'"

If a filter already exists (for example one dealing with Listbox 2, which is now being accessed), the new content is attached to the previous content stored in the hidden control.

If oForm.Filter <> ""

This must only happen when the same field has not yet been filtered. For example, if we are filtering for Year, a repetition of the filter will find no additional records for the Name listbox. A person can only be found in one year. We must therefore exclude the possibility that the filter name has already been used.

And InStr(oForm.Filter, """"+Trim(aFilter(0))+"""='") = 0 Then

stFilter = oForm.Filter + " AND " + stFilter

If a filter exists and the field that will be used for filtering is already present in the filter, the previous filtering on this fieldname must be deleted and a new filter created.

ElseIf oForm.Filter <> "" Then

stFilter = Left(oForm.Filter,

InStr(oForm.Filter, """"+Trim(aFilter(0))+"""='")-1) + stFilter

End If

End If

Then the filter is entered into the form. This filter can also be empty if the first listbox was selected and has no content.

oForm.Filter = stFilter

oForm.reload()

The same procedure will run if the form does not need to be filtered immediately. In this case, the filter value is stored in the mean time in a hidden control.

Else

oFieldHidden = oForm.getByName(Trim(aFilter(1)))

If oField.getCurrentValue <>"" Then

stFilter = """"+Trim(aFilter(0))+"""='"+oField.getCurrentValue()+"'"

If oFieldHidden.HiddenValue <> ""

And InStr(oFieldHidden.HiddenValue, """"+Trim(aFilter(0))+"""='") = 0 Then

stFilter = oFieldHidden.HiddenValue + " AND " + stFilter

ElseIf oFieldHidden.HiddenValue <> "" Then

stFilter = Left(oFieldHidden.HiddenValue,

InStr(oFieldHidden.HiddenValue, """"+Trim(aFilter(0))+"""='")-1) + stFilter

End If

End If

oFieldHidden.HiddenValue = stFilter

End If

If the Additional information has an entry numbered 4 (numbering begins at 0), the following listbox must be set to the corresponding entry from the caller listbox.

If UBound(aFilter()) > 1 Then

oField1 = oForm.getByName(Trim(aFilter(2)))

aFilter1() = Split(oField1.Tag,",")

The necessary data for the filtering is read from the Additional information (Tag) in the corresponding listbox. It is not possible to write only the fresh SQL code into the listbox and then to read the listbox values. Instead the values corresponding to the query must be written into the listbox directly.

The creation of the code starts from the fact that the table to which the form refers is the same one to which the listboxes refer. Such a listbox is not designed to transfer foreign keys to the table.

If oField.getCurrentValue <> "" Then

stSql = "SELECT DISTINCT """+Trim(aFilter1(0))+""" FROM """+oForm.Command+ """ WHERE "+stFilter+" ORDER BY """+Trim(aFilter1(0))+""""

oDatasource = ThisComponent.Parent.CurrentController

If Not (oDatasource.isConnected()) Then

oDatasource.connect()

End If

oConnection = oDatasource.ActiveConnection()

oSQL_Statement = oConnection.createStatement()

oQuery_result = oSQL_Statement.executeQuery(stSql)

The values are read into an array. The array is transferred directly into the listbox. The corresponding indices for the array are incremented within a loop.

inIndex = 0

While oQuery_result.next

ReDim Preserve aContent(inIndex)

acontent(inIndex) = oQuery_result.getString(1)

inIndex = inIndex+1

WEnd

Else

aContent(0) = ""

End If

oField1.StringItemList = aContent()

The content of the listbox has been created afresh. Now it must be read in again. Then, using the Additional information property of the listbox that has been refreshed, each of the dependent listboxes that follows is emptied, launching a loop for all following listboxes until one is reached that has no fourth term in its Additional information.

oField1.refresh()

While UBound(aFilter1()) > 1

Dim aLeer()

oField2 = oForm.getByName(Trim(aFilter1(2)))

Dim aFilter1()

aFilter1() = Split(oField2.Tag,",")

oField2.StringItemList = aEmpty()

oField2.refresh()

Wend

End If

End Sub

The visible content of the listboxes are stored in oField1.StringItemList. If any additional value needs to be stored for transmission to the underlying table as a foreign key, as is usual for listboxes in forms, this value must be passed to the query separately and then stored with oField1.ValueItemList.

Such an extension requires additional variables such as, in addition to the table in which the values of the form are to be stored, the table from which the listbox contents are drawn.

Special care must be given to formulating the filter query.

stFilter = """"+Trim(aFilter(1))+"""='"+oField.getCurrentValue()+"'"

This will work only if the underlying LibreOffice version is 4.1 or later, since it is the value which is to be stored that is given as CurrentValue(), and not the value that is displayed. To ensure that it works in different versions, set Property: Listbox > Data > Bound Field > '0'.

Entering times with milliseconds

To store times to millisecond precision requires a timestamp field in the table, separately adapted by SQL for the purpose (see “Table creation” in Chapter 3). Such a field can be represented on a form by a formatted field with the format MM:SS,00. However on the first attempt to write to it, record entry will fail. This can be corrected with the following macro, which should be bound to the form’s “Before record action” property:

SUB Timestamp

Dim unoStmp As New com.sun.star.util.DateTime

Dim oDoc As Object

Dim oDrawpage As Object

Dim oForm As Object

Dim oFeld As Object

Dim stZeit As String

Dim ar()

Dim arMandS()

Dim loNano As Long

Dim inSecond As Integer

Dim inMinute As Integer

oDoc = thisComponent

oDrawpage = oDoc.Drawpage

oForm = oDrawpage.Forms.getByName("MainForm")

oField = oForm.getByName("Time")

stTime = oField.Text

The variables are declared first. The rest of the code is executed only when the Time field has something in it. Otherwise the internal mechanism of the form will act to set the field to NULL.

If stTime <> "" Then

ar() = Split(stTime,".")

loNano = CLng(ar(1)&"0000000")

arMandS() = Split(ar(0),":")

inSecond = CInt(arMandS(1))

inMinute = Cint(arMandS(0))

The entry in the Time field is broken down into its elements.

First the decimal part is separated out and right-padded with null characters to a total of nine digits. Such a high number can only be stored in a long variable.

Then the rest of the time is split into minutes and seconds, using the colon as a separator, and these are converted into integers.

With unoStmp

.NanoSeconds = loNano

.Seconds = inSecond

.Minutes = inMinute

.Hours = 0

.Day = 30

.Month = 12

.Year = 1899

End With

The timestamp values are assigned to the standard LibreOffice date of 30.12.1899. Of course the actual current date can be stored alongside it.

Note

Getting and storing the current date:

Dim now As Date
now = Now()
With unoStmp
 .NanoSeconds = loNano
 .Seconds = inSecond
 .Minutes = inMinute
 .Hours = Hour(now)
.Day = Day(now)
.Month = Month(now)
.Year = Year(now)
End With
oField.BoundField.updateTimestamp(unoStmp)
End If
End Sub

Now the timestamp we have created is transferred to the field using updateTimestamp and stored in the form.

In earlier tutorials, NanoSeconds were called HundrethSeconds. This does not match the LibreOffice API and will cause an error message.

One event – several implementations

It can happen when using forms that a macro linked to a single event is run twice. This occurs because more than one process is linked simultaneously to, for example, the storage of a modified record. The differing causes for such an event can be determined in the following way:

Sub Determine_eventcause(oEvent As Object)

Dim oForm As Object

oForm = oEvent.Source

MsgBox oForm.ImplementationName

End Sub

When a modified record is stored, there are two implementations involved named org.openoffice.comp.svx.FormController and com.sun.star.comp.forms.ODatabaseForm. Using these names, we can ensure that a macro only runs through its code once. A duplicate run usually causes just a (small) pause in the program execution, but it can lead to things like a cursor being put back two records instead of one. Each implementation allows only specific commands, so knowing the name of the implementation can be important.

Saving with confirmation

For complicated record alterations, it makes sense to ask the user before execution whether the change should actually be carried out. If the answer in the dialog is No, the save is aborted, the change discarded, and the cursor remains on the current record.

Sub Save_confirmation(oEvent As Object)

Dim oFormFeature As Object

Dim oFormOperations As Object

Dim inAnswer As Integer

oFormFeature = com.sun.star.form.runtime.FormFeature

Select Case oEvent.Source.ImplementationName

Case "org.openoffice.comp.svx.FormController"

inAnswer = MsgBox("Should the record be changed?" ,4, "Change_record")
Select Case inAnswer

Case 6 ' Yes, no further action

Case 7 ' No, interrupt save

oFormOperations = oEvent.Source.FormOperations

oFormOperations.execute(oFormFeature.UndoRecordChanges)

Case Else

End Select

Case "com.sun.star.comp.forms.ODatabaseForm"

End Select

End Sub

There are two trigger moments with different implementation names. These two implementations are distinguished in SELECT CASE. The code will be executed only for the FormController implementation. This is because only FormController has the variable FormOperations.

Apart from Yes and No, the user might also click on the close button. This however yields the same value as No, namely 7.

If the form is navigated with the tab key, the user sees only the dialog with the confirmation prompt. However, users who use the navigation bar will also see a message saying that the record will not be altered.

Primary key from running number and year

When invoices are prepared, yearly balances are affected. This often leads to a desire to separate the invoice tables of a database by year and to begin a new table each year.

The following macro solution uses a different method. It automatically writes the value of the ID field into the table but also takes account of the Year field which exists in the table as a secondary primary key. So the following primary keys might occur in the table:

Table 28: Example primary keys combining year and ID values

year

ID

2014

1

2014

2

2014

3

2015

1

2015

2

In this way an overview of the year is more easily obtained for documents.

Sub Current_Date_and_ID

Dim oDatasource As Object

Dim oConnection As Object

Dim oSQL_Command As Object

Dim stSql As String

Dim oResult As Object

Dim oDoc As Object

Di