none
Web form macros: Are cascading combo boxes possible in web forms? RRS feed

  • Question

  • I have read a number of declarations that cascading combo boxes aren't supported in Access Services applications, but Albert Kallal has suggested that there is a way. Does anybody know?
    Dave
    Tuesday, November 22, 2011 12:10 AM

Answers

  • Glad you asked this (and I see no one stepped up to the plate here).

    The way you do this is quite simple. Let's assume two tables and two combo box.

    tblCity

    tblStores

    The first table is tblCity and we have a combo box that will let us select a city. The next combo box (the one to cascade) will restrict the combo box to a list stores for that given city.

    So we have a form that looks like this:

     

    screen1


    The code in the first combo box (cboCity) after update event will be this code:

    SetTempVar  (strCity,[cboCity])
    Requery (cboStores)

    Note the setting of a tempvar to the city we want to cascade in the second combo box called cblStores. Note the re-query command of the second combo.

    Ok, now the secret "trick" of herbs and spice:

    Build a query for the second combo box but DO NOT use a web query. A web query is going to be placed and run on the server. We need this to occur in the browser.

    So launch the query builder DIRECTLY on the combo box. (so we are going to build the sql on the combo box and NOT use a web query. If you build a web query and then set this combo box to that web query this will NOT work).

    However, building the query on the combo box will work, and the query will look like this:

    formquery

    When you close the query builder, then the sql is stuffed into the row source in the property sheet like this:

    propsheet


    The above is important since then the sql in forced to be in the browser and NOT sitting on the server halfway across the world where that query cannot get at or see the TempVar we set.

    However, if you place the sql in the form as above, then it works.


    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

    • Proposed as answer by Bruce Song Thursday, November 24, 2011 11:35 AM
    • Marked as answer by Bruce Song Monday, November 28, 2011 9:43 AM
    Tuesday, November 22, 2011 5:00 PM

