Created attachment 116256 [details]
example

... I have forgotten the example.

The macro fires, if I hover the next record button with my mouse without to press it? This is strange ...

Confirming on OSX 10.10.3

Version: 5.1.0.0.alpha1+
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)

I get the same error with 

Version: 4.1.4.2
Build ID: 0a0440ccc0227ad9829de5f46be37cfb6edcf72

Same error in 4.0.5.2

This also fails for me on

Version 4.0.3.3 (Build ID: 0eaa50a932c8f2199a615e1eb30f7ac74279539)

Also fails on

Version 4.0.0.3 (Build ID: 53fd80e80f44edd735c18dbc5b6cde811e0a15c)
TinderBox: MacOSX TDF Release, Branch:libreoffice-4-0, Time: 2013-01-31_11:01:36

Also fails on

Version 3.6.7.2 (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

Same error in 

LibreOffice 3.3.0 
OOO330m19 (Build:6)
tag libreoffice-3.3.0.4

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 ?

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)
END FUNCTION
---------------

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 4.0.6.2 
(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.

I have used Windows 8.1.

An event could be started by different implementations. There are two implementations which would happen when changing from one row to another:
com.sun.star.comp.forms.ODatabaseForm
org.openoffice.comp.svx.FormController

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
....
END IF
END SUB

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.

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.

So, the question remains ? 
Bug or design feature ?
Or RFE ?

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.

I have opened bug 91879.

Christian, I don't understand. You say that in LibreOffice 4.0.6.2, 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.")
      Term.Reset()
      Context.Reset()
      Found = True
    End If
  Wend
End Sub

Where "make_query" is bound to the form's "when loading" event.

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.

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.

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,
'
http://docs.oracle.com/javase/7/docs/api/java/lang/NumberFormatException.html
'
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.

Status should be NEW.

Reproduced with:

Version: 6.1.1.2 (x64)
Build-ID: 5d19a1bfa650b796764388cd8b33a5af1f5baa1b
CPU-Threads: 1; BS: Windows 6.1; UI-Render: Standard; 
Gebietsschema: de-DE (de_DE); Calc: group threaded

Hi,

I have download the first example and I have got the same behaviour in LO 7.0.4.2 as well as in LO 3.5.4.2. 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,
Christian

Created attachment 171272 [details]
corrected example

example with changed event trigger