Keeping track of data in a Listbox

[cajsoft]

Hi,
I've been trying all day to create a small program that allows a user to select an entry from table2 which in turn will populate another box table1.
so for example, If a user clicks on Table2 Stopreason, I would like the listbox for Table1 to show the Faultcause's (1 to many) associated with the Stopreason. This should be possible as there is a Primary and Foreign keys in place between the 2 databases (FaultId)

Table1
------
CauseId (Pk)
FaultId (FK)
Faultcause

Table 2
------
Faultid (Pk)
Stopreason
Fault

Do I need an array to achieve this result? If so, how would I code it so that the array keeps all information on the 2 tables, bearing in mind that I dont know the size of the database (SQL) I'm using ADO.

This is driving me mad!

Thanks
Craig.

[Negative0]

What you can do is store the faultid in the itemdata of the first listbox. Then you can fill the second list box with all of the records which have that same fault id.

The code would look something like this:

Sub Form_Load()
  dim rs as New Recordset 
  csql = "SELECT * FROM TABLE2"
  rs.open csql,Conn
  list1.clear
  if rs.eof = false then
    while rs.eof = false 
      list1.additem rs.fields("stopreason")   
      list1.itemdata(list1.newindex) = rs.fields("faultid")
      rs.movenext 
    wend
  end if 

End Sub

Sub List1_Click()
  dim rs as new Recordset
  if list1.listindex <> -1 then 
    csql = "SELECT * FROM TABLE1 WHERE faultid=" & list1.itemdata(list1.listindex)
    rs.open csql,Conn    
    list2.clear
    if rs.eof = false then
      while rs.eof = false 
        list2.additem rs.fields("faultcause")   
        rs.movenext 
      wend
    end if 
  end if
End Sub

Hope this helps


[Edited by Negative0 on 08-14-2000 at 02:41 PM]