SQL JOIN FIELD NAME 1

[compucop]

Hi All. I have utilized ADODB for a recordset and used a inner join to combine two tables.

rs.open "select table1*,table2* from table1 where table1.tableid = table2.tableid.. you get the drift.

How do i refer to the joined field name through code. WHen i try to use RS!table1.tableid it gives me an error. Is there a way to refer to the joined field name. Thanks for any help it is greatly appreciated.

 

[Remou]

A few options:

[tt]rs![table2.ID]
rs.Fields("table2.ID")
rs.Fields(n)[/tt]

Where n is the ordinal position of the field.

 

[compucop]

Thanx Remou, I already tried all those options. When I try to refer to the joined field (Primary Key). I get the run time error 3265. Item Cannot be found in the collection corresponding to the requested name or ordinal.

I am attempting to fill a listview and wanted to use the joined field name for reference on the item click event. But I cannot populate the listview with the joined field value. I have success with all other fields that from the tables that are joined, the only exception is the joined field value. Thanks again.

 

[Remou]

Have you tried (debug) printing all the field names?

PS It is usually better to list the fields to include in a query.

 

[compucop]

That did it Remou Your the Man. it was RS.field("table2.tableid") .... It works like a charm. Should have posted sooner. Thanks again.