Tekrar eden kayıtlar

[vivident]

Merhaba userform üzerinde birkaç tane ilişkili comboboxım var. Süzme işlemi uyguluyorum. Ama combobox'a tekrar eden kayıtlarda geliyor. Kodda nasıl bir değişiklik yapmam gerekiyor

Private Sub ComboBox2_Change()
Süz
For i = 0 To ListBox1.ListCount - 1
ComboBox3.AddItem ListBox1.Column(3, i)
Next i
End Sub

 

[Muzaffer Ali]

Aşağıdaki kodları deneyin

Private Sub ComboBox2_Change()
    Dim i
    For i = 1 To ListBox1.ListCount - 1
        ComboBox1.Text = ListBox1.Column(3, i)
        If ComboBox1.ListIndex < 0 Then
            ComboBox1.AddItem ListBox1.Column(3, i)
        End If
      
    Next i
    ComboBox1.Text = ""
End Sub

 

[vivident]

Ne yazık ki olmadı. Ben daha iyi anlaşılabilmesi için dosyamı ekliyorum.

Hatayı görmek için aşağıdaki örneği inceleyebilirsiniz
Combo2'den A2'yi seçin
Combo3'de Pal,Rab,Pal,Enj,Pal gibi veriler çıkıyor. Ben benzer olanların gelmesini istemiyorum.

 

[vivident]

ComboBox1.AddItem ListBox1.Column(3, i)

Yukarıda kodu kullandığımızda Listbox1 3. sutunundaki tüm verileri alıyor. Mükerrerleri bir türlü aldıramadım. Konu günceldir

 

[vivident]

Arkadaşlar her comboboxa şart yazdım.
Combo3 boşsa şunu yap
Combo3 Combo4 boşsa şunu yap
Combo3 Combo4 Combo5 boşsa şunu yap

Bir nevi oldu gibi, Bu şartların daha kısa yolu, veya mükerrer listelemenin başka yolu varmıdır ? Kontrol için dosyam ektedir

