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.
