insert into from multiply tables problam

insert into from multiply tables problem

Hello folks
im trying to insert to table foo (filed_1 , filed_2 )
from 2 different table something like that :

INSERT INTO foo (Myname ,Sname) values ((SELECT id FROM foo1 WHERE TheName='dodo' ) , (SELECT id FROM foo2 WHERE TheName='dsdsdsdsd' )(

but i keep getting error msg "reserved error (-3025)"

if i run each sub queries it works
but i try to combine them to one it don't

You could do each select then the insert:



Set Recordset = Conn.Execute(SELECT id FROM foo1 WHERE TheName='dodo')
sfoo1 = Recordset(0)


Set Recordset = Conn.Execute(SELECT id FROM foo2 WHERE TheName='dsdsdsdsd')
sfoo2 = Recordset(0)

Conn.Execute(INSERT INTO foo (Myname ,Sname) values sfoo1, sfoo2)

Hello tnx for the fast reply
yeah i know that ..
but im looking to know if i can do it in one sql Statement
or if access support it even..
tnx