Issues with updating records in a subform

English
#1

Environment: PC with Windows 10 Pro , 64 bit,
LO version 7.2
Firebird DB, installed as extended server on same PC
Firebird version 3.0

Description of the situation.
I am using a form to maintain a relational table between two parent-tables. (ie. Song and Performance)
The (Main)form shows one record of the Performance-tabel using a Query (so navigation is disabled)
A subform is defined for updating a relation-table to the Song table.
On the subform a listbox has been defined to maintain the relation-table (i.e. SongsPerPerformance)
The subform has navigation-buttons to scroll through existing entries and to add new ones.
To select a Song to be used a Listbox a defined to be used.

Issue description.
When starting the mainform everything is displayed correcty, but when there are no existing entries the in the relation-table following issue happens:
When than an entry is selected from the listbox to identify the Song to be linked and than the save-button is used an error-message is displayed “Object variable not set”.
When one of the navigation buttons is used (i.e. "first " or “last” or “new”) and than a selection from the list-box is made, followed by a save everything works fine.
All subsequent activitities for the same mainform-value work also fine as of that moment.
When there is initially an existing relation everything works also well when starting the form.

Its looks that initially the data-definition of the subform is not recognized when there is no entry in the relation-table.

Can anyone shed some light on this issue?

image

image
image1184×932 77.6 KB

First picture : the data-structure
Second picture: the concerning form

  1. The mainform, base on a Query, showing one Performance (Optreden)
  2. A subform, base on a query, showing the current list of Songs per Performance, based on re relational table. This list will be updated if a change is made and a reload is performed.
    (is not relevant for the issue mentioned)
  3. A subform, used to maintain relationships between a particular Performance (listed in de mainform part) and Songs as defined in table T_Muziek (Songs), using T_MuziekUitvoering as data.
    The red circle shows the linking between Performance and te relation-table
    The Listbox (Muziektitel) is a query from table T_Muziek (Song) showing the titel and the key
    The key in the listbox points to the foreign key in the relation table.
    The field with M as label is the key to Songs, once selected in the listbox

The image shows the initial form for a Perfomance without any connected Songs.
When now selecting an entry from the listbox and pressing the save-button (Opslaan) an error is shown.
When first using one of the other navigation buttons and than selecting an entry and hitting save no error is show and the relational record is created.

Hopefully this (long) story clearifies my earlier remarks.

I did some further investigation and found something:
After loading or reloading the form I issued a oForm.First statement in a macro if the listbox is empty, which is the case when no relationships do exist.
Interesting enough that disabled the listbox and enable the same when hitting the “new-record” button.
This is a somewhat cumbersome slution, but it works.

Anyway, a better solution is always welcome!

Sorry for not expressing the issue clearly enough:
Forget the query-base subform as listed under number 2, that may confusing.
The subform at the bottom is connected to the relational table, which has two foreign keys:
one to the Performance table (as listed and linked on the main form part 1) and another to the Song table. The key to the song-table is obtained from the listbox (select key and name from song-table,
The listbox data property (using the first field) is pointing to the relation table field, holding the foreign key to song table)
The actual issue therefore is that LO does not have a current recordset to work with when no relation records do exist when opening the form. Using oForm.first resolves that, but is not a nice solution. The other “solution” would be to force the user the use a navigation button before using the listbox, but when the user accidentally uses the listbox first we have still the issue.

Gentlemen,
I have just created a simplified testform doing the same as the form giving the error when starting with an empty record set in de subform. This testform works correctly.
The difference between the two forms is that the testform is using the standard navigation method while the other uses own navigation buttons.
I have also successfully installed the indicated firebird fix, but don’t know where to find the content.xml file and do not understand what to do with it.
I am also a bit confused by the fact that three people trying to respond, but appreciate the help anyway.

#2

Forget about embedded Firebird. It does not work.

#3

Hello,

Of course Firebird embedded works - just as well as other databases in LO - problems with each in some regard.
.
However, are you using Firebird server and what version?
.
Also, have read through your post a few times and do not comprehend the actual structure of the form and al its’ components. Further definition or sample would help. Is this a many-to-many relation? Not clear from your description.

Edit:
See this comment → Base noob - Creating dropdown from one to many

#4

