Solved Combo box filter in 1 table (1 Viewer)

[Sarameier]

Dear Forum,

I am trying to set up a filter function for only one table, the tbl User.

It is about listing all users following their country of origin. In my case, it is either
1- FR
2- UK
3- GE

How could I do this getting in the combo box
- only these 3 countries displayed
- and NOT all record values from the attribute "country of origin" (i.e. multiple times FR, UK, GE)
- but having all users, coming from the one country, displayed after choosing this country?


Thanx
Sara

 

[CJ_London]

perhaps the rowsource to the combo should be something like

SELECT DISTINCT Country FROM tblUsers

Assuming your form has a recordsource of tblUsers then in the after update event of the combo

me.filter="Country='" & me.comboname & "'"
me.filteron=true

 

[theDBguy]

- but having all users, coming from the one country, displayed after choosing this country?

Displayed where? On the form or in another Combobox dropdown?

 

[Sarameier]

For me, it makes no difference where the results (list of related users) would be displayed.
Therefore, it should probably be displayed on the same form.


I am actually just searching for a comfortable way the get search results for attributes within only one table.
Of course, I could also set up s separate table "tbl_Country" which would make it easier to filter related rows within tbl_User. But I dont think it is good to set up a separate table for each attribute describing tbl_User.

 

[jdraw]

It might improve communications if you provide us a sample/mockup of what your search and results should be.
Do you have a description of WHAT tbl_User represents, and the attributes involved?

 

[Sarameier]

Hi,

@ jdraw

in my present case, it is indeed just a table for users (members) of travel groups. And I just wanted to be able to list all people coming from a certain country.
An other example could be the table books which all relate to exactly one genre.

I thought, there might be a comfortable way to search within one table, similar to the form/sub-form structure with two tables (1:n).
Of course, using the data sheet view for a table and simply applying the filter function for one column could also be an option. But it is not as comfortable as searching through a combo box.

@ CJ and other:

Could someone give me a small advise how to implement your code?

 me.filter="Country='" & me.comboname & "'"
me.filteron=true

If I click on the "after update" macro, it would show me the following...

Shall I delete these existing phrases?
Or shall I apply "Add New Action" and add "apply filter"?

 

[Sarameier]

I mean: filtering within one table is somehow also provided by the inherent Access function "filter by form". But is is not as comfortable as using a large "combo box" in a form

 

[jdraw]

...users (members) of travel groups. And I just wanted to be able to list all people coming from a certain country.
An other example could be the table books which all relate to exactly one genre.

Sounds like you have a set up similar to below image. Might be a good time to prepare a description of what the "proposed database" should support (requirements). I recommend that you get your tables and relationships identified before jumping into physical database. I would also recommend using vba rather than macros.

 

[Pat Hartman]

Access is a Rapid Application Development tool and comes with a lot of built in functionality. Looks like you found the filter by form featur. I don't care for it either so when I have many search options, I build a search form. The search form builds an SQL String (NOT a filter). It then uses a DCount() on the SQL String and if only one row is returned, it passes that ID to the normal edit form. If multiple rows are returned, it opens a list type form and replaces that form's RecordSource property with the new SQL String. From the list form, double clicking on the ID will open the single record edit form

What you are asking for will overload the form. You CANNOT use bound controls for searching. When you type something in the form, how do you know that the user is trying to search or that he is trying to change something. I would NEVER do this, it is far too dangerous. Most of my forms have one or two unbound controls that are used for searching. Otherwise, I use the separate search form method. I'm pretty sure that there is at least one example of a search form posted here.

 

[CJ_London]

You can just right click on a country field and filter from there

 

[Sarameier]

HI all,

Appreciating all your answers.

My system is now running quite well.

Thanks for your support although I am just a newcomer...

 

[mike60smart]

HI all,

Appreciating all your answers.

My system is now running quite well.

Thanks for your support although I am just a newcomer...

Hi

See the example attached

In the Header of the Form there is a Search Box which is Unbound.
In the after update of the textbox there is some code that allows you to search on any field within the Form.
There is also a Clear Filter Button

You can search in fields:-
Company
Lastname
Firstname
Job Title
City
State
Zip
County