Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2002
    Posts
    7

    Angry Unanswered: textbox/listbox whose values depend on combobox value & query

    Hi.

    I have a db table like this

    CHILD CHILDTYPE PARENT PARENTTYPE
    object1 A position1 P
    position1 P system1a S
    system1a S system1b S
    system1b S location1 L

    object2 A position2 P
    position2 P system2a S
    ...etc

    So it displays the hierarchy of the object, where object(1,2...) is the lowest in it. I'm displaying objects in a combo box. When the user selects one and clicks a button, the whole hierarchy of the selected object should appear in text or list boxes. Like:
    txtbox1: position
    txtbox2: systema
    txtbox3: systemb
    txtbox4: location

    In the click event of the button I tried doing something like
    txtbox1.controlsource = ("SELECT [table].[CHILD] FROM [table] WHERE
    [CHILDTYPE] = 'A' AND [PARENTTYPE] = 'P' AND [CHILD] = [combobox].Column(0)")
    txtbox2.controlsource = ("SELECT [table].[CHILD] FROM [table] WHERE
    [CHILDTYPE] = 'P' AND [PARENTTYPE] = 'S' AND [CHILD] = [txtbox1].text")
    and so on.
    Unfortunately I keep on getting the #Name?

    Anyone know the right way to do this???

  2. #2
    Join Date
    Jul 2002
    Posts
    22
    im working on answer - but mind one thing, that you will get circular references because you use "S" key 4 times instead of two. the answer i will post will change last two "S"'es to "R"s

  3. #3
    Join Date
    Jul 2002
    Posts
    22
    Your Table:

    "Table"

    CHILD CHILDTYPE PARENT PARENTTYPE
    object1 A position1 P
    position1 P system1a S
    system1a S system1b R
    system1b R location1 L
    object2 A position2 P
    position2 P system2a S

    Mind that there is no distinction between CHILDTYPE of object1 and object2

    Your 4 additional sub queries you need:

    "qryQuerySub1":

    SELECT Table.CHILD
    FROM [Table]
    WHERE (((Table.CHILDTYPE)='A') AND ((Table.PARENTTYPE)='P'));

    "qryQuerySub2":

    SELECT Table.CHILD
    FROM [Table]
    WHERE (((Table.CHILDTYPE)='P') AND ((Table.PARENTTYPE)='S'));

    "qryQuerySub3":

    SELECT Table.CHILD
    FROM [Table]
    WHERE (((Table.CHILDTYPE)='S') AND ((Table.PARENTTYPE)='R'));

    "qryQuerySub4":

    SELECT Table.CHILD
    FROM [Table]
    WHERE (((Table.CHILDTYPE)='R') AND ((Table.PARENTTYPE)='L'));


    And the main query for combobox

    "qryQueryCombo":

    SELECT Table.CHILD AS combobox, Table.PARENT AS txtbox1, Table_1.PARENT AS txtbox2, Table_2.PARENT AS txtbox3, Table_3.PARENT AS txtbox4
    FROM (([Table] INNER JOIN [Table] AS Table_1 ON Table.PARENTTYPE = Table_1.CHILDTYPE) INNER JOIN [Table] AS Table_2 ON Table_1.PARENTTYPE = Table_2.CHILDTYPE) INNER JOIN [Table] AS Table_3 ON Table_2.PARENTTYPE = Table_3.CHILDTYPE
    WHERE ((Table.CHILDTYPE)='A');


    On your form add following event:

    "form":

    Private Sub combobox_AfterUpdate()
    Me.txtbox1.Value = Me.combobox.Column(1)
    Me.txtbox2.Value = Me.combobox.Column(2)
    Me.txtbox3.Value = Me.combobox.Column(3)
    Me.txtbox4.Value = Me.combobox.Column(4)
    End Sub

    set the properties of combobox to:
    ColumnWidths = 0.6";0.6";0.6";0.6";0.6"
    ColumnCount = 5
    ListWidth = 3"

  4. #4
    Join Date
    Jun 2002
    Posts
    7
    Thanks a bunch, "unknown", it's working now!
    The only thing that confused me was the inner joins, Table.PARENTTYPE = Table_1.CHILDTYPE where it should've been Table.PARENT = Table_1.CHILD. I was getting all the possible combinations where the child type is e.g.'A' and parent 'P'... Luckily I figured it out.
    The reason I'm using type 'S' several times because that's how it is with the software and that's how it's saved in its underlying database. Not my idea... But hey, it's still working.

    Thank you, merci beacoup.

  5. #5
    Join Date
    Jul 2002
    Posts
    22
    yes you if the Parent/Child records are unique then its better to use them instead of Parent/Child Types. The new qryQueryCombo will look like:

    SELECT Table.CHILD AS combobox, Table.PARENT AS txtbox1, Table_1.PARENT AS txtbox2, Table_2.PARENT AS txtbox3, Table_3.PARENT AS txtbox4
    FROM (([Table] LEFT JOIN [Table] AS Table_1 ON Table.PARENT = Table_1.CHILD) LEFT JOIN [Table] AS Table_2 ON Table_1.PARENT = Table_2.CHILD) LEFT JOIN [Table] AS Table_3 ON Table_2.PARENT = Table_3.CHILD
    WHERE (((Table.CHILDTYPE)='A'));

Posting Permissions

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