Form Based Filter not working

[JWDog]

I have a database for a game collection. I am trying to use the form based filter on a dropdown control with a one to many relationship. The form is based on a games table (fk) connected to a systems table (pk). The form based filter works for one of the system table records, but not for others, even though records in the games table have those other system records. I am not sure why it is not consistant with the filter. Please advise. Systems Table 1 : M Games Table

[Villeroy]

Download the attached database document having an embedded HSQLDB with a one-to-many relation.
Open the document and the embedded form.
Click the form filter button on the navigation bar.
The form is in criteria input mode now. Notice the additional toolbar with 3 icons ("Form Filter").
Select a person from the PID column.
Click the funnel icon on the form filter toolbar (tooltip: "Apply form based filter").
Result: The form is back in data mode showing only the records having the person that has been selected in criteria mode.

[JWDog]

Villeroy, my database is probably too big to post here for you to see what I am asking about.i am going to try and shrink it. Do you know what size of .odb I can post?

I did get the form based filter to work on the database and form you posted.

Thanks.

[Villeroy]

[Forum] How to attach a document here

Remove all forms and reports except one form demonstrating the issue.
Call Tools>SQL.... and run command

Code: Select all

SHUTDOWN COMPACT

Close the document.
If the document is still larger than 128 kB, remove records and do another SHUTDOWN COMPACT.

[Villeroy]

General recipe for a 2-column list box with a first visible field (index 0, text) and a second bound field (index 1, ID number). This is how it works in my example database.

All your primary keys and foreign keys are names (text). The items are stored in tables having a single column. Your visible fields are the same as the bound fields, so the bound field index should be 0.

Integer auto-IDs have the advantage that you can easily rename your items and the name will instantly change in all referencing list boxes. Integer keys are more efficient because computers prefer to look up integer numbers instead of long byte sequences representing strings.

[JWDog]

Thank you for this explanation, I will try to integrate this into my database. And let you know if it worked.

[Villeroy]

Set all bound field properties of your list boxes to 0 and test the form based filter. Then you may decide to modify the relations so they are linked through integer auto-IDs.

[JWDog]

My database is now in recovery mode, and when recovery is complete and i try to open the form or change to tables, openoffice base crashes, please helpl!?

Thanks!

[Villeroy]

No backup, no mercy.

[UnklDonald418]

[Tutorial] Recovering a damaged odb file
If that fails, open a new Writer document or Calc spreadsheet.
From the Menu select Tools>Options
In the Options dialog select OpenOffice>Paths
In Windows explorer navigate the the directory shown for Backups. With a little luck you might find a usable version there.