referencing field in reocrdset

[johnnyv]

Hello all I am playing around inner joins and have run into a problem. Here is my code

strsql = "SELECT Client.ClientName, Product.ProductID, Product.Product from Client " & _
"INNER JOIN Product ON Product.ClientID = Client.ClientID Where Client.ClientID = " & Val(txtClientID)



adorecordset.Open strsql, adoconnection, adOpenStatic, adLockReadOnly
adoconnection.Close
txtClientName = adorecordset!clientname

So My problem is with the last line. How to I reference a value in the returned recordset if a join is used. If I was not using a join, ie pulling data from 1 table I could have the following line of code

txtClientName = adorecordset!clientname

but this does not seem to work in this case??

thanks

 

[hmckillop]

The ! is old syntax you should probably use
txtClientName.Text = adorecordset("clientname")

Buy anyway if you need to access the fields you could either do it by index reference i.e.

txtProduct.Text = adorecordset(2)

of you could name your fields in the query using as
strsql = "SELECT Client.ClientName as ClientName, Product.ProductID as ProductID, Product.Product as Product from Client " & _
"INNER JOIN Product ON Product.ClientID = Client.ClientID Where Client.ClientID = " & Val(txtClientID)

And then reference using
txtProduct.Text = adorecordset("Product")

Hope this helps

"I'm living so far beyond my income that we may almost be said to be living apart

 

[dvannoy]

you can also use
txtProduct.Text = adorecordset.Fields("Product")& ""