Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2008
    Posts
    10

    Unanswered: Drop Down Menu on Forms

    Is there a way to have a form the when you chose one option say Numbers or Letters on a drop down list (Based on a table) that it will change the rest of the drop down menus on the form.

    Example
    First Drop Down gives you the choice of Letters or Numbers

    If you chose Letters the next Drop down is A,B,C,D (also based on another table) you choice Numbers in the next drop down shifts to 1,2,3,4 also based on a completely new table. I will then take the data on the final form and write it to a table based on the information that was supplied on the form. Please Help me. I know this can be done in C and VB but VBA is giving me trouble and I do not know how to integrate VB into VBA.

    Thanks

    Dale

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Go to http://www.utterangel.com/utterangel.aspx and look for Cascading Combo boxes.

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I did something like this a while ago in Excel:
    http://www.dbforums.com/showthread.php?t=1633243
    The principle is the same, but the execution will be rather different.

  4. #4
    Join Date
    Oct 2008
    Posts
    3

    Drop down menus

    You can do this in SQL databases but in MS Access there is no option for that kind of drop down menus. With in this database you can make simple drop down menus that consists on numeric or alphabetic data or both.


    Shadow Stewart


    Social Bookmarking Service

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Shadow Stewart, I have to disagree.
    You can set an unbound combo box to display either "Letter" or Number". You can then set its Change event to set the row source of the next combo box to the SQL statement that you require.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Shadow Stewart is just spamming garbage posts into forums and "conveniently" linking a completely irrelevant site into the post to give his site linkbacks and bouncetraffic.

    Every single post he's made is the same:

    RANDOM GARBAGE
    Link to his site.


    I'd remove his links in every post,... actually, I'd probably just delete all his posts.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Shadow Stewart - what are you basing your answers on? Is this from your personal MSAccess experience? You might want to say something in the lines "from my limited experience in working with MSAccess". I only say that because it's dangerous to say that "in MSAccess there is no option to do this" as I've done this (as probably many other experienced MSAccess users have - ie. see wejas post) many times in MSAccess and your site link doesn't show a backround of any real MSAccess experience.

    I can forgive a lack of knowledge on how to do this from your end but I have a problem with incorrectly guiding other users.
    Last edited by pkstormy; 10-15-08 at 23:00.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Thanks, StarTrekker - I was getting worried about my memories for a moment then!

  9. #9
    Join Date
    Oct 2008
    Posts
    10

    More Detail

    Ok I understand how to use cascading drop down menu. But what if I want the 2nd drop down menu to change. Example. I will use wire that I would like to classify. 1st menu - 3 choices (wire, coaxial, cable). If you pick wire the 2nd drop down would be a choice between Gauge and Metric, if you pick coaxial cable the 2nd drop down menu would be type (Coaxial, triax, etc..). How is that done. Please help

  10. #10
    Join Date
    Oct 2008
    Posts
    10

    My question again

    Ok, I just asked the same question again. What I really want to know is this. I am Classifing Wire.

    1st Choice - Type
    Wire
    Cable
    I picked Wire

    2nd choice -Color
    Black
    Red
    White
    I picked White

    3rd choice Stripe
    Yes
    No
    I picked no
    4th Choice Gauge
    18
    20
    22
    Now if on the 3rd choice I said Yes, the 4th choice would be Stripe Color and 5th choice would then be Gauge. My question is how do you add a extra drop down menu based on a choice.

    Thanks
    Dale

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I find it easiest to create all of the boxes you need and toggle their visibility. I'm not a big fan of dynamically creating controls in Access.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I "think", if I'm reading you right, that you need to first somehow correlate the relationships in the tables these dropdowns are based on. For example, if you select "wire" in your first combobox box, the 2nd combobox needs to somehow have "wire" in the 2nd (3rd, or 4th) column of the dropdown's query which has the values "wire", "coaxial", "cable". (so if you were to look at all the records in the rowsource query for the 2nd combobox it would show something like this....
    Other Value, Type
    "Guage", "wire"
    "Metric", "wire"
    "XXXX", "coaxial"
    "YYYY", "coaxial"
    "WWW", "cable"
    "ZZZZ", "cable"

    Then in the rowsource query for the 2nd combobox, you'd put in the criteria for the Type column, =Forms!MyFormName!My1stComboboxName (in the above example then, if I were to select "cable" in my 1st combobox, my choices would be WWW and ZZZZ in the 2nd combobox.

    Either that or you would set the rowsource of the 2nd combobox to a query you want based on what is selected in the 1st combobox.
    ie.

    Private sub MyComboboxNameAfterUpdate()
    if me!MyComboboxName = "wire" then me!2ndCombobox.rowsource = "Select FieldName from SomeTable"
    if me!MyComboboxName = "coaxial" then me!2ndCombobox.rowsource ="Select FieldName from SomeTable2"

    (or if the 2nd Combobox is configured for a Rowsource Type: Values verses on a Table/Query)
    if me!MyComboboxName = "wire" then me!2ndCombobox.rowsource = "XXX;YYY;ZZZZ"
    if me!MyComboboxName = "coaxial" then me!2ndCombobox.rowsource ="AAA;BBBB;CCCC"

    Somehow though I think what you're trying to do is based on a little different structure without any correlation between the values of the 1st combobox to the 2nd combobox and the 3rd combobox,...etc.... If this is so and there's no correlation (ie. relationship in the tables on the values), you pretty much need to code in what the values of the 2nd combobox should be based on what is selected/entered in the 1st combobox. (or have several different comboboxes each based on a set of values and do like Teddy suggested and make the combobox(s).visible = true or false dependant upon what is selected in the 1st combobox.)

    I may not have related this in the best way but does it kind of make sense?
    Last edited by pkstormy; 10-18-08 at 01:55.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Oct 2008
    Posts
    10
    Ok.

    I figure out a better way to do this. After reading several books on VBA, I figure out I could just have the user pick the type on the form then move the object such as metric or gauge around the form and make some visable and some not. That way they are not given the option of making the wrong choice.

    I thank everyone for their help

    Dale

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I don't quite understand your solution but it seems quite different from what you were asking in the posts. Thanks for updating us though.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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