I've got a form that uses a query for its source data. That query is made up of three tables. Two tables are linked (related, one to many) using what we'll call "Field 1." One of those tables is linked to the third table (related, one to many) using what we'll call "Field 2."

Field 1: User selects a value on the form (from table 1) and it is automatically updated in table 2 using referential integrity. So far, so good.

Field 2: Table two uses an autonumber property to create a "record id."

Problem: I need the value in Field 2 from table 2 to automatically copy to the related field in table 3 (when it is generated). Referential integrity would do this for me, but it would require the user to populate at least one field from table 3 when the record is initiated (not guaranteed to happen).

Another approach I tried was to use a very simple VBA sub to copy the Field 2 value to table 3 [dim variable, variable = field 2(table 2), field 2(table 3) = variable]. I cannot get this to work if I place it on the autonumber field (WHY?). It doesn't error--nothing happens at all. (It works when I place it on another field, but this approach will not work because it causes other problems: namely, anytime this other field is updated it tries to change the value of field 2, table 3 but can't because it's not null).

Any thoughts? I'm using Access 2002.