Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Combo Box (2000)

    How do I limit the contents of one combo box based on the value selected in another

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Combo Box (2000)

    Use the Before Update event of the first combo to set the recordsource of the second combo.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Combo Box (2000)

    I think it would be better in the after update event of combo1
    If your selection in a numeric value :
    <pre>Me!Combo2.RowSource = "Select Field1, Field2 from Table1 Where SelectionField = " & Me!Combo1
    Me!Combo2.Requery</pre>

    If your selection in a string
    <pre>Me!Combo2.RowSource = "Select Field1, Field2 from Table1 Where SelectionField = '" & Me!Combo1 & "'"
    Me!Combo2.Requery</pre>

    Francois

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Combo Box (2000)

    I have a main form for a literary Source and a subform in which a user can enter many Themes and Subthemes for that Source. The subform is a continous form type. Based on the Theme, only certain SubThemes populate the second combo box.
    Your code works well; I modified it to:

    Private Sub ThemeID_LostFocus()

    Me!SubThemeID.RowSource = "Select SubThemeID, ThemeID, SubTheme from tblSubTheme Where ThemeID = " & Me!ThemeID
    Me!SubThemeID.Requery

    End Sub

    But I have a problem that I am not sure how to address. This works well for the first record in the subform, but when a user tries to enter another subform record, the entry in SubThemeID for the previous records appear as blank. The proper value is saved to the table for every subform record, but the user sees a blank and that doesn't inspire their confidence. I understand the Requery is doing this, but how can I get this to work and still display the previously entered values on the previous records?

    TIA

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Combo Box (2000)

    This is typical behavior with comboboxes on a continuous form because there is really only one combobox , the one on the current record. The rest are illusory. When you change the rowsource of the combobox, you're seeing the current rowsource displayed in all the records, and that rowsource may not include the values that exist in the other records.

    There are several ways to deal with this, but I prefer to use a textbox bound to the same field as the combobox. I put the textbox on top of the combobox (you can also shrink the combobox down until only the dropdown arrow is visible) and lock the value in the textbox. I set the tabstop property of the combobox to No so that the user has to actually click the arrow to drop the combobox down. That way, the textbox will still display the actual value even if the textbox rowsource doesn't match that particular record's value.
    Charlotte

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Combo Box (2000)

    Charlotte,

    What do you enter in the textbox when the bound field of the combo is an ID and you want to display another column of the combo ?
    Francois

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Combo Box (2000)

    In that case, I *don't* bind them to the same field, I bind the textbox to a calculated field that returns the display value rather than the underlying value.
    Charlotte

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Combo Box (2000)

    Thanks
    Francois

  9. #9
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Combo Box (2000)

    This was precisely what I needed and it works great. I did need to create the calculated textbox to display the description, rather than the ID.

    Between you and Francois, my issue is solved. My thanks to both of you.

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Combo Box (2000)

    No charge ... this time. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

Posting Permissions

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