[kiad_198]

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

[raziel_]

Have you tried using Inner join

[BobRodes]

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.

[kiad_198]

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...