Page 1 of 1

Reset Button

Posted: Tue Nov 29, 2016 12:15 pm
by RogerC
I am new to Open Office and programming. My problem is that I have a great deal of text info in the records of a database. Users can select what data they require by ticking a a box which is part of the relevant record. This is then filtered using a query and presented as a Report. What I would love to do is to use a button to reset all of the selected boxes back to "False" to avoid confusion on the next selection and report generation. I have managed to do this using the Sql facility (Update -Set) but that would be too complicated of the users of my database Thanks in advance for any help received. :)

Re: Reset Button

Posted: Tue Nov 29, 2016 6:08 pm
by UnklDonald418
I have managed to do this using the Sql facility (Update -Set) but that would be too complicated of the users of my database
The Open Office API has a Statement Service for executing SQL statements via a macro.
Place the following macro in the macro library for your database document

Code: Select all

Sub ExecuteSQLviaStmtServ

Dim Context
Dim DB
Dim Conn 
Dim Stmt
Dim Result
Dim strSQL As String


FormUrl = ThisComponent.Parent.URL
Context=CreateUnoService("com.sun.star.sdb.DatabaseContext")
DB=Context.getByName(FormUrl)
Conn=DB.getConnection("","")
Stmt=Conn.createStatement()
REM note that any Quotation marks ”  in the SQL statement must be doubled 
REM ie Update “Table1” SET “checkBox1” = FALSE
REM would need to be strSQL = ”Update  ”” Table1”” SET ”” checkBox1”” = FALSE” 
strSQL = “Your SQL statement"
Result = Stmt.executeUpdate(strSQL)
Conn.close()

End Sub
You will need to edit the macro to include Your SQL statement, formatted as explained in the macro REMarks

Next right click on the Reset Button on your form and select Control to get to the Control Properties dialog.
Click on the Events tab and then on the ellipsis next to Approve action and complete the dialog to connect the button event to the macro.
Also note that for this to work your database must be registered.