Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Populate combo box based on previous combo box selection

    Hi all,
    I am trying to populate a second combobox which depends on selection in first combobox. To this end I have created four tables:


    categoryType This table has the data for the first combo box and contains three records, Expenses, Income and Refunds


    categoryRef This table contains about 10 records and the data should be displayed when Refunds is selected in the first combo box


    categoryInc - This table contains about 15 records and the data should be displayed when Income is selected in the first combo box


    categoryExp - This table contains about 20 records and the data should be displayed when Expenses is selected in the first combo box


    Is the above the best way to achieve what I am trying to do? Are there any alternatives?
    I need this to be fairly low maintenance as there may be a need to add/edit/delete records from any of the tables from time to time.

    Thanks in advance.

    Bill

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    I think the easiest way is to have 3 combo boxes, one each for Refunds, Income, and Expenses. Then based on the Type (in the forms Current event and in the AfterUpdate event of cboType), you just change which of the 3 is visible.

    The alternative is to have 1 combobox for all 3 Types and change the rowsource in those same events.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Mark.
    If I understand you correctly, I don't think the first solution will work. Seems a little clunky having to change which of the three is visible. I was hoping to select one of the three categoryType options and then the appropriate options will become available for selection depending on what of the three options were selected in the first field. I'm having a little trouble understanding where your fits in there. However I am a newbie and I sure it's my lack of understanding.

    How would your second suggestion work?

    Thanks again for your help.

    Bill
    Last edited by bnorthby; 2012-06-10 at 11:17. Reason: Spelling error

  4. #4
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    Hi Bill,

    > Is the above the best way to achieve what I am trying to do? Are there any alternatives?

    No and yes to your two questions. Having similar information (catagory subtype) in three different tables is not in accordance with database normalization rules. The much better alternative is to have a categoryType lookup table, as you do, and a categorySubType table that includes all of the data in the three tables. You will relate the data by creating a relationship between the primary key of the categoryType table and the foreign key of the categorySubType table. You can use either a text-based primary key in the categoryType table (ie. the actual data: Expenses, Income and Refunds), or a meaningless autonumber (surrogate) primary key. I prefer using autonumbers myself. I'll use an autonumber primary key (pk) and Long Integer foreign key (fk) in my example.

    tblCategoryTypes
    pkCatType (autonumber / set as primary key)
    CategoryType

    tblCategorySubTypes
    pkCatSubType (autonumber / set as primary key Note: Pretty much every table should have a primary key.)
    CatSubTypeName (this would likely be the same data that you have in the three tables).
    fkCatType (long integer. If you are using Access 2003 or earlier, remove the default value of 0).
    plus any other fields that describe attributes specific to category subtypes.

    The fkCatType field will contain values from the pkCatType field in the tblCategoryTypes table.

    Here is an example, from the Northwind sample database for Access 2002 and 2003 (the same logic works in any version of Access). This example looks to use numeric keys:

    How to synchronize two combo boxes on a form in Access 2002 or in Access 2003
    http://support.microsoft.com/?id=289670

    In your case, the field "CatSubTypeName" would be used in place of ProductName in the Northwind sample, I think something like this [untested "air code"]:
    Code:
    Me.cboCatSubType.RowSource = "SELECT CatSubTypeName FROM" & _
       " tblCategorySubTypes WHERE fkCatType = " & Me.cboCategories & _
       " ORDER BY PCatSubTypeName
    Me.cboCatSubType = Me.cboCatSubType.ItemData(0)
    In the above example, I have used a commonly used naming convention, "cbo", as part of the names of two combo boxes on your form. Try working the example out first, in the Northwind sample database. Then try mimicking this example in your database, that has table and field names specific to your application.

    Naming Conventions
    Special characters that you must avoid when you work with Access databases
    http://support.microsoft.com/?id=826763

    Commonly used naming conventions
    http://www.mvps.org/access/general/gen0012.htm
    http://www.xoc.net/standards/default.asphttp://www.xoc.net/standards/rvbanc.asp#Access

    Using a Naming Convention
    http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx

    Reserved Words
    Problem names and reserved words in Access
    http://allenbrowne.com/AppIssueBadWord.html
    Last edited by tgw7078; 2012-06-10 at 13:46. Reason: Grammer corrections and added some additional clarifying information.
    Tom Wickerath
    Microsoft Access MVP
    4/1/2006 - 3/31/2012

  5. #5
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Wow, this is more difficult than I had hoped. Probably beyond my meager abilities.
    Thanks all so much for your help but I may have to think of another way of achieving what I'm after.

    Bill

  6. #6
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    Hi Bill,

    Implementiong a synchronized combo box is really not that difficult, if you spend some time working with the Northwind sample database. I agree that implementing proper database design can be difficult, especially for beginners, but doing so will pay dividends in the future. There is lots of good information on the topic of database normalization:
    .

    Database Design Tips by Michael Hernandez:
    http://www.accessmvp.com/JConrad/dow...DesignTips.zip

    http://www.accessmvp.com/JConrad/acc...abaseDesign101

    Good Luck
    Last edited by WendellB; 2012-06-12 at 10:02. Reason: Increase the font size to something readable
    Tom Wickerath
    Microsoft Access MVP
    4/1/2006 - 3/31/2012

  7. #7
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Tom, appreciate your encouragement.

    I will give it another go, but I find Microsoft's Help and accompanying explanations quite frustrating and in the end demoralising.

    Will see how I go.

    Thanks.

    Bill

  8. #8
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    Hi Bill,

    Feel free to post a compacted and zipped sample, that shows your efforts, if you run into problems. Of course, remove any sensitive data first, or just create a new database importing just the bare minimum to show the functionality. Note: I have a personal preference for the .mdb file format.
    Tom Wickerath
    Microsoft Access MVP
    4/1/2006 - 3/31/2012

  9. #9
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Arhh!

    Perhaps someone can see where I have gone wrong.

    In the attached db, in the Category Management form I am trying the populate the Category combo box depending on the selection in the Type combo box. I can't get it to work.

    Anyone see where I have gone wrong?

    Thanks heaps.

    Bill
    Attached Files Attached Files

  10. #10
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    Standby....I'm looking at your database now.
    Tom Wickerath
    Microsoft Access MVP
    4/1/2006 - 3/31/2012

  11. #11
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Tom.

  12. #12
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    Hi Bill,

    The first two changes I made have nothing to do with the combo boxes, but I consider important nonetheless:
    1.) Disabled Name Autocorrect.
    2.) Disabled the option that allows one to make design changes to a table in Datasheet view.

    Name Autocorrect (AKA Name Autocorrupt) has always been buggy. I recommend always disabling this "feature" in all Access databases. I guess I'm just a purist, but it violates my senses to have the Add a column feature available in datasheet view. I have nicknamed this feature the denormalization wizard, since it encourages new users to add fields to tables, without necessarily thinking through the database design.

    In the Category table, you had a field named fkCategoryType, but this field was a text data type, and had the same values as the lookup table, CategoryTypes. The text data would only be appropriate if you were using text-based primary and foreign keys. I deleted this field, and renamed the empty field with this name. I then populated this field with the appropriate numeric values. Note: I've already forgotten the original name of the 4th empty field, and at this point, I would have to restart a virtual machine, with Access 2007 installed, just to quote the original fieldname correctly.

    Your two combo boxes were named "Text0" and "Combo2". I renamed these to "cboCategoryType" and "cboCategory", respectively. I also reset the bound column of the Text0/cboCategoryType combo box from 2 to 1. This way, when one selects a value in this top combo box, the bound column will be the first column in the rowsource, which is the numeric primary key.

    Make sure to enable code in your database now, since the first combo box, cboCategoryType, now has an AfterUpdate event procedure. This code will not run unless the database is in a trusted folder (Thank You, Microsoft! [NOT]).

    Hope this helps,
    Attached Files Attached Files
    Last edited by tgw7078; 2012-06-11 at 02:06. Reason: Needed to attach .zip file with revised sample.
    Tom Wickerath
    Microsoft Access MVP
    4/1/2006 - 3/31/2012

  13. #13
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Tom.

    Thanks heaps for your efforts with this. However there is one problem.

    No matter what is selected in the Category Type field, the same options appear in the Category field. I was hoping that only each relevant category would be displayed whenever either Income, Expense, Refund were selected in the Category Type field.

    Can this be done?

    Thanks.

    Bill

  14. #14
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Bill,
    I don't know why you think this is so difficult. The code to determine which to make visible is this:

    cboRefund.Visible = (cboType="Refund")
    cboIncome.Visible = (cboType="Income")
    cboExpense.Visible = (cboType="Expense")
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  15. #15
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by MarkLiquorman View Post
    Bill,
    I don't know why you think this is so difficult.
    Because it's a while since I've touched Access and some of the terminology is killing me:-)

    Thanks for your advice on the code. Much appreciated. Where do I put it?

Page 1 of 2 12 LastLast

Posting Permissions

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