Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Two Combo Boxes (2000)

    I have a form with 1 combo box with the following code:

    Private Sub Combo2_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ProdDesc] = '" & Me![Combo2] & "'"
    Me.Bookmark = rs.Bookmark
    End Sub
    When they pick a choice in the combo box, I have a subform that displays all the records matching that choice.

    I would like to have 2 combo boxes so that they can choose out of one combo box and then narrow it down with the other combo box. Say I want to choose a SIC Code and then I want to choose a zip code. How would I set this up? Thanks for any help you can provide.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Two Combo Boxes (2000)

    Your code does not actually do what you say it does.

    Your code finds the first matching entry, but it does not hide any records.

    To hide records you need to build a filter string, then set the filteron property to true.

    If you want to hide records based on two combo boxes, you need to build a filter string based on both of them.

    Do you want the contents of the second combo alos restricted based on the first choice?
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Two Combo Boxes (2000)

    If you want to narrow down the choices in the second combo box based on the selection in the first one, see MSKB articles ACC2000: How to Synchronize Two Combo Boxes on a Form and ACC2000: How to Create Synchronized Combo Boxes, or do a search for cascading combo boxes in this forum.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two Combo Boxes (2000)

    I am using the wizards to create the combo box and the subform and that is the code it creates.

    I would like them to be able to choose from the first combo box and then the second. Also, if it is possible to bring up a zip code in the second box without the first box if that is possible.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two Combo Boxes (2000)

    How do you search for cascading combo boxes?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Two Combo Boxes (2000)

    If you click Search in the Lounge menu, you'll get a search window in which you can specify what to look for, and where.

  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two Combo Boxes (2000)

    Hans, I must applaud your Search button circling abilities <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Two Combo Boxes (2000)

    Microsoft Paint to the rescue! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two Combo Boxes (2000)

    I have attached the database (stripped down) with the form I am trying to put two combo boxes on - one for Sic Code (with description) and one for zip code. I would like to be able to choose a SIC code or choose a SIC code and a zip code or choose a zip code. The attached form is what I want (with the subform bringing up the results of what is choosen in the combo boxes. I read the "How to Create Synchronized Combo Boxes" but how do you get the results like the attached form. I would also like to print the results (like the form does now with just one option). Is this possible (I guess anything is possible unless you are "Access knowledge challenged" like I am. Thanks for your patience and help.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Two Combo Boxes (2000)

    It is not entirely clear to me what you want. Do you want to filter records (only display those that match the value(s) selected in the combo boxes), or display all records and go to the first one that matches?

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two Combo Boxes (2000)

    I want what it is doing now - displaying what is selected in the combo box but the ability to select just a SIC code and have it display those clients with that SIC code irregardless of the zip.. To be able to select a SIC code and a zip and have it display the clients that have that SIC code and that zip code or just select the zip code and have it display the clients that have just that zip code irregardless of the SIC code and then ultimately print the results. I hope I am explaining this correctly.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Two Combo Boxes (2000)

    See the attached database. I have used just one form (no subform). Selecting a SIC in the first combo box filters both the form and the ZIP combo box. If you want to select an arbitrary ZIP in the second combo box, you must clear the SIC combo box (manually or through the button). The report prints the same records as displayed in the form.

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two Combo Boxes (2000)

    Hans - Thank you so much for the form. I would have never figured it out. I will be sure to give you credit. Only one thing. Why when I close that form I get a box that says Microsoft Access and an OK button. How do I get it to not appear?

  14. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two Combo Boxes (2000)

    Never mind - I just recreated the close command button and that message doesn't appear.

    Thanks again

Posting Permissions

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