Lookup a field in 2 tables

[rbasram]

Hi,

I have two tables that have this field I want to know how can if there is a way when the code is running the code looks up the value in the table1 if it is not in there it looks up in the table2 and also if its not there an insert box pops ups (it assumes it it a new record and then puts the value in table1)and this value is added into the table1.
To let you know that table1 and table 2 are not joined. And the field is of type number.

Any help will be appreciated.

 

[klasse]

Hi,

I would do something like
Dim MyRST, MyRST1 as recordset
set MyRST = currentdb.openrecordset("SELECT (FIELD) FROM TABLE1 WHERE (FIELD)= " & yourvalue,dbopentable)
with MyRST
if MyRST.recordcount=0 then
set MyRST1 = Currentdb.openrecordset("SELECT (FIELD) FROM TABLE2 WHERE (FIELD)= " & yourvalue,dbopentable)
with MyRST1
if MyRST1.recordcount=0 then
open de popup window
...

...


Hope it helps?

Klasse

 

[pcdaveh]

Here is a good link for working with recordsets if your not familiar.

http://www.devdos.com/vb/page/lesson4.shtml.