Running a simple query from a macro

[nebergall]

I created a simple query.
I’d like to imbed it in a macro that I can call from a button in a form.
I created a macro containing the query based on an LibreOffice example.
When I run the macro, I get a “Variable not found” for the line that starts “oConn”, yet oConn is clearly defined.
Also, I may not have the correct number of quotation marks in my SQL_string string - I’m learning.
The macro code is shown below.
What am I doing wrong?
Thanks

REM routine to display the highest WO#
Sub Last_WO
Dim oConn as Object
Dim SQL_string as String
Dim oQuery as Object
oConn = ThisDatabaseDocument.DataSource.getConnection("","")
SQL_string = “SELECT MAX( ““WO#”” ) FROM ““OHA_DB-WO-New”””
oQuery = oConn.createStatement()
oQuery.executeQuery(SQL_string)
End Sub

[RobertG]

This macro is designed to start from the Base-GUI. Did you try to start it directly in macro editor? Then it wouldn’t know anything about ThisDatabaseDocument.

[nebergall]

Robert,

Yes.
Is the base GUI the form (with the button) that I want to execute it from?
Thanks,
Erik

[RobertG]

Yes, it’s the button. Did you start it from this button?
Start with

oDatasource = thisDatabaseDocument.CurrentController
IF NOT (oDatasource.isConnected()) THEN oDatasource.connect()
oConn = oDatasource.ActiveConnection()

But you could also start it by the event of the button:

Sub Last_WO(oEvent AS OBJECT)
oForm = oEvent.Source.Model.Parent
oConn = oForm.activeConnection()

[fpy]

@nebergall

created a simple query

based on what info / source ?

[nebergall]

Robert,

I’d like to run it from an event in the button definition.
Could you please correct my macro code so it will execute when the macro is called by the button event.
Thanks,
Erik

[RobertG]

Sub Last_WO(oEvent AS OBJECT)
Dim oForm AS OBJECT, oConn as Object, oQuery as Object, oResult AS OBJECT
Dim SQL_string as String, stVar AS STRING
oForm = oEvent.Source.Model.Parent
oConn = oForm.activeConnection()
SQL_string = "SELECT MAX( ""WO#"" ) FROM ""OHA_DB-WO-New"""
oQuery = oConn.createStatement()
oResult = oQuery.executeQuery(SQL_string)
WHILE oResult.next
   stVar = oResult.getString(1)
   msgbox stVar
WEND
End Sub

When getting the result you have to step to next to get the first value of the result. You could get nearly all results as string. I prefer getString if I don’t want to calculate with the result.

[nebergall]

Robert,

Wow. That’s very different.
Thanks. I’ll give it a try.
Erik

[nebergall]

Robert,

1. I copied your updated macro you kindly provided into my macro library and called it from a button in my form.
   It executes, but stops at the line: “oConn = ThisDatabaseDocument.DataSource.getConnection(”",””)" with the error: "Variable not found”. This is where my original macro stopped as well. Any ideas? (The "OHA_DB-WO-New” table is the third in a series of related tables: OHA_DB-Customer > OHA_DB-Aircraft > OHA_DB-WO-New, if that matters.)

I have a two other questions as well:
2) Where is the oEvent argument (in the subroutine definition) used or assigned in the macro?
3) Since the result of the macro is a single number, I don’t believe I need the When code. If this is the case, how do I convert the object oResult into a string for displaying in the msgbox?

Thanks for your help.
Erik

[RobertG]

I see: It’s a duplicate, while I copied the old content from you. Have corrected in original post.

[Villeroy]

Using the OpenOffice/LibreOffice database component since 23 years, I never needed any macro code to show the aggregate of a column.
The infamous “form wizard” is fake. You can create huge hierarchies of forms and subforms at any depth and link them in many ways that are not covered by the “form wizard”.
Trivial example https://ask.libreoffice.org/uploads/short-url/gK9hcyzEnMVotx6Mtmr6CqCDsPR.odb with a main form storing filter criteria (from data, until date, person, category), a subform showing the filtered data and another subform showing some aggregates of the filtered data (sum, min, max, average).

[nebergall]

Robert,

What do you mean by original post? Where can I find it? Or could you please provide the code again here so there is no confusion. Also, did you see my other questions?
Thanks. I really appreciate your help!
Erik

[RobertG]

Look a little above. Running a simple query from a macro - #7 by RobertG

[Wanderer]

@RobertG

Look a little above

It may not work for him. There are little envelopes shown in his post. So I guess @nebergall answeres directly to to email-notifications of our posts and is not using the website ask.libreoffice.org

[nebergall]

Robert,

It works!. Thanks,

• How can I add one (1) to the value returned to show the next work order number?

• Also, do I need the When code since the result will always be a single number, and so, what code needs to remain?

A big thanks again.
Erik

[RobertG]

You could set without WHILE … WEND for only one value.

oResult.next
stVar = oResult.getString(1)

If you want to get next value you could change the query:

SQL_string = "SELECT MAX( ""WO#"" ) + 1 FROM ""OHA_DB-WO-New"""

[nebergall]

Robert,
Thanks for responding to my When Question. I will give it a try.
Erik

[nebergall]

Robert,

Both suggestions worked great.
Thanks,
Erik