Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2015
    Posts
    3

    Unanswered: How do I restrict form combo box values based on the selection of a prior combo box?

    Hi,

    This is my first time asking a question, so I will apologize in advance for any awkwardness. I have created an access database for tracking expense reports from board of directors members and a form for entering in the data. The first drop down box allows the user to choose between three different legal entities. The allowable values are hard coded, since there are only three options and they will not change. The second drop down box allows the user to pick the individual member. The source for the second drop down box is a table that lists each member. This table also has the relevant legal entity code for each member. Most members are only in one legal entity, but a couple are in multiple legal entity. The remainder of the form is just for entering in expenses such as airfare, meals, etc.

    What I want to do is this: If the user picks entity A in the first drop down box, then the only members that the user can pick from the second drop down box (board members) are ones whose legal entity code is entity A. I have tried to use the expression builder as a Validation Rule in the form properties, but have been unable to do so. I have also done several forum searches, but have not had any luck. I'm not sure if this can be done in access, vs. using SQL Express/Server. The database only has a few users and we typically have 10 to 15 reports per week. Any help would be very much appreciated. Thanks.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    To do the multi-combo box
    each combo box has a query for its recordsource.
    Here the user picks a state, then in the next combo picks a City (in that state)

    The 1st combo,(say cboStates)... User picks a state, then picks a city from the cboCity box.
    The cboStates AfterUpdate event will trigger when the user picks it, and this will update the next combo.


    Code:
    sub cboStates_AfterUpdate()
     cboCity.requery
    end sub

    The cboCity query (say qsCityViaState) will reference the cboStates in the query sql
    Select [city] from tZipCodes where [ST] ='" & forms!frmMain!cboStates & "'"

    The City combo must be refreshed (cboCity.requery action) after user picks the state so it can deliver the resulting dataset.

    If there is another combo after this say cboEmps to pick employees, then the cboEmp must be refreshed after user picks cboCity.
    Code:
    sub cboCity_AfterUpdate()
     cboEmps.requery
    end sub

  3. #3
    Join Date
    Aug 2015
    Posts
    3
    Hi ranman256

    Thank you very much for the response. It took me some time to get it to work (still learning Access syntax), but I was able to do so.

    Bill

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    FYI, the concept is called 'cascading comboboxes,' although the exact same thing applies to Listboxes with the MultiSelect Property set to None, and the code is interchangeable.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Aug 2015
    Posts
    3
    I will have to remember that. I've seen the term cascading before, but didn't know what it meant. I was able to cascade it down from entity to member to meeting in the form (based on the entity chosen in the first box). It has vastly improved the form, since users will now only the members and meetings appropriate for that entity. So thanks to everyone for the help. I know a little more than I did before.

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we could help! We're all learning...all the time! I've been doing this for two decades, now, and seldom a day goes by when I don't learn something new, often from helping others, like yourself! It's what, to me, makes Access development fun!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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