Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Box (2000)

    Hi,

    I current have two combo box (One is named Code and another one is named Detail) on one of form. The data of both combo box are come from one table. My question is how can I put VB code, so after user select data from Code then data that connect with code appear on Detail combo box. Also after user select data from Detail combo box then the data that connect with Detail appear on Detail combo box.

    Regards

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Combo Box (2000)

    Here are two articles from the MS Knowledge Base that might help.

    ACC2000: How to Synchronize Two Combo Boxes on a Form

    ACC2000: How to Create Synchronized Combo Boxes

    If you also search back through past posts under "synchronized combo" or 'cascading combo" you will find lots of posts.
    Regards
    John



  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box (2000)

    Hi,

    I readed the MS knowledge Base example over and over. But I still can't figure how this works in my case. The data of two Combo Box (a&[img]/forums/images/smilies/cool.gif[/img] are come from one table, Please see code below:

    Me.b.RowSource = "SELECT Diagnosis FROM" & _
    " b WHERE codes = " & Me.a & _
    " ORDER BY Diagnosis"
    Me.b = Me.b.ItemData(0)

    After I select data from Combo Box a and nothing appears in Combo Box b.

    Thanks

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

    Re: Combo Box (2000)

    1) Where did you put this code?

    2) Is b also the name of a table?

    3) What type of field is codes (text, number, date, yes/no)?

  5. #5
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box (2000)

    Hi Hans,

    Sorry for unclear information.
    1) I put the code under Combo Box a, after update.
    2) b is the combo box name. I have two combo box ( One name is a, another one name is [img]/forums/images/smilies/cool.gif[/img]
    3)The type of field is codes (text)

    Regards

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

    Re: Combo Box (2000)

    Try
    <code>
    Me.b.RowSource = "SELECT Diagnosis FROM" & _
    " b WHERE codes = " & Chr(34) & Me.a & Chr(34) & _
    " ORDER BY Diagnosis"
    Me.b = Me.b.ItemData(0)
    </code>
    Text values must be enclosed in quotes; Chr(34) is the double quote ".

  7. #7
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box (2000)

    Thanks, Hans.

    I tried the code, but still not working. So I attached the sample DB.

    Thank you so much for your help.

    Thank you.

    Regards

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

    Re: Combo Box (2000)

    In my first reply in this thread, I asked you whether b was also the name of a table. You chose to ignore this question. It now turns out that it isn't the name of a table (or query), so the SQL statement

    SELECT Diagnosis FROM b WHERE ...

    makes no sense. The FROM part of an SQL statement can only refer to

    a) a table
    [img]/forums/images/smilies/cool.gif[/img] a query
    c) another SQL statement

    but not to the name of a combo box. So you should use

    SELECT Diagnosis FROM Diagnoses WHERE ...

    Moreover, if you use a name containing spaces or punctuation in an SQL statement, you must put square brackets [ and ] around it, otherwise Access gets confused:

    ... WHERE [ICD-9/DSM-IV codes] = " & Chr(34) & Me.a & Chr(34) & _
    ...

    Finally, this example is useless, since there will be only one record in the table for each selection in the first combo box. So there is no need for a second combo box: why have a dropdown list if there is only one choice?

Posting Permissions

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