Results 1 to 8 of 8
  1. #1
    HillChris7714 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    14

    Cascading Combo Boxes on a Datasheet...

    I have a Datasheet view that contains 2 combo boxes: Category and Type. When the user selects a Category, they should get different options for Type.

    Meaning, there is a preset list of Types, which are bound to different Categories. So, if they select Category 1, they should only see Types A, B and C. If they select Category 2, they see Types X, Y and Z.

    My problem is, when I requery the Type dropdown, it requeries ALL the Type dropdowns in the entire grid. This is no good because, the records that have Category 1 will be able to see Types X, Y and Z, which should only be available for records that have Category 2.



    My research thus far proves that what I'm doing is impossible because, when you change a RowSource, you're changing the RowSource for ALL instances in the gird. So naturally, my first question is: Is this true?

    But, even if it is, how come, in the following code, Me.Type = "" blanks out the Type box for just THAT record, but the Me.Type.RowSource updates the RowSource for ALL records? How come Me.Type = "" is able to segregate the combo box on THAT record, but RowSource isn't? That doesn't make sense... am I missing something?

    Code:
    Private Sub cboCategory_Change()
        Me.Type = ""
        Me.Type.RowSource = "SELECT dbo_RequirementType.Descr FROM dbo_RequirementType WHERE dbo_RequirementType.Category = '" & Me.cboCategory & "'"
        Me.Type.Requery
    End Sub

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397

  3. #3
    HillChris7714 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    14
    Just in case someone comes upon this question in the future, and the link Ajax provided doesn't work for some reason... or if someone is just looking for a straight answer and don't want to be pointed to a question on another forum, then have to download a sketchy file without knowing what it is or what it does, dissecting their code and basically stabbing at it until they figure it out, I'll attempt to elaborate and give an actual answer to my own question:

    Unfortunately, you can't segregate just one combo box on one row and update it's RowSource. But, you can fool the user into thinking that's exactly what's happening.

    Rather than updating the RowSource on the Category's "Change" event, update it on the Type's "Enter" event. So, whenever you update the RowSource on the Type combo box, you're updating every instance in the grid, but the only one the user is concerned with is the one they're interacting with. By updating the RowSource on the Type level, you're ensuring the RowSource gets updated every time they interact with the Type field... ensuring they see values that are tied to that row's selected Category!

    Hope this helps!

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I believe Ajax supplied a very useful link to some code that would help. All of the helpers are volunteers and so any assistance they provide should be appreciated rather than issuing rather snide remarks. Please try and maintain a kind and gentle forum or just refrain from commenting altogether. Your last post was useful and appreciated but you spoiled it with the first paragraph.

  5. #5
    HillChris7714 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    14
    Yeah, it was a fairly useful link that contained an answer to my question. I just thought that, if someone like me stumbles upon this post, they'd rather just see an answer rather than a link that said, "Look here, STUPID!!!" Cuz, that's kinda how it felt. I mean, did he already know where to find the answer to that question, or did he perform a search in some way that I hadn't thought of, or...? I just thought my answer would be a better contribution.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Chris,

    You are not the first relatively new poster (or seasoned veteran) who simply wants an answer. But often these questions lack context or specifics. Responders are often guessing at what the poster wants/needs, and also what experience level the poster has, and/or if there is some other factor(s) involved that haven't been mentioned.

    Experienced responders tend to review a post, do a quick check on
    the use of terminology and the completeness of the question and the amount of contextual information supplied and make an educated choice of response to the poster. Could be a link, could be a tutorial, could be a direct answer, could be a series of questions for more detail etc.
    Ajax pointed you to something he was familiar with in a different forum that seemed to meet your needs.

    That you felt
    "Look here, STUPID!!!" Cuz, that's kinda how it felt.
    is unfortunate. But again, you aren't the first. Many posters think regulars on the forums are all-knowing with 100% clairvoyance. We aren't - were some unpaid volunteers who are just trying to give back some of the info and experience we have gathered over the years. And we have developed a certain "thick skin" from hours of "learning the hard way" or responding to the same question many times. And many of our guesses, although well-intended, may be off-base.

    You seem to be working with SQL SERVER (guess on my part because of the dbo.) and have identified Cascading combos as a requirement.There are many links leading to details and examples of Cascading combos in MSAccess available through Google or Bing etc. You seem to have encountered one of the idiosyncrasies of datasheet.

    There are excellent tutorials, although they are a little older at DataPig.
    For cascading combos see:
    http://www.datapigtechnologies.com/f...combobox1.html and
    http://www.datapigtechnologies.com/f...combobox2.html

    There is a whole series of free tutorials re combo boxes at TechOntheNet

    Good luck with your project.

  7. #7
    HillChris7714 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    14
    But, now that we've had THIS little exchange, my answer is now "in the fray" and might not be evident to anyone who comes here looking for an answer, so...

  8. #8
    HillChris7714 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    14
    All I did was elaborate on his freaking answer so that, in case someone comes here wanting a straight answer, they can get it. I appreciate Ajax's help. All I did was re-factor his help in a way that someone like me might have better appreciated it. If you think I'm unappreciative of his answer or misunderstand what this forum is for, I'm sorry you fell that way, but that's your problem.

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

Similar Threads

  1. Replies: 8
    Last Post: 09-09-2015, 07:07 AM
  2. Cascading combo boxes in datasheet form
    By gemadan96 in forum Forms
    Replies: 1
    Last Post: 06-20-2014, 09:59 AM
  3. Cascading combo boxes
    By combine21 in forum Forms
    Replies: 3
    Last Post: 12-02-2010, 12:57 PM
  4. Cascading combo boxes in datasheet view
    By Hjava in forum Forms
    Replies: 1
    Last Post: 06-28-2010, 06:02 PM
  5. Cascading Combo Boxes
    By desireemm1 in forum Programming
    Replies: 1
    Last Post: 10-05-2009, 06:00 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