Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2002
    Posts
    9

    Question Unanswered: Combo Box Synchronization

    I've seen the numerous threads on this topic but I still can't get it to work correctly. What I'm trying to do is have a series of combo boxes that are linked to each other sort of step by step. For example, this is for a place that manufactures paint so the first would be selecting the product line, second would be selecting the sheen, and so on.... Keep in mind each product line would have a different set of sheens. I have a single table setup with one column with the names of the product lines and one column for each of the product lines(as the header) with their various sheens listed below it. I've tried all the methods I've seen and no dice...The most I get is the first combo box listing the product lines but the second combo box listing absolutely nothing. Any help would be greatly appreciated.

    Thanks.

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

    Re: Combo Box Synchronization

    Originally posted by JimboSHU
    I've seen the numerous threads on this topic but I still can't get it to work correctly. What I'm trying to do is have a series of combo boxes that are linked to each other sort of step by step. For example, this is for a place that manufactures paint so the first would be selecting the product line, second would be selecting the sheen, and so on.... Keep in mind each product line would have a different set of sheens. I have a single table setup with one column with the names of the product lines and one column for each of the product lines(as the header) with their various sheens listed below it. I've tried all the methods I've seen and no dice...The most I get is the first combo box listing the product lines but the second combo box listing absolutely nothing. Any help would be greatly appreciated.

    Thanks.
    ill give you the therie, my example is too complicated so im sure it will confuse.

    what you want to do is set the rowsource of the combo boxes dynamicaly

    to do this in the on focus event of the combo put in code similar to this

    dim comob1 as string
    combo1 = [forms]![form]![name_combo]

    last_name_combo.rowsourcetype "table/query"
    last_name_combo.rowsource = select last_name from data_table where first_name like" & """ & combo1 & """

    that is the basic way to do it, set up a variable for ever combo box that you have, set them all to current settings and then use them in a sql statment to set the rowsource of the current combo.
    btw a basic understanding of SQL is important. This example only shows interaction with one combo. if there are many combos you will need to embed SQL key words into the varialbes and then string all the variables together.

    if you need more help just post some one will be able to help
    (im leaving soon for the weekend so Ill try to get back to you asap)
    Jim

  3. #3
    Join Date
    Dec 2002
    Posts
    9
    yep you're right now i'm definitely confused. I have no knowledge of SQL but do have a basic understanding of Access. I kind of got thrown into this project and it's only a temp job at that...
    Maybe I should stick to my original idea of setting up a series of forms through command buttons?

  4. #4
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by JimboSHU
    yep you're right now i'm definitely confused. I have no knowledge of SQL but do have a basic understanding of Access. I kind of got thrown into this project and it's only a temp job at that...
    Maybe I should stick to my original idea of setting up a series of forms through command buttons?
    ill post my code maybe you can make sence of it.


    '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_Office_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
    Set Record_Set = Cur_DB.CreateQueryDef("Combo", "SELECT [REF_PCRKMS_COMBO_LU_TBL].[sh_office]" & _
    " FROM REF_PCRKMS_COMBO_LU_TBL" & _
    " where week between " & From_week & to_week & _
    " and [REF_PCRKMS_COMBO_LU_TBL].[sh_office] is not null " & Trade & Service_Contract & _
    Reef_Dry & BTN & _
    Equ & Sh_Concern & Shipper & Cn_Concern & Consignee & Cn_Office & _
    Line & RCountry & POR & Load & Discharge & Pod & DCountry & _
    " Group By [REF_PCRKMS_COMBO_LU_TBL].[sh_office]" & _
    " Order by [REF_PCRKMS_COMBO_LU_TBL].[sh_office]")
    Record_Set.Connect = "ODBC;Description=DataWarehouse;DRIVER=SQL Server;SERVER=scnc044db;UID=LineDept;PWD=reader;Ne twork=DBMSSOCN;"
    Shipper_Office_Combo.RowSource = "Select sh_office from Combo order by sh_office"
    It looks like alot but in its simpilest form it isnt. I have conditional logic that checks the combo boxes and if they are populated then i set the variables acordingly, if not i set the variable to blank string.
    the magic comes into play in the select statment for the querydef, wich I dont htink you will need I used the query def because I needed a sql pass threw query so insetead of using the query def you can put your select statment striaght into your combo_name.rowsorce statment.
    If you set you your variables correctly you should not have to do anyting else but string them together in the rowsource statment.

    So there are two magic places here in the setting of the variables and the sql statment.
    let me know if this clears it up at all
    Jim

  5. #5
    Join Date
    Dec 2002
    Posts
    9
    that's all way over my head. i'm sorry.
    what i think i'm going to wind up doing is forming a switchboard and have the end result of each switchboard navigation the user ends up in a form made for that specific paint. the only reason why i don't like doing that is that it's a lot of extra clutter when multiple types will link to the same formula tables when extracting a formula. If it all could be selected through one menu instead of clicking through a bunch it would be better but until i really learn how to do that stuff I'll keep it simplified.

  6. #6
    Join Date
    Oct 2002
    Location
    NSW Australia
    Posts
    61
    It's really fairly simple.
    build queries for the rowsources and test them by opening them all one after the other and leaving them open. This tests your logic.
    If that works then put the queries as the rowsources and make sure you put a your-next-boxname.requery in the after update of the currentbox.

    post your app if you want more help.

  7. #7
    Join Date
    Dec 2002
    Posts
    9
    If I'm understanding you correctly what I would be doing is building queries that isolate each product line with its individual finishes. I can do that and understand that, I think, but my question to you is what would be the correct formulation for the rowsource of the 2nd combobox which would reference the certain query based on the selection in the 1st.

    Thanks for all your help so far. I wish I knew more about this so that I wouldn't need such in-depth help.

  8. #8
    Join Date
    Dec 2002
    Posts
    9
    nevermind, solved it.
    Last edited by JimboSHU; 12-23-02 at 14:11.

Posting Permissions

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