By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,404 Members | 1,987 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,404 IT Pros & Developers. It's quick & easy.

List View Control Columns From 2 Tables???

100+
P: 103
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. ListView1.Refresh
  3. ListView2.Refresh
  4. ListView1.View = lvwReport
  5. ListView2.View = lvwReport
  6. ListView1.ColumnHeaders.Add , , "ID"
  7. 'ListView1.ColumnHeaders.Add , , "NAME"
  8. ListView2.ColumnHeaders.Add , , "DATE"
  9. ListView2.Refresh
  10. rs.Open "select distinct id  from saldetail", conn, adOpenStatic, adLockOptimistic
  11. ListView1.Refresh
  12. While Not rs.EOF
  13.  
  14. With ListView1
  15. Set lvitem = ListView1.ListItems.Add(, , rs!id)
  16. 'rs1.Open "select * from empdetail where id = '" & ListView1.ListItemsItem & "'", conn, adOpenStatic, adLockOptimistic
  17. 'lvitem.SubItems(1) = rs1!Name
  18. End With
  19. rs.MoveNext
  20. Wend
  21. rs.Close
  22. Set rs = Nothing
  23. ListView1.Refresh
  24. ListView2.Refresh
  25. end sub
  26.  
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
Jan 11 '08 #1
Share this Question
Share on Google+
15 Replies


QVeen72
Expert 100+
P: 1,445
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
Jan 11 '08 #2

100+
P: 103
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
Jan 11 '08 #3

100+
P: 103
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
Jan 14 '08 #4

QVeen72
Expert 100+
P: 1,445
Hi,

try this :


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

Regards
Veena
Jan 14 '08 #5

100+
P: 103
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
Jan 14 '08 #6

QVeen72
Expert 100+
P: 1,445
Hi,

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

Regards
Veena
Jan 14 '08 #7

100+
P: 103
Hi,
Before adding sub-items, you have to add Column Headers to the listview..
its added at first
but error showing invariable optional
Nick
Jan 14 '08 #8

pureenhanoi
100+
P: 175
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. 'ListView1.Refresh    ' the list view is blank on start-up, does not need any refresh
  3. 'ListView2.Refresh  ' donot refresh
  4. ListView1.View = lvwReport
  5. ListView2.View = lvwReport
  6. ListView1.ColumnHeaders.Add , , "ID"
  7. ListView1.ColumnHeaders.Add , , "NAME"
  8. ListView2.ColumnHeaders.Add , , "DATE"
  9. 'ListView2.Refresh 'again
  10. rs.Open "select distinct id  from saldetail", conn, adOpenStatic, adLockOptimistic
  11. 'ListView1.Refresh   'and again
  12. While Not rs.EOF
  13.  
  14. 'With ListView1      'donot need "With", 
  15. Set lvitem = ListView1.ListItems.Add(, , rs!id)
  16. 'this code seem good, except ListView1.ListItemsItem ??? what does it mean
  17. 'rs1.Open "select * from empdetail where id = '" & ListView1.ListItemsItem & "'", conn, adOpenStatic, adLockOptimistic
  18. 'Try this
  19. rs1.Open "select * from empdetail where id = '" & rs!id & "'", conn, adOpenStatic, adLockOptimistic
  20. lvitem.SubItems(1) = rs1!Name
  21. 'End With
  22. rs.MoveNext
  23. Wend
  24. rs.Close
  25. Set rs = Nothing
  26. 'ListView1.Refresh
  27. 'ListView2.Refresh
  28. end sub
  29.  
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
Jan 15 '08 #9

100+
P: 103
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
Expand|Select|Wrap|Line Numbers
  1.  
  2. ListView2.Refresh
  3. rs1.Open "select empdetail.name,saldetail.id from empdetail,saldetail where saldetail.id = empdetail.id", conn, adOpenStatic, adLockOptimistic
  4. While Not rs1.EOF
  5. With ListView1
  6. vitem.SubItems(1) = rs1!Name
  7. End With
  8. rs1.MoveNext
  9. Wend
  10. rs1.Close
  11. Set rs1 = Nothing
  12.  
that adds only one record,ie the last record,please help...........
Jan 17 '08 #10

pureenhanoi
100+
P: 175
but that doesnt work,now i have used the code
Expand|Select|Wrap|Line Numbers
  1.  
  2. ListView2.Refresh
  3. rs1.Open "select empdetail.name,saldetail.id from empdetail,saldetail where saldetail.id = empdetail.id", conn, adOpenStatic, adLockOptimistic
  4. While Not rs1.EOF
  5. With ListView1
  6. vitem.SubItems(1) = rs1!Name
  7. End With
  8. rs1.MoveNext
  9. Wend
  10. rs1.Close
  11. Set rs1 = Nothing
  12.  
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
Jan 17 '08 #11

QVeen72
Expert 100+
P: 1,445
Hi,

Try this :

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. ListView1.Refresh
  3. ListView2.Refresh
  4. ListView1.View = lvwReport
  5. ListView2.View = lvwReport
  6. ListView1.ColumnHeaders.Add , , "ID"
  7. ListView1.ColumnHeaders.Add , , "NAME"
  8. ListView2.ColumnHeaders.Add , , "DATE"
  9. ListView2.Refresh
  10. rs.Open "select empdetail.name,saldetail.id from empdetail,saldetail where saldetail.id = empdetail.id", conn, adOpenStatic, adLockOptimistic
  11. If Not rs.EOF Then
  12.     rs.MoveFirst
  13.     Do While Not rs.EOF
  14.          With ListView1
  15.          Set lvitem = ListView1.ListItems.Add(, , rs!id)
  16.          lvitem.SubItems(1) = rs!Name
  17.          rs.MoveNext
  18.      Loop
  19. End If
  20. rs.Close
  21. Set rs = Nothing
  22. ListView1.Refresh
  23. ListView2.Refresh
  24. end sub
  25.  
  26.  
Regards
Veena
Jan 17 '08 #12

100+
P: 103
Hi,

Try this :

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. ListView1.Refresh
  3. ListView2.Refresh
  4. ListView1.View = lvwReport
  5. ListView2.View = lvwReport
  6. ListView1.ColumnHeaders.Add , , "ID"
  7. ListView1.ColumnHeaders.Add , , "NAME"
  8. ListView2.ColumnHeaders.Add , , "DATE"
  9. ListView2.Refresh
  10. rs.Open "select empdetail.name,saldetail.id from empdetail,saldetail where saldetail.id = empdetail.id", conn, adOpenStatic, adLockOptimistic
  11. If Not rs.EOF Then
  12.     rs.MoveFirst
  13.     Do While Not rs.EOF
  14.          With ListView1
  15.          Set lvitem = ListView1.ListItems.Add(, , rs!id)
  16.          lvitem.SubItems(1) = rs!Name
  17.          rs.MoveNext
  18.      Loop
  19. End If
  20. rs.Close
  21. Set rs = Nothing
  22. ListView1.Refresh
  23. ListView2.Refresh
  24. end sub
  25.  
  26.  
Regards
Veena

do loop has a pblm???
Jan 17 '08 #13

QVeen72
Expert 100+
P: 1,445
Hi,

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

Regards
Veena
Jan 17 '08 #14

100+
P: 103
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
Jan 17 '08 #15

100+
P: 103
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.....................
Jan 17 '08 #16

Post your reply

Sign in to post your reply or Sign up for a free account.