Filter Data On a Query

[kobiashi]

Hi

Can I filter Data in a query to be used in a combo box.

so i have create a many to many relationship between three tables

table_Statement
Statement_ID | Statement
1 | Statement 1
2 | Statement 2
3 | Statement 3

table_statementType
StatementType_ID | StatementType
1 | StatementType 1
2 | StatementType 2
3 | StatementType 3

table_StatementRelationship
Statement_IDFK | StatementType_IDFK
1 | 1
2 | 1
3 | 2


Can I create a query from this then use it in another query, then filter to a specific StatementType to be used in a combo box

 

[June7]

Sounds like cascading comboboxes, which was already addressed in your other thread https://www.access-programmers.co.uk/forums/showthread.php?t=299682

If this is not what you want, then provide more information, such as example of desired data output.

 

[kobiashi]

Hi

thanks for the reply, its not cascading combo box, as in the example shown, i want to be able to store data in a many to many relationship, then use that data in a combo box, without having to select the first combo box, so for example,

Country
UK 1
US 2

City
LON | UK
NY | US
MAN | UK


then filter Cities so only UK Cities are available in combo box, but without having to select country in the another combo box

 

[June7]

Okay, can have code that modifies the combobox RowSource based on the country of the selected item. But then you will probably need a button that sets the RowSource back to all records so you will be able to select a different city/country.

But what purpose does that really serve? If you can select desired city, why do you need to restrict the list to that city's country?

 

[kobiashi]

i have a number of statements stored in a table, i thought instead of having a table for each statement type, i could just filter the selection on the query, as there will be a number of different statement types on the same form

 

[James Dickinson]

You need a reset mechanisim perhaps a button or a checkbox or something to reset the country and city recordsources to what I've typed below or something similar.

So your first Combo box should have a recordsource
Select Country ID, Country from "your country table"

the second combo recordsource
select CityID, city, CountryID from "your query or table uniting the two"

Then after you select something in one combo box, you need to create a SQL string on the fly in VBA that reads your combo and swaps the recordsource of the other combo.

strSQL = "Select Country from Countries Where CountryID =" & me.City.column(2).value

me.CBOCountry.recordsource = strSQL

when you reset with your button, return both combo box buttons to their original recordsources.

 

[Cronk]

What's the relationship between Statements/StatementType and Cities/Countries?

If you only want UK cities to show in a combo, the source sql would be

select city where CountryID = 1

But I'm sure what is required is not that simple

 

[kobiashi]

What's the relationship between Statements/StatementType and Cities/Countries?

If you only want UK cities to show in a combo, the source sql would be

select city where CountryID = 1

But I'm sure what is required is not that simple

This look like what i may need, does this just go in the criteria box on the query?

 

[June7]

No, it goes in VBA code to set the combobox RowSource property as shown in post 6.