Base form data does not load first time

[dougcb68]

I have a form (named sqlForm) that composes an SQL string to retrieve user selected data from the database. Once the SQL string is composed there is a button to click to execute the SQL command and open another form (named Answers) showing the output.

The problem is that the Answers form opens showing no data even though the AnswerTable contains the correct data. If I close the Answers form and then click on the execute button in the sqlForm form again the Answer form behaves properly.

Here is an example database that exhibits the problem: example.odb

Starting with the form sqlForm clicking on the button opens the Answers form, but without any data. If the Answers form is closed and the button in the sqlForm is clicked again, the Answers form opens with the correct data.

[Ratslinger]

@dougcb68

When editing a question, please leave original information intact, append the new information and specify the new information as edited as can be seen in my answer.

By removing previous information, others looking at this question and the corresponding answer will become confused as the answer is responding to something no longer there.

[Ratslinger]

Hello,

Have duplicated your set up and cannot understand how this macro is working at all on your system. The way you have this written, you have defined oSQLstmt as a String but in the code body fill it with an Object (sqlTxtField). This creates an error anytime the macro is executed.

Here is the code I tested with and works:

Sub OpenAnswer

  DIM oDatasource AS OBJECT
  DIM oConnection AS OBJECT
  DIM oStatement AS OBJECT
  DIM oSQLstmt AS STRING
  oDataSource = ThisComponent.Parent.CurrentController
  IF NOT (oDataSource.isConnected()) Then
    oDataSource.connect()
  End IF
  oConnection = oDataSource.ActiveConnection()
       'retrieve SQL statement from form  '  
  oForm = ThisComponent.Drawpage.Forms.getByName("sqlForm")
  oSQLstmt = oForm.getByName("sqlTxtField").Text
      'execute the sql statement and open the Answer form    '
  oStatement = oConnection.createStatement()  
  oStatement.execute(oSQLstmt)
  ThisDatabaseDocument.FormDocuments.getbyname("Answers").open()    

End Sub

Only two of the lines were changed; retrieval of oSQLstmt and the actual execution line of code.

Edit 2019-03-11:

Not certain where your problem lies as you have not posted a sample.

Do not see this happening on the one I created for testing:

Sample ------ TestFormCall.odb.

Records are returned on first try.

Edit 2019-03-13:

You should not be using labels for this type of information but rather a text box as in my example above. You can place default information in a text box. Have posted below your example with a slight change in the macro:

Dim mySQL AS String
mySQL = oSQLstmt.Label

      'execute the sql statement     '
  oStatement = oConnection.createStatement()
'  oStatement.execute(oSQLstmt.Label)'
  oStatement.execute(mySQL)

That is to extract statement to a string variable before executing the SQL. Works now as my example posted previously does.

Sample ------ FixedExample.odb

[Ratslinger]

Also, try to stay away from dispatch commands. Learn the proper code. There are a number of macro documents available. See → To learn LibreOffice Base are there introductions or tutorials?

Also note, using my posted sample you can enter the same statement as you presented in your question. Have record set for that.

[Ratslinger]

@dougcb68 Have not seen a response as to whether my sample in the edited answer has worked for you or not.

[dougcb68]

Thank you very much for your attention to this matter. I really do appreciate it. I have just modified my original question and posted an example database that demonstrates the problem, as per your suggestion. I can see now that is what I should have done in the first place, Sorry I have been so slow in getting back to you, but this project is a pretty low priority for me. I have accessed all of the LibreOffice tutorials you referenced but I am finding the learning curve to be rather steep. I am having particular trouble getting a fix on the object model hierarchy. I’ve got bits and pieces of it, but am having trouble figuring out how they relate to one another. I have yet to locate any reference material that provides an overall, top down description of the model, much less a graphical representation (I have a GIS background and am VERY spatially oriented). Once again, thank you, and I apologize for wasting your time with my poorly constructed initial post.

[Ratslinger]

Will update my answer with your fixed example.

[dougcb68]

Thanks Ratslinger. This works!

I honestly don’t remember why I chose label objects to do my text manipulation rather than text objects. It seemed to me that the label property of a label object would be a string type. Obviously not! So, I learned something. Now I have to go back and rework all the code where I used label objects to form the sql statement. Thanks again.

[Ratslinger]

@dougcb68 That is a string. It is still a bad choice. The problem was you were extracting the string from the object while trying to execute the SQL. One too quick for the other. The code in both samples extract the string first then execute the SQL. Safe approach.

[dougcb68]

So, if I understand it, the issue is not specific to label objects.

Would you say that, generally, when using a property value from any object as a parameter for any method one should first assign the property value to a variable of the appropriate type and then use the variable as the parameter in invoking the method?

[Ratslinger]

In this particular case and generally, yes. This is not always true but there is no list stating when & when not to. Sometimes trial & error.

This is also true in design. For example, it appears you may have many of these forms. I would have just one, use a list box for selection and construct most of the SQL statement in the single macro itself. Don’t need all the wording appearing on form as it is just confusion for the user.