List View Control Columns From 2 Tables???

[Vbbeginner07]

Private Sub Form_Load()
ListView1.Refresh
ListView2.Refresh
ListView1.View = lvwReport
ListView2.View = lvwReport
ListView1.ColumnHeaders.Add , , "ID"
'ListView1.ColumnHeaders.Add , , "NAME"
ListView2.ColumnHeaders.Add , , "DATE"
ListView2.Refresh
rs.Open "select distinct id  from saldetail", conn, adOpenStatic, adLockOptimistic
ListView1.Refresh
While Not rs.EOF
 
With ListView1
Set lvitem = ListView1.ListItems.Add(, , rs!id)
'rs1.Open "select * from empdetail where id = '" & ListView1.ListItemsItem & "'", conn, adOpenStatic, adLockOptimistic
'lvitem.SubItems(1) = rs1!Name
End With
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
ListView1.Refresh
ListView2.Refresh
end sub
 

the above code is to retrieve id from a table saldetail,where i have to retrieve the corresponding names of the ids from another table,is it possible in alist view control???
where i have gone wrong???
please help

[QVeen72]

Hi,

Open Recordset like this :

rs.Open "select distinct S.id , e.* from saldetail S, EmpMas E Where S.ID = E.ID ", conn, adOpenStatic, adLockOptimistic

REgards
Veena

[Vbbeginner07]

Hi,
Open Recordset like this :
rs.Open "select distinct S.id , e.* from saldetail S, EmpMas E Where S.ID = E.ID ", conn, adOpenStatic, adLockOptimistic
REgards
Veena

But Veena,
I cant understand that,can u please explain it once more,
even im getting errors in EmpMass
Please explain it

[Vbbeginner07]

Hi,
Open Recordset like this :
rs.Open "select distinct S.id , e.* from saldetail S, EmpMas E Where S.ID = E.ID ", conn, adOpenStatic, adLockOptimistic
REgards
Veena

rs1.Open "select empdetail.id,saldetail.name where empdetail.id = saldetail.id", conn, adOpenStatic, adLockOptimistic

'i have tried with this code too tat too error occurs

[QVeen72]

Hi,

try this :


rs1.Open "select empdetail.id,saldetail.name from empdetail , saldetail where empdetail.id = saldetail.id", conn, adOpenStatic, adLockOptimistic

Regards
Veena

[Vbbeginner07]

Hi,
try this :
dim ivitem as Listitem
rs1.Open "select empdetail.id,saldetail.name from empdetail , saldetail where empdetail.id = saldetail.id", conn, adOpenStatic, adLockOptimistic
Regards
Veena

rs.Open "select distinct id from saldetail", conn, adOpenStatic, adLockOptimistic
rs1.Open "select empdetail.name,saldetail.id from empdetail,saldetail where saldetail.id = empdetail.id", conn, adOpenStatic, adLockOptimistic
ListView1.Refresh
While Not rs.EOF
While Not rs1.EOF
With ListView1
Set lvitem = ListView1.ListItems.Add(, , rs!id)
lvitem.SubItems(1) = rs1!Name
End With
rs.MoveNext
rs1.MoveNext
Wend
Wend
rs1.Close
Set rs1 = Nothing
rs.Close
Set rs = Nothing
ListView1.Refresh
ListView2.Refresh


this codeis to add two coulmns to a listview where both are from different tables.
But the line
lvitem.subitems(1)=rs1!Nameis not working

[QVeen72]

Hi,

Before adding sub-items, you have to add Column Headers to the listview..

Regards
Veena

[Vbbeginner07]

Hi,
Before adding sub-items, you have to add Column Headers to the listview..

its added at first
but error showing invariable optional
Nick

[pureenhanoi]

Private Sub Form_Load()
'ListView1.Refresh    ' the list view is blank on start-up, does not need any refresh
'ListView2.Refresh  ' donot refresh
ListView1.View = lvwReport
ListView2.View = lvwReport
ListView1.ColumnHeaders.Add , , "ID"
ListView1.ColumnHeaders.Add , , "NAME"
ListView2.ColumnHeaders.Add , , "DATE"
'ListView2.Refresh 'again
rs.Open "select distinct id  from saldetail", conn, adOpenStatic, adLockOptimistic
'ListView1.Refresh   'and again
While Not rs.EOF
 
