Database is embedded Firebird.
Here’s want i want to do: present a field in a form where the user can input a year value, and then a button beside the field that will delete records from three tables which have a date field older than the year given by the user, thus executing three delete commands.
Now I’ve found items where a.o. Ratslinger replied that in LO base it is not possible to address a form field in an SQL-Command macro. But all these questions and answers are in IT-terms quite old, youngest dating from 2021. And they all address select commands, not update or insert or delete.
The workaround is to use a “filter” table to build this condition in a standard SQL-command. Is that still true today???
TIA
Could you get the text content of the form field by a macro? Then there shouldn’t be any problem to set an SQL-command depending on this text, which could be executed.
Don’t know anything about older threads where it shouldn’t be possible.
I’m sorry for being so forgetfull.
I created this application almost 4 years ago and since then did very little in development in LO. Had to do some in MS Access (not very willingly) in between and two such systems in one head is confusing.
Turns out I had sorted out that problem four years ago, by fetching the control field, then its value and then use that variable in the SQL command.
Tx for being so helpfull.
Of course, it is possible. Just use a parameter query and substitute the parameters with parent form values.
Yes, and it works far better (and easier) than any macro.
Even when working with macros, the actual record data are more helpful than form control values.
Well, this requires a macro, indeed.
Having a filter table storing the criteria to be used with a delete statement, create 3 SQL statements like:
DELETE FROM "TBL_1" WHERE YEAR("DT") < (SELECT "DT1" FROM "Filter" WHERE "ID"=0);
DELETE FROM "TBL_2" WHERE YEAR("DT") < (SELECT "DT1" FROM "Filter" WHERE "ID"=0);
DELETE FROM "TBL_3" WHERE YEAR("DT") < (SELECT "DT1" FROM "Filter" WHERE "ID"=0);
Adjust the quoted names and the row number 0 of your filter table.
Based on the same filter criteria, you can show the data to be deleted in a subform and you can add a push button where you put the 3 semicolon separated SQL statements into the “Additional info” box of the button properties. The following macro will read the statements from the additional info of its calling button. If cMsgbox = True, a message box will be shown to confirm each SQL statement.
Sub RunSQLButton(e)
Const cMsgbox = True
Const cMaxLen = 1000
Const cTitle = "Command "
oModel = e.Source.Model
frm = oModel.getParent()
oCon = frm.ActiveConnection
aTags() = split(oModel.Tag, ";")
n = uBound(aTags)
for i = 0 to n
s = trim(aTags(i))
sMsg = s
if len(s) > cMaxLen then sMsg = Left(s, cMaxLen) & cHR(10) &" [...]"
if len(s)>0 then
If cMsgbox then
x = Msgbox(sMsg, 33, cTitle & i +1 &"/"& n +1 )
else
x = 1
endif
if x = 2 then exit sub 'Cancel
oStmt = oCon.prepareStatement(s)
on error goto errMsg
r = oStmt.executeUpdate()
if cMsgbox then Msgbox r &" records affected", 64, cTitle
endif
next
frm.reload()
exit sub
errMsg:
error(err)
End Sub
Like this:
qa109607.odb (16.6 KB)