Results 1 to 6 of 6
  1. #1
    Userdd is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    6

    Dynamic Cascading Combo Box

    There is one table comprises of 16 columns. In a form, i need to keep two combo boxes.
    combo box 1 : displays the field names in the table. User will select any one filed from that.
    -achieved
    combo box 2: should display the distinct values for the field selected in combo box 1.
    ??????? how to do tht ??????
    eg: table : series no | revision no | catalog
    A | 1 | VBN
    B | 2 | BNJ


    A | 1 | GHU
    a) Field name selected : Series No
    combo box 2: shud display the list in combo box as ----- A
    B
    b) Field name selected : revision No
    cb2 shud display the list in combo box as -----1
    2

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,605
    As far as I know must use VBA code to set the RowSource. Here is example from my project.
    Code:
    With Combo2
    Select Case Combo1
    Case ""
    .Value = Null
    .RowSourceType = ""
    .RowSource = ""
    Case "State Number"
    .RowSourceType = "Table/Query"
    .RowSource = "SELECT DISTINCT StateNum FROM Submit LEFT JOIN Projects ON Submit.ProjRecID=Projects.ProjRecID WHERE Not Projects.StateNum Is Null ORDER BY Projects.StateNum;"
    Case "Sample Type"
    .RowSourceType = "Value List"
    .RowSource = "Acceptance;Independent Assurance;Information;Preconstruction;Quality"
    Case "Material Type"
    .RowSourceType = "Table/Query"
    .RowSource = "SELECT MaterialType FROM MaterialType WHERE Active=True;"
    Me.cbxTest.RowSource = "SELECT TestNum FROM MaterialTest WHERE MaterialType=[cbxFor" & Right(strSelection, 1) & "];"
    Case "Program Code"
    .RowSourceType = "Table/Query"
    .RowSource = "SELECT ProgramCode FROM ProgramCodes;"
    Case "Item Number"
    .RowSourceType = "Table/Query"
    .RowSource = "SELECT DISTINCT ItemNum FROM Submit;"
    End Select
    End With
    Combo2.Requery
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Userdd is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    6

    Thumbs up

    Thank you !
    It Worked...

    Following is the code i used, query was quite simple since my data is in single table.

    Fieldtoselect - Combo box 1
    distinValue - Combo box 2

    Private Sub Fieldtoselect_Change()
    distinValue.Value = Null
    With distinValue
    Select Case Fieldtoselect
    Case ""
    .Value = Null
    .RowSourceType = ""
    .RowSource = ""
    Case "Catalog"
    .RowSourceType = "Table/Query"
    .RowSource = "SELECT DISTINCT Catalog FROM Drawing;"
    Case "Series No"
    .RowSourceType = "Table/Query"
    .RowSource = "SELECT DISTINCT [Series No] FROM Drawing;"
    Case "Revision No"
    .RowSourceType = "Table/Query"
    .RowSource = "SELECT DISTINCT [Revision No] FROM Drawing;"
    End Select
    End With
    distinValue.Requery
    End Sub

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,605
    My sample code is for a search form, not data entry, so some of it probably doesn't apply to your case, such as the empty string case. Since your source field names are the RowSource and RowSourceType is the same for all, can simplify even further:
    Private Sub Fieldtoselect_Change()
    distinValue.RowSource = "SELECT DISTINCT [" & Fieldtoselect & "] FROM Drawing ORDER BY [" & Fieldtoselect & "];"
    distinValue.Requery
    End Sub

    The Change event works okay? I would have used AfterUpdate.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Userdd is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    6
    Actually am also using for the search form, so the empty string case,quite suits well

    The change event works well.. Can i know the difference between the two events when will they be activated..

  6. #6
    Userdd is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    6
    Based on the field selected in combo box 1 and value from the combo box 2, we need to display the values from the table.
    Can you please suggest some method.. how to query!?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Cascading Combo Box Help
    By euphoricdrop in forum Forms
    Replies: 3
    Last Post: 04-12-2011, 05:35 PM
  2. cascading combo
    By rexb in forum Forms
    Replies: 9
    Last Post: 10-26-2009, 04:10 PM
  3. Cascading Combo Box Problem
    By skiptotheend in forum Forms
    Replies: 2
    Last Post: 10-26-2009, 05:31 AM
  4. cascading combo form
    By tonysomerset in forum Forms
    Replies: 0
    Last Post: 08-27-2008, 02:10 AM
  5. Cascading Combo Box
    By nywi6100 in forum Forms
    Replies: 0
    Last Post: 10-23-2006, 01:45 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums