Create a new record in one table when a record is created in another?

Hi folks,

I am trying to set up my database to create a new record in one table when a new record is created in another. Since it is a one-to-one realtionship it certainly would be easier to put the data into one table. Originally it was one table, but I split the table to create conditional field-level security (read-only unless one box was unchecked). However I'm trying to fix one flaw in the whole scheme: When a user adds a new record in Table1, there's no matching record in Table2. When I tried creating an append query Table2 doesn't apear on the list of table to append to. (All other table were listed. They wer all created before I implemented User-Level Security. Could that have an effect?)

If I could make it work then all I would have to do is the following:

Code the Button_OnClick to run the script to

1. If the IDNo = IsNull Then Run AppendQuery to add the new record. (with Forms!Table1Data!IDNo as the criteria)
2. If CheckBox = False Then Run RWOPQuery to edit the record (with same criteria.
3. Else show msgbox "This record is locked."

If anyone has a simpler idea, please share!

Doug

re: Create a new record in one table when a record is created in another?

Never mind... I found the culprit:

Apparantly when creating the append query, Access lists all the table *except* the first one on the list. Renaming the table to move it down the alphabetical list solved that problem.

Doug