0

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?

4
Contributors
8
Replies
9
Views
10 Years
Discussion Span
Last Post by plusplus
0

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.

0

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?

0

Try this query.

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

0

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

0

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

0

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?

0

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

0

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.