Jump to content

Macros/ScriptForge/IdentifySelectedCellInDatabaseQueryExample

From The Document Foundation Wiki


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, ...

A table as a result of a query, showing a custom menu.
A table as a result of a query, showing a custom menu.

How to run it in BASIC

  1. Open the Basic IDE
  2. Go to [My Macros and Dialogs].Standard library
  3. Select the first available blank module
  4. Copy and paste the Basic code
  5. 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

See also