Beginner stuck linking combo to subform

[saint]

Welcome beginner. What is your question or comment?
Please try to briefly and clearly tell us: What you want, What you tried, and What happened.
-----------------------------------------------------------------------------------------------------------

I just started using ooBase having been an MS Access user. I want to write a very simple database with one table and a form showing the table but filtered by some user input. ie
Table with: Artist, SongName
Combo box showing unique artist list which, when the user chooses an artist, it updates the subform to show the list of songs by the artist.

I'm obviously missing the big picture here because this would take me 5 mins in Access, but after a day of struggling I have to ask for help. A link to a tutorial or similar example would help. The ones I have looked at on wiki haven't helped me.

thanks in advance

By the way, having saved a draft post, how do you then submit it! I had to cut/paste this one

[Villeroy]

There are several macros to filter a form by the content of some combo or list box. It's so ridiculous that nobody provides a built-in method for such a trivial matter of course. The obstacle lies in the fact that Base requires the combo to be bound although you do not want to write the value anywhere.
I'll attach my solution for this problem which works without macro. The main form is bound to a query that returns one record in table "Filter". The single record in "Filter" does nothing but taking the combo's value.

Code: Select all

select * from "Filter" where "ID"=0

The numeric primary key "ID" is required to make the other field "Value" writable. The filtering combo is bound to that field getting it's value from the existing ones in "Table1":

Code: Select all

select distinct "Value" from "Table1" order by "Value"

Main form and subform are connected through their respective field "Value".
The subform reflects "Table1" with a (hidden) auto-ID. The button labeled "Refresh" with action property "Refresh" belongs to the subform since it refreshes the subform once you changed the main form's combo.

Edit: Removed database in favour of new version:

combo_filter2.ods

[voobase]

Hi Saint,

My method is similar to Villeroys, useing a single line "dummy" table for the mainform. The combobox binds its value in the mainform and there is then a link/join between Mainform and Subform. There is also a simple macro to do the reloading of the subform when the combobox is used.

http://www.oooforum.org/forum/viewtopic.phtml?t=72134

Cheers

Voo

[Villeroy]

Nice thread, indeed. There are people out ther who have fun with macro programming. However this may be a little bit too involved for some people. Another drawback is, that your database does not transfer as easily as a single-file database should. You have to install a macro first. Next time you want to do the same trick in another db, you have to use the same names of fields and controls respectively or install a modified version of the same macro.

My dummy table named "Filter" is far from elegant and the form can not show all records. You may try another variant of the same form:
1. Create a copy of my "Form1" (drag "Form1" a little bit aside, drop and provide a name for the copy)
2. Open in edit mode (right-click)
3. Call the form navigator (5th button on toolbar "Form Design")
4. Drag the subform up to the "Forms" container, so it becomes an independent form.
4a. To avoid confusion you may rename "Main" to "Filter" and "Subform" to "Table" which reflects their new roles.
5. Change the source of the former subform to SQL command (parsed or direct SQL):

Code: Select all

SELECT "T"."ID", "T"."Value" FROM "Table1" AS "T", "Filter" AS "F" WHERE ( "F"."ID" = 0 ) AND ( ( "T"."Value" = "F"."Value" ) OR ( "F"."Value" IS NULL ) )

Now you can clear the combo box, push the refresh button and the grid will show all records.
Drawback: The query returns a read-only recordset, but you can connect it through "ID" to an editable sub form.

Edit: Attached combo_filter2.odb with "Form2", based on the above suggestion.

[josephllc]

Hi, I must say that it is really simple and fast way to do filtering. Thks for sharing!
However, is there a possibility to have multiple filtering combo boxes using the same way?
I have tried to use 2 combo boxes, but failed. Whenever the form is loaded, both the combo boxes are locked or disabled. I believe something to do with the SQL query on the Main form.
How can I resolve this problem?
Attached is the previous sample with modification on Form1....2 combo boxes.
Anyone can help... Thks!

[Villeroy]

Create a filter table with one record and two filter-fields.
If you use the form/subform approach bind the filter fields to their respective fields in your main table.
The SQL query of the second approach ends like ... WHERE ("Filter"."Criterion1"="Table"."Field_A") AND ("Filter"."Criterion2"="Table"."Field_B") AND ("Filter"."ID"=0)

[josephllc]

Hi Villeroy,

Its work like magic!!!! Thks!!!

[eremmel]

Villeroy, I've a challenge for you. I've also two filter fields, but those are dependent on each other. The first one defines a classification and the second a item in that classification (Genre/Book titles; Car vendors/Car models; etc). I prefer to use list boxes iso combo boxes, but tried both. I implemented two different filters (ID=0 and ID=1). The one for the classification is straight forward. I made the filter on the Item as a sub-form of the classification. The query for the list/combo box of item has a where clause that utilize the value of the classification filter table. But the problem is that when I select a different classification and move to the item filter the values of the control do not get reloaded. I've to do a manual form or control refresh to see the right list of Items. Do you know a way to solve this without macros?

[Villeroy]

Don't use boxes. For a few main categories you can draw a single-column, read-only table control with hidden navigation bar. This animal looks and behaves like a multi-line list box.
Contrary to a list box, which selects values, a table control selects records and a subform can show the subitems related to the record selected in the main form. This is the easiest way if there are not too many main items to select from.

Since v3.1 the following is possible. A table control with one alphabet column ...
A
B
C
...
Z
... and a subform bound to a query like
SELECT "Name","ID","Others" FROM "Table" WHERE "Name" LIKE :paramLetter ||'%'
Then link the main form's alphabet field with "paramLetter". This shows all names starting with the letter selected from the table control.
Before v3.1 Base was unable to handle parameters in the context of functions and concatenations.

[evwool]

One more thing, if you're coming from Access, the ListBox in Base behaves and looks more like the Combo box in Access ie Base listboxes don't display all the items at the same time. I tend to use list boxes in Base when I would have used a combo in Access.

[Villeroy]

In my input forms the combo selects text into text fields (one field).
A list box selects numbers into foreign key fields (one displayed field, one sets the value).
A table control (or the form's view in the beamer) selects a whole record and the selected record can filter a subform by various fields.

[evwool]

A list box selects numbers into foreign key fields (one displayed field, one sets the value

That's exactly one of the things which a Combo box does in Access, Villeroy.