Problem to get informations from my recordset

Here is my recordset :
sql02 = "select * from Tab1, Tab2 " _
& " where (Tab1.id = Tab2.id)" _

Set Rst02 = New adodb.Recordset
Rst02.Open sql02, db, adOpenForwardOnly, adLockReadOnly, adCmdText

So far it's Ok, but now that I want to get the Key used as connection, i can't ???!!! probably question of syntax !!!
Here is my code :

dim VarId as string
VarId = Rst02!id
I also tried ...
VarId = Rst02!Tab1.id ou bien
VarId = Rst02!Tab2.id
VarId = rst02!(tab1.id) etc...
Always wrong, so :( ? who can help me ?!

Don't you see this yourself? Ofcourse not, otherwise you shouldn't ask...lol... :D

Look at your SQL Statement:


sql02 = "select * from Tab1, Tab2 " _
& " where (Tab1.id = Tab2.id)" _


You are asking both the same id's from the table so you have to make the difference in calling them by the tableName!

Put this in your code and find out what the value is:

dim msg as string
dim i as integer
For each field in Rst02.Fields
msg = msg & Rst02.Fields(i).Value & vbCrLf
i = i + 1
Loop
msgBox msg

Do you really want all fields in both tables? If not, you can use aliases and list out only the fields you need. And, you could use a JOIN instead. However, if you want all fields from both tables, and both of these fields have exactly the same name, you are going to have to loop all the fields, and get the assigned index. (12, 24, etc). Since you are building a query off of two tables, the ID fields will be listed in the order they appear on both tables.

Ok, I understand where it comes from, but I didn't find the good syntax !
Sorry TheDutch IceMan but I tested what you wrote and the word "Field" is not recognised !!!

So here what I tried :
sql02 = "select Tab1*, Tab2.Id, Tab2.Name from Tab1, Tab2 " _
& " where (Tab1.id = Tab2.id)" _

and then :
Dim VarId as string
VarId = Rst02!id
VarId = Rst02!Tab2.id
VarId = rst02!(tab2.id) etc...
Always the same problem, I know it must be the wrong syntax but which is good ?

This works fine for me :)



Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

sSQL = "SELECT * FROM myTable, table1 WHERE myTable.ID = table1.ID"

With cn
.ConnectionString = CurrentProject.Path & "\db1.mdb"
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open
.Properties("Jet OLEDB:Max Buffer Size") = 256
End With

rs.Open sSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText

If Not rs.EOF = 0 Then
MsgBox "no records found"
Exit Sub
End If

'I get the proper value of 12 here

MsgBox rs.Fields("Table1.ID").Value


rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

Ok, thank you MKoslof, I have quiet the same but use an MDI projet and so have my connexion in my MDI form and then all my Recordset are in other forms. Well, I steal don't understand why the word "Field" is not recognised ??? !!! (I have a message sort of - no command !)
Well, I applied your recommandations with succes ! so thousand of Thanks. But that mean that we have to be very careful the day the structure of the table will change !!! = change the index
Thanks :)

Glad to help. Good luck