BASIC runtime error oDS=ThisDatabaseDocument.CurrentController

SteveRay · December 3, 2025, 2:27pm

I keep getting a BASIC runtime error Variable not defined when doing the following
oDS = ThisDatabaseDocument.CurrentController
oDS.connect()
oConnection = oDS.ActiveConnection()

	I also tried the following which gives the same error
          oConnection = ThisComponent.DataSource.getConnection("","")

the first line causes the error. The subroutine will run one time when i first open the data base, but will not run after that.
I am using FireBird embedded on a linux system
I am a newbe for sure
I am running this macro from the GUI and from the macro editor, not attached to any event

sokol92 · December 3, 2025, 2:45pm

Demonstration of this type of error:

Option Explicit

Sub Test()
  v = 1
End Sub

The solution is simple: add variable declarations (not necessarily with types):

Sub Test()
  Dim v as Long
  v = 1
End Sub

RobertG · December 3, 2025, 6:00pm

Where did you save the macro code? Should be saved inside the database file.

Villeroy · December 3, 2025, 6:12pm

Having a form (embedded of not) calling some macro (embedded or not):

Sub Some_Form_Event(ev)
oForm = ev.Source
oConnection = oForm.ActiveConnection

End Sub

Sub Some_FormControl_Event(ev)
oModel = ev.Source.getModel()
oForm = oModel.getParent()
oConnection = oForm.ActiveConnection

End Sub

SteveRay · December 3, 2025, 3:06pm

All variables are declared as type object

SteveRay · December 3, 2025, 3:09pm

I am trying to run an SQL update query, which sometimes works when this error does not occur

sokol92 · December 3, 2025, 4:46pm

Could you upload a sample of the problematic file and provide the sequence of actions that cause the problem.

SteveRay · December 3, 2025, 7:54pm

Sub UpdateRecordFromAnotherTable
Dim oDS As Object
Dim oConnection As Object
Dim oStatement As Object
Dim sSQL As String

	'This does not work from button push event or running from macro tool. I get a    variable not defined error
	oDS = ThisDatabaseDocument.CurrentController
	oDS.connect()
	oConnection = oDS.ActiveConnection()
	
	'The following works when called from a button push event
    ' Get the current database connection
    ' oConnection = ThisComponent.Parent.DataSource.getConnection("","") 
   
    ' Create a SQL statement objectPa
    oStatement = oConnection.createStatement()

    ' Construct the SQL UPDATE statement
    ' Replace "TargetTable", "TargetColumn", "SourceTable", "SourceColumn", "JoinColumn", and "Condition"
    ' with your actual table and column names.
   	sSQL = "UPDATE ""BidItemDetailT""	 " & _
   		" SET ""Description"" = (SELECT ""ResorceT"".""Description"" FROM ""ResorceT"" " & _
    	"WHERE ""ResorceT"".""Resorce_ID"" = ""BidItemDetailT"".""Resorce_ID"") " & _
   		"WHERE ""BidItemDetailT"".""Resorce_ID"" = 2"

	'Set mulitple fields value from another table
	'sSQL = "UPDATE ""BidItemDetailT""	 " & _
	'	" set ""BidItemDetailT"".""Description"" = ""ResorceT"".""Description"", " & _
	'		"	""BidItemDetailT"".""Price"" = ""ResorceT"".""Price"" "& _
	'	"From ""ResorceT""	 WHERE ""BidItemDetailT"".""Resorce_ID"" = 8 "
	'	"WHERE ""BidItemDetailT"".""Resorce_ID"" = 8"

    ' Execute the SQL statement
    oStatement.executeUpdate(sSQL)

    ' Close the connection (optional, but good practice if not immediately reused)
    'oConnection.close()

    MsgBox "Record updated successfully!", 0, "Update Status"

End Sub

this is the whole sub routine the parts that are commented out don’t work. The second sSQL string is where i am trying to copy some of the values from another table.

Are attachments possible?

SteveRay · December 3, 2025, 8:10pm

