Solved Combo box filter in 1 table

[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.

 

[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