Hello,
For working wit selective listboxes, I use the following macro:
Sub LoadList_Customer(oEvent)
Dim oForm As Object
Dim oShop_country As Object
Dim oShop_name As Object
Dim sSelectedItem As String
Dim sSQL As String
Dim bClearList As Boolean
oForm = ThisComponent.DrawPage.Forms.getByName("MainForm")
oShop_country = oForm.getByName("lb_CC1")
oShop_name = oForm.getByName("lb_CN1")
sSelectedItem = oShop_country.SelectedValue
bClearList = 0
If IsEmpty(sSelectedItem) Then sSelectedItem = 0: bClearList = 1
sSQL = "Select ""Shop_name"" || ' - ' || ""Shop_place"" || ' - ' || ""Shop_adress"", ""Shop_id"" FROM ""Table_Customer"" WHERE ""Shop_country"" = '" & sSelectedItem & "' ORDERBY ""Shop_name"" || ' - ' || ""Shop_place"" || ' - ' || ""Shop_adress"""
oShop_name.ListSource = array(sSQL)
oShop_name.refresh()
If bClearList Then
While oShop_name.ItemCount > 0
oShop_name.removeItem(0)
wend
End If
End Sub
This works fine, but my problem is:
In the following line of the coding for the macro, i would like to add a sort of data in cell 2 before selecting as this gets overwriiten when selection needs to be altered. I tried adding ORDERBY at several spots, but I run into errors.
If IsEmpty(sSelectedItem) Then sSelectedItem = 0: bClearList = 1 sSQL = “Select ““Shop_name”” || ’ - ’ || ““Shop_place”” || ’ - ’ || ““Shop_adress””, ““Shop_id”” FROM ““Table_Customer”” WHERE ““Shop_country”” = '” & sSelectedItem & “’”
I tried things like:
sSQL = “Select ““Shop_name”” || ’ - ’ || ““Shop_place”” || ’ - ’ || ““Shop_adress””, ““Shop_id”” FROM ““Table_Customer”” WHERE ““Shop_country”” = '” & sSelectedItem & “’ ORDERBY ““Shop_name”” || ’ - ’ || ““Shop_place”” || ’ - ’ || ““Shop_adress”””
analog to sql in initial query in the second cell:
SELECT “Shop_name” || ’ - ’ || “Shop_place” || ’ - ’ || “Shop_adress”, “Shop_id” FROM “Table_Customer” WHERE “Shop_country” = ‘Austria’ ORDER BY ( “Shop_name” || ’ - ’ || “Shop_place” || ’ - ’ || “Shop_adress” ) ASC
What am I doing wrong?