Base: Dropdown list inactive when using Form data from Query

[madMatti]

Question

How can I use a dropdown list to display only certain datasets of a query?

Problem

I’m using a query to display an overview of data sets with certain fields and aggregate functions. I want to use a list field to set a filter and it works fine filtering table entries. But as soon as I set the form data to the query instead of the table the list field is greyed out and cannot be used anymore. This happens when I copy the query into the SQL field of the form as well as using a named query from the menu.

I’m just using the ID returned by the list selection. I don’t want to change any data. This query is just for displaying results.

If this does not work I think I will make the dropdown list “stupid”, meaning I will try to disconnect it from the data. Then I will try to populate it onLoad using a SQL query in a macro. At least that is one way I can think of. I would like to avoid using filter tables.

---

Example

I made an example file with a list of Dogs showcasing the problem. Hope you can see from it what I’m trying to achieve. I use the macro for setting the form filter.

DropdownTest.odb

DB structure and macro

Query qDogList:

SELECT 
  Dogs.dog_name, 
  Dogs.dog_id, 
  Breeds.breed_name, 
  Owners.owner_name, 
  Owners.owner_id 
FROM Dogs 
LEFT OUTER JOIN Owners ON Dogs.owner_id = Owners.owner_id 
LEFT OUTER JOIN Breeds ON Dogs.breed_id = Breeds.breed_id

Macro frm_dogs:

REM  *****  BASIC  *****

Sub FilterByOwner(event As Object)
  Dim lstOwner As Object
  Dim form As Object
  Dim selectedId As String

  lstOwner = event.Source.Model
  form = lstOwner.Parent

  selectedId = lstOwner.getCurrentValue()

  If selectedId = "" Then
    form.Filter = ""
  Else
    form.Filter = "owner_id = " & selectedId
  End If

  form.reload()	
End Sub

Sub ResetFilter(event As Object)
  Dim form As Object

    form =  event.Source.Model.Parent
    form.Filter = ""	

    form.reload()	
End Sub

LO

Version: 7.1.3.2 (x64) / LibreOffice

OS: Win10

Solution

Thanks to @Ratslinger I was able to get it working. Here is the changed example file in case someone has that problem in the future. I had to move the Reset Filter button to the subform and add a reset action + macro to it because it kept trying to submit the list.

DropdownTest_v2.odb

Regarding my dislike of filter tables:

Maybe I do not understand the filter table concept completely. But from what I got so far I do not like them. I did not want to use filter tables because they are LibreOffice specific artifacts that help the GUI solve things it should be able to do on its own. I cannot repurpose them for caching or optimisation either, the DBMS does that already and it does it well. Imagine if you used multiple GUIs for a DB and every GUI stored its own metadata garbage tables in the database. It gets even worse once you have multiple DB users. I would only use filter tables if I did not have a choice, like if I needed a document without macros and for that they are OK.

[madMatti]

Woops… it seems I made the screenshot before changing a column or the query. You can ignore the broken name column in the screenshot. Also the problem is DB independent.

[Ratslinger]

Have no concern to convince you otherwise about filter tables. You are free to create as you wish. I believe you just do not understand them. You can have just one in entire application serving many forms. Also can (and have) used temporary tables to accommodate multiple users. Much less painful than macros.

[madMatti]

Sometimes I just try a bit too hard to separate GUI from DB layer but I can understand using filter tables from a practical standpoint. I guess if I really wanted I could add a second DB just for LibreOffice stuff. But if it is just 1 utility table for all the forms then maybe I’ll have to look into filter tables after all. For now the macro solution seems fine. Maybe I will do it next time I have a problem like this.

[Ratslinger]

Hello,

You request not to use a filter table but it would make all a lot simpler.

Notwithstanding, got it working with following changes:

• Moved List box to a separate internal form (my name was FilterLB)

• Adjust macro to read correct form for MainForm (original name but event now returns new form added)

• Changed source of MainForm from the Query to a View.

All works.

[madMatti]

Ohhh, nice. I hadn’t thought of using a separate form for that. Thanks a lot for the help. And thanks for your advice with using PostgreSQL, way less problems since I switched.