Results 1 to 7 of 7

Thread: Selection

  1. #1
    Join Date
    Dec 2002
    Posts
    31

    Question Unanswered: Selection

    Dear all,
    I have three fields :
    1-make (the name of car, e.g Honda, Ford, Kia, ....)
    2-Model (different models for each make)
    3-Country ( the nationality of these cars, e.g Japan, USA, Korea)
    In a database for a car services for these cars (makes) we register
    car number, miles, name of the owner, in addition to car information,
    what I want to do is:
    when i select the car make, the Model combo will show only the different models of this makes to choose from, and the manufacturer field its value will be set to the correct country e.g japan, Korea, or USA

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: Selection

    Originally posted by mselima
    Dear all,
    I have three fields :
    1-make (the name of car, e.g Honda, Ford, Kia, ....)
    2-Model (different models for each make)
    3-Country ( the nationality of these cars, e.g Japan, USA, Korea)
    In a database for a car services for these cars (makes) we register
    car number, miles, name of the owner, in addition to car information,
    what I want to do is:
    when i select the car make, the Model combo will show only the different models of this makes to choose from, and the manufacturer field its value will be set to the correct country e.g japan, Korea, or USA
    ok what you will have to do is set up a few variables, now the code im pasting below is very complicated because i have lots of combos, after the past ill try to make sence of it for you



    'Set Week Variables
    If Week_From_Combo = "Please Select Week" Or Week_To_Combo = "Please Select Week" Then
    TimeBox = MsgBox("Please Select a Time Range", vbOKOnly, "Unknown Time Period")
    Shipper_Combo.RowSource = ""
    End
    Else
    From_week = [Forms]![Form:_PCRKMS_Selection]![Week_From_Combo]
    to_week = " and " & [Forms]![Form:_PCRKMS_Selection]![Week_To_Combo]
    End If

    'Set Trade Variable
    If Trade_Combo <> "*" Then
    Trade = " and [REF_PCRKMS_COMBO_LU_TBL].[Trade] like " & "'" & [Forms]![Form:_PCRKMS_Selection]![Trade_Combo] & "'"
    Else
    Trade = ""
    End If

    'Set Service Contract Variable
    If Sh_Service_Contract <> "*" Then
    Service_Contract = " and sc like" & "'" & [Forms]![Form:_PCRKMS_Selection]![Sh_Service_Contract] & "'"
    Else
    Service_Contract = ""
    End If

    'Set Reefer Dry Variable
    If Reefer_Dry_Combo <> "*" Then
    Reef_Dry = " and cargo like " & "'" & [Forms]![Form:_PCRKMS_Selection]![Reefer_Dry_Combo] & "'"
    Else
    Reef_Dry = ""
    End If

    'Set BTN Variable
    If BTN_Combo <> "*" Then
    BTN = " and BTN like " & "'" & [Forms]![Form:_PCRKMS_Selection]![BTN_Combo] & "'"
    Else
    BTN = ""
    End If

    'Set Equ Variable
    If Equ_Combo <> "*" Then
    Equ = " and EQU like " & "'" & [Forms]![Form:_PCRKMS_Selection]![Equ_Combo] & "'"
    Else
    Equ = ""
    End If

    'Set Shipper Concern Variable
    If Shipper_Concern <> "*" Then
    Sh_Concern = " and SH_Concern like " & "'" & [Forms]![Form:_PCRKMS_Selection]![Shipper_Concern] & "'"
    Else
    Sh_Concern = ""
    End If

    'Set Shipper Variable
    'If Shipper_Combo <> "*" Then
    'Shipper = " and shipper like " & "'" & [Forms]![Form:_PCRKMS_Selection]![Shipper_Combo] & "'"
    'Else
    'Shipper = ""
    'End If

    'Set Shipper Office Variable
    If Shipper_Office_Combo <> "*" Then
    Sh_Office = " and Sh_office like " & "'" & [Forms]![Form:_PCRKMS_Selection]![Shipper_Office_Combo] & "'"
    Else
    Sh_Office = ""
    End If

    'Set Consignee Concern Variable
    If Consignee_Concern_Combo <> "*" Then
    Cn_Concern = " and cn_concern like " & "'" & [Forms]![Form:_PCRKMS_Selection]![Consignee_Concern_Combo] & "'"
    Else
    Cn_Concern = ""
    End If

    'Set Consignee Variable
    If Consignee_Combo <> "*" Then
    Consignee = " and consignee like " & "'" & [Forms]![Form:_PCRKMS_Selection]![Consignee_Combo] & "'"
    Else
    Consignee = ""
    End If

    'Set Consignee Office Variable
    If Consignee_Office_Combo <> "*" Then
    Cn_Office = " and cn_office like " & "'" & [Forms]![Form:_PCRKMS_Selection]![Consignee_Office_Combo] & "'"
    Else
    Cn_Office = ""
    End If

    'Set Line Variable
    If Line_Combo <> "*" Then
    Line = " and line like " & "'" & [Forms]![Form:_PCRKMS_Selection]![Line_Combo] & "'"
    Else
    Line = ""
    End If

    'Set RCountry Variable
    If RCountry_Combo <> "*" Then
    RCountry = " and r_country like " & "'" & [Forms]![Form:_PCRKMS_Selection]![RCountry_Combo] & "'"
    Else
    RCountry = ""
    End If

    'Set Por Variable
    If POR_Combo <> "*" Then
    POR = " and Por like " & "'" & [Forms]![Form:_PCRKMS_Selection]![POR_Combo] & "'"
    Else
    POR = ""
    End If

    'Set Load Variable
    If Load_Combo <> "*" Then
    Load = " and [Load] like " & "'" & [Forms]![Form:_PCRKMS_Selection]![Load_Combo] & "'"
    Else
    Load = ""
    End If

    'Set Discharge Variable
    If Discharge_Combo <> "*" Then
    Discharge = " and discharge like " & "'" & [Forms]![Form:_PCRKMS_Selection]![Discharge_Combo] & "'"
    Else
    Discharge = ""
    End If

    'Set POD Variable
    If POD_Combo <> "*" Then
    Pod = " and pod like " & "'" & [Forms]![Form:_PCRKMS_Selection]![POD_Combo] & "'"
    Else
    Pod = ""
    End If

    'Set DCountry Variable
    If DCountry_Combo <> "*" Then
    DCountry = " and d_country like " & "'" & [Forms]![Form:_PCRKMS_Selection]![DCountry_Combo] & "'"
    Else
    DCountry = ""
    End If

    Dim Cur_DB As DAO.Database
    Dim Record_Set As DAO.QueryDef
    Set Cur_DB = CurrentDb()


    ' delete qrydef if it exists
    For Each qdf In Cur_DB.QueryDefs
    If qdf.Name = "Combo" Then
    Cur_DB.QueryDefs.Delete "Combo"
    Exit For
    End If
    Next qdf
    Shipper_Combo.RowSourceType = "Table/Query"
    Set Record_Set = Cur_DB.CreateQueryDef("Combo", "SELECT [REF_PCRKMS_COMBO_LU_TBL].[Shipper]" & _
    " FROM REF_PCRKMS_COMBO_LU_TBL" & _
    " where week between " & From_week & to_week & _
    " and [REF_PCRKMS_COMBO_LU_TBL].[shipper] is not null " & Trade & Service_Contract & _
    Reef_Dry & BTN & _
    Equ & Sh_Concern & Sh_Office & Cn_Concern & Consignee & Cn_Office & _
    Line & RCountry & POR & Load & Discharge & Pod & DCountry & _
    " Group By [REF_PCRKMS_COMBO_LU_TBL].[Shipper]" & _
    " Order by [REF_PCRKMS_COMBO_LU_TBL].[Shipper]")
    Record_Set.Connect = "ODBC;Description=DataWarehouse;DRIVER=SQL Server;SERVER=scnc044db;UID=LineDept;PWD=reader;Ne twork=DBMSSOCN;"
    [Shipper_Combo].RowSource = "Select Shipper from Combo order by Shipper"

    basicaly this is code that sets the variables and then querydef to fetch the data. you can probly get by with
    combo.rowsource = "select model from car_table where model is not null" & var1 & var2 & " order by model" I think you can skip the querydef
    I know it looks complicated but its not to bad if you break it down
    let me know if i can help more
    Jim

  3. #3
    Join Date
    Dec 2002
    Posts
    31
    Ia that what I'm asking about?

  4. #4
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    basicaly you need a query with a dynamic where clause to look at previous selections to determin futuer selections.

    what Im saying is
    create variables and with if than logic that populates the variables with the combo box selections that are already made, then use the variables in the query that sets the rowsource for the combo that has focus.

    basiacaly if you ahve ford selected and you were trying to figure out what models to put in the model combo the query would look like

    select models from table where make like 'ford'.

    using like in the query you can use wild cards in the combo selection. There are also other benifits, if you put the same code in all of the combos then you will neaver be able to make a selection that does not exhist. I wish I could send you my db that has it as an example but you would not be able to get any data because it is stored on my local SQL server.

  5. #5
    Join Date
    Dec 2002
    Posts
    31

    Lightbulb

    I'm Really new at Dbase programing, and I cant figure out how to make a where condition expression, if u have a simple code I can make use of it
    Thanks

  6. #6
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    if you are doing this in the query designer see image below

    if you are trying to write the sql
    something like
    select colun_name from table
    order by mid(colunm_name,1,3) asc

  7. #7
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by JDionne
    if you are doing this in the query designer see image below

    if you are trying to write the sql
    something like
    select colun_name from table
    order by mid(colunm_name,1,3) asc
    Attached Thumbnails Attached Thumbnails tes2t.bmp  

Posting Permissions

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