Page 1 of 1

[Solved] Open a database query from a button

Posted: Thu Feb 07, 2008 9:57 pm
by Echelon
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/ ... 54&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.

Re: Open a database query from a button

Posted: Fri Feb 08, 2008 5:19 pm
by DrewJensen
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?

Re: Open a database query from a button

Posted: Fri Feb 08, 2008 6:04 pm
by Echelon
I simply wanted to run the query as if it had been double clicked from the list of queries in the GUI.

Re: Open a database query from a button

Posted: Fri Feb 08, 2008 6:12 pm
by DrewJensen
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.

Re: Open a database query from a button

Posted: Fri Feb 08, 2008 7:23 pm
by Echelon
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!

Re: Open a database query from a button

Posted: Sat Feb 09, 2008 6:38 am
by DrewJensen
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

sub onClickButton( oEvent as object )
      openQueryDataView( "Query1", oEvent.Source.Model.Parent.ActiveConnection )
end sub
And here is the procedure openQueryDataView:

Code: Select all

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.

Re: Open a database query from a button

Posted: Sat Feb 09, 2008 10:37 pm
by Echelon
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.

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

Posted: Sun Feb 10, 2008 12:01 am
by DrewJensen
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.