473,320 Members | 1,841 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Dependent listboxes on access forms

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
Feb 27 '07 #1
6 3476
MMcCarthy
14,534 Expert Mod 8TB
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
Feb 27 '07 #2
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Country_AfterUpdate()
  2.     With Me![Region]
  3.         If IsNull(Me!Country) Then
  4.             .RowSource = ""
  5.         Else
  6.             .RowSource = "SELECT [RegionName] " & _
  7.                         "FROM tlkpRegion " & _
  8.                         "WHERE [CountryID] = " & Me!Country
  9.         End If
  10.         Call .Requery
  11.     End With
  12. 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
Feb 28 '07 #3
MMcCarthy
14,534 Expert Mod 8TB
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Country_AfterUpdate()
  2.     With Me![Region]
  3.         If IsNull(Me!Country) Then ' if no country is selected
  4.             .RowSource = ""  ' leave row source of Region blank
  5.         Else ' if country is selected
  6. ' populate the Region combo box with Regions where Country is as selected
  7.             .RowSource = "SELECT  RegionID, RegionName " & _
  8.                         "FROM tlkpRegion " & _
  9.                         "WHERE CountryID=" & Me.Country
  10.         End If
  11.         Call .Requery ' requery the region combo box to show the selection
  12.     End With
  13. End Sub
Mary
Feb 28 '07 #4
Thankyou so much, it is working now!

Nicola
Feb 28 '07 #5
MMcCarthy
14,534 Expert Mod 8TB
Thankyou so much, it is working now!

Nicola
You're welcome!
Feb 28 '07 #6
NeoPa
32,556 Expert Mod 16PB
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.
Mar 2 '07 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Simon Templar | last post by:
I need the following functionality: With 2 listboxes populated from a database with the SAME data, I need any of the listboxes to stop displaying the option when selected at the other listbox. Eg:...
2
by: Justin Koivisto | last post by:
Firstly, I want to thank all that have helped me out with getting grips on Access each time I've had questions. This has got to be one of the most helpful groups that I've posted to over the years....
6
by: Ralph2 | last post by:
Some time ago with a lot of help from this group I made a reasonably successful database to keep track of our shop drawings. However the searching mechanism is too complicated for the occasional...
4
by: bill yeager | last post by:
I have several template columns inside of a datagrid. Inside of these template columns are databound listboxes: <asp:TemplateColumn HeaderText="Crew Chiefs"> <ItemTemplate> <asp:listbox...
8
by: Galina | last post by:
Hello I have 6 dependent list boxes on my ASP page:  Faculty;  Lecturer;  Course;  Course occurrence;  Group;  Week commencing date. When faculty is selected, lists of lecturers and...
3
by: VB Programmer | last post by:
In VB.NET 2005 (winform) any sample code to drag & drop items between 2 listboxes? Thanks!
4
by: Full NameEntry | last post by:
Hi all, I'm new to csharp... learning it by muddling my way though examples. In my current example, I'm trying to make a form with a bunch of listboxes that have drag and drop enabled amongst...
14
by: Ima Loozer | last post by:
OK folks I'll try to explain my objective as clearly as I can... Might be hard... I want a list box on one of my forms. List box will have the persons name in column 0. Next three columns...
0
by: annivanova | last post by:
Hi, I saw posts in Dependent listboxes on access forms, which problem is very similar to my. I’m from Bulgaria and working over to create an MS Access application. I used in my project given code...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.