Issue with interacting Base forms

[dougcb68]

I have a bit of a head scratcher. I have created a Base application with two interacting forms: “Document Search” and “Document Search Results”.

The “Document Search” form has five drop down boxes that allows the user to select search criteria and an execute button. On execution the code formulates an SQL command by adding appropriate “where” clauses to the global string variable which is initialized at the start of the sub routine to:

_sSQLTemplate= "INSERT INTO “&CHR(34)&“AnswerTable”&CHR(34)&” SELECT “&CHR(34)&“MasterTable”&CHR(34)&”.* FROM "&CHR(34)&“MasterTable”&CHR(34)

This part of the code functions as expected.

The problem emerges in the following code:

    'execute sql queries

oConnection = ThisComponent.Parent.CurrentController.ActiveConnection()

    'execute the sql statement to empty the answer table   

oStatement = oConnection.createStatement()
oStatement.execute(_truncTable)

    'execute the sql statement and open the Answers form    

oStatement1 = oConnection.createStatement()
oStatement1.execute(_sSQLTemplate)

     'open search results form screen  

ThisDatabaseDocument.FormDocuments.getbyname(“Document Search Results”).open()

where: _truncTable="TRUNCATE TABLE "&CHR(34)&“AnswerTable”&CHR(34)

The first execute command works as expected resulting in an empty “AnswerTable”

The second execute command also works as expected, filling the “AnswerTable” with correct data.

The problem is with the Open command. The FIRST time the code is executed the “Document Search Results” form opens with no data in the embedded “AnswerTable”.

If I return to the “Document Search” form and execute the code a SECOND time the “AnswerTable” data appears as expected.

While attempting to debug the issue I noticed that if I put a print statement between the two sql execution sections the code worked as expected. So I tried inserting a non-functional code line (junkvar=1) at that location and the code also works as expected. Magic!

I should be happy that the code works, and I am. But I am skeptical of magic and I wonder if anybody has any thoughts about what is actually going on here.

[CRDF]

May be a
.reload()

[dougcb68]

I tried that both before and after the open command in the “Document Search” form and in the OnOpen property in the “Document Search Results” form.
No Joy

[CRDF]

When loading

[dougcb68]

OpenAI offered the following explanation and solution:

This exact pattern is very common in LibreOffice Base macros:
the form opens before Base has finished committing the previous SQL command to the internal HSQLDB/Firebird/DB engine, so the form loads “too early,” before the data is actually visible.

When you insert a print statement or any “junk” code, you unintentionally introduce a timing delay, giving the database engine enough time to complete the transaction before the form loads. That’s why it appears like “magic.”

Below is what’s actually happening, and how to fix it cleanly.

---

What’s Really Going On

1. execute() is asynchronous for embedded Base databases

In LibreOffice Base (especially with embedded HSQLDB), oStatement.execute() returns immediately even though the engine may still be processing the statement internally.

So your sequence:

1. TRUNCATE AnswerTable
2. INSERT INTO AnswerTable SELECT…
3. Open the Results form

…actually behaves like:

• TRUNCATE finishes quickly
• INSERT begins, but may still be running
• Form opens immediately → sees empty or partially updated AnswerTable

On your second run, the table is already populated by the time the form reopens, so it seems “fixed.”

Your dummy instruction (junkvar=1) works because it delays execution by a few milliseconds—just enough for the database engine to finish writing.

---

The Proper Fix: Force Base to finish the transaction before opening the form

Choose ONE of the following solutions:

---

Solution A: Add a short Wait

Simplest, most common workaround:

oStatement.execute(_truncTable)
oStatement1.execute(_sSQLTemplate)

Wait 100   'Wait 0.1 seconds for DB write to complete

ThisDatabaseDocument.FormDocuments.getByName("Document Search Results").open()

100–200 ms is usually enough.

This is the LibreOffice community’s standard workaround.