Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2004
    Location
    long beach, california
    Posts
    7

    Unanswered: SQL Query using text from a combobox

    Hello all,

    I will try to explain this without sounding like a complete idiot.

    I have a form and 3 tables right now. The two tables that i'm interested in at this point are called "Class items" and "Class data"

    On the form there are two comboboxes:

    combobox "ItemMainClass" takes its data from table "Class items"
    combobox "ItemSubClass" takes its data from table "Class data"

    Right now, ItemMainClass uses an SQL query to get its data.

    SELECT [Class items].ProductsID, [Class items].ProductName FROM [Class items] ORDER BY [Class items].ProductsID;

    What I am trying to achieve, is when a user selects something from "ItemMainClass", the contents of "ItemSubClass" are values that belong to the main class. In both tables, I have a column with identical data which are referenced to each other. In "Class items" it is "Product Name" and in "Class data" it is "ProductsID"

    In class data I have a few records, some with matching ProductsID fields. The outcome should be that "ItemsSubClass" combo is filled with all of the records in "Class data" who's "ProductsID" field match that of the "Product Name" field in ItemMainClass.

    To do this I was trying something like this:

    SELECT [Class data].ProductsID, [Class data].Description FROM [Class data] WHERE ProductsID=[ItemMainClass].text ORDER BY [Description];

    But when I do this the combo is empty...could someone maybe point me in the right direction? I am pretty sure that the problem lies in the WHERE clause but after trying various things I have had no luck.

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    first of all I think you dont need .text extension in the last select operation. second did you requery second combobox after first combo is choosen?
    Last edited by ghozy; 08-12-04 at 18:30. Reason: typo
    ghozy.

  3. #3
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    I am assuming you have this in the afterupdate of the first combo box?

    Here is an after update event I have used in the past to do the same thing you are wanting except for with list boxes. (I am also actually setting some of the boxes manually instead of through tables).

    Code:
    Private Sub lst2_AfterUpdate()
        On Error GoTo lst2_err
        If lst2.Value = "Series" Then
            lstParts.RowSource = ""
            lst4.Visible = False
            With lst3
                .Visible = True
                .Enabled = True
                .ColumnCount = 2
                .ColumnHeads = True
                .Width = 3000
                .RowSourceType = "Value List"
                .RowSource = "SeriesID, Series Name;1, Flat Stock;2, Scallop;3, Botsko;4, Shaw Traditional;5, Ernst;6, Shaw Special;7, Fluted;8, Vase;9, Accessories"
            End With
        ElseIf lst2.Value = "Class" Then
            lstParts.RowSource = ""
            lst4.Visible = False
            With lst3
                .Visible = True
                .Enabled = True
                .ColumnCount = 2
                .ColumnHeads = True
                .Width = 3000
                .RowSourceType = "Value List"
                .RowSource = "ClassID, Class Name;HD, Header;TR, Trim;SP, Special;BL, Balustrade;CP, Coping/Cap;SL, Sill"
            End With
        
        ElseIf lst2.Value = "Both" Then
            lstParts.RowSource = ""
            With lst3
                .Visible = True
                .Enabled = True
                .ColumnCount = 2
                .ColumnHeads = True
                .Width = 3000
                .RowSourceType = "Value List"
                .RowSource = "SeriesID, Series Name;1, Flat Stock;2, Scallop;3, Botsko;4, Shaw Traditional;5, Ernst;6, Shaw Special;7, Fluted;8, Vase;9, Accessories"
            End With
            With lst4
                .Visible = True
                .Enabled = True
                .ColumnCount = 2
                .ColumnHeads = True
                .Width = 3000
                .RowSourceType = "Value List"
                .RowSource = "ClassID, Class Name;HD, Header;TR, Trim;SP, Special;BL, Balustrade;CP, Coping/Cap;SL, Sill"
            End With
        End If
    
    lst2_exit:
        Exit Sub
    
    lst2_err:
        MsgBox Err.Description
        Resume lst2_exit
        
    End Sub
    Let me know if you have any questions. After that list box is completed it goes to another... and then from there I pull from a table with a query. Let me know if you want the rest of the code. (I am using mine as a search function on the top of a form for my Salesmen to generate sales orders.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  4. #4
    Join Date
    Aug 2004
    Location
    long beach, california
    Posts
    7
    I tried it without .text originally with no luck. When i take a normal text box and put =ItemMainClass.Text for the control source, it shows the correct value. if I put =ItemMainClass, it shows the Item# of the correct value, which is an autoincrement.

    I'm not sure what you mean by "requery". What I do is I select something from the "ItemMainClass" combo, and click on it, which makes the textbox I mentioned above fill with the value I am searching for. Then i drop down the "ItemSubClass" combo box and there is no data in it. Is there a different method to re-query after I select something?

    In response to JSThePatriot:

    Correct me if I am wrong but the code that you gave me, doesn't that require that you use static definitions of the data? Your .RowSource field contains the data that I need to select from the database, which is why I was using a query.

  5. #5
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    in your first combox' afterupdate event you should enter secondcombox.requery so when you choose something from firstcombobox, it will refresh second combobox with new values.
    ghozy.

  6. #6
    Join Date
    Aug 2004
    Location
    long beach, california
    Posts
    7
    Yes i tried that and it does work, but with just one problem.

    It does indeed populate the list correctly, but no matter what i do, it ALWAYS uses the first value for the combobox, and saves the record like that. I'm not entirely sure why...

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I am not sure where you have defined the RowSource but the easiest way is by assigning it to a stored query in design view and just requiring in the AfterUpdate event.

    In this case the sql equivalent of the designed query would be something
    Like

    SELECT [Class data].ProductsID, [Class data].Description FROM [Class data] WHERE ProductsID= Form![YourFormName]![ItemMainClass] ORDER BY [Description];

    If you are defining the RowSource in code (in the after Update event!) then it should look like this

    ItemSubClass.RowSource = “SELECT [Class data].ProductsID, [Class data].Description FROM [Class data] WHERE ProductsID= “ & [ItemMainClass] & “ ORDER BY [Description];”

    It will still need requerying in the AfterUpdate event.


    You will not the difference, in the first you have to make reference to the FORM and in the second you have to concatenate the VALUE of the first combo box otherwise it will read as a literal string and not a value.

    I don’t know if that is the problem ?

    MTB

  8. #8
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    I believe attaching a sample database would greatly help. I'm confused.
    ghozy.

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    going back a couple of steps to
    When i take a normal text box and put =ItemMainClass.Text for the control source, it shows the correct value. if I put =ItemMainClass, it shows the Item# of the correct value, which is an autoincrement.
    repeat your experiment, instead of =ItemMainClass.Text, use (make many text boxes - this is an experiment after all)
    =ItemMainClass.column(0)
    =ItemMainClass.column(1)
    =ItemMainClass.column(2)
    ...and so on until you get an error.

    you combo keeps things in rows & columns. one of the columns is the "bound column", that is what you get if you use
    =ItemMainClass.Value

    and since .Value is the default property for a Combo, you get the same from
    =ItemMainClass
    where the .Value is implicit

    and very probably you (or the wizard) set up the combo with the ID field as the first column (0 based index, so column(0) is first) so you probably also get the same from
    =ItemMainClass.column(0)
    if indeed 0 is the bound column

    you can also read the other columns in your combo (as the experiment showed (i hope!!!)).

    it is interesting to see that combo.text works for you
    .text doesn't feature as a combo property in my a2k help
    by way of contrast, the .column(n) approach is thoroughly documented

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    oh, i should add that .text requires you to setfocus to the combo, but .column(n) does not.

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Aug 2004
    Location
    long beach, california
    Posts
    7
    Well i was preparing a sample DB (just erasing and cleaning up a copy of the current one) to show you guys, and i fixed it.

    izyrider, the problem was indeed the boundcolumn. I changed the boundcolumn on item_sub_class to 2 instead of 1, and it all of a sudden started working. so overall i added item_sub_class.requery in the itemmainclass_afterupdate event, and changed the bound column.

    I attached the sample database anyways just in case someone searching the forums for a problem like mine would like it, or whatever.

    also, izy, I have noticed that in the code you need to have the focus to use .text, but if you're using it in a query (i've been using the properties box mainly), it does actually work. But i will try to move over to .column() instead.

    thanks for you help guys!
    Attached Files Attached Files

Posting Permissions

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