Not a single one of my database forms work with FB. No matter how much time I invest. Base and FB fail when it comes to subforms based on parameter queries.

#5

Have converted personal Budget System containing about 20 tables/views with about the same number of forms a few years ago. While there were some adjustments, all worked and data balanced. Have been able to work around many issues (as have using other DB’s).
Edit:
No subforms in budget system using parameters but tried simple one:

SELECT * FROM "Contacts" WHERE "Name" LIKE :name || '%'

and no problem. Update record without issue. Contacts table was linked to Client table on ID. Subform then limited by entered beginning of name.

Edit 2:
Did look at old notes & there is a problem with dates and parameters. Got around this in past with macro.

Edit 3:

Must start again. Do have a Base file with Firebird embedded where date selection works. Same file used for SQL above. Now to find out why & how.

Edit 4:

@Villeroy

See How to unzip ODB file and edit the content.xml file to fix Firebird bug? to fix parameter first.

Then in Firebird Base file content.xml there is an entry (possibly multiple - one for each query):

db:escape-processing="false"

change to “true” and save. Parameters should work.
In my file which was working this entry wasn’t even present.
.
All seems to be applicable to older Firebird created files. Newly created empty files seem to work without fixes.

#6

@TomKuipers

Do not understand why the use of Queries.

First form - table for Performance.
Subform is table for many-to-many links. This is linked to the performance table via the form properties for linking Master & slave data. The song selection can be a list box for the Songs table.
If you need to add a new song, have a second internal main form for adding a new song & then refresh your other main form when done.
.
Do not see any need for code.

#7

@TomKuipers
We will need an example database for this. Only a database with some dummy data will be enough.

I could test it with internal Firebird, external Firebird and also Firebird server.

#8

There is no such attribute in my Base document’s content.xml with embedded FB.

That’s one reason why FB does not work with Base.

Next one:
Filter by any given parameter ignoring missing parameter:

SELECT * FROM TBL WHERE COL_INT = :Param1 OR :Param1 IS NULL

Fails when you give an integer parameter but works as expected (return all) when you pass none.
Work-around:

SELECT * FROM TBL WHERE COL_INT Coalesce(:Param1 COL_INT)

Now the query correctly returns the row set filtered by Param1 or the entire row set when Param1 is missing.
I’m not sure if the work-around is always equivalent to the first method. However, the same trick with a date column fails to work anyway:

SELECT * FROM TBL WHERE COL_DATE = :Param1 OR :Param1 IS NULL
SELECT * FROM TBL WHERE COL_DATE Coalesce(:Param1 COL_DATE)

[quote=“error message”]The data content could not be loaded. /home/buildslave/source/libo-core/connectivity/source/commontools/dbtools.cxx:747
SQL Status: HY004

Incorrect type for setString /home/buildslave/source/libo-core/connectivity/source/commontools/dbexception.cxx:462[/quote]

Needless to say that this works with HSQL, H2 and with MySQL.

#9

Did state:

While I agree with the problem with the date field, The other worked for me as:

SELECT * FROM "ParamTest" WHERE :Param1 IS NULL OR "col_int" = :Param1
#10

I’m baffled by your work-around switching the operands. This would be useful if it worked with all data types. Most of my filter tables filter record sets by time interval WHERE (:D1 IS NULL OR :D1 >= DATE_COL) AND ( :D2 IS NULL OR :D2 >= DATE_COL).

#11

I am confused now, please see also the latest remark I made in the original text.
Is there anything I can/should do?

#12

@TomKuipers
.
First, my apologies as it is my fault that many of the comments are off topic and do not pertain to your question.
.
There are two comments addressed directly to you each beginning with your name.
.
At this point the comment by @RobertG asking for a sample may be the best way to help.
.
There is no need for you to modify any files.
.
Again, my apologies for adding to this confusion.

#13

How can we answer your question from a screen shot?
General recipe for a many-to-many subform (your T_MuziekUitvoering):
The subform is linked to table T_MuziekUitvoering.
Its parent form is linked to T_Muziek.
TMU_TM_ID is linked to the parent form’s TM_ID.
The subform consists of one table control.
The table control has a listbox for the TMU_TU_ID and a text box or something for the TMU_Info.
Listbox source type: SQL
Listbox source: SELECT , TU_ID FROM “T_Uitvoering”
Bound field: 1
can be any text or concatenation of text from “T_Uitvoering”
Alternatively, you may store the SQL as a query and use source type “Query” with the query name as source.