'With ListView1      'donot need "With", 
Set lvitem = ListView1.ListItems.Add(, , rs!id)
'this code seem good, except ListView1.ListItemsItem ??? what does it mean
'rs1.Open "select * from empdetail where id = '" & ListView1.ListItemsItem & "'", conn, adOpenStatic, adLockOptimistic
'Try this
rs1.Open "select * from empdetail where id = '" & rs!id & "'", conn, adOpenStatic, adLockOptimistic
lvitem.SubItems(1) = rs1!Name
'End With
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
'ListView1.Refresh
'ListView2.Refresh
end sub
 

the above code is to retrieve id from a table saldetail,where i have to retrieve the corresponding names of the ids from another table,is it possible in alist view control???
where i have gone wrong???
please help

Your actual code can do things you need, except it has lot of extra statement

[Vbbeginner07]

Your actual code can do things you need, except it has lot of extra statement

but that doesnt work,now i have used the code

 
ListView2.Refresh
rs1.Open "select empdetail.name,saldetail.id from empdetail,saldetail where saldetail.id = empdetail.id", conn, adOpenStatic, adLockOptimistic
While Not rs1.EOF
With ListView1
vitem.SubItems(1) = rs1!Name
End With
rs1.MoveNext
Wend
rs1.Close
Set rs1 = Nothing
 

that adds only one record,ie the last record,please help...........

[pureenhanoi]

but that doesnt work,now i have used the code

 
ListView2.Refresh
rs1.Open "select empdetail.name,saldetail.id from empdetail,saldetail where saldetail.id = empdetail.id", conn, adOpenStatic, adLockOptimistic
While Not rs1.EOF
With ListView1
vitem.SubItems(1) = rs1!Name
End With
rs1.MoveNext
Wend
rs1.Close
Set rs1 = Nothing
 

that adds only one record,ie the last record,please help...........

No no, not that SQL state ment. See my reply above. you can copy that code and paste into your program. After that, you can delete all comment line. Try that code.
One more else, the saldetail table, as i think it has more than one field (ID), so, why do you SELECT only ID on salDetail

[QVeen72]

Hi,

Try this :

Private Sub Form_Load()
ListView1.Refresh
ListView2.Refresh
ListView1.View = lvwReport
ListView2.View = lvwReport
ListView1.ColumnHeaders.Add , , "ID"
ListView1.ColumnHeaders.Add , , "NAME"
ListView2.ColumnHeaders.Add , , "DATE"
ListView2.Refresh
rs.Open "select empdetail.name,saldetail.id from empdetail,saldetail where saldetail.id = empdetail.id", conn, adOpenStatic, adLockOptimistic
If Not rs.EOF Then
    rs.MoveFirst
    Do While Not rs.EOF
         With ListView1
         Set lvitem = ListView1.ListItems.Add(, , rs!id)
         lvitem.SubItems(1) = rs!Name
         rs.MoveNext
     Loop
End If
rs.Close
Set rs = Nothing
ListView1.Refresh
ListView2.Refresh
end sub
 
 

Regards
Veena

[Vbbeginner07]

Hi,

Try this :

Private Sub Form_Load()
ListView1.Refresh
ListView2.Refresh
ListView1.View = lvwReport
ListView2.View = lvwReport
ListView1.ColumnHeaders.Add , , "ID"
ListView1.ColumnHeaders.Add , , "NAME"
ListView2.ColumnHeaders.Add , , "DATE"
ListView2.Refresh
rs.Open "select empdetail.name,saldetail.id from empdetail,saldetail where saldetail.id = empdetail.id", conn, adOpenStatic, adLockOptimistic
If Not rs.EOF Then
    rs.MoveFirst
    Do While Not rs.EOF
         With ListView1
         Set lvitem = ListView1.ListItems.Add(, , rs!id)
         lvitem.SubItems(1) = rs!Name
         rs.MoveNext
     Loop
End If
rs.Close
Set rs = Nothing
ListView1.Refresh
ListView2.Refresh
end sub
 
 

Regards
Veena

do loop has a pblm???

[QVeen72]

Hi,

Tried the Code...?
Copy the entire code and run..
What error you are getting...?

Regards
Veena

[Vbbeginner07]

Hi,
Tried the Code...?
Copy the entire code and run..
What error you are getting...?
Regards
Veena

yes,
Loop without do
i tried with while that too wend wthout while........

Nick

[Vbbeginner07]

Hi,
Tried the Code...?
Copy the entire code and run..
What error you are getting...?
Regards
Veena

Yes ,
a slight pblm occured due to end withnow its working
THANKS VEENA.....................