Listbox in table control: change ListSource property w/ macro?

[fcsw]

Windows 10 Pro 22H2 19045.5371
LibreOffice Version: 24.8.4.2 (X86_64)

I’ve got a subform containing trees structured as adjacency lists. Each tree is linked to one of the records in the main form. The subform records are contained in a table control where the last column is a listbox rather than the usual textbox. The listbox column contains the ID of the record’s parent node. I want the listbox to show only the IDs for the records in the current tree, so the user can only link a child node to a parent node in the current tree.

I know this can be done with a values table, but this may become multi-user and I think a values table is really single-user. I’ve bookmarked ratslinger’s code for inserting values into a table-listbox. However, according to the documentation, a standard listbox has a ListSource property that I can insert an SQL Select into. (The documentation says “ListSource”. The listbox itself says “List content”.) This would be my favored solution, but I don’t know how to get to the ListSource property through the API. The documentation tells me how to do this with a stand-alone listbox, but not a table-listbox, and my experiments have only generated errors.

Does anyone know how to use the API to access the ListSource property in a listbox inside a table control?

[RobertG]

Have a look here: Base Guide - Listbox in a macro
You will get the tablecontrol the same way as a normal control.
Form: oForm = oDrawpage.Forms.GetByname(“MainForm”)
Tablecontrol: oTablecontrol = oForm.getByname(“MyTableControl”)
Listbox in the tablecontrol: oListbox = oTablecontrol.getByName(“MyListBox”)

Remember: You could only change the data source for the whole content a a column in a tablecontrol. Changing data only for one row of the tablecontrol isn’t supported.

[fcsw]

Thanks for the reply, but it doesn’t help. I’m very familiar with the Macro chapter in the Base Guide. The listbox code in the chapter works fine with a stand-alone listbox. It doesn’t work with a listbox in a table control. I know how to get a column object from a table control. The problem is that I can’t write to the column’s ListSource.

Sub ChangeListSource
    Dim objForm As Object
    Dim objSubForm As Object
    Dim objTableCtl As Object
    Dim objColumn As Object
    Dim strSql As String

    objForm = ThisComponent.getDrawPage().getForms().getByName("mnFrmProviders")
    objSubForm = objForm.getByName("subFrmAddress")
    objTableCtl = objSubForm.getByName("tblCtlAddress")
    objColumn = objTableCtl.getByName("ParentID")
    strSQL = "Select ""ID"" FROM ""tblProviderAddresses"" WHERE ""ProviderID"" = 1"
    objColumn.ListSource = strSql
End Sub

This returns an “Object variable not set” error. If I change the last line to

    oColumn.ListSource(0) = sSQL

I get no error, but it doesn’t write the new SQL statement to the ListSource property. I’ve tried “InsertItem” and “InsertItemValue”, which are methods that are exposed by other objects with array based properties, but they produce errors here.

The question is not how to access the column. It’s how to write to the column’s listbox ListSource property.

[RobertG]

Code you have written is wrong:
strSql(0) → should be an array with one value and should be declared as array:
DIM strSql(0) AS STRING
strSql(0) = “SELECT …”
and then the complete array should be submitted to ListSource:
objColumn.ListSource = strSql

Works here in a table control without any problem - says the original author of the whole Base Guide (German: Base Handbuch).

[fcsw]

On second thought, it would be better to just thank you for your help, instead of making a jokey response.