The column of listboxes in the table control shows all Uitvoerings associated with the Muziek selected in the parent form and you can edit, remove and add new Uitvoerings with associated TMU_Info.

See also [Example] Relations reflected by list boxes in forms where the relation between persons and things is many-to-many.
I just created a Firebird version of the same example database:
relations2listboxes.FB.odb (59.2 KB)
With the only exception of the report, this version works without Java.

#14

Dear Villeroy,
What you just described (and earlier also Ratslinger) is exactly what I have done:
The “Main” form is pointing to one parent-table as data source, the subform is using the relational table as data source, linked to mainform by primary key of parent table and foreign key od relational table.
The linkage to the other parent table is using the listbox, that selects key and name (for verification) from that parent table. The key-field is linked to the other foreign key field of the relational table.
(I have both tried using a Query and an SQL command, no difference).
When opening the form with an entry in de main form that has no records in the relational table, the subform does not show any data as to be expected, but it is possible to make a selection from the listbox, when then hittting the save-button an error message “Object variable not set” is show. As stated before: I have created a second form in exact the same way, with the difference that I used the standard LO navigation, while the other form uses own navigation buttons.
In the testform the listbox is disabled when there no relations, but becomes enabled when selecting the "new"button of the navigation bar.
I have use a macro in the original form to perform a Form.First action, which does what the testform does automatically: disable the listbox for an empty recordset until a navigation button is used.
That’s the issue.
Once one or more entried have been created everything works perfectly even when deleting all existing relations and then creating a new one…

#15

@TomKuipers
.
Don’t see any need for macros. Again, it would be most helpful if you would post a sample.

#16

Oops, oops,
I found the reason for this issue…
Ratslinger has a point that the use of macro’s should be avoided…
When saving a new record a macro is executed that, amoungst some other coding, is executing an SQL-statement and then executes a save command. LO loses the document-settings and sends an error message.
The save-command used is:
oDispatcher.executeDispatch(oDocument, “.uno:RecSave”, “”, 0, Array())

When changing to:
oDocument = ThisComponent.CurrentController.Frame
oDispatcher = createUnoService(com.sun.star.frame.DispatchHelper)
oDispatcher.executeDispatch(oDocument, “.uno:RecSave”, “”, 0, Array())
everything works perfectly.

In most other forms where no addional coding is required these statements are included in the “standard” macro connected to the save-button.
This is also applicable to the testform where no special save-macro was used…

Many thanks for your patience and time, another lesson learned !!

#17

You can set the buttons’ Action property to save a record (and other things). No code needed:
.
Screenshot at 2022-02-26 14-25-15

#18

One more time since the question seems to be answered.
.
First appears your statement needs adjustment. both are >= and believe one s/b <=.
.
Took a couple of minutes to try something which works. Don’t want to spend more time but may give you some other ideas. Parameter date entry for this needs to be YYYY-MM-DD format but it works:

Select * from "ParamTest" where (cast("myDate" as varchar (10)) >= :p1 or :p1 IS NULL) AND (cast("myDate" as varchar (10)) <= :p2 or :p2 IS NULL)
#19

Well, yes. Somehow. However, this won’t work nicely with a subform unless you pass the date from a pattern field or concatenated from 3 Y,M,D controls.
And no, we can’t filter any Firebird date fields in the Base GUI. Base can not handle FB dates properly.

#20

Since this works:

WHERE ( "DateServed" >= :p1 ) AND ( "DateServed" <= :p2 )

So you just can’t hit enter and must enter a date. Seems the issue is really with parameters and not dates itself. And although I have lost most enthusiasm for Firebird, my only point is there are lots of ways to get around most of the problems. I have a few obscure ones which haven’t worked. But this is LibreOffice Base. There are many many problems dealing with everyday issues such as form sizes. This used to work nicely but bug upon bug upon bug has caused a problem with it. Now you need a macro for size & position).
.
And at worst case, a macro can work for this date situation with NULL while the bug is reported and possibly fixed.
.

And if that were correct then you may want to say Forget about Base.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.