Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Design question & cascading combo boxes (XP,2000)

    In a form used to input information about businesses and their various locations, I have a number of combo boxes that show the state (rowsource is a statelookup query). Then I have combo boxes that show the counties within that state. The county combo boxes rowsource is a select statement looking at the states-counties table selecting only those counties where the state matches the contents of the previously mentioned state combo box.

    Now, I learn that I need to also have the country identified as well as an industry country codes, industry state codes, & industry county codes.

    Here is my design dilemma

  2. #2
    Star Lounger
    Join Date
    Sep 2003
    Location
    Ocean Gate, New Jersey, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design question & cascading combo boxes (XP,2000)

    Don,

    Happy New Year. I understand your questions however I am not enough of a pro at Access to answer all your questions. What I can tell you is I have done something like this with one table but multiple queries and then used a cascading combo box (demo attached). I do know you want to stay away from duplicating information (use one county name no matter how many states have the same county name).

    Hope this info is helpful.
    Attached Files Attached Files

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Design question & cascading combo boxes (XP,2000)

    The fully relational approach would be to set up a Country table, a State table and a County table. The Country table would have one record for each country, including a CountryID. For optimum speed, the ID should be numeric. The State table would have a record for each state/province with the CountryID as a foreign key. States could have the same name as long as the countryID is different. The county table would have a record for each county for each state. It would have the County information, which could be duplicated between states/countries, and a StateID linking it to the State table. There is no need for a direct link between country and county, since each State record contains a link back to its country. You would use queries to link the tables on the related fields for your comboboxes. For instance, a query joining the State and County tables will also contain the CountryID, since that is in the State table. Does that answer your question?
    Charlotte

  4. #4
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design question & cascading combo boxes (XP,2000)

    That answered my question perfectly. My cascading comboboxes work fine now. One thing I thought I could do was to automatically update the text box that holds the industry code for the country, state, & county. I thought I would be able to use the same query but inasmuch as they are text boxes, I don't know where to begin making the select statement - if, indeed, that is what I would do. I want the user to be able to pick country, state & county from cascading comboboxes but the industry codes associated with each of those should automatically fill their respective text boxes.

    Where do I begin for this? I looked at the column property for comboboxes but was unable to understand the explanation.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Design question & cascading combo boxes (XP,2000)

    I don't understand. Surely there are multiple industry codes for a country, state and county, aren't there? What are you trying to show in a textbox?
    Charlotte

  6. #6
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design question & cascading combo boxes (XP,2000)

    <P ID="edit" class=small>(Edited by Don_Sadler on 02-Jan-04 18:25. Correcting my mistake about textbox)</P>I'm sure that every industry has its own set of codes. In this case, the industry has a two digit code for countries & states and a three digit code for counties. Since I followed your earlier advice and created queries upon which my comboxes rely, I also included in those queries the column that has the country, state, or county code as appropriate. What i want is for the user to select the country, state, county in comboboxes. Next to each combobox is a text box with the label "IC" for industry code. I had hoped to be able to have these boxes automatically take the code associated with its combobox. Does that make any more sense? MY mistake, I thought you said textbook - now I see it's textbox.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Design question & cascading combo boxes (XP,2000)

    Since the industry code is part of the row surce of the combo boxes, you should be able to use it.

    If the industry code is the first column in the Row Source, your combo boxes probably have a Column Count of 2, and the first column has width 0 (in the Column Widths property), and the Bound Column is 1. In that case, the value of the combo box is the industry code, so you can set the Control Source of the text boxes to =[ComboBoxName]

    If the industry code i"s a "later" field in the Row Source, you must set the Column Count to include the industry code. Say that it is the third column. You can refer to this column as follows:

    =[ComboBoxName].[Column](2)

    The columns are numbered starting with 0, so Column(0) is the first column, Column(1) the second, etc.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Design question & cascading combo boxes (XP,2000)

    If the related code is in a column of the combobox recordset, it can be referred to by the column number, which starts at zero for the first column. You would ordinarily do this in the AfterUpdate event of the combobox and the CurrentEvent of the form. So if you have included the indsutry code as the 4th column in the combobox , probably with a column width of zero, you would put something like this into the AfterUpdate of the combobox:

    Me!txtIC = Me!combobox1.Column(3)

    In this case, I'm assuming that txtIC is the name of the textbox and that combobox1 is the name of the combo being referred to. Keep in mind though, that this won't work properly on a continuous form unless it is in the header of the form. Since the textbox is unbound, using this code on a textbox in the detail section of the form will result in the same industrycode showing for every record.
    Charlotte

  9. #9
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design question & cascading combo boxes (XP,2000)

    Hi Don,
    This is a side question. In your post you talk about using select statements for the county combo box to only show the counties for the state selected, would you mind posting that select statement and where you put it? I believe I want to do something similar.
    Thanks,
    Deb

  10. #10
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design question & cascading combo boxes (XP,2000)

    Rather than post code, I just created a file that shows what I did. Please download the example mdb and feel free to view/copy/use any of the code in there as it is all snippets and procedures from, or inspired by, everyone.

    The form has a tab control - I wanted to take all the controls off the tab but when I did, it didn't work so I just left the tab control in place. If anyone can figure out why it doesn't work when I cut/paste the controls from the tab to the form, I would appreciate knowing.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Design question & cascading combo boxes (XP,2000)

    If you cut one or more controls from the tab control, the event procedures associated with these controls remain in the form module as orphans - they don't belong to an existing control any more. When you paste the controls back into the form, the associations with the event procedures are not restored. You have to select [Event Procedure] and click the builder button ... in each of the events to reconnect the event procedures. A lot of work!

  12. #12
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design question & cascading combo boxes (XP,2000)

    Thanks for the explanation. Wish there were an easier/faster way to do this.

Posting Permissions

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