Results 1 to 10 of 10
  1. #1
    JonathanT is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    63

    Update a Cascading Combo Box


    Iím trying to create a cascading combo box. I want the values in the Ratecbo to be determined by the value in the Properties Bo. When I set a value in the PropertiescboĒ I get the correct values in my Ratecbo except for two properties. The only difference with those 2 properties is that the name of the property is 2 words instead of one. I get zero results in my ďRatecboĒ for those 2 properties. I also have a Requery set up in the Event of the Propertiescbo to requery the Ratecbo. I changed the names of the 2 problem properties to a single name and got the correct values. How do I get the correct values when I spell the names correctly. Any ideas would be appreciated. I can't post the database because of proprietary information.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,597
    I think you'll have to post what the combos are based on (sql statements?). Not sure what 2 words means. Could be "this that" or "this, that" in which case you have a multi value field (not a great idea). You could also examine the row source property for each control - make a test query using their sql statements and look at them in design view for clues if your data is not in a multi value field. Whatever the criteria is, it doesn't work as you think.
    EDIT - you also have to consider that the 2nd combo is filtered based on the bound column of the 1st combo - if the combo is bound. You might be seeing "this" in the control but if that isn't the bound column, the 2nd combo is basing the list on whatever is in the bound column of the 1st combo.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  3. #3
    JonathanT is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    63
    The values in Propertiescbo are actually city names. Cities like Dallas, Houston that are one word produce the correct result in the Ratecbo. Cities like New York, St Louis produce no results on the Ratecbo. I'll see if I can create a cleansed copy of the file without the proprietary information and post the database

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,251
    Can you show us the row source SQL for the Ratecbo?
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    JonathanT is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    63

    Tes2Database

    I've attached a test Database without the proprietary information. The Ratecbo is actually called RatePlanscbo
    Test2.zip

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,597
    Look at the property values in Properties. Which of those 2 word cities are in PropRates.Properties?
    The values of Property.ID should be in PropRates.Property, not repeating the city names. Both Property and Properties are reserved words, which means you really shouldn't use them for object names.
    http://www.allenbrowne.com/AppIssueBadWord.html
    Last edited by Micron; 02-20-2021 at 09:19 PM. Reason: added info
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,473

  8. #8
    mike60smart is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    623
    Hi Jonathan

    In the attached I changed the names for each tables Primary Key

    I added the Foreign Key "ProperyID" to the table "PropRates"

    The cascade now works as it should
    Attached Files Attached Files

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,597
    PropRates does not need the field "Property". Denver is 2, Dallas is 1. No need for both.
    RateID PropertyID Property RatePlan
    1 2 Denver 15MARC
    2 1 Dallas $20GAS
    3 5 Omaha $25FB
    In the same vein, it's likely that there should be a table for rate plans and PropRates.RatePlan ought to be PropRates.RatePlanFK and its values should be the ID of tblRatePlans. Some normalization needed for this db I think.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  10. #10
    JonathanT is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    63
    Thanks to all. I changed the name of any field that used the name "Property" to Cities. The names of the 2 problem cites in the PropRates table were spelled wrong. Can't believe I missed that. Everything is functioning as expected now.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 12-09-2014, 12:24 PM
  2. Replies: 1
    Last Post: 02-27-2014, 03:43 PM
  3. Replies: 2
    Last Post: 09-10-2013, 09:10 AM
  4. Replies: 6
    Last Post: 02-01-2013, 10:02 AM
  5. Replies: 7
    Last Post: 12-29-2011, 10:13 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums