3 Replies - 4480 Views - Last Post: 17 April 2011 - 08:26 AM Rate Topic: -----

#1 kiad_198  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 130
  • Joined: 19-November 08

How to show two(2) tables in one listview?

Posted 11 April 2011 - 09:57 AM

Hi guys!

I've got modify my question.

How to join 2 tables in one list view? They have common value of "userID" as there relation.

Here's the two tables:

Table 1 (tbluserinfo) | Table 2 (tbllogin)
UserID UserID
Name Datelogin
address Timelogin


Here's the code for data to load in listview that will show:

Sub load()
lvlogin.ListItems.Clear
connection
rs.Open "tbllogin", con, 3, 3
    rs1.Open "select * from tbluserinfo where UserID = '" & rs("UserID") & "'", con, 3, 3
    Do Until rs.EOF
        With lvlogin.ListItems.Add(, , rs("ISBN"))
        .SubItems(1) = rs1(UCase("Name"))
        .SubItems(2) = rs(UCase("Datelogin"))
        .SubItems(3) = rs(UCase("Timelogin"))
      
        End With
    rs.MoveNext
    Loop
    rs1.Close: Set rs1 = Nothing
rs.Close: Set rs = Nothing
con.Close: Set con = Nothing

End Sub





Does anyone knows? Please I need your help..


thanks


kiad

This post has been edited by kiad_198: 11 April 2011 - 01:19 PM


Is This A Good Question/Topic? 0
  • +

Replies To: How to show two(2) tables in one listview?

#2 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 465
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: How to show two(2) tables in one listview?

Posted 12 April 2011 - 08:04 AM

Have you tried using Inner join
Was This Post Helpful? 1
  • +
  • -

#3 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: How to show two(2) tables in one listview?

Posted 12 April 2011 - 08:14 PM

You can do this easily using a join as nobrain mentions. Don't use two recordsets, don't just open a table. Just open one recordset with the proper select statement, which you can probably figure out by reading the link he gave you. Then you'll have all the fields you need in a single record.
Was This Post Helpful? 0
  • +
  • -

#4 kiad_198  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 130
  • Joined: 19-November 08

Re: How to show two(2) tables in one listview?

Posted 17 April 2011 - 08:26 AM

Hi Guys!

Sorry for the late reply...

Thank you NoBrain and BobRodes for the reply and it helps alot...

Its working now and here's the code:
Sub load()
	lvlogin.ListItems.Clear
	connection
	rs.Open "SELECT tbluserinfo.Name, tbllogin.Datelogin, tbllogin.timelogin FROM tbluserinfo INNER JOIN tbllogin ON tbluserinfo.UserID = tbllogin.UserID ", con, 3, 3


	    Do Until rs.EOF
	        With lvlogin.ListItems.Add(, , rs("UserID"))
	        .SubItems(1) = rs(UCase("Name"))
	        .SubItems(2) = rs(UCase("Datelogin"))
	        .SubItems(3) = rs(UCase("Timelogin"))
	       
	        End With
	    rs.MoveNext
	    Loop

	rs.Close: Set rs = Nothing
	con.Close: Set con = Nothing	 
	End Sub



Thanks again guys...
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1