Macro - how to read row data from the current value of a Combo or Text Box

[jderica]

I’m using Libre Office 7.0.4.2 with firebird on a Windows machine.
I have two Forms, one with a Query(Q) as content that contains a combo box and another form with a table as content. I want the combo box to apply a filter on the second form, based on the users selection or key inputs.

I bound the following macro to the key pressed evenbt of the combo box.

EDIT: this is the macro that works well

SUB Filter_direct_GRAB_ID_SQL(oEvent as object)
	dim oForm as object
	dim oForm2 as object
	dim oField as object
	dim stListValue as string
	dim oStatement As Object
	dim sQuery As Object
	dim sSQL As String
	dim sID As String
	
	oField = oEvent.Source.Model
	oForm = oField.Parent
	oForm2 = ThisComponent.Drawpage.Forms.getByName("L1PR")
	stListValue = oField.getCurrentValue()
	
	IF stListValue = "" THEN
		oForm2.Filter = ""
	ELSE
 		sSQL = " SELECT DISTINCT ""ID_PR"" FROM ""Q_L1PR_SELECT_SEARCH"" WHERE ""PR_SELECT"" LIKE '" & stListValue & "' "
   		oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()
 		sQuery = oStatement.ExecuteQuery(sSQL)
		sQuery.Next()  
		sID = sQuery.GetString(1)
		oForm2.Filter = "ID_PR = '" + sID + "'"
	END IF
	oForm2.reload()
END SUB

Still haven’t found a way to get this version to work (this applies a filter of “ID_PR” = ‘1’ for any selection:

SUB Filter_direct_GRAB_ID(oEvent as object)
	dim oForm as object
	dim oForm2 as object
	dim oField as object
	dim stListValue as string
	dim iID as integer
	
	oField = oEvent.Source.Model
	oForm = oField.Parent
	oForm2 = ThisComponent.Drawpage.Forms.getByName("L1PR")
	stListValue = oField.getCurrentValue()

	IF stListValue = "" THEN
		oForm2.Filter = ""
	ELSE
	iID = oField.getCurrentValue().getColumns().getByName("ID_PR").getInt()
		oForm2.Filter = "ID_PR = '" + iID + "'"
	END IF
	oForm2.reload()
END SUB

[jderica]

i added

dim result

Now I’m getting an error

BASIC runtime error.
An exception occurred
Type: com.sun.star.sdbc.SQLException
Message: Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 91; � [SQLState:42000, ISC error code:335544634].

[Ratslinger]

Hello,

You may find it helpful that you can get the selected value from oEvent:

stListValue = oEvent.Source.Model.SelectedValue

Will save some coding.

[jderica]

Got it to work! The double quotes on the < LIKE (" & stListValue & “) > was the culprint. It works well now with < LIKE '” & stListValue & "’ > .

I’ll update the main post.