Ask Your Question
0

Base form data does not load first time

asked 2019-03-10 17:39:42 +0100

dougcb68 gravatar image

updated 2019-03-13 19:03:25 +0100

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: C:\fakepath\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.

edit retag flag offensive close merge delete

Comments

@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 gravatar imageRatslinger ( 2019-03-13 20:12:27 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-03-10 19:08:35 +0100

Ratslinger gravatar image

updated 2019-03-13 20:22:09 +0100

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

edit flag offensive delete link more

Comments

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 gravatar imageRatslinger ( 2019-03-11 19:38:43 +0100 )edit

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

Ratslinger gravatar imageRatslinger ( 2019-03-13 17:07:44 +0100 )edit

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 ...(more)

dougcb68 gravatar imagedougcb68 ( 2019-03-13 19:29:16 +0100 )edit

Will update my answer with your fixed example.

Ratslinger gravatar imageRatslinger ( 2019-03-13 20:13:05 +0100 )edit

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.

dougcb68 gravatar imagedougcb68 ( 2019-03-13 23:36:55 +0100 )edit

@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.

Ratslinger gravatar imageRatslinger ( 2019-03-14 01:39:03 +0100 )edit

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?

dougcb68 gravatar imagedougcb68 ( 2019-03-14 17:24:52 +0100 )edit

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.

Ratslinger gravatar imageRatslinger ( 2019-03-14 21:32:10 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-03-10 17:39:42 +0100

Seen: 59 times

Last updated: Mar 13