Append & Update Table

[Simon]

I have two tables: Boxes & Imported
These tables have the same fields as each other.

Is it possible to check each value within Imported.F6 and compare it with
the values within Boxes.F6
If there is no match then a new record should be append to Boxes from
Imported.
If there is a match then I would like to update the record for Boxes.F3 with
the value within Imported.F3

Thanks,

Simon

 

[Guest]

hi
recordsets.
sub comparetables()
dim db as database
dim brs as DAO.recordset
dim irs as DAO.recordset
set db = codedb
set brs = db.openrecordset("boxes",opendynaset)
set irs = db.openrecordset("inmported",opendynaset)
rsBOM.MoveFirst
rsTAG.MoveFirst
begintrans
do until brs.EOF
if brs!F6 = irs!F6 then
'update code here
brs.edit
brs!F6 = irs!F6
brs.update
else
new record code here
' i don't know how many fields you have
with brs
.addnew
!F6 = irs!F6
!otherfields = irs!otherfields
'1 line for each field
.update
end with
end if
brs.movenext
irs.movenext
loop
committrans
brs.close
irs.close
end sub

 

[Simon]

Thanks for the code.

But when I compile it, I get an error.

dim db as database

It say that database is User-type that is not defined.
Can anyone help?

Thanks.

 

[Simon]

I have read another post that addressed this issue