Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    559

    Unanswered: List box based on choice?

    Here's my issue:

    I'm creating a database which I have designed for the user to select the "Continent" (ie: North America, Central America, etc). What I want to do is when they select a particular continent, I need the "Country" field on the form to pull ONLY the results from the corresponding countries you can find in that Continent.

    Say for instance, if they picked "Central America", then (I'm guessing by an If --> Then statement) the table (tblCentralAmericaCountries) would populate the list box for the user to select the specific country in that Continent.

    Is this possible? Do I have the right idea? Not explain it well enough?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you need tables that identify continents and then countries within continents
    then defne a list box with continents.. id suggest uou populate it yourself with the continent and as the first item "all"
    define a list box with countries

    place some code in the fist list box's on click event some code which then sets the recordsource for the country list box, and the requery the country list box

    id suggest the 'all' option in the continent for those who don't need/want to drill down a list box.

    your code could looks like
    if lbcontinent.value=-1 then 'we have selected the 'all' option
    lbcountry.recordsource = "select ID, Name from Countries order by name"
    else
    lbcountry.recordsource = "select ID, Name from Countries where ContinentID=" & lbcontinent.value & "order by name"
    endif
    lbcountry.requery


    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2006
    Posts
    559
    Cool, so I was kinda right?

    I guess the only thing that is bad about this is that I'll have a hellacious amount of tables (like SouthEast Asia, SouthWest Asia, Asia, Eastern Europe, Western Europe, etc)?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no bad, BAAAD idea
    two tables
    one containing continents
    one containing countries

    if you were a tricky SQL wizard argiuably one table with a self referencing key pointing to a parent

    eg

    id name parentid
    1 asia null
    2 australasia null
    3 europe null
    4 america, north null
    5 america, south null
    10 united kingdon 3
    11 ireland 3
    9 canada 4

    you could even go don to regions
    eg
    35 england 10
    26 wales 10
    99 scotland 10

    conceivably yu could even go down to counties
    100 Cheshire 35
    13 yorkshire

    y'get the picture

    it may be worth having a refresh on normalisation.. rudys site is prety good, as is one from tony marsden that the glove puppet keps mentoning
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Not at all. Use two tables, one tblContinents and one tblCountries. Then use two combo boxes on your form, one each tied into one table.

    To explain. The tblContinents structure will be:

    PrimaryKey (Autonumber)
    ContName (Text)

    That only takes a few records.

    tblCountries structure:

    PrimaryKey (Autonumber)
    ForeignKey (Long - from the tblContinents table)
    Country (Text)

    If I had more time, I'd explain more fully how to tie the combos down, but I gotta leave rather suddenly. Hope this helps,

    Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •