Use a choice from a drop-down sourced from a query as a query parameter

NickSamuel · August 7, 2025, 1:29pm

Sorry for having to ask this but I have looked at lots on content without being able to understand how to do this.

I have a query ‘A’ with a parameter that selects a subset of results by filtering against a field ‘X’.
I have a query ‘B’ that lists all unique values for field ‘X’.

What I want to do is have drop-down of the result of query ‘B’ and to select a value from the drop-down to use as the parameter in query ‘A’.

Presumably a combination of sub-forms, combo boxes etc. but I cannot see how to do it.

CRDF · August 7, 2025, 3:33pm

Have neither LO nor a dummy DB in this machine to check. But I think you could try setting the “ListBoxA” ListSource:

Sub SetA(evt As Object)
    Dim oLBA As Object, oLBB As Object
    Dim sSQL(0) As String ' it must be an array I think
    Dim x As String ' for the X parameter
    [...]
    ' collect x = the selected value from "ListBoxB"
    ' compose query A:
    sSQL = "SELECT [...] WHERE ""the_field"" = '" & x & "'"
    oLBA.ListSource = sSQL
    oLBA.refresh
End Sub

Just speculative: don’t know if this will work!
Please see

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

Villeroy · August 7, 2025, 3:45pm

https://ask.libreoffice.org/uploads/short-url/gK9hcyzEnMVotx6Mtmr6CqCDsPR.odb

NickSamuel · August 15, 2025, 10:27am

I am not a LibreOffice / OpenOffice Basic programmer so I do not know how to apply the code to form(s). ChatGPT suggests the following.

Sub SetA(evt As Object)
Dim oLBA As Object, oLBB As Object
Dim sSQL(0) As String ’ Array with one element
Dim x As String ’ For the X parameter

' Get a reference to your controls
' (Adjust names "ListBoxA" and "ListBoxB" to your form)
oLBA = evt.Source.Model.Parent.getByName("ListBoxA")
oLBB = evt.Source.Model.Parent.getByName("ListBoxB")

' Collect the selected value from ListBoxB
x = oLBB.CurrentValue

' Compose query A into the first array element
sSQL(0) = "SELECT [...] WHERE ""the_field"" = '" & x & "'"

' Assign array to ListSource and refresh
oLBA.ListSource = sSQL
oLBA.Refresh

End Sub

CRDF · August 15, 2025, 10:49am

I misunderstood your question.
You want ONE ListBox “B” to select parameter for query (not a ListBox) “A”.
It’s it?
If so it’s simpler: collect the parameter from LB “B” via the
oLBB.current value
and compose the query and run it via code
or (I think better for your case)
alter a saved “QueryA” inserting this parameter on it. And then open it via code.
Code below does this.
You save “QueryA” placing ? as the parameter.
Code:

It places the parameter as a constant in the saved query.
Opens (runs) the query in the GUI grid.
Resets the query (re)placing ? au lieu of parameter.

But first see @Villeroy 's method with Filter Table.
It can be more convenient to your case.

Option Explicit
Sub Consulta(evt As Object)
	Dim Formulario As Object, LBAnos As Object, Con As Object, QD As Object
	Dim ano As Integer
	Dim sSQL As String
	Formulario = evt.Source.Model.Parent
	LBAnos = Formulario.getByName("lbAnos")
	ano = LBAnos.CurrentValue
    REM THE CONNECTION
	Con = Formulario.ActiveConnection()
	QD = Con.Queries.getByName("TheQuery")
	sSQL = QD.Command
	sSQL = Replace(sSQL, "?", ano)
	MsgBox sSQL
	QD.Command = sSQL
	REM OPEN THE PARAMETRIZED QUERY
	OpenQuery("TheQuery")
	REM RESET THE QUERY
	sSQL = Replace(sSQL, ano, "?")
	QD.Command = sSQL     
End Sub
REM TO OPEN A QUERY
Sub OpenQuery(query As String)
	Dim TipoObjeto
	TipoObjeto = com.sun.star.sdb.application.DatabaseObject.QUERY
	ThisDatabaseDocument.CurrentController.loadComponent(TipoObjeto, query, FALSE)
End Sub

Villeroy · August 15, 2025, 10:54am

FilterData_LO7.odb (107.8 KB)
A filtering main form with a filtered subform.
Both forms have 2 list boxes filtering by person and products (categories). Persons and products are stored as unique items in separate tables.
The list boxes let you select the ID number of one item by the item name.
The filtering by means of form and subform can only work if the main form stores its data in some table.
The “Filter” table stores all filter criteria in a distinct record (Filter.FID=1). The filtering main form is bound to SELECT * FROM "Filter" WHERE "FID"=1.
The filtered subform’s parameter query is designed to ignore missing filter criteria.
A second subform shows calculated results based on the same filter criteria.

If you do not store unique items in a separate table, the exact same method works with filtered strings from combo boxes.
BerlinStreets_LO7.odb (629.0 KB)