Ask Your Question
0

Macro To Open Query

asked 2020-11-07 05:01:37 +0200

Pumper gravatar image

updated 2020-11-07 05:21:50 +0200

robleyd gravatar image

I am trying to write a macro to open a query while in a form. I tried this and got the message,BASIC runtime error.An exception occurred Type: com.sun.star.container.NoSuchElementException Debian10 LO 5.2.2.2 Could someone please help w/this. Thank You, Pumper

SUB QueryChange( sQueryName )
  Dim ObjTypeWhat
  Dim ObjName As String
  Dim sName as String
  Dim sTitle As String
  Dim iStart As Integer
  ObjName = sAVAILABLE1
  ObjTypeWhat = com.sun.star.sdb.application.DatabaseObject.QUERY
       ThisDataBaseDocument.CurrentController.Connect() 'If the query exists connect to the database'
     ThisDatabaseDocument.CurrentController.loadComponent(ObjTypeWhat, ObjName, FALSE) 'Open the query'      

End Sub
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2020-11-07 05:33:39 +0200

Ratslinger gravatar image

updated 2020-11-08 01:49:42 +0200

Hello,

Quick test:

SUB QueryChange
  Dim ObjTypeWhat
  Dim ObjName As String
  Dim sName as String
  Dim sTitle As String
  Dim iStart As Integer
  ObjName = "sAVAILABLE1"
  ObjTypeWhat = com.sun.star.sdb.application.DatabaseObject.QUERY
       ThisDataBaseDocument.CurrentController.Connect() 'If the query exists connect to the database'
     ThisDatabaseDocument.CurrentController.loadComponent(ObjTypeWhat, ObjName, FALSE) 'Open the query'      
End Sub

Removed parameter (just for test). Surround sAVAILABLE1 with quotes (guessing this is name of query). Attached to a button on a form. Runs. Sub does have more Dim statements than needed - possible future code.

EDIT:

Adding sample. Run by clicking button on only form. Asks for parameter - enter 0 or 1 or 3.

Eliminated extra Dim statements from code.

Sample -------RunQuery.odb

edit flag offensive delete link more

Comments

Ratslinger, Thanks for quick answer..I tried that and got message> BASIC runtime error. An exception occurred Type: com.sun.star.container.NoSuchElementException Message: sAVAILABLE1.

Do I need to remove some Dim Statements?

Pumper gravatar imagePumper ( 2020-11-07 08:24:03 +0200 )edit

I had properties set to table, not query. Now parameter box comes up, but get message: BASIC runtime error. An exception occurred Type: com.sun.star.container.NoSuchElementException Message: sAVAILABLE1.

Pumper gravatar imagePumper ( 2020-11-07 08:55:10 +0200 )edit

@Pumper,

sAVAILABLE1 as mentioned in my answer was a guess as to the name of your query. It is also surrounded by quotes.

Do I need to remove some Dim Statements?

No, but many of them are of no use. Just extra worthless lines of code.

Did test again with query using a parameter and ran without a problem. You are getting the error message because there is no query by that name.

If you continue to have problems, Edit your original question and add a sample Base file containing the problem. Please eliminate any personal or confidential information before posting.

I have also just noticed you are using a very old version of LibreOffice. Consider upgrading.

Ratslinger gravatar imageRatslinger ( 2020-11-07 16:00:00 +0200 )edit

I don't have the paper clip here!!!

Pumper gravatar imagePumper ( 2020-11-07 23:44:29 +0200 )edit

Ratslinger you were correct twice. The quotes around the query name was needed. 2nd There was no query, as sAVAILABLE1, should not have the s. I got the macro from one that opens a form, and it had an s before the name, which didn't make sense as a real name. I had seen a lower case s as prefix in other macros. Example:sSQL = "INSERT INTO " So it is solved, thank You very much.

Pumper gravatar imagePumper ( 2020-11-08 02:18:46 +0200 )edit

@Pumper,

As you have been helped, please help others to know the question has been answered by clicking on the ✔ in upper left area of answer which satisfied the question.

Ratslinger gravatar imageRatslinger ( 2020-11-08 03:13:06 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-11-07 05:01:37 +0200

Seen: 110 times

Last updated: Nov 08 '20