Bug 91828 - BASIC - CurrentValues are empty after opening a form which triggers a macro - incorrect event execution sequence onLoad should not occur after onRecordChange
Summary: BASIC - CurrentValues are empty after opening a form which triggers a macro -...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected) release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Macro
  Show dependency treegraph
Reported: 2015-06-03 12:33 UTC by christian_kuhn
Modified: 2021-04-18 13:49 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:
Regression By:

example (132.38 KB, application/vnd.sun.xml.base)
2015-06-03 12:34 UTC, christian_kuhn
macro for LO 4.0.6 (2.04 KB, text/plain)
2015-06-04 18:52 UTC, christian_kuhn
order of events (31.07 KB, application/vnd.oasis.opendocument.database)
2015-06-10 22:46 UTC, christian_kuhn
corrected example (132.84 KB, application/vnd.oasis.opendocument.database)
2021-04-18 13:49 UTC, christian_kuhn

Note You need to log in before you can comment on or make changes to this bug.
Description christian_kuhn 2015-06-03 12:33:57 UTC

I have a form, which triggers a macro after opening the document and before a record is changed. In earlier versions (4.0) it works fine, but now I get empty values for the 'CurrentValue' attribute of ListBoxes, so that the SQL query fails, which contains these values.


SELECT "Table_2"."Nr." FROM "Table_2" WHERE "Table_2"."Begriff: Nr." = '' AND "Table_2"."Kontext: Nr." = ''

To illustrate this, I have inserted a MsgBox instruction into the macro to show the wrong SQL statement.

To reproduce this, you must enable the processing of macros for the attached document. Reopen the database and after this open the form 'Table 2'.
Comment 1 christian_kuhn 2015-06-03 12:34:42 UTC
Created attachment 116256 [details]

... I have forgotten the example.
Comment 2 christian_kuhn 2015-06-03 22:58:44 UTC
The macro fires, if I hover the next record button with my mouse without to press it? This is strange ...
Comment 3 Alex Thurgood 2015-06-04 07:40:16 UTC
Confirming on OSX 10.10.3

Build ID: 55431a84c264a8bcca593b9207aae0ad81d10f30
Locale : fr-FR (fr.UTF-8)

After the MsgBpx, the IDE opens and I get a Basic error :

Erreur d'exécution BASIC.
Une exception s'est produite : 
Type: com.sun.star.sdbc.SQLException
Message: Wrong data type: java.lang.NumberFormatException: For input string: "".

from the follwowing line of code :

Result = Statement.ExecuteQuery(Sql)
Comment 4 Alex Thurgood 2015-06-04 07:51:05 UTC
I get the same error with 

Build ID: 0a0440ccc0227ad9829de5f46be37cfb6edcf72
Comment 5 Alex Thurgood 2015-06-04 08:39:07 UTC
Same error in
Comment 6 Alex Thurgood 2015-06-04 08:47:09 UTC
This also fails for me on

Version (Build ID: 0eaa50a932c8f2199a615e1eb30f7ac74279539)
Comment 7 Alex Thurgood 2015-06-04 09:52:17 UTC
Also fails on

Version (Build ID: 53fd80e80f44edd735c18dbc5b6cde811e0a15c)
TinderBox: MacOSX TDF Release, Branch:libreoffice-4-0, Time: 2013-01-31_11:01:36
Comment 8 Alex Thurgood 2015-06-04 10:12:16 UTC
Also fails on

Version (Build ID: e183d5b)

@christian : I can not confirm that this is a regression, at the moment, it looks like this never worked, at least on OSX
Comment 9 Alex Thurgood 2015-06-04 10:18:18 UTC
Same error in 

LibreOffice 3.3.0 
OOO330m19 (Build:6)
tag libreoffice-

so this was a problem from the beginning of the LO project, and probably inherited from OOo.

@christian : in which earlier versions of LO did it work, and which OS ?
Comment 10 Robert Großkopf 2015-06-04 11:44:26 UTC
Since LO 4.1 CurrentValue of a listbox changed to the value, which should be saved in the database. Older versions will show the text, which could be seen on the screen.
if you are using macros and do not know which version of LO should be use, you have to use a function like this:
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)
Comment 11 christian_kuhn 2015-06-04 18:52:09 UTC
Created attachment 116290 [details]
macro for LO 4.0.6

Sorry - I have not reported:

before I changed to LO 4.3 and the to LO 4.4.3, I have used LO 
(Build ID: 2e2573268451a50806fcd60ae2d9fe01dd0ce24). 

For this version, I have used a macro like in the attached text snippet, and here I get no error, if I open the form, because here I get empty strings and not missing numbers for the CurrentValue attribute of ListBoxes, so that no error occurs (probably this is wrong).

