One table automatically updates another table based on criteria entered.

[Mark]

Rod,

Access doesn't have record triggers at the table level
like Oracle does. In order to do what you wish, you'll
have to create a form (you should be doing this anyway
because you NEVER EVER want a user to access the data
from the tables). Then add an AfterUpdate event to the
Section tasked field.

Mark

-----Original Message-----
I appologies upfront if this question has been asked

before, but I am on time sensitive deadline.

What I have is two tables. A Workorder table and an

Acquisition table. The two tables are linked by the
WorkorderID field.

In populating the Workorder table, there is one field

(SectionTasked) that designates a section that will buy
equipment under the Acquisition table. What I am trying
to figure out is a way for when the record under the
WorkOrder table is completed and the Section Tasked field
entered (Not NULL), that a new record in the Acquisition
table will be entered with the WorkOrderID already there.

I have joined these two table in a select query, and all

seems fine except that the user has to manually add a
record under the Acquisition table and select the
WorkOrderID.

 

[Rod]

Wow, that work pretty good. Its good to learn new things

I had the AfterUpdate event run a Macro to run an Append query based on the Section Tasked field being Not Null and on the WorkOrderID of the current record only (as the foreign key to the WorkOrderID in the Acquisition table is set for No Duplicates). I was wondering if this is a good way of doing this or is there a better way

Also, not to get greedy on my questions here, but there was one thing that didn't work perfectly with the AfterUpdate event. When I was entering a new Record in the WorkOrder table, and I got to entering data into the Section Tasked field, the append query would trigger, but would say that it was only going to append 0 rows. The only way to get it to append 1 row was to exit, then restart the form. It seems like the current record I was entering was being recognized until after I exited the form. Any suggestions

Thanks again

Rod