Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Sep 2004
    Location
    Louisville, Kentucky, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change values in a list box based on other list bo (Access 2003)

    I need to have two list boxes on a form. The first list box values will remain the same. The second list box is to have a multiple value lists based on the selection in the first list box. How can I accomplish this? eg. 1st box values "A" and "B". The second has value lists of "A1,A2,A3" and "B1,B2,B3". How can I make the second list box show "A1,A2,A3" when "A" is selected in the first box?

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

    Re: Change values in a list box based on other list bo (Access 2003)

    This is usually done by creating a table containing the possible choices, setting the Row Source Type of the second combo/list box box to Table/Query and setting the Row Source to a query that selects the appropriate values. See How to synchronize two combo boxes on a form in Access 2002 or in Access 2003.
    However, it can also be done with value lists. Use the After Update event of the first list box like this:

    Private Sub lbxChoice1_AfterUpdate()
    If Me.lbxChoice1 = "A" Then
    Me.lbxChoice2.RowSource = "A1;A2;A3"
    Else
    Me.lbxChoice2.RowSource = "B1;B2;B3"
    End If
    End Sub

  3. #3
    Lounger
    Join Date
    Sep 2004
    Location
    Louisville, Kentucky, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change values in a list box based on other list bo (Access 2003)

    Thank you. I'll try it both ways.

  4. #4
    Lounger
    Join Date
    Sep 2004
    Location
    Louisville, Kentucky, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change values in a list box based on other list bo (Access 2003)

    I tried both ways. The query method would work better for my application. However, I cannot get it to work. I have attached a sample of the tables and form that I am using. Can you help me find what I am doing wrong? Thanks in advance!
    Attached Files Attached Files

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

    Re: Change values in a list box based on other list bo (Access 2003)

    You had set up the tables not quite correctly. The ShopArea table should have ShopAreaID as primary key, and the ToolList table's AreaID field should link to the ShopAreaID field. Moreover, the ToolList table shouldn't have an Area field - it's superfluous.

    This also requires the combo boxes to be set up slighty differently. See the attached version.

    Warning: the AreaID's I assigned might be off.
    Attached Files Attached Files

  6. #6
    Lounger
    Join Date
    Sep 2004
    Location
    Louisville, Kentucky, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change values in a list box based on other list bo (Access 2003)

    Thank you! This works great! I appreciate your help very much. 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
  •