dcsimg
Results 1 to 11 of 11

Thread: [RESOLVED] Combo cascade

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2015
    Location
    ANTANANARIVO
    Posts
    457

    Resolved [RESOLVED] Combo cascade

    Hello VBForums
    Hello Gentelmans
    Please how to make for the names displayed in Combo2 are only :
    USA
    Germany
    Thank you in advance for help
    Cordially
    MADA
    Last edited by MADA BLACK; Jan 19th, 2017 at 11:16 AM.

  2. #2
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,098

    Re: Combo cascade

    use a SELECT DISTINCT SQL query...if you don't know how to do that, look it up (GOOGLE will tell you plenty)

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2015
    Location
    ANTANANARIVO
    Posts
    457

    Re: Combo cascade

    Hiii SamOscarBrown
    Thank you very much .. i try to put the separate word .. but i do not want to change all these codes for that separate SQL word ..
    Code:
    RS.Open "SELECT  distinct*  from Data_Name where country_name='" & (Combo2) & "' ", DB, adOpenStatic, adLockPessimistic
    Thank you
    Cordially
    MADA

  4. #4
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    646

    Re: Combo cascade

    > "Select * From Data_Name Order By [ID] ASC"

    "select *" - Never, never use this in application code.
    Only select the columns that you need to work with.

    I would not use this query to duplicate your list of country names.
    Ideally you should have a table that lists countries. Then another table that lists the cities within each country. Then this table, that holds whatever it holds.

    The query to populate your ComboBox then becomes:
    Code:
    select country_name 
    from countries 
    order by country_name
    If you don't have a Countries "reference" table, then you should still use a separate query to get the list of names:
    Code:
    select country_name 
    from Data_Name 
    group by country_name 
    order by country_name
    Don't be afraid of using many queries to populate your application. You don't have to do everything in one go.

    Beware "select distinct". IME, it almost always indicates poorly structured data and can be surprisingly heavy-weight (especially when combined with "select *"!).

    Under the covers, this ...
    Code:
    select distinct a, b, c, d, e, f, g, h, i, j 
    from t1
    ... does effectively the same thing as this ...
    Code:
    select a, b, c, d, e, f, g, h, i, j 
    from t1 
    group by a, b, c, d, e, f, g, h, i, j 
    order by a, b, c, d, e, f, g, h, i, j 
    
    That could be a lot of sorting and filtering that you really shouldn't need.

    Regards,
    Phill W.

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2015
    Location
    ANTANANARIVO
    Posts
    457

    Re: Combo cascade

    Hiiii Phill.W
    Hiiii gentelman
    Thank you very much for your help and for your extra explanations
    Very nice of you
    I have followed all your instructions ..
    But always i have this nightmare
    Thank you in advance for help me to resolve this problem
    Cordially
    MADA
    Last edited by MADA BLACK; Jan 19th, 2017 at 11:18 AM.

  6. #6

  7. #7
    PowerPoster
    Join Date
    Jun 2001
    Location
    Trafalgar, IN
    Posts
    4,132

    Re: Combo cascade

    See if this get you there
    Code:
    Dim DB As New ADODB.Connection
    Dim RS As New ADODB.Recordset
    
    Private Sub Form_Load()
        DB.Provider = "Microsoft.JET.OLEDB.4.0;"
        DB.Open App.Path & "\MADA.mdb "
        'RS.Open "Select country_name From Data_Name group by country_name Order By country_name", DB, adOpenStatic, adLockPessimistic
        Make_Word
    End Sub
    
    Private Sub Make_Word()
        Combo2.Clear
        Combo3.Clear
        
        Combo3.AddItem "Empty"
        Combo3.Text = Combo3.List(0)
    
        If RS.State = adStateOpen Then RS.Close
    
    '    RS.Open "[Data_Name]", DB, adOpenStatic, adLockyReadOnly
        RS.Open "Select country_name From Data_Name group by country_name Order By country_name", DB, adOpenStatic, adLockPessimistic
    
        If RS.EOF And RS.BOF Then
            Combo2.AddItem "Empty"
            Combo2.Text = Combo2.List(0)
    '        Combo3.AddItem "Empty"
    '        Combo3.Text = Combo3.List(0)
        Else
            Do Until RS.EOF
                Combo2.AddItem RS![country_name]
                Combo3.AddItem RS![city_name]
                RS.MoveNext
            Loop
        End If
    End Sub
    
    Private Sub Combo2_Click()
        If RS.State = adStateOpen Then RS.Close
    
        RS.Open "select city_name from Data_Name where country_name='" & Combo2.Text & "' group by city_name Order By city_name", DB, adOpenStatic, adLockPessimistic
        Combo3.Clear
    
        Do While Not RS.EOF
            Combo3.AddItem RS![city_name]
            RS.MoveNext
        Loop
    End Sub

  8. #8

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2015
    Location
    ANTANANARIVO
    Posts
    457

    Re: Combo cascade

    Hiii Gibra
    Thank you very much for intervention
    I put this word but unfortunately always same problem
    Thank you
    MADA
    Last edited by MADA BLACK; Jan 19th, 2017 at 11:19 AM.

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2015
    Location
    ANTANANARIVO
    Posts
    457

    Re: Combo cascade

    Hiii Market
    Thank you very much for help and for your code
    In Combo2 it work very well and is displayed in this Combo2 only USA Germany .. very nice
    But i have an error mesage :
    Name:  1.jpg
Views: 117
Size:  31.0 KB
    In this line of Private Sub Make_Word()
    Combo3.AddItem RS![city_name]
    Thank you in advance for help
    Cordially
    MADA

  10. #10
    PowerPoster
    Join Date
    Jun 2001
    Location
    Trafalgar, IN
    Posts
    4,132

    Re: Combo cascade

    In English, what does that error say?

    Never mind. I see the problem.
    In the Make_Word sub, comment out the line that is causing the error.

    Combo3 will not be populated until after an Item in combo2 is selected.
    Last edited by MarkT; Jan 18th, 2017 at 05:50 PM.

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2015
    Location
    ANTANANARIVO
    Posts
    457

    Re: Combo cascade

    Hiii Markt
    Thank you very much
    I erased this line which causes the error and now it work very well
    Thank you again gentelman
    Perfectly resolved by your code
    Cordially
    MADA

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width