Macros/ScriptForge/IdentifySelectedCellInDatabaseQueryExample
Appearance
< Macros | ScriptForge
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
How to identify the selected cell in a database query
Authored by Jean-Pierre Ledure.
The example opens a datasheet based on the Bibliography database. The query is expressed as a SQL SELECT statement. A simple menu is created in the menubar of the datasheet. The first menu item reads the current cell and displays a simple message box. In the real world, identifying the cell, the row, the record, etc. creates the opportunity to run many rich functions, like opening a sub-datasheet, opening a form, loading a spreadsheet with database data, ...

How to run it in BASIC
- Open the Basic IDE
- Go to [My Macros and Dialogs].Standard library
- Select the first available blank module
- Copy and paste the Basic code
- Run Main()
Code
REM How to identify the selected cell in a database query ?
REM Minimal required version: LibreOffice 7.6
REM Used services
REM SFDatabases.Database, SFDatabases.Datasheet, SFWidgets.Menu
Option Explicit
Global database As Object ' The parent database
Global datasheet As Object ' The datasheet
'*******************************************************************
'*** Adjust module name if not "Module1"
'*******************************************************************
Const subreadcell = "vnd.sun.star.script:Standard.Module1.ReadCell?language=Basic&location=application"
Const subcloseall = "vnd.sun.star.script:Standard.Module1.CloseAll?language=Basic&location=application"
'*******************************************************************
'* Run the demo below
'*******************************************************************
Sub Main(Optional event As Object)
GlobalScope.BasicLibraries.loadLibrary("ScriptForge")
Set database = CreateScriptService("Database", , "Bibliography")
SetupDatasheet(database)
End Sub
'*******************************************************************
'* Specific demo code
'*******************************************************************
Sub ReadCell(Optional event)
' Triggered by the Read Cell menu item in the datasheet
With datasheet
MsgBox "Column header = " & .CurrentColumn _
& Chr(10) & "Row number = " & .CurrentRow _
& Chr(10) & "Value = " & .GetText(.CurrentColumn) _
, MB_OK, "ScriptForge"
End With
End Sub
'*******************************************************************
'* Housekeeping
'*******************************************************************
Sub CloseAll(Optional event)
' Triggered by the Close menu item in the datasheet
datasheet.CloseDatasheet()
database.CloseDatabase()
End Sub
'*******************************************************************
'* Setup the datasheet and add a menu in the menubar
'*******************************************************************
Sub SetupDatasheet(database As Object) As Object
Dim SQL As String ' A SQL statement
Dim menu As Object ' A menu service instance
SQL = "SELECT [Author], [Pages], [Publisher], [Title], [Year], [Custom1] As [Language], [ISBN] FROM [biblio]"
Set datasheet = database.OpenSql(SQL, directsql := True)
Set menu = datasheet.CreateMenu("ScriptForge")
With menu
.AddItem("Read cell", Script := subreadcell)
.AddItem("Close", Script := subcloseall)
.Dispose()
End With
End Sub