Libre Office database

How to set up a form to use a macro to call a bit of SQL code, and
update a field in all records with a given value in one of the other fields.
(The text to go into the updated field is determined by user interactively.)

(Page's URL: fdb6updateselectedrecords.htm)

You may notice that links in this take you to http(no S)://SheepdogGuides.com/...". (And links from there take you here (httpS://wywtk.com/...") I control both sites. Neither asks you for any information, so I would argue that the "S" element is not an issue. Do you know what it would cost me to get an "S" certificate for SheepdogGuides.com? If you want to pay the annual fee for that, I'll happily accept your $upport.

This is not a "typical" "tutorial" from me, sorry. I merely shows you something that worked, but without much explanation.

Heart of the story

"All" you need to do is set up a form with two text boxes and a button. Create a macro called when the button is clicked. It sends a line of SQL to the page. The punctuation in the following was the issue that took me the longest time to get right...

Messy details

Generally speaking, my environment is Windows 10, Libre Office, embedded Firebird (when database work is involved.) I did what follows in August 2021, using LO 7.1.5.2. The macro worked under 7.1.2.2 also.

Imagine you had the following...

... and you wanted to change the entry in all of the records which had "Kipling" in the "Author" field. Wanted to make the "ToBeFilled" entry for those records "KipRud". i.e....

Remember: Once there is a macro in your document, you must tell LibreOffice that you are willing to allow macros. (They can be a source of malware.)

"All" you need to do is...

Add the following macro to your system. As a beginner, I prefer to "add" macros JUST to the document that uses them.

REM  *****  BASIC  *****

Sub ButtonClick (oEvent)

'This updates records in a table, based in user input to two text boxes)
'  It does all the records that match a criterion
'  The "match" is case sensitive.
'Pretty well "working", as of 30 Aug 2021, 15:32

'With effusive thanks to...
'https://forum.openoffice.org/en/forum/viewtopic.php?p=192460

'Declare constants
sControlMatchFieldName="tbToMatch"
sControlFillWithName="tbFillWith"

oForm=oEvent.source.model.parent 'get the calling Form from a control

'get the ...
oControlMatch=oForm.getbyname(sControlMatchFieldName)
oControlFillWith=oForm.getbyname(sControlFillWithName)

sToMatch=oControlMatch.text
sToFillWith= oControlFillWith.text

'MsgBox(sToMatch+" ..xxx "+sToFillWith) 'For debugging work

oStatement = oForm.ActiveConnection.createStatement() 'Create an SQL statement object

'ONLY TOOK MOST OF A DAY TO GET sSQLCmndToDo= EXACTLY right!!!

'Create an SQL command...
'WORKS! 30 Aug 2021, 15:00: sSQLCmndToDo="UPDATE ""Books"" SET ""ToBeFilled""='zzz' WHERE ""Author""='Kipling'"
'The "match", e.g. 'Kipling' IS case sensitive

sSQLCmndToDo="UPDATE ""Books"" SET ""ToBeFilled""='"+sToFillWith+"' WHERE ""Author""='"+sToMatch+"'"

'MsgBox(sSQLCmndToDo)

oStatement.executeUpdate(sSQLCmndToDo) 'Execute the SQL command

oForm.Reload()'Does refresh so what's on screen reflects post update state of table.

End Sub

You must edit the macro to reflect the names of the fields you want involved. As long as it is called by a form of the relevant database, it will be "okay" as far as that is concerned... but you will have to edit the SQL in the macro to tell it what table to access. (The code presented here is written to alter the "Books" table.

... and then create a form with the relevant TextBoxes and a button. I'd suggest you use the wizard to create a simple "display the table as datasheet, all fields" form, and then edit it to add the text boxes and button. Note that you must use the text box names used in the macro (tbToMatch, tbFillWith) ((or edit the macro!)). EvErYtHing here is case-senSiTiVe!

Configure the button so that its "OnExecute" event triggers the macro you set up.

Annoying fly in my soup...

For some reason, I can't get the form to allow the user to directly edit the data in the table, using the Data Sheet presented on the form. Any ideas? Yes, the component is "enbled".

Using tab does a strange thing to the cursor in the Data Sheet, but nothing troublesome.

A few words from the sponsors...

Please get in touch if you discover flaws in this page. Please mention the page's URL. It is: wywtk.com/libo/fdb/fdb6updateselectedrecords.htm

If you found this of interest, please mention in forums, give it a Facebook "like", Google "Plus", or whatever. If you want more of this stuff, help!? There's not much point in me writing these things, if no one hears that they are available.

Unlike the clever Google search engine, this one merely looks for the words you type, so....
*    Spell them properly.
*    Don't bother with "How do I get rich?" That will merely return pages with "how", "do", "I"....

Please also note that I have three other sites, and that this search will not include them. They have their own search buttons.

My SheepdogSoftware.co.uk site, where you'll find my main homepage. It has links for other areas, such as education, programming, investing.

My SheepdogGuides.com site.

My site at Arunet.

Page has been tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org. It passes in some important ways, but still needs work to fully meet HTML 5 expectations..

AND passes...

....... P a g e . . . E n d s .....