Controlling color of some combobox items by another combobox choice

[Doctor GME]

This is my form and database design:



the lower comboboxes depend on each other in loading items from the database
all i want is when i choose (Available) in the combobox named (Status) like this:



the items in the combobox (Name) which has (Yes) in Database corresponding
to the column (Available) changes color to red.
i changed the drawitem event of combobox named (comboname) to owner drawfixed
but couldn't make the event function with database
here is my code:

Imports System.Data.OleDb
Public Class Form1
Dim cnn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Stock.accdb")
Dim da As OleDbDataAdapter
Dim cm As OleDbCommandBuilder
Dim cmd As OleDbCommand
Dim itemRoute As String()
Private Sub ComboType_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboType.SelectedIndexChanged

ComboName.Items.Clear()
ComboQuantity.Items.Clear()
ComboStore.Items.Clear()

If ComboType.SelectedItem = "Food" Then

Dim dt1 As New DataTable
dt1.Clear()
Dim sql As String = "SELECT * FROM Food"
da = New OleDbDataAdapter(sql, cnn)
cm = New OleDbCommandBuilder(da)
da.Fill(dt1)
For ii As Integer = 0 To dt1.Rows.Count - 1
ComboName.Items.Add(dt1(ii)(0))
Next

End If

If ComboType.SelectedItem = "Clothes" Then

Dim dt1 As New DataTable
dt1.Clear()
Dim sql As String = "SELECT * FROM Clothes"
da = New OleDbDataAdapter(sql, cnn)
cm = New OleDbCommandBuilder(da)
da.Fill(dt1)
For ii As Integer = 0 To dt1.Rows.Count - 1
ComboName.Items.Add(dt1(ii)(0))
Next

End If
End Sub

Private Sub ComboName_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboName.SelectedIndexChanged

ComboQuantity.Items.Clear()
ComboStore.Items.Clear()

If ComboType.SelectedItem = ("Food") Then

Dim dt2 As New DataTable
dt2.Clear()
Dim sql2 As String = "SELECT * FROM Food WHERE FoodName = '" & ComboName.SelectedItem & "'"
da = New OleDbDataAdapter(sql2, cnn)
cm = New OleDbCommandBuilder(da)
da.Fill(dt2)
Dim quants As String = dt2(0)(1)
Dim quant As String() = quants.Split("-")
For ii As Integer = 0 To quant.Count - 1
ComboQuantity.Items.Add(quant(ii))
Next
Dim stores As String = dt2(0)(2)
Dim store As String() = stores.Split("-")
itemRoute = store

End If

If ComboType.SelectedItem = ("Clothes") Then

Dim dt2 As New DataTable
dt2.Clear()
Dim sql2 As String = "SELECT * FROM Clothes WHERE ClothesName = '" & ComboName.SelectedItem & "'"
da = New OleDbDataAdapter(sql2, cnn)
cm = New OleDbCommandBuilder(da)
da.Fill(dt2)
Dim quants As String = dt2(0)(1)
Dim quant As String() = quants.Split("-")
For ii As Integer = 0 To quant.Count - 1
ComboQuantity.Items.Add(quant(ii))
Next
Dim stores As String = dt2(0)(2)
Dim store As String() = stores.Split("-")
itemRoute = store

End If

End Sub
Private Sub ComboQuantity_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboQuantity.SelectedIndexChanged

ComboStore.Items.Clear()
ComboStore.Items.Add(itemRoute(ComboQuantity.SelectedIndex))

End Sub
Private Sub ComboName_DrawItem(sender As Object, e As DrawItemEventArgs) Handles ComboName.DrawItem

If e.Index = -1 Then Return

Dim myFont As Font = New Font("times new roman", 10, FontStyle.Regular)
Dim mycolor As New Color
Dim myBrush As Brush = Brushes.Black
Dim rect As Rectangle = New Rectangle(0, e.Bounds.Top, e.Bounds.Width - 1, e.Bounds.Height - 1)

e.DrawBackground()
e.DrawFocusRectangle()

Select Case ComboName.Items(e.Index).ToString
Case 1
mycolor = Color.Pink
Case Else
mycolor = Color.White
End Select

If e.State = DrawItemState.Selected Then
myBrush = Brushes.Red
Else
myBrush = Brushes.Black
End If

e.Graphics.DrawRectangle(New Pen(mycolor), rect)
e.Graphics.FillRectangle(New SolidBrush(mycolor), rect)
rect.Inflate(1, 1)
e.Graphics.DrawString(ComboName.Items(e.Index).ToString(), myFont, myBrush, e.Bounds, StringFormat.GenericDefault)

End Sub

End Class

Continue reading...