Private Sub ComboBox2_Change() 'Atl filitre
Süz
Set baglan = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
Call baglanti
If ComboBox3 = "" And ComboBox4 = "" And ComboBox5 = "" Then
ComboBox4.Column = baglan.Execute("select distinct Uet from [N1Listem] where Atl ='" & ComboBox2.Text & "'").getrows
ComboBox3.Column = baglan.Execute("select distinct Hat from [N1Listem] where Atl ='" & ComboBox2.Text & "'").getrows
ComboBox5.Column = baglan.Execute("select distinct Tip from [N1Listem] where Atl ='" & ComboBox2.Text & "'").getrows
ElseIf ComboBox3 > 0 And ComboBox4 = "" And ComboBox5 = "" Then
ComboBox4.Column = baglan.Execute("select distinct Uet from [N1Listem] where Atl ='" & ComboBox2.Text & "'and Hat= '" & ComboBox3.Value & "'").getrows
ComboBox5.Column = baglan.Execute("select distinct Tip from [N1Listem] where Atl ='" & ComboBox2.Text & "'and Hat= '" & ComboBox3.Value & "'").getrows
ElseIf ComboBox3 = "" And ComboBox4 > 0 And ComboBox5 = "" Then
ComboBox3.Column = baglan.Execute("select distinct Hat from [N1Listem] where Atl ='" & ComboBox2.Text & "'and Uet= '" & ComboBox4.Value & "'").getrows
ComboBox5.Column = baglan.Execute("select distinct Tip from [N1Listem] where Atl ='" & ComboBox2.Text & "'and Uet= '" & ComboBox4.Value & "'").getrows
ElseIf ComboBox3 = "" And ComboBox4 = "" And ComboBox5 > 0 Then
ComboBox3.Column = baglan.Execute("select distinct Hat from [N1Listem] where Atl ='" & ComboBox2.Text & "'and Tip= '" & ComboBox5.Value & "'").getrows
ComboBox4.Column = baglan.Execute("select distinct Uet from [N1Listem] where Atl ='" & ComboBox2.Text & "'and Tip= '" & ComboBox5.Value & "'").getrows
ElseIf ComboBox3 > 0 And ComboBox4 > 0 And ComboBox5 = "" Then
ComboBox5.Column = baglan.Execute("select distinct Tip from [N1Listem] where Atl ='" & ComboBox2.Text & "'and Hat= '" & ComboBox3.Value & "'and Uet= '" & ComboBox4.Value & "'").getrows
End If
Set rs = Nothing
End Sub
Private Sub ComboBox3_Change() 'Hat filitre
Süz
Set baglan = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
Call baglanti
If ComboBox2 = "" And ComboBox4 = "" And ComboBox5 = "" Then
ComboBox4.Column = baglan.Execute("select distinct Uet from [N1Listem] where Hat ='" & ComboBox3.Text & "'").getrows
ComboBox2.Column = baglan.Execute("select distinct Atl from [N1Listem] where Hat ='" & ComboBox3.Text & "'").getrows
ComboBox5.Column = baglan.Execute("select distinct Tip from [N1Listem] where Hat ='" & ComboBox3.Text & "'").getrows
ElseIf ComboBox2 > 0 And ComboBox4 = "" And ComboBox5 = "" Then
ComboBox4.Column = baglan.Execute("select distinct Uet from [N1Listem] where Hat ='" & ComboBox3.Text & "'and Atl= '" & ComboBox2.Value & "'").getrows
ComboBox5.Column = baglan.Execute("select distinct Tip from [N1Listem] where Hat ='" & ComboBox3.Text & "'and Atl= '" & ComboBox2.Value & "'").getrows
ElseIf ComboBox2 = "" And ComboBox4 > 0 And ComboBox5 = "" Then
ComboBox2.Column = baglan.Execute("select distinct Atl from [N1Listem] where Hat ='" & ComboBox3.Text & "'and Uet= '" & ComboBox4.Value & "'").getrows
ComboBox5.Column = baglan.Execute("select distinct Tip from [N1Listem] where Hat ='" & ComboBox3.Text & "'and Uet= '" & ComboBox4.Value & "'").getrows
ElseIf ComboBox2 = "" And ComboBox4 = "" And ComboBox5 > 0 Then
ComboBox2.Column = baglan.Execute("select distinct Atl from [N1Listem] where Hat ='" & ComboBox3.Text & "'and Tip= '" & ComboBox5.Value & "'").getrows
ComboBox4.Column = baglan.Execute("select distinct Uet from [N1Listem] where Hat ='" & ComboBox3.Text & "'and Tip= '" & ComboBox5.Value & "'").getrows
ElseIf ComboBox2 > 0 And ComboBox4 > 0 And ComboBox5 = "" Then
ComboBox5.Column = baglan.Execute("select distinct Tip from [N1Listem] where Hat ='" & ComboBox3.Text & "'and Atl= '" & ComboBox2.Value & "'and Uet= '" & ComboBox4.Value & "'").getrows
End If
Set rs = Nothing
End Sub
Private Sub ComboBox4_Change() 'Uet filitre
Süz
Set baglan = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
Call baglanti
If ComboBox2 = "" And ComboBox3 = "" And ComboBox5 = "" Then
ComboBox3.Column = baglan.Execute("select distinct Hat from [N1Listem] where Uet ='" & ComboBox4.Text & "'").getrows
ComboBox2.Column = baglan.Execute("select distinct Atl from [N1Listem] where Uet ='" & ComboBox4.Text & "'").getrows
ComboBox5.Column = baglan.Execute("select distinct Tip from [N1Listem] where Uet ='" & ComboBox4.Text & "'").getrows
ElseIf ComboBox2 > 0 And ComboBox3 = "" And ComboBox5 = "" Then
ComboBox3.Column = baglan.Execute("select distinct Hat from [N1Listem] where Uet ='" & ComboBox4.Text & "'and Atl= '" & ComboBox2.Value & "'").getrows
ComboBox5.Column = baglan.Execute("select distinct Tip from [N1Listem] where Uet ='" & ComboBox4.Text & "'and Atl= '" & ComboBox2.Value & "'").getrows
ElseIf ComboBox2 = "" And ComboBox3 > 0 And ComboBox5 = "" Then
ComboBox2.Column = baglan.Execute("select distinct Atl from [N1Listem] where Uet ='" & ComboBox4.Text & "'and Hat= '" & ComboBox3.Value & "'").getrows
ComboBox5.Column = baglan.Execute("select distinct Tip from [N1Listem] where Uet ='" & ComboBox4.Text & "'and Hat= '" & ComboBox3.Value & "'").getrows
ElseIf ComboBox2 = "" And ComboBox3 = "" And ComboBox5 > 0 Then
ComboBox2.Column = baglan.Execute("select distinct Atl from [N1Listem] where Uet ='" & ComboBox4.Text & "'and Tip= '" & ComboBox5.Value & "'").getrows
ComboBox3.Column = baglan.Execute("select distinct Hat from [N1Listem] where Uet ='" & ComboBox4.Text & "'and Tip= '" & ComboBox5.Value & "'").getrows
ElseIf ComboBox2 > 0 And ComboBox3 > 0 And ComboBox5 = "" Then
ComboBox5.Column = baglan.Execute("select distinct Tip from [N1Listem] where Uet ='" & ComboBox4.Text & "'and Atl= '" & ComboBox2.Value & "'and Hat= '" & ComboBox3.Value & "'").getrows
ElseIf ComboBox2 > 0 And ComboBox3 > 0 And ComboBox5 = "" Then
ComboBox5.Column = baglan.Execute("select distinct Tip from [N1Listem] where Uet ='" & ComboBox4.Text & "'and Atl= '" & ComboBox2.Value & "'and Hat= '" & ComboBox3.Value & "'").getrows
End If
Set rs = Nothing
End Sub
Private Sub ComboBox5_Change() 'Tip filitre
Süz
Set baglan = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
Call baglanti
If ComboBox2 = "" And ComboBox3 = "" And ComboBox4 = "" Then
ComboBox3.Column = baglan.Execute("select distinct Hat from [N1Listem] where Tip ='" & ComboBox5.Text & "'").getrows
ComboBox2.Column = baglan.Execute("select distinct Atl from [N1Listem] where Tip ='" & ComboBox5.Text & "'").getrows
ComboBox4.Column = baglan.Execute("select distinct Uet from [N1Listem] where Tip ='" & ComboBox5.Text & "'").getrows
ElseIf ComboBox2 > 0 And ComboBox3 = "" And ComboBox4 = "" Then
ComboBox3.Column = baglan.Execute("select distinct Hat from [N1Listem] where Tip ='" & ComboBox5.Text & "'and Atl= '" & ComboBox2.Value & "'").getrows
ComboBox4.Column = baglan.Execute("select distinct Uet from [N1Listem] where Tip ='" & ComboBox5.Text & "'and Atl= '" & ComboBox2.Value & "'").getrows
ElseIf ComboBox2 = "" And ComboBox3 > 0 And ComboBox4 = "" Then
ComboBox2.Column = baglan.Execute("select distinct Atl from [N1Listem] where Tip ='" & ComboBox5.Text & "'and Hat= '" & ComboBox3.Value & "'").getrows
ComboBox4.Column = baglan.Execute("select distinct Uet from [N1Listem] where Tip ='" & ComboBox5.Text & "'and Hat= '" & ComboBox3.Value & "'").getrows
ElseIf ComboBox2 = "" And ComboBox3 = "" And ComboBox4 > 0 Then
ComboBox2.Column = baglan.Execute("select distinct Atl from [N1Listem] where Tip ='" & ComboBox5.Text & "'and Uet= '" & ComboBox4.Value & "'").getrows
ComboBox3.Column = baglan.Execute("select distinct Hat from [N1Listem] where Tip ='" & ComboBox5.Text & "'and Uet= '" & ComboBox4.Value & "'").getrows
ElseIf ComboBox2 > 0 And ComboBox3 > 0 And ComboBox4 = "" Then
ComboBox4.Column = baglan.Execute("select distinct Uet from [N1Listem] where Tip ='" & ComboBox5.Text & "'and Atl= '" & ComboBox2.Value & "'and Hat= '" & ComboBox3.Value & "'").getrows
End If
Set rs = Nothing
End Sub
Private Sub Süz()
On Error Resume Next
ListBox1.Clear
Set baglan = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
Call baglanti
s = "select KIMLIK,Acilis,"
s = s + " Atl,Hat,Uet,Moder,Makina,Manuel,Referans,Operasyon,Problem,Tip,Servis,ABE,Durus,Sorumlu,Aksiyon,Kapanis,Durum from [N1Listem] WHERE not isnull(KIMLIK)"
s = s + " and [N1Listem].Atl LIKE '%" & ComboBox2.Text & "%'"
If ComboBox2.Text <> "" Then s = s + " and [N1Listem].Atl LIKE '%" & ComboBox2.Text & "%'"
If ComboBox3.Text <> "" Then s = s + " and [N1Listem].Hat LIKE '%" & ComboBox3.Text & "%'"
If ComboBox4.Text <> "" Then s = s + " and [N1Listem].Uet LIKE '%" & ComboBox4.Text & "%'"
If ComboBox5.Text <> "" Then s = s + " and [N1Listem].Tip LIKE '%" & ComboBox5.Text & "%'"
rs.Open s, baglan, 1, 1
With ListBox1
    .RowSource = Empty
    .ColumnCount = 19
    .ColumnWidths = "15;47;17;20;27;30;30;30;50;30;150;55;20;30;15;50;150;47;20"
    .Column = rs.getrows
End With
    rs.Close
Set rs = Nothing: Set baglan = Nothing
End Sub