All replies

  • Glad you asked this (and I see no one stepped up to the plate here).

    The way you do this is quite simple. Let's assume two tables and two combo box.

    tblCity

    tblStores

    The first table is tblCity and we have a combo box that will let us select a city. The next combo box (the one to cascade) will restrict the combo box to a list stores for that given city.

    So we have a form that looks like this:

     

    screen1


    The code in the first combo box (cboCity) after update event will be this code:

    SetTempVar  (strCity,[cboCity])
    Requery (cboStores)

    Note the setting of a tempvar to the city we want to cascade in the second combo box called cblStores. Note the re-query command of the second combo.

    Ok, now the secret "trick" of herbs and spice:

    Build a query for the second combo box but DO NOT use a web query. A web query is going to be placed and run on the server. We need this to occur in the browser.

    So launch the query builder DIRECTLY on the combo box. (so we are going to build the sql on the combo box and NOT use a web query. If you build a web query and then set this combo box to that web query this will NOT work).

    However, building the query on the combo box will work, and the query will look like this:

    formquery

    When you close the query builder, then the sql is stuffed into the row source in the property sheet like this:

    propsheet


    The above is important since then the sql in forced to be in the browser and NOT sitting on the server halfway across the world where that query cannot get at or see the TempVar we set.

    However, if you place the sql in the form as above, then it works.


    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

    • Proposed as answer by Bruce Song Thursday, November 24, 2011 11:35 AM
    • Marked as answer by Bruce Song Monday, November 28, 2011 9:43 AM
    Tuesday, November 22, 2011 5:00 PM
  • Albert,

    I replied to this post yesterday to thank you, but the reply is apparently not showing up here. Again, this works great - thanks very much!

    Dave


    Dave
    Wednesday, November 23, 2011 1:29 PM
  • Albert,

       As usual your responses are excellent.  You display superior knowledge especially in the web-based use of Access.

       I'm building an app based on the web-enabled Issues template.  Your tip on how to employ cascading combo box fields was exactly what I needed help with.  Being a rookie Access developer I'm struggling to get everything working as it should.

       What I've done is in the Issues template is change the Priority and Category fields on the IssueNew form to cascading combo boxes, with a selection in the Priority field setting the display of content in the Category field.  What I'm finding is these two fields are used in numerous places within the template, in several different forms, tables, and queries.  

       Finally, here's my question.  Is there a way to easily view where these two field's values are used within the template, other than just clicking and scanning visually each individual object?  It's very difficult to locate all the places where the changing of two fields impacts other elements of the template.

        Thanks for you help with this question, and for all the other excellent responses you've provided the Access Community!

    Lance Bowman

    Monday, August 6, 2012 5:15 PM
  • Well, actually there is VERY cool and little used feature in Access called "object dependences" that will help a lot in this regards.

    Unfortunately, the feature does not "resolve" down to the "column" level, but it does resolve down to the table level (so you can list out all objects that use that table).

    Keep in mind that there might be queries based on that table – so you would need to view dependences on those queries (the object dependences features does do this).

    So you can most certainly can have Access tell you what objects do depend and use the table in question (Issus table in your case). 

    Simply open up your database and then click on (highlight) the Issues table. Then in the ribbon in database tools area, click on object dependences.

    The result is a nice "tree-view" of your whole database and ALL of the objects that depend on what you just highlighted. It is a very useful tool. And you can not only navigate the tree-view, but clicking on any object in the tree-view will open up that object in design (layout) view.

    You will see this setup:

    So, you can't view dependences by field name, but you can by table.

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Monday, August 6, 2012 6:27 PM
  • You are a life saver!!.. Thank you very much for this info...
    Monday, August 27, 2012 6:50 AM
  • Hi,

    Thank you for posting this information, it is very useful as I too am trying to carry out a similar action with a CRM database that I am building to publish on SharePoint 2010. However, I haven't managed to get Access to do what I want yet! Here's what I'm trying to do:

    I have a table called tblCompanies which stores company details and I have a form based on this table that will allow users to add records to the database. Now though I have been asked to narrow down the options that users have for completing some of the fields by adding drop down lists to the form. For example, I have an 'Industry' field which I want to constrain to a specific list of Industries, this part is easily accomplished just by creating a lookup. Next I have a 'Sector' field which again needs to be limited to a specific list of options, but these options need to be restricted just to those related to the Industry selected previously. Is this possible, and are Combo boxes (as described above) the way to do this?

    Using the guidance in this post I have managed to create two combo boxes which appear to work, one which pulls up a list of Industries from a new table called tblIndustryList, and another which enables the user to select from a restricted number of sector options derived from another table called tblSectorList. This is great, however what I can't work out is how to use this to populate the appropriate fields on the original table (tblCompanies), can anyone help me with this?

    Sorry for adding to an old post but it is the closest to providing an answer that I could find after many hours of searching and working on this part of the database. I've only been using Access for a couple of weeks so I'm a complete beginner.

    Any help will be gratefully Received.

    Tuesday, October 9, 2012 3:45 PM
  • Next I have a 'Sector' field which again needs to be limited to a specific list of options, but these options need to be restricted just to those related to the Industry selected previously. Is this possible, and are Combo boxes (as described above) the way to do this?

    The above set a series of steps of outline should work for you.  Often the problem is people build a separate web query, and save it.  So please note that the trick to making this work is you cannot save the web query, you have to embed the query into the combo box.

    The other additional detail that may affect you, is keep in mind that while using local vars and temp Vars should work (and they do when running this in the access client, after you publish, you MUST use TempVars (global).

    So try the above steps, and see if that gets this working.

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada


    Friday, October 12, 2012 3:04 PM
  • Thank you, I have managed to get this working now, once again I appreciate your help.

    Sam

    Thursday, November 1, 2012 11:02 AM
  • Hello,

    I have used temp var strLevel1, ([strLevel1],[CmbLevel2]) but I am getting an error "Object doesn't contain the Automation object 'strLevel1'.. plse help

    Wednesday, December 19, 2012 12:35 PM
  • I figured the error was I was using the wrong combo box, now I used the first combo box..

    basically I dont have the error but no result.. pls let me explain my problem..

    first combo box: select query I am getting name & domain id while I am displaying name only

    second combo box: In select query I have to use the domain id above and display name.

    my afterupdate on combo 1 is setTempvar [cmbLevel1]![Domain_ID]   as strLevel1

    and sql for using the strLevel1 in where condition for parent_id =[TempVars]![strLevel1]

    Please tel me is there something wrong.. I dont get values ( just to be sure I gave values manually to parent id and got results so the sql is correct...)

    thx

    Wednesday, December 19, 2012 1:43 PM
  • Hi Albert. I  have a small doubt on this. How do you eliminate the duplicates from the [city] Column here?. I mean in the combo box only unique city should be shown .Lets say one city can have multiple stores hence obviously the city column can contain the duplicates as shown in the image.  Your reply on this query is greatly appreciated.

    Thank

    santhosha


    Santhosh

    Tuesday, September 8, 2015 8:54 AM