Filtering a list box from another

[Primus]

Hello.

I have a command entry form (frmOrders) that includes a subform (frmOrdersSub) for the command lines. In the main form (frmOrders), a list box (lstSupId) selects the supplier. Then, in frmOrderSub, in the datagrid (grdOrdersSub), a list box (lstProId) selects the product. In the T_PRODUCTS table, a column (SUP_ID) indicates the supplier of each product.

Once the supplier is selected, I would like the sub-form to display only the supplier’s products. In MS Access, it’s easy :

SELECT
PRO_ID,
SUP_ID,
PRO_LIB,
PRO_PRICE
FROM T_PRODUCTS
WHERE (((T_PRODUCTS.SUP_ID)=forms!frmOrders!SUP_ID));

But in LO Base I think it takes a macro to get there.

Thank you.

mdlOrders.odb (14,1 Ko)

[RobertG]

How should it work?

You have connected a main form to a sub form by “ORD_ID” (number of the order). If you change supplier in this main form the listbox in sub form should show all content in list for “Product” for this supplier, but wouldn’t show the “Product” content for other suppliers. So changing “Supplier” will only show products for this supplier in subform. No products for other suppliers.

You have to link also “SUP_ID” to show all content in subform.

I would prefer to add supplier in the listbox for product instead.

[Primus]

In the subform, the SQL for lstProId is :

SELECT ("PRO_LIB" || ' ' || "PRO_PRICE") , "PRO_ID" FROM "T_PRODUCTS" ORDER BY PRO_LIB ASC

So it’s normal for all suppliers’ products to be displayed. And that’s the purpose of this topic, I don’t know how to filter them. And even if I add the supplier column to the list, it won’t change anything.

[RobertG]

You couldn’t get a list box in a table control to show different content for each row. This is what you want, if you try to “filter” the listbox. (bug 143758)
Should the sub form only show the content for one supplier? So the whole sub form must be also filtered by the supplier, not only by the number of order.
Code for the listbox must be set by macro. But first you have to solve the problem between main form and sub form. If you don’t solve this you will get empty fields in sub form → product.

SUB Listfieldfilter(oEvent AS OBJECT)
	DIM oField AS OBJECT
	DIM oForm AS OBJECT
	DIM stID AS STRING
	DIM stSql(0) AS STRING
	stID = oEvent.Source.Model.CurrentValue
	oForm = oEvent.Source.Model.Parent
	oField = oForm.getByname("frmOrdersSub").getByName("grdOrdersSub").getByName("lstProId")
	stSql(0) = "SELECT (""PRO_LIB"" || ' ' || ""PRO_PRICE"") , ""PRO_ID"" FROM ""T_PRODUCTS"" WHERE ""SUP_ID"" = '"+stID+"' ORDER BY PRO_LIB ASC"
	oField.ListSource = stSql
	oField.refresh
END SUB

You could connect this macro to the listbox in main form. Try the event “modified”. You will see: Content of products will be blank, if you change the listbox in the main form.

[Primus]

Ok, it works. Indeed, if after selecting products we change the supplier, the content empties. But once we start seizing products, we don’t have to change suppliers. For this reason, we can integrate a macro of this kind in order to lock the list box :

Sub DisableSuppliersList

	Dim objForm As Object
	Dim objControl As Object

	objForm = ThisComponent.Drawpage.Forms.GetByName("frmOrders")
	objControl = objForm.GetByName("lstSupId")
	objControl.Enabled = "False"

End Sub

And the same macro with enabled = false for each new record.

Thank you for your help.