Vba code or function for Filtering records (1 Viewer)

mercystone

Member
Local time
Today, 12:50
Joined
Sep 20, 2021
Messages
108
I have two tables.
1. Table for countries
2. Table for cities
Out of this the main form is from table for countries and subform for table for cities.
In the table for countries, the counties have been entered
1. USA
2. CANADA
3. ENGLAND.
In the table for cities the user of database enters the cities manually.
Question- How will I create a filter such that the user will filter let's say USA and the city the user has entered manually. For example
USA- the user of database might have typed Washington.
Please assist me.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:50
Joined
Feb 19, 2013
Messages
16,553
your country table needs to include a PK (primary key, usually an autonumber), your cities table needs to include an FK (foreign key) which matches the PK of the country table. As to the filter, this could be based on the pk or fk depending on which tables you are using

Recommend you show your table design and relationships as your description of what you have seems incomplete - e.g.

In the table for countries, the counties have been entered
 
Last edited:

mercystone

Member
Local time
Today, 12:50
Joined
Sep 20, 2021
Messages
108
tblCountries
PK- AUTONUMBER
USA
CANADA
ENGLAND
tblCities
PK/FK- AUTONUMBER
(Cities to be manually typed by the user of database)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:50
Joined
Feb 19, 2013
Messages
16,553
to clarify

tblCountries
CountryPK...CountryName
1.......................USA
2.......................Canada
3,,,,,,,,,,,,,,,,,,,,,,,England


tblCities
CityPK....CountryFK.....CityName
1...............1.......................New York
2................1......................Chicago
3................2......................Quebec
4................3......................London
5................3......................Birmingham


to find the cities on a country, filter on the CountryFK

in a form have a subform

the main form based on tblCountries
the subform based on tblCities
in the subform linkchild property put CountryFK
in the subform linkmaster property put CountryPK
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:50
Joined
May 7, 2009
Messages
19,169
your country table will likely never change the cities?
and this table is only use for Lookups.
so it is better to use a Single table:

tblCountry:

Country (short string)
City (short string).

on your form create 2 combos (one for country and the other for cities).

cboCountry, rowsource:

select Country from tblCountries Group by Country.

cboCities, rowsource:

select City from tblCounties Where Country = [Forms]![yourForm]!cboCountry;"
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:50
Joined
Feb 19, 2002
Messages
42,970
I know that country names don't change all that often but they DO CHANGE. So, rather than duplicate data which can change, always use two tables. You are never wrong to properly normalize your data. That is what we do when we work with relational databases. There is simply no advantage to violating the normalization rules in this situation.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:50
Joined
Sep 21, 2011
Messages
14,038
No please? :(

Use a subform for the cities and a combo for the country in the mainform. Link by combo and countryFK in subform.
Washington exists in the UK as well, probably well before the one in the USA :)
 

mercystone

Member
Local time
Today, 12:50
Joined
Sep 20, 2021
Messages
108
Now after using the main form and subform how will i filter take for example the user has typed Washington in usa. I need a filter to select people in Washington in usa. Lets say
Name- Peter Hillary
City- Washington
Country- USA
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:50
Joined
Jul 9, 2003
Messages
16,243
You are never wrong to properly normalize your data

If you have taken Pat's advice and normalised, then you should find this method explained on my website useful:-

 

Users who are viewing this thread

Top Bottom