Button for Update Query in Base Macro

[cpb]

@TNBuilder,
don’t think I have ever seen this error message before.
.
I copied and used the wrong “FinishDate” values in my previous post.
this sql contains the same values that you successfully executed, could you please execute this from menu:>Tools>SQL and post the outcome.

UPDATE "Electricity Monthly Totals" SET "Cost" = 
(
	SELECT SUM("NoUnits" * "Power" * "OpDays" * 24 / 1000 * 0.09983) 
	+ 
	(
		SELECT SUM("Cost") 
		FROM "Expenses Table" 
		WHERE "ExpCatID" = 8 
		AND "AcctID" BETWEEN 4999000 AND 4999999 
		AND "Date" BETWEEN '2024-05-01' AND '2024-05-31' 
		AND "ConJobID" in(13,14) 
	) 
	FROM "Elec Est Table" 
	WHERE "FinishDate" BETWEEN '2024-04-16' AND '2024-05-15'
) 
WHERE "ElecTotalID" = 9

[TNBuilder]

Yes, this works. Results below:

[TNBuilder]

Hi RobertG
I had forgot to remove an apostrophe or i would have sent it with above. Thx!

[cpb]

@TNBuilder,
ok so lets try a slightly different approach.
1} hit Queries icon.
2) hit Create Query in SQL View.
3) menu:>Edit>Run SQL command directly.
4) paste the sql from my previous post.
5) save as “qDO_UPDATE”.
.
paste this code into a module, execute it directly from the IDE and post the outcome:

Sub DoUpdate
    Dim oContext As Object
    Dim oDatabase As Object
    Dim oConnection As Object
    Dim oStatement As Object
    Dim sSQL As String
    dim oQueries as object, oQuery as object
    
    oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
    oDatabase = oContext.getByName("New")
    oConnection = oDatabase.getConnection("", "")

    oQueries = oConnection.queries
    oQuery = oQueries.getbyname("qDO_UPDATE")
    sSQL = oQuery.command
    oStatement = oConnection.createStatement()
    oStatement.execute(sSQL)
'msgbox sSQL
End Sub

[TNBuilder]

Results:
The SQL (when ran by clicking on the “Run Query”, says “Statement does not generate a Result Set”)

And the Module

I tried to follow directions accurately. Thanks for all of your help! Sorry for response time.

[Villeroy]

This happens with any SQL statement other than SELECT statements.

1. save the SELECT part of the statement.
2. Test if it returns the right figure to update.
3. Right-click the query icon and save the query as a view.

Finally:

UPDATE "Electricity Monthly Totals" SET "Cost" = (SELECT "Cost" FROM "your_view").

update_button.odb (64.2 KB)
P.S. Documentation of the attachment:

1. The query returns one record per “Product” item from the “Data” table.
2. The view is derived from the query (right-click>“Create as view…”). The database software (HSQL) is unaware of any query, forms and reports, whereas views are queries that are saved in the realm of the database application.
3. Based on a view I can run an update statement more easily. The statement updates the “Products” table’s “Total” column.
4. The two form buttons run the same macro with different UPDATE queries in their “Additional info” properties.

• The macro reads the additional info from the calling button (ev.Source.Model.Tag) and gets the database connection from the button’s containing form (ev.Source.Model.Parent).
• The string is split by semicolons, if you want to run multiple SQL statements.
• For each statement a confirmation pops up, so you can cancel the execution.
• Another prompt reports the cound of affected records.
• Finally, the form is reloaded to show the changes.
• In case of error, the error string is reported.

Storing calculated data in cached tables is bad practice. In rare cases it may help to avoid running expensive queries over and over again. However, you should be aware that the stored values may not up to date.

[TNBuilder]

Wow, please dont take this wrong, I never believed that it would take so much to make my query into a button. But, Thank You!, This gives me something to work on.
BTW, I ran the above instructions, made my view, and it worked. I ran the UPDATE through TOOLS – SQL and it said it updated 16 rows, the amount of rows I have in the table, but only the correct month changed.
I am going to study the .odb you sent and see what I can figure out. Thanks again for all the help, and hopefully, when I get it accomplished, again hopefully within a few days, I’ll update the post.
My next project is how to open a report or form using a listbox, so wish me luck. Thanks!

[cpb]

you have to appreciate that I have been unable to test anything.
it would appear that star basic validates that the query is in fact a real query when executing the command getbyname(“QueryName”).
I took a look at some of my existing databases and can see that I used different coding:

oStatement.execute(oQueries.QUERY_NAME.command)

I wonder if you would take the time to try this, the code is only slightly different.
rename the query “qDO_UPDATE” as “DO_UPDATE” then execute this code from the IDE:

Sub DoUpdate2
    Dim oContext As Object
    Dim oDatabase As Object
    Dim oConnection As Object
    Dim oStatement As Object
    Dim sSQL As String
    dim oQueries as object, oQuery as object
    
    oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
    oDatabase = oContext.getByName("New")
    oConnection = oDatabase.getConnection("", "")

	oQueries = oConnection.queries
	oStatement = oConnection.createStatement()
	oStatement.execute(oQueries.DO_UPDATE.command)
'msgbox sSQL
End Sub

[TNBuilder]

I renamed to DO_UPDATE and received this error:

[TNBuilder]

Tada! Success. I was able to use this explanation to update tables from a button. At first, I forgot to limit it to only one entry, so all entries were updated, but a small edit to add a WHERE to the UPDATE statement in the Additional Information of the button solved the problem of having to do it manually. Thank you! And, thanks to all that helped. I received some great tips for a beginner.