But in LO 4.0.6, the behaviour of the trigger is the same, if I uncomment the MsgBox("Test") like in the attached example, it popped up, if I open the form or if I hover again over the next-record-button with my mouse after I have changed the record. This implies, that the macro fires in these cases. 

But I think, this should not happen, because the macro should only fire, before the record is changed (or if the ListBoxes are touched, because I have triggers here) and not after the form is opened.
Comment 12 christian_kuhn 2015-06-04 21:04:47 UTC
I have used Windows 8.1.
Comment 13 Robert Großkopf 2015-06-05 06:21:51 UTC
An event could be started by different implementations. There are two implementations which would happen when changing from one row to another:

The implementation, which fires while opening the form, is ...ODatabaseForm. Loading of the content is changing the content for this implementation.

You could start your macro like this:
Sub Check_rl_term_context(oEvent AS OBJECT)
IF oEvent.source.ImplementationName = "org.openoffice.comp.svx.FormController" THEN

So only one implementation starts - the implementation, which doesn't start while opening the form.

You are German. Have a look to the Base-Handbuch. It is described at page 412 (Handbuch 4.4). Also the different behavior of ListBoxes is described: page 388.
Comment 14 christian_kuhn 2015-06-05 12:59:12 UTC
Not the triggers for the ListBoxes are the problem, but only the form's trigger which shall trigger the macro before the record will be changed. I mean two things:

I have seen, that the macro fires two times, one event source is 'com.sun.star.comp.forms.ODatabaseForm' and another is 'org.openoffice.comp.svx.FormController'. And it is described in the Base-Handbuch for LO 4.4 (p. 141 - 142), that already the opening of the form is such a changing-the-record-event (ok, I think, I must accept this). But it is not so easy to understand, why the macro should fire, if the mouse pointer hovers over the change button after the record was changed, because the semantics of this event mean 'before changing record'.

If I use the event 'while loading' to trigger the macro, then the ListBoxes are correctly initialized and have content (CurrentValues are not empty), so that the SQL query of the example does not fail. But if I use the event 'before changing record' as trigger, it seems, that the ListBoxes are not yet initialized and the CurrentValue attributes are still empty, if the macro fires at first time. In the earlier version of Base (LO 4.0) this causes not an error, because the CurrentValue attribute of the ListBox contains only an empty string. Now (LO 4.4) it should contain a number, but it is empty and contains nothing. 

I have used the If-then-contruct to filter the right event source and it works, but in my opinion this is a workaround, because the semantics of this event trigger are not clear for me.
Comment 15 Alex Thurgood 2015-06-05 15:11:08 UTC
So, the question remains ? 
Bug or design feature ?
Or RFE ?
Comment 16 Robert Großkopf 2015-06-05 15:11:53 UTC
This bug description is about three different behaviors in the form:
- changed current value of listboxes
- different implementations for an event
- occuring of an event while moving with the mousepointer over a field in the navigationbar

One bug - one buggy behavior.

Please open a new bug-description for the mousepointer over the fields and add an example without all other "buggy" behavior.
Comment 17 christian_kuhn 2015-06-05 18:52:11 UTC
I have opened bug 91879.
Comment 18 Lionel Elie Mamane 2015-06-06 08:25:18 UTC
Christian, I don't understand. You say that in LibreOffice, you got an empty string instead of a "Variant/Empty" in the variables IDString, TermID and  CtxtID, and you say that then the "Statement.ExecuteQuery(Sql)" does not make an error.

However, if these variables contain an empty string or they are Empty, the resulting content of variable Sql is the same, so I don't see how it can make a difference for your macro.

Anyway, if you want a string, you can easily force that by adding:

  Dim IDString  As String
  Dim TermID    As String
  Dim CtxtID    As String

While I read your macro, I notice that in the SQL command (query), you try to compare an integer column to a string literal. This is not guaranteed to work on all database management systems. For example, it does not on PostgreSQL:

  ERROR:  operator does not exist: integer = character
  HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

It happens to work with HSQLDB (or MySQL/MariaDB) because those will do automatic type conversions.

I suggest that you use integers when comparing to integer. For example
  SELECT "Table_2"."Nr." FROM "Table_2"
  WHERE "Table_2"."Begriff: Nr." = 0 AND "Table_2"."Kontext: Nr." = 2
instead of
  SELECT "Table_2"."Nr." FROM "Table_2"
  WHERE "Table_2"."Begriff: Nr." = '0' AND "Table_2"."Kontext: Nr." = '2'

This will be more portable.

In order to avoid trying to use invalid values you could do something like:

1) Leave variables IDString, TermID and CtxtID as Variant.

