[Solved] Button - Filter Form

[mikros]

Hi,

I want to have a form with Grid Table with all my customers. Because my customers are more than 500+ i want to place buttons and filter results based on "Name" starting letter. Example, button_A will show only customers with "Name" starting letter is "A".

In Ms Access i had a form with Grid Table and buttons. Each Button was a letter.
Here is the VBA code attach:

Code: Select all

DoCmd.ApplyFilter "", "[Name] Like ""A*"""

Result to Grid Table, all names starting with letter "A".

Thank you..

[Arineckaig]

Much the simplest way to filter a grid/table form control is to add a Navigation Bar form control and use its comprehensive filter button icons as described in the Help File.

[mikros]

Thank you but it is more simple the buttons.

Grid table and under it some buttons to click.

I need to press a button and filter grid table.
Press another button and (cancel previous filter) show new filter.

[Arineckaig]

Thank you but it is more simple the buttons

If you require a button driven system it will require resort to macros. One work around without macro is shown in the attached demo file: stripped down from a 27K record database to permit uploading as an attachment.

Select any letter from the left data form to filter the records shown in the right hand form. Select the empty/NULL record at the top of the left form to remove the filtering. The method makes use of the BASE main form/subform relationship, whereby moving the pointer in the main form automatically filters the record-set in its linked subform.

The other method that I would recommend is Villeroy's Power Filter route - the forum contains many of his examples and posts.

[mikros]

Thank you very much...

It is not buttons solution, but it is a nice one and clever...

I test it and i am happy... :):)

I will use it in my database.

[mikros]

Arineckaig,

Is it possible to add on a field more than one LETTER?
Example:
--LETTER--
A, B, C => SubForm showing all Names stared with A or B or C
D, E, F => SubForm showing all Names stared with D or E or F

[RPG]

Hello

When you want work with forms then you have to learn about SQL. Special how your engine, probably HSQLDB, works. You have also to learn how OOo-base is working. It is better to avoid macros but when you work with OOo-base then you cannot without knowledge about SQL.

I think rewrite the index table , the query in the masterform and also adjust the slave fields. It depends a little about how you want have it.

Forms are form formatting your data. The SQL in your form is for selecting your data. The combination of mainform and subform is for selecting data in your in your subform.

Romke

[Arineckaig]

Is it possible to add on a field more than one LETTER?
Example:
--LETTER--
A, B, C => SubForm showing all Names stared with A or B or C

It could be done with three indexing fields in the main form together with consequential boolean additions to the SQL data source and extra parameter linking fields for the sub-form. Probably it would be easier with just two indexing fields to represent inclusive ranges of initial letters. Either method will require care for the handling of NULLs.

IMHO, either way may be a case of pushing Base beyond its design remit, and I suggest Villeroy's Power Filtering methods will prove more productive in the long run: Base requires quite different techniques from those applicable to MS Access. The demo file was offered merely as a potential match to the Access line of buttons: it does not pretend to be well suited to Base for filtering a record-set.

[mikros]

Ok.

Thank you.

[Villeroy]

Things can be easy and efficient when you go with the application.
http://www.mediafire.com/download/x5vbg ... rsons3.odb
Open "Filter Form 2".

[mikros]

Super....

Thank you very much, Arineckaig and Villeroy...

[mikros]

Villeroy,

i am getting an error in sql...
Maybe there is a bug or a mistake.
your code is :

Code: Select all

SELECT * FROM "Persons" 
WHERE UPPER( left( "SN", 1) ) between upper(left(:pTXT,1)) and upper(right(:pTXT,1))

but i am getting this:

Code: Select all

SELECT * FROM "Persons" 
WHERE UPPER( left( "SN", 1) )=between upper(left(:pTXT,1)) and upper(right(:pTXT,1))

i am manual correcting it and it is working. But if i tried to go to SQL design mode i am getting an error!!!

[Villeroy]

SQL "design mode" too spoils many valuable SQL statements except for the most trivial baby SQL. Forget design mode.