How to create a macro for use in base form via a button?

[cristham]

Hi, I have a hard time in trying to figure out on how to create a macro on doing a routine repeatedly so that I do not have to execute the SQL in Tools->SQL repeatedly.

I have 2 tables. “Container” and “NEW”. I do not input data directly into Container but via NEW table.

My SQL is :

INSERT INTO “Container”(“Cont_ID” ) SELECT “Cont_ID” FROM “NEW”
WHERE “NEW”.“Cont_ID” IS NOT NULL;
DELETE FROM “NEW”

I can successfully insert new records into Container table and clear all data in NEW table with above SQL.

What I am planning is to include a button in my NEW Form where I can press to execute the above SQL command to update my Container table and then clear up all data in NEW table for next update.

I have read a lot about macro but I can’t really understand the basic.

Hope someone can help me on a sample of this macro.

Thanks so much in advance.

EDIT by @Hrbrgr - Screaming off

[Ratslinger]

Hello,

Unfortunately many who create an account here to ask a question never bother to try and comprehend how the site works. When you have signed in you are on the main page. On that page, there, in the right hand column, is a section called Resources. Please take just a minute or two to read How to use the Ask site. It’s not all encompassing but will provide basic information to ask a bit better question than you have.

There are also a few other links at the bottom of the page - FAQ & Help which are often overlooked and contain other information.

[Ratslinger]

Hello,

The answer to this question can actually take many different directions. For starters here is minimum code for the macro:

Sub executeMergeSQL
REM Get internal form and create an SQL statement object
    oForm = ThisComponent.Drawpage.Forms.getByName("YOUR_INTERNAL_FORM_NAME_HERE")
    oStatement = oForm.ActiveConnection.createStatement() 
REM Create SQL string
    stSQL = "INSERT INTO ""Container""(""Cont_ID"") SELECT ""Cont_ID"" FROM ""NEW"" WHERE ""NEW"".""Cont_ID"" IS NOT NULL"
REM Execute SQL for Insert
    oStatement.executeUpdate(stSQL)
REM Create SQL string
    stSQL = "DELETE FROM ""NEW"""
REM Execute SQL for Delete
    oStatement.executeUpdate(stSQL)
End Sub

Did not test but have no reason to believe it will not work. Used your SQL but you need to set your internal form name in oForm line (default I seem to recall is MainForm).

Please see my answer in this post → Operation in a sql in a macro. This may help you understand how to create the string for the macro based upon your existing SQL

This is also nothing in this macro about error processing. That is up to you. For more on that, see my answer in this post → CalcBASIC+Non-registred HSQLDB : How to deal with .odb.lck ?

Also, for other SQL (like SELECT) my answer in this post may be of interest → In base Convert Query to macro

Note the comment posted below your question. I did this mostly because you did not include some basic information in your question → OS, specific LO version and with Base the database being used and in some cases the connector to the database. While it does not affect this answer in many cases it plays a role and not providing may cause delays in getting an answer.

Hope this all is of help.

Edit: Forgot to surround DELETE statement with quotes. Above code corrected.

[Ratslinger]

As with all questions asked, if this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[cristham]

I tested your code just now and yes it works perfectly well as what I wanted. Thanks so much for making my day bright.