Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combobox List to Depend on Option Selected (Access 2000)

    We have a database form with an unbound Option Group called "Estimated Cost" with 2 options--Value 1 is labeled "Under $5,000" and Value 2 is labeled "Over $5,000." The form also contains a bound combobox field called "AuthorizedBy" with its Visible property initially set to "No." Depending on what option the user selects, we want the AuthorizedBy combobox to become visible but reflecting only the names of those who can approve the work order for the option amount selected, say, for Option Value 1, the list should include John Doe1, John Doe2, John Doe3, John Doe4; but if option Value 2 is selected, the AuthorizedBy combobox should reflect only John Doe3 and John Doe4 in the list--No matter what option or what name is chosen, the AuthorizedBy name needs to be reflected in the underlying table's AuthorizedBy field. Any help with code to accomplish this task would be much appreciated. Thank you...Mary

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

    Re: Combobox List to Depend on Option Selected (Access 2000)

    Can the condition "Under $5,000" or "Over $5,000" be translated into criteria for the row source of the combo box? In other words, can you set up a query that would select those authorized to approve thingumajigs with an estimated cost under $5000? And the same, but for an estimated cost over $5000? (And what happens if the estimated cost is exactly $5000?)

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combobox List to Depend on Option Selected (Access 2000)

    Hello, Hans...I never thought about the query. Currently the form is bound to the main table, not a query. But if you think that might be better, then I'll try it. I was hoping there was an event for the option group that could be triggered to do so. By the way, the bound combobox "AuthorizedBy" does not necessarily have to be invisible to start. We just want to be sure that if the cost is $5,000 or over that only the two people authorized to sign for the order appear in the combobox; otherwise, several other people are authorized if the cost is below $5,000 and all names should appear. (Your right--I guess we should clarify labels in case cost is exactly $5,000 (which is unlikely to happen but possible). Thanks for your help!...Mary

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

    Re: Combobox List to Depend on Option Selected (Access 2000)

    I didn't mean that the form should have a query as record source, but that the combo box should have a query as row source. You would change the row source of the combo box in the After Update event of the option group. I don't have enough information to give specific advice, that's why I asked how you would select the persons that can authorize something under or over $ 5,000 in a query.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combobox List to Depend on Option Selected (Access 2000)

    Hans, sorry; I misunderstood. I'll try working with the AfterUpdate event of the Option Group and the combobox's Row Source property, which now lists all persons authorized to sign. Will be back in touch if I run into trouble. Many thanks...Mary

  6. #6
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combobox List to Depend on Option Selected (Access 2000)

    Hans, many thanks! Sometimes one can't think of the simply solutions! Appreciate your help--the follow code works great!...Mary

    Private Sub EstimatedCost_AfterUpdate()
    If Me.EstimatedCost.Value = 1 Then
    Me.AuthorizedBy.RowSource = "John Doe1; John Doe2; John Doe3; John Doe4; John Doe5"
    End If

    If Me.EstimatedCost.Value = 2 Then
    Me.AuthorizedBy.RowSource = "John Doe1; John Doe2; John Doe3"
    End If

    If Me.EstimatedCost.Value = 3 Then
    Me.AuthorizedBy.RowSource = "John Doe1; John Doe2"
    End If

    End Sub

  7. #7
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combobox List to Depend on Option Selected (Access 2000)

    Hans, many thanks! Sometimes one can't think of simple solutions! Appreciate your help--the following code works great!...Mary

    Private Sub EstimatedCost_AfterUpdate()
    If Me.EstimatedCost.Value = 1 Then
    Me.AuthorizedBy.RowSource = "John Doe1; John Doe2; John Doe3; John Doe4; John Doe5"
    End If

    If Me.EstimatedCost.Value = 2 Then
    Me.AuthorizedBy.RowSource = "John Doe1; John Doe2; John Doe3"
    End If

    If Me.EstimatedCost.Value = 3 Then
    Me.AuthorizedBy.RowSource = "John Doe1; John Doe2"
    End If

    End Sub

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

    Re: Combobox List to Depend on Option Selected (Access 2000)

    Hello Mary,

    This will work fine if the number of persons is limited and if the list won't change often. If you have a large number of persons, or if there are frequent changes, I would add a field in the "persons" table that indicates what kind of transactions they can authorize. You could then select persons based on this field in a query. It may be more work to set up initially, but in the end it is more flexible. One other point in favor of using queries is that you only need to modify values in a table; the method you used requires that you edit the design of the form each time you need to add, remove or change a name. This requires that you open the database for exclusive use, or if you give every user a local frontend, that you distribute a new frontend...

  9. #9
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combobox List to Depend on Option Selected (Access 2000)

    Many thanks, Hans...For now, there are only 2 to 5 persons, and they are key officers in the company so the values won't change often. I'll experiment with your suggestion, though. While I have your attention, may I ask another question--I just realized that even though initially the unbound option group has no default value when the form is open, but after completing one record having selected one of the 3 options in the unbound option group, that selection sticks when I go to the next record. Could you provide me with a snippet of code that will reset the option group to no default values when moving to the next record? Many thanks for your help...Mary

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

    Re: Combobox List to Depend on Option Selected (Access 2000)

    Hello Mary,

    Put the following instruction in the On Current event of the form:

    Private Code Form_Current()
    Me.EstimatedCost = Null
    End Sub

  11. #11
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combobox List to Depend on Option Selected (Access 2000)

    Many, many thanks, Hans! I appreciate your help more than I can say!...Mary

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

    Re: Combobox List to Depend on Option Selected (Access 2000)

    One other remark: when you need to check several values of the same control or variable, a Select Case block is more convenient than repeated If ... End If blocks:

    Private Sub EstimatedCost_AfterUpdate()
    Select Case Me.EstimatedCost
    Case 1
    Me.AuthorizedBy.RowSource = "John Doe1;John Doe2;John Doe3;John Doe4;John Doe5"
    Case 2
    Me.AuthorizedBy.RowSource = "John Doe1;John Doe2;John Doe3"
    Case 3
    Me.AuthorizedBy.RowSource = "John Doe1;John Doe2"
    End Select
    End Sub

Posting Permissions

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