How to retrieve data on the form which has fields from two different tables.

[ket_shah]

How to retrieve data on the form which has fields from two differen
tables.

Table1
------
code1
code2
code3

Table2
------
code1
test2
test3

column code1 is available between both the tables.

A form has a find button , text box and a tab control with 2 pages.

page 1 has

code2
test2

page 2 has

code3
test3

Enter value (code1) in text box and click on the find button

Want to get data on both the pages of tab control


I was able to get data from one table.

mstrSQL = "SELECT * FROM Table1 Where code1 = " text box "

.RecordSource = mstrSQL


How can I do that ? Any help is appreciated.

Thanks in advance

 

[Pavel Romashkin]

Set the record source of the form to

SELECT Table1.code2, Table1.code3, Table2.test2 Table2.test3 FROM Table1
INNER JOIN Table2 ON Table1.code1 = Table2.code1 Where Table1.code1 = "Criteria"

Pavel

 

[ket_shah]

Pavel Romashkin thanks a lot for your help. It was a perfect solution.:
:

 

[ket_shah]

Table1
------
code1
code2
code3

Table2
------
code1
test2
test3

SELECT Table1.code2, Table1.code3, Table2.test2 Table2.test3 FROM
Table1 INNER JOIN Table2 ON Table1.code1 = Table2.code1 Where
Table1.code1 = "Criteria"

What if I want to join more than two tables.

Table3
------
code1
test22
test22

It is possible ? Please advise.

 

[ket_shah]

Table1
------
code1
code2
code3

Table2
------
code1
test2
test3

SELECT Table1.code2, Table1.code3, Table2.test2 Table2.test3 FRO
Table1 INNER JOIN Table2 ON Table1.code1 = Table2.code1 Wher
Table1.code1 = "Criteria"

What if I want to join more than two tables.

Table3
------
code1
test22
test22

It is possible ? Please advise

 

[Pavel Romashkin]

SELECT Table1.code2, Table1.code3, Table2.test2 Table2.test3,
Table3.test22 FROM
(Table1 INNER JOIN Table2 ON Table1.code1 = Table2.code1) INNER JOIN
Table3 ON Table1.code1 = Table3.code1 Where Table1.code1 = "Criteria"

If it fails (I always get confused with parenthesis), just build it in
the query builder. It will make all the right SQL for you.

Pavel