Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    20

    Unanswered: Get the Combo box selected value to SQL sentence

    Example:

    I have a table like this:

    Table name CARS


    Code:
    Nro		Type		Model
    ----------------------------------------------------
    1		Luxus		Mercedes S 500
    2		Luxus		Mercedes S 400
    3		Sport		Mercedes SLR
    4		Sport		Mercedes SLK
    5		Family		Mercedes E 240
    6		Family		Mercedes E 280
    And I have a Form like this from table called RESERVATION

    Form name RESERVATIONS


    Code:
    Textbox:   Color
    Textbox:   year
    Textbox:   Price
    Combo box: Type
    Combo box: Model <-- hidden
    Type combobox get's the values from the Cars table like this:

    SELECT CARS.Nro, CARS.Type FROM CARS;

    And then I have an afterupdate event at the type Combo box which will make the model combo box
    visible
    , like this:

    Private Sub Type_AfterUpdate()

    Model.Visible = True

    End Sub


    Then this is where the problem is.. I now need to have an SQL sentence what would get the
    car models from the table CARS to the Model combo box depending on what Type was selected.

    I've tryed the model combo box like this:

    SELECT CARS.Nro, CARS.Model FROM CARS WHERE CARS.Type LIKE Forms!RESERVATIONS!Type;

    But that doesnt work.. It wont show up any models in the Model combo box..
    How can that be done? That LIKE Forms!RESERVATIONS!Type doesn't get the currently selected
    value from the combo box, I guess..

    help please!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    try putting something like this in the afterupdate event:

    Code:
    Private Sub Type_AfterUpdate()
    
    Model.Visible = True
    cboModel.RowSource = "
    SELECT CARS.Nro, CARS.Model FROM CARS WHERE CARS.Type LIKE '" & Type & "'"
    End Sub
    On a side note, you should really consider making tables for your types and models, then referencing via an ID instead of strings. it will make life easier for you later on.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jul 2004
    Posts
    20
    Thanks for the quick reply, that didnt quite do the job, but I noticed what was wrong when I looked back to my post..

    The Type combo box got the values with this SQL sentence:

    SELECT CARS.Nro, CARS.Type FROM CARS;

    But that first column was hidden, so I didnt thought about it at first.. When I did the SQL sentence below to the after update event:

    Model.RowSource = "SELECT CARS.Nro, CARS.Model FROM CARS WHERE CARS.Type LIKE '" & Type & "'"

    That Type got the fist colum from the combo box also so it came up like "1 Luxus" and "5 Family" and such.. so that was wrong..

    I did a new sql sentence to the Type combo box:

    SELECT DISTINCT CARS.Type FROM CARS;

    And now it works like it should. Thanks for that combo box rowsource thing, now it updates the list ok every time I select the type..


Posting Permissions

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