Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Edinburgh, Scotland
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Limiting Lists (Access 97)

    Hi, can't get my head round this one - can anyone help. I have two combi boxes Select 1 and Select 2. Select 1's underlying table has 4 items id'd A, B, C and D. Select 2 has 10 items id'd 1:10.

    What I am trying to do:

    If Select1.Value = A then I want to show 1:10 in Select 2. If Select1.Value = B Then I want to show value 1 only, If Select1.Value = C OR D then I want to show values 1, 3 and 5. I have been trying to code as an After Update event on the Select 1 combi, but am not having any success as my syntax is not good. Can anyone help please? Many thanks.

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

    Re: Limiting Lists (Access 97)

    Create a table with all possible combinations of Item and ID. It would look like this:

    <table border=1><td align=center>ItemID</td><td align=center>OtherID</td><td>A</td><td align=right>1</td><td>A</td><td align=right>2</td><td>A</td><td align=right>3</td><td>A</td><td align=right>4</td><td>A</td><td align=right>5</td><td>A</td><td align=right>6</td><td>A</td><td align=right>7</td><td>A</td><td align=right>8</td><td>A</td><td align=right>9</td><td>A</td><td align=right>10</td><td>B</td><td align=right>1</td><td>C</td><td align=right>1</td><td>C</td><td align=right>3</td><td>C</td><td align=right>5</td><td>D</td><td align=right>1</td><td>D</td><td align=right>3</td><td>D</td><td align=right>5</td></table>
    Let's assume this table is named tblJoin.
    Set the row source of Select2 to
    SELECT OtherID FROM tblJoin WHERE ItemID = Forms!frmMyForm!Select1
    where frmMyForm should be replaced by the name of your form.

    In the AfterUpdate event of Select1, requery Select2:
    Select2.Requery

    If you need more columns in Select2, you'll need to set its row source to a query that joins your original table and tblJoin.

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    Edinburgh, Scotland
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limiting Lists (Access 97)

    Thanks Hans

Posting Permissions

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