Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Mar 2001
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Value from Another Table (Acess 2000 SR-1)

    Can someone save me from acute cyberrage? I work in a law office. I'm constructing a form for users to add a new record (representing a new case) to the Cases table. The user will be furnished with a numerical issue ID. I have a separate table (Issues) which contains the issue numbers and brief descriptions of the issue. I have added a combo box which displays a dropdown list of all the issue numbers from the Issues table. When the user selects an issue number, I want an adjacent control to show the descriptive text associated with that issue number, from the Issues table. Can I do this with built-in abilities of Access, as opposed to using VBA? I am very much of a newbie to Access, so hope to keep things simple ... Thanks!

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

    Re: Value from Another Table (Acess 2000 SR-1)

    You don't need much in the way of VBA for this. Just include the description as one of the fields in the combobox. It isn't necessary to display it in the combobox if you don't want to, you can just set that column width to zero to hide it.

    Then give your textbox a controlsource that is an expression something like "=MyCombo.Column(2)" (no quotes, please), where MyCombo should be replaced with the actual name of the combobox and where the column number is 1 less than the column the description is in (columns start numbering at 0, so column(2) is actually the third column).

    In the Afterupdate event of the combobox, put something like MyTextBox.Requery, replacing "MyTextBox" with the name of the actual control.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Mar 2001
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Value from Another Table (Acess 2000 SR-1)

    Charlotte,
    Thanks for your help!. Unfortunately, I am the Newby of all Newbies, so I need a little more help ...
    Ahem ... when you say " ... include the description as one of the fields in the combobox ..." I am lost. I didn't know you could include multiple fields in a combo box. What property of the combo box do you edit to get that, "Bound Column"? I tried filling that in with "0,1" (without the quotes.) But it didn't stick ... Also, I don't think I understand how to " ... display it in the combobox ...", although maybe if I get the first part right, MS Access will show me what the second part means?
    I really appreciate you and the other loungers being around to help while I try to get my feet under me.

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Value from Another Table (Acess 2000 SR-1)

    Hi Charles:
    <hr>didn't know you could include multiple fields in a combo box<hr>
    That's what puts the combo in combobox. You want to include the description in RowSource. The easiest way would be to let the combobox wizard walk you through. It will give you the option of selecting from a table or building your own list. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    New Lounger
    Join Date
    Mar 2001
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Value from Another Table (Acess 2000 SR-1)

    Brian, and Charlotte,
    Thanks very much to you both for your help. I never did digest all of Charlotte's advice, but trying to follow it I stumbled on sufficient new (to me!) resources to get the job done in a simpler way than I had originally planned. Thanks again, you've been a big help to this user at the bottom of the learning curve!

Posting Permissions

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