[Solved] Open a database query from a button

Creating a macro - Writing a Script - Using the API

[Solved] Open a database query from a button

Postby Echelon » Thu Feb 07, 2008 9:57 pm

What can I use in place of ".FormDocuments" when I want to open a query rather than a form?

I have a database which is being accessed via a single menu form with buttons that link to three other forms, using a macro similar to that described in this previous topic:
http://user.services.openoffice.org/en/forum/viewtopic.php?f=39&t=354&p=10437

The problem I am having is that I also want to have a button that opens a database query using a similar macro, but this is not working since the query is not a form and therefore not recognised in the "OEvent.Source.Model.Parent.ActiveConnection.Parent.DatabaseDocument.FormDocuments" parameter.

Any assistance in finding the right parameter or a macro that would achieve this would be appreciated, or even if this is actually possible.
Last edited by Echelon on Sat Feb 09, 2008 10:38 pm, edited 1 time in total.
Echelon
 
Posts: 11
Joined: Sun Feb 03, 2008 12:37 pm

Re: Open a database query from a button

Postby DrewJensen » Fri Feb 08, 2008 5:19 pm

Do you want to simply run the query and display the records ( as if you had double clicked it in the GUI )

Or

Do you want to open it for editing?
The Document Foundation member
Apache OpenOffice Incubator podling PPMC member
LibreOffice & OpenOffice.org on Ubuntu 11.04 + Windows XP - 7
User avatar
DrewJensen
 
Posts: 1733
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Open a database query from a button

Postby Echelon » Fri Feb 08, 2008 6:04 pm

I simply wanted to run the query as if it had been double clicked from the list of queries in the GUI.
Echelon
 
Posts: 11
Joined: Sun Feb 03, 2008 12:37 pm

Re: Open a database query from a button

Postby DrewJensen » Fri Feb 08, 2008 6:12 pm

OK

Well, I am having a little difficulty with that myslef - although it has been a little while since I last tried.

The issue is that the query window is not acutally a document ( as is a form ) but rather a child window of the Base document. My thnking is that it should be doable with something like

aconnection.queries.getByname( "Query1").Execute( <parameters> )

( as an aside it is not the only way to go, and I might be wrong - last time I worked this I was able to get an empty query definition window open but never successfully connected it back to the parent datasource - which is kind of important if you really want the data...LOL )

The parameters for the execute function is what are driving me a little bonkers - anyway I am sending an email to the developers list at the base project now asking for some help on this ( as I need this for some community work presently ) - they are usually pretty good about getting back so should have something in the next day or 2.

I'll post as soon as I hear back.
The Document Foundation member
Apache OpenOffice Incubator podling PPMC member
LibreOffice & OpenOffice.org on Ubuntu 11.04 + Windows XP - 7
User avatar
DrewJensen
 
Posts: 1733
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Open a database query from a button

Postby Echelon » Fri Feb 08, 2008 7:23 pm

Thanks very much Drew. If you do hear anything from the devs I would be much obliged if you would let me know. It seems like there should be a simple solution, but I just can't seem to find it and the constant supply of runtime and syntax errors are driving me insane!
Echelon
 
Posts: 11
Joined: Sun Feb 03, 2008 12:37 pm

Re: Open a database query from a button

Postby DrewJensen » Sat Feb 09, 2008 6:38 am

Well, not sure this is THE final answer - but it works.

You have form with a button, the form is in a database with a query "Query1".

The button could assigned then to this procedure:

Code: Select all   Expand viewCollapse view
sub onClickButton( oEvent as object )
      openQueryDataView( "Query1", oEvent.Source.Model.Parent.ActiveConnection )
end sub

And here is the procedure openQueryDataView:
Code: Select all   Expand viewCollapse view
sub OpenQueryDataView( aQueryName as string, aConnection as variant )

    oDesktop = createUnoService("com.sun.star.frame.Desktop")
   
    Dim aURL as New com.sun.star.util.URL
    aURL.Complete = ".component:DB/DataSourceBrowser"
   
    oDispatchObject = oDesktop.queryDispatch(_
                        aURL, _
                        "_Blank",_
                        com.sun.star.frame.FrameSearchFlag.CREATE)
   
    Dim aProps(5) as New com.sun.star.beans.PropertyValue
    aProps(0).Name = "ActiveConnection"
    aProps(0).Value = aConnection
    aProps(1).Name = "CommandType"
    aProps(1).Value = com.sun.star.sdb.CommandType.QUERY
    aProps(2).Name = "Command"
    aProps(2).Value = aQueryName
    aProps(3).Name = "ShowMenu"
    aProps(3).Value = True
    aProps(4).Name = "ShowTreeView"
    aProps(4).Value = FALSE
    aProps(5).Name = "ShowTreeViewButton"
    aProps(5).Value = FALSE   
   
    oDispatchObject.dispatch(aURL, aProps)

End Sub


With a few changes you can make that work with a Table or a SQL select statement. ( I will put together a more expansive example for the snippet forum ASAP witht those options )

By the way - didn't ever hear from a developer, rather this was result of some collaborative 'digging' on the list with Ariel Constenla-Haile..who gets most of the credit here.
The Document Foundation member
Apache OpenOffice Incubator podling PPMC member
LibreOffice & OpenOffice.org on Ubuntu 11.04 + Windows XP - 7
User avatar
DrewJensen
 
Posts: 1733
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Open a database query from a button

Postby Echelon » Sat Feb 09, 2008 10:37 pm

Drew, many thanks for this. The code appears to be working perfectly and I have been able to achieve exactly what I was looking for, so I'll mark this as Solved.
Echelon
 
Posts: 11
Joined: Sun Feb 03, 2008 12:37 pm

Re: [Solved] Open a database query from a button

Postby DrewJensen » Sun Feb 10, 2008 12:01 am

Well- for one thing there is a better version of the macro now - but alas it is still not quite correct. The new window is not linked into the hierarchy of Base windows as it should be - so if you use the macro above and then close the Base file, while the query result window is still open it will be left on screen as an empty shell. Not the worst bug in the world, but a bug none the less - hopefully on Monday we can get a little help with that last step of linking in the propert fashion. When that is finished I;ll put a revised version here.
The Document Foundation member
Apache OpenOffice Incubator podling PPMC member
LibreOffice & OpenOffice.org on Ubuntu 11.04 + Windows XP - 7
User avatar
DrewJensen
 
Posts: 1733
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 7 guests