Please help me.

I have two  table:

Table1 has 3 record:  A (with primary key 1), B (primary key 2, C (3).

Table2: has no record in it.

I want to insert A (1) to Table2. I want Access to automatically search Table1 for record A and delete it.

After the insertion:
  Table1:  B (2), C (3)    ==> A (1) is gone
  Table2:  A (1)

Thank you very much.

RE-phrase please....

how would you like to do?? do want search table1 1 for the data to insert and then delete??

    

andrew

I am not sure if I understand coorectly. If you have 3 records in one table, and want to copy 1st record (which has value 1 in primary key field) to table 2, and after that to delete it, then you need to do the following

' add

INSERT INTO Table2 SELECT * FROM Table1 WHERE [prim_key_field]=1

' delete

DELETE * FROM Table1 WHERE [prim_key_field]=1

If I didnt undertand you, then please try to be more specific.

____________________________
If you find the answer helpful, please mark this topic as solved.

Sorry for the ambiguous question.

I want to create 2 Table:  tableOne and tableTwo. I want every time I insert a record in tableTwo, Access has code that automatically searches tableOne. If it finds the matching record, it will delete that record in tableOne.

Example:
   Originally tableOne has 3 records: A (with primanry key 123 in field number 1),  B (456), and C (789)
   tableTwo has no record in it.

After inserting A (with foreign key 123) into tableTwo:
   tableOne now has only 2 records since record A has been deleted:   B (456) and C (789)
  tableTwo:  A (123)

Please help me.

I believe you have already been helped. After inserting the data, since you have a primary key value that defines the row which you want to delete, just use the delete function I posted

dim deletedrecords as long conn.execute "DELETE * FROM Table1 WHERE [prim_key_field]=" & ValueOfOrimaryKeyToDelete,deletedrecords, adexecutenorecords if deleterecords=0 then      msgox "error" else     msgbox "record deleted" end if

whre ValueOfPrimaryKey in your example should be 123. I assumed that primary key field is of numeric type. If not, tell us what data type it is.

____________________________
If you find the answer helpful, please mark this topic as solved.

Thank you.