VB6 and SQL help needed

[Pendaz]

This is kinda hard for me to explain so please bare with me here...


I have 2 list boxes and 3 text boxes which display info from a database.

Item Catagory | Item No. | Description | Qty | Type


The user should be able to make a selection from Item catagory which will then filter and only display the items that match that catagory. The user will then select from the Item No. listbox to display the required record in the rest of the fields...



I have the data reading into the list box using some example code i got from this page. which works fine:

'turn MousePointer to HourGlass to show that we are busy processing
    Me.MousePointer = vbHourglass
    
    'instantiate the connection object
    Set cn = New ADODB.Connection
    'specify the connectionstring
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                          "Data Source=" & App.Path & "\data\data.mdb"
    'open the connection
    cn.Open
    
    'instantiate the recordset object
    Set rs = New ADODB.Recordset
    'open the recordset
    With rs
        .Open "test_data", cn, adOpenKeyset, adLockPessimistic, adCmdTable
           
        'loop through the records until reaching the end or last record
        Do While Not .EOF
            CMBItemCat2.AddItem rs.Fields("Filter")
            rs.MoveNext 'moves next record
        Loop
        
        If Not (.EOF And .BOF) Then
            rs.MoveFirst    'go to the first record if there are existing records
            'FillFields      'to reflect the current record in the controls
        End If
        
    End With
    
    
  'Below is code to add data from database to CMBItemCat1
    
  Set rs = New ADODB.Recordset

                           'Load the data
'** change this SQL to load the data you want.
  strSQL = "SELECT filter from test_data"

'** change oConn to the name of your Connection object
  rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
                           'Fill the combo box (or ListBox)
'** change the name of the combo to the one you want to fill
  With CMBItemCat1
    .Clear
    Do While Not rs.EOF
'** change the name of the field here to the one you want to show
      .AddItem rs.Fields("Filter").Value
     ' .ItemData(.NewIndex) = rs.Fields("Filter").Value
      rs.MoveNext
    Loop
  End With

                           'Tidy up
  rs.Close
  Set rs = Nothing
  
  Me.MousePointer = vbNormal 'sets the mouse pointer to the normal arrow
End Sub

but i struggle to filter the data in the next few fields.


Heres what i have, but i figure its something wrong with the SQL statement:

Private Sub CMBItemCat1_LostFocus()
Dim item As String
ItemName = CMBItemCat1.Text
  Set rs = New ADODB.Recordset

                           'Load the data
'** change this SQL to load the data you want.
  strSQL = "SELECT TABLE.Part FROM Test_data WHERE Filter =[%0]"
  
   
  
  

'** change Conn to the name of your Connection object
 rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
                           'Fill the combo box (or ListBox)
'** change the name of the combo to the one you want to fill
  With CMBItemNo
    .Clear
    Do While Not rs.EOF
'** change the name of the field here to the one you want to show
      .AddItem rs.Fields("part").Value
     ' .ItemData(.NewIndex) = rs.Fields("Filter").Value
      rs.MoveNext
    Loop
  End With

                           'Tidy up
  rs.Close
  Set rs = Nothing
End Sub

Really hope someone can help me with this as my deadline for it is Wednesday :/


Thanks in advance

-Pendaz


**EDIT**

I figured it might help if i shown some example data: heres a screenshot of the data table:

http://i45.tinypic.com/av4w87.jpg



So the users must select an item from "Filter_1" which will then only show items that = the selection, THEN the user will select from the "Part" field to show only one entry.