according to libreOffice Base Guide 7.3 in Chapter 9 Macros

Dim oDatasource as object
Dim oConnection as object

oDatasource = ThisDatabaseDocument.CurrentController
If Not (oDatasource.isConnected()) Then oDatasource.connect()
oConnection = oDatasource.ActiveConnection()

Says: if the macro is to be launched through the user interface and not from an event in a form
This is not working for me.

Thanks for the responses.

{Code formatting by @Lupp.}

RobertG · December 4, 2025, 7:13am

Please don’t write answers - all what you have written are comments, because nothing seems to solve your problem.

Add an example file so we could have a look.

Add a screenshot of the macro editor with your macro.

CRDF · December 4, 2025, 10:27am

To me here the connect works when running it directly from Macros GUI.

Option Explicit
Sub Conectar
	'Globalscope.BasicLibraries.LoadLibrary("MRILib")
	Dim CC As Object, Con As Object, Stmt As Object, Rst As Object
	Dim sSQL As String, rating As String
	On Error GoTo Erro
	REM CONSTRUCT THE SQL
	sSQL = "SELECT ""rating"" FROM ""Estrelas"" WHERE ""eid"" = 0"
	print sSQL
	REM SET CONNECTION VARIABLES
	CC = ThisDatabaseDocument.CurrentController
	CC.connect()
	Con = CC.ActiveConnection()
	Stmt = Con.createStatement()
	REM EXECUTE THE QUERY
	Rst = Stmt.executeQuery(sSQL)
	Rst.next
	rating = Rst.getString(1)
	print rating
	Exit Sub
	Erro: MsgBox "ERRO " & Err & Chr(10) & "na linha " & Erl
End Sub

ConFromGUI

cpb · December 4, 2025, 4:17pm

@SteveRay,
where you ‘Set mulitple fields value from another table’:
you are missing a closing bracket “)” and your UPDATE statement is just plain wrong.

try this, execute directly from the IDE:
EDIT: twice mistakenly used “ID”, have replaced with "Resorce_ID"

Sub DoUpdate
	dim oController as object, oCon as object, oStatement as object
	dim sSQL$
	oController = thisdatabasedocument.currentcontroller
	if isnull(oController.activeconnection) then oController.connect
	oCon = oController.activeconnection
	oStatement = oCon.createstatement
	
	sSQL = "update ""BidItemDetailT"" " &_
	"set " &_
	"""Description"" = (select ""Description"" from ""ResorceT"" where ""Resorce_ID"" = 8), " &_
	"""Price"" = (select ""Price"" from ""ResorceT"" where ""Resorce_ID"" = 8) " &_
	"where ""Resorce_ID"" = 8" &_
	";"

	oStatement.executeupdate(sSQL)
End Sub

SteveRay · December 4, 2025, 7:15pm

I get a BASIC runtime error, Variable not defined when i run this. program stops at:
oController = thisdatabasedocument.currentcontroller

I am running it from the main IDE window

RobertG · December 4, 2025, 7:38pm

All this macros should be saved in the Base file and should be started from the Base GUI, not from the Basic-editor. How should the Basic editor know anything about ThisDatabaseDocument? It isn’t a database document.

cpb · December 4, 2025, 8:09pm

have you created a module in your embedded Firebird db?
.
open your database
from your database menu:Tools>Macros>Edit Macros
expand your db name
expand Standard
do you see a module?

if yes
double click your module and paste the sub
click somewhere in the sub
hit F5 to execute
.
if no
hit ‘Modules’ icon on top tool bar
expand your db name
click on Standard
hit New
hit OK (will create a module called ‘Module1’)
close the pop-up
double click on ‘Module1’
delete everything in the window
paste the sub
click somewhere in the sub
hit F5 to execute

CRDF · December 4, 2025, 8:14pm

RobertG · December 4, 2025, 8:40pm

@CRDF : And? Where did you save this code? If i is saved in “My Macros & Dialogs” it won’t work, because This is the Basic editor.