I have the following select statement which works, I need help to adapt it

adobau.RecordSource = "SELECT * FROM Liegenschaften INNER JOIN tblbau ON Liegenschaften.Rimo_Liegnr = tblbau.Liegenschaft"

my adobau.recordset loops through Liegenschaften and gives me all the info of Liegenschaften that has match in tblbau, but I want some info from tblbau also, how would I do this?

there is no problem with the query . You might need to change the join condition . Kindly post both of your tables structure for more details.

tbl 1 has fields id, name,address
tbl2 has fields id, date
tbl2.id refers to same id of tbl1
I want all records from tbl2 print the name address and date.
name and address are in tbl1 and date in tbl2
Can this be done in one select statement?

Try this query.

adobau.RecordSource = "SELECT Liegenschaften.name,Liegenschaften.address,tblbau.date FROM Liegenschaften INNER JOIN tblbau ON Liegenschaften.Rimo_Liegnr = tblbau.Liegenschaft"

My problem is not the recordsource, it gives me all the records I need.
My problem is the recordset, it points only to one table and I need info from secondtable also

I think you should give a try the yello's suggestion, and to call certain column use: adobau("columnname"), columnname could be any of the columns used in the query, not exclusively of a certain table, the recordset does not know anything about the tables used in your query, it only can see the columns used in the query, all of them:

p.e:
adobau.RecordSource = "SELECT Liegenschaften.name,Liegenschaften.address,tblbau.date FROM Liegenschaften INNER JOIN tblbau ON Liegenschaften.Rimo_Liegnr = tblbau.Liegenschaft"

and the call:

textbox1.text = adobau("name")
textbox2.text = adobau("address")
textbox3.text = adobau("date")

and so on no matter which table you used in the query. That's why you should name with different names, similar columns in both tables.

Cheers,

Omar

ok, I guess that was my problem, I have in both tables a column with same name and when I try to get that one it gives me an error. I thought it's because the recordset doesn't point to that table, now I see that it's because it doesn't know which column, which table.
I can't change the names of the columns, because I'm using an existing database that's being used for hundreds of programs already. I got it to work with two ados and two select statement, but I'd still like to do it in one. Do you have any suggestions?

If you have same column name in two tables you must refer the column by [Table Name].[Column Name]

It works thank you all for your help
this was the syntax that worked
ado1.recordset ("tblname.columnname")