Dependent listboxes on access forms

[Imicola]

Hi,

I've recently been tasked with developing a database to store data which has been collected by a lot of different people. I have quite alot of access experience, but not that much on database development, so I'm learning quite a lot as I go along.

My problem is that I need to keep information about locations. All the data we have has been collected in different villages. Each village belongs to a Parish, each Parish to a Sub county, each sub county to a County, each county to a district, each district to a region, and each region to a country.

What I really want to do is be able to select a country, then in the region list have only regions in that country come up, then when selecting a region have only districts in that region come up and so on. I beleive this can be done using dependent listboxes.

I need a bit of advice on
a) how to store all the location information. Should each village have ALL the other locational information stored in the same table, or should I try to separate out all the different levels of information and then link them together, or should I do it another way?
b) How easy/hard is it to create a form using dependent listboxes? Does this require programming, as I have no programming experience?

I dont yet have all the linked up locational information - I'll have to enter that in once I figure out the best way to store it. At the moment I have separate tables for each administrative unit, but I can change this if it is not the best way.

I really hope this makes a bit of sense to someone as its quite difficult for me to explain!

I'd appreciate any help anyone can give me.
Thanks,
Nicola

[MMcCarthy]

Hi,

I've recently been tasked with developing a database to store data which has been collected by a lot of different people. I have quite alot of access experience, but not that much on database development, so I'm learning quite a lot as I go along.

My problem is that I need to keep information about locations. All the data we have has been collected in different villages. Each village belongs to a Parish, each Parish to a Sub county, each sub county to a County, each county to a district, each district to a region, and each region to a country.

Hi Nicola,

You will need a table for each of these lists as follows

CountryList
CountryID (Primary Key - AutoNumber)
CountryName

RegionList
RegionID (Primary Key - AutoNumber)
RegionName
CountryID (Foreign Key referencing Primary Key of CountryList table)

DistrictList
DistrictID (Primary Key - AutoNumber)
DistrictName
RegionID (Foreign Key referencing Primary Key of RegionList table)

CountyList
CountyID (Primary Key - AutoNumber)
CountyName
DistrictID (Foreign Key referencing Primary Key of DistrictList table)

SubCountyList
SubCountyID (Primary Key - AutoNumber)
SubCountyName
CountyID (Foreign Key referencing Primary Key of CountyList table)

ParishList
ParishID (Primary Key - AutoNumber)
ParishName
SubCountyID (Foreign Key referencing Primary Key of SubCountyList table)

VillageList
VillageID (Primary Key - AutoNumber)
VillageName
ParishID (Foreign Key referencing Primary Key of ParishList table)

Now the main data table need only to reference the VillageID and all other links can be referenced through the above table links. Have a look at this tutorial on cascading combo/list boxes on how to reference the lists.

Mary

[Imicola]

Hi, thanks for this.

I'm still struggling with the code though as I have never ever used code before!

What I have is, for example,
tlkpCountry which has the country information
CountryID
CountryName

tlkpRegion which has the region information
RegionID
RegionName
CountryID

The code I am trying, which could be totally wrong is:

Private Sub Country_AfterUpdate()
    With Me![Region]
        If IsNull(Me!Country) Then
            .RowSource = ""
        Else
            .RowSource = "SELECT [RegionName] " & _
                        "FROM tlkpRegion " & _
                        "WHERE [CountryID] = " & Me!Country
        End If
        Call .Requery
    End With
End Sub

I dont really understand what each command means, which is probably why I cant figure it out, and haven't really found the online help much use.

Any more advice would be greatly appreciated!

Thanks,

Nicola

[MMcCarthy]

Hi Nicola,

Two combo boxes on the form. One called Country and the other called Region.

Properties of Country are set as follows:

Under Format tab
Column Count = 2
Column Widths = 0cm; 2.5cm

Under Data tab
Row Source = "SELECT CountryID, CountryName FROM tlkpCountry ORDER BY CountryName"
Bound Column = 1

Properties of Region are set as follows:

Under Format tab
Column Count = 2
Column Widths = 0cm; 2.5cm

Under Data tab
Bound Column = 1

And the Row Source is set using the following VBA code after the Country is selected.

Private Sub Country_AfterUpdate()
    With Me![Region]
        If IsNull(Me!Country) Then ' if no country is selected
            .RowSource = ""  ' leave row source of Region blank
        Else ' if country is selected
' populate the Region combo box with Regions where Country is as selected
            .RowSource = "SELECT  RegionID, RegionName " & _
                        "FROM tlkpRegion " & _
                        "WHERE CountryID=" & Me.Country
        End If
        Call .Requery ' requery the region combo box to show the selection
    End With
End Sub

Mary

[Imicola]

Thankyou so much, it is working now!

Nicola

[MMcCarthy]

Thankyou so much, it is working now!

Nicola

You're welcome!

[NeoPa]

Another resource that may help in circumstances similar to these is Example Filtering on a Form.
I appreciate that the OP has found her solution already though, this is for anyone searching for a similar problem.