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]