link between 2 combo boxes... need help

[squrel]

hello everyone..
i have a combobox and in its drop down i have 4 options (company,court, advocate,party) .... the address of these options is saving in my database called address.... whenever i select anyone of these option from the dropdown the addresses related to each one of thm has to appear in other combobox automatically and user wil select any of the addresses... i have done the code and its working the problem is showing only the first address of the options and not the rest.. for e.g. if i have 2 address related to company its showing me only the first address.. can anyone help me in this case? thankx in advance
i m putting my code here.


Private Sub cmbadrof_click( )
Dim Rs As New ADODB.Recordset
Dim cn As String
Dim address As String
Dim Advocate As String
Dim company As Integer
Dim Court As String
Dim Party As String
Dim adrof As Integer

If cmbadrof.Text = "Advocate" Then
If Rs.State = adStateOpen Then Rs.Close
Rs.Open ("select address from address where adrof = '" & cmbadrof.Text & "'"), Db, adOpenDynamic, adLockOptimisti c
cn = Rs.Fields(adrof ).Value
Combo1.List(adr of) = cn
'Combo1.Refresh
End If

If cmbadrof.Text = "Company" Then
If Rs.State = adStateOpen Then Rs.Close
Rs.Open ("select address from address where adrof = '" & cmbadrof.Text & "'"), Db, adOpenDynamic, adLockOptimisti c
cn = Rs.Fields(adrof ).Value
Combo1.List(com pany) = cn
'Combo1.Refresh
End If

If cmbadrof.Text = "Court" Then
If Rs.State = adStateOpen Then Rs.Close
Rs.Open ("select address from address where adrof = '" & cmbadrof.Text & "'"), Db, adOpenDynamic, adLockOptimisti c
cn = Rs.Fields(adrof ).Value
Combo1.List(adr of) = cn
'Combo1.Refresh
End If

If cmbadrof.Text = "Party" Then
If Rs.State = adStateOpen Then Rs.Close
Rs.Open ("select address from address where adrof = '" & cmbadrof.Text & "'"), Db, adOpenDynamic, adLockOptimisti c
cn = Rs.Fields(adrof ).Value
Combo1.List(adr of) = cn
'Combo1.Refresh
End If


End Sub

[debasisdas]

you just need to loop through the recordset.

[squrel]

so i dont knw how to do tht... wld u plz tel me how and wht line i have to add to my code?
thank u

[debasisdas]

check the if there is any more records in the recordset in a loop. if it is there add too control else exit.

try this

For i = 1 To RS.RecordCount
Combo1.AddItem RS(0)
RS.MoveNext
Next

[squrel]

Thank u very much.... it worked :)