2) add:

  if IsEmpty(IDString) OR IsEmpty(TermID) OR IsEmpty(CtxtID) Then
    exit sub
  end if

before computing the value of "sql".

And actually, the most robust way to do that kind of things is not to construct SQL commands on the fly, but to use parametrised queries:

Option Explicit

Global statement as Object

Sub make_query
  'Exit Sub
  statement = ThisComponent.DrawPage.Forms.GetByName("frm_tbl2").activeConnection.prepareStatement("SELECT ""Table_2"".""Nr."" FROM ""Table_2"" WHERE ""Table_2"".""Begriff: Nr."" = ? AND ""Table_2"".""Kontext: Nr."" = ?")
End sub

Sub Check_rl_term_context
  Dim MainForm  As Object
  Dim Result    As Object
  Dim Term      As Object
  Dim IDControl As Object
  Dim Context   As Object
  Dim Found     As Boolean
  Dim IDValue   As Variant
  Dim TermID    As Variant
  Dim CtxtID    As Variant
  Found               = False
  MainForm            = ThisComponent.DrawPage.Forms.GetByName("frm_tbl2")
  IDControl           = MainForm.GetByName("Nr-Feld")
  Term                = MainForm.GetByName("Begriff-Feld")
  Context             = MainForm.GetByName("Kontext-Feld")
  TermID              = Term.CurrentValue
  CtxtID              = Context.CurrentValue
  if IsEmpty(TermID) OR IsEmpty(CtxtID) Then
    exit sub
  end if
  statement.setLong(1, termID)
  statement.setLong(2, CtxtID)
  Result = statement.ExecuteQuery()
  While (Not Found AND Result.Next)
    If (StrComp(Result.GetString(1), IDValue) <> 0) Then
      MsgBox("Der Begriff wurde in diesen Kontext schon eingetragen.")
      Found = True
    End If
End Sub

Where "make_query" is bound to the form's "when loading" event.
Comment 19 christian_kuhn 2015-06-06 13:56:49 UTC
What I mean is: either the elements of the form should be correctly initialized before the macro will be executed by the trigger for the event 'before changing record' (I have not yet noticed for LO 4.0, that the contents of the form's elements are still empty, if the macro will be executed at first time), or the macro should not be executed by this event trigger after opening the form, because for this case the event trigger 'while loading' already exists and such events are handled by it.
Comment 20 christian_kuhn 2015-06-10 22:46:36 UTC
Created attachment 116451 [details]
order of events

(In reply to Lionel Elie Mamane from comment #18)

Something is wrong with the order of the occuring events. The event 'when loading' should not occur after the event 'before changing record'. The workaround in your code for this is 

>   if IsEmpty(TermID) OR IsEmpty(CtxtID) Then
>     exit sub
>   end if

I have attached an example to show this.
Comment 21 vlad 2015-06-24 00:19:58 UTC
Hi to all.
Well,I am not a developer ,but...
The error you have I think is an error in Java,and Java is used only in HSQLDB,which one are written in above mentioned language.
Then ,the error is :NumberFormatException.
If you will take a look to official Java definitions,
you can find the definition of this error:" the application has attempted to convert a string to one of the numeric types, but that the string does not have the appropriate format".
What I am trying to say:this is the bug of enabled database,but not a LibreOffice,Libre just gives a SQL command to database engine and attend the anwer.It is possible to write to authors of mentioned database,but I am afraid that the answer will be:"It's time to change version"(because the LO version database is about 10 years old,there are more recent versions.)
Hope it helps.
Comment 22 Buovjaga 2016-10-06 12:21:17 UTC
Status should be NEW.
Comment 23 QA Administrators 2018-08-29 02:42:20 UTC Comment hidden (obsolete)
Comment 24 christian_kuhn 2018-09-14 23:21:48 UTC
Reproduced with:

Version: (x64)
Build-ID: 5d19a1bfa650b796764388cd8b33a5af1f5baa1b
CPU-Threads: 1; BS: Windows 6.1; UI-Render: Standard; 
Gebietsschema: de-DE (de_DE); Calc: group threaded
Comment 25 christian_kuhn 2018-09-14 23:37:25 UTC Comment hidden (obsolete)
Comment 26 QA Administrators 2021-04-18 03:48:57 UTC Comment hidden (obsolete)
Comment 27 christian_kuhn 2021-04-18 13:48:05 UTC

I have download the first example and I have got the same behaviour in LO as well as in LO Now I have changed the event trigger of the form to 'Vor der Datensatzaktion' / before record action, and now it works for me.

Best regards,
Comment 28 christian_kuhn 2021-04-18 13:49:39 UTC
Created attachment 171272 [details]
corrected example

example with changed event trigger