Create Related Records

twsinc · October 30th, 2006, 05:12 PM

I am developing a human resources application in Access 2002 (VBA). After the user adds the personal information for a new employee, I want the program to automatically add that new employee's EmployeeID to 6 additional, related tables. This will allow the user to click a button on the main employee form and go to another form to input data such as "Emergency Contact." When the user clicks the button ("Emergency Contact")to input information on the new employee, I want the "Emergency Contact" form to pull that person's record, which presumably will have nothing bu EmployeeID. I've got that code to work fine. I need to know how to have Access to automatically place the EmployeeID in these other tables.

Any suggestions?

Thanks in advance

David

gbianchi · October 31st, 2006, 09:16 AM

hi there..

since you are working with access, you will have to do it by yourself. Access lack any facility to do that kind of things (like triggers or Store procedure). So you will have to do the inserts in all the related table. And don't forget to use a transaction.

HTH

Gonzalo

BrianWren · October 31st, 2006, 06:51 PM

Gonzalo is essentially right, but if you do your data entry using a form, you can use events in the form to run code that will do what you want. Forms have the following events:

Code:

BeforeInsert User types the first character in a new record. 
BeforeUpdate User updates the record. 
AfterUpdate  Record is updated. 
AfterInsert  Record updated is a new record.

One or more of those should do the trick for you.