Append to 2 Tables with Same Form? 1

[jdegeorge]

Hi

I have 2 tables. tblPassword with 3 fields: UserID (AutoNum), User (Text), and Password (Text), and tblDept with 3 fields: DeptID (AutoNum), Dept (Text), DeptMgr (Numeric).

I have a form called frmNewManager that's based on tblPassword, and it has 3 fields: ctlUser (from tblPassword), ctlPassword (frm tblPassword), and ctlDept (from tblDept). The fields appear the order described above.

How can I add a record that will take the values of the 3 form fields and update both tables? I know that I need to store the new UserID from tblPassword to the DeptMgr field in tblDept, but if this isn't assigned by Access until after the record is inserted, how can I get the Dept name in the 2nd table?

Any help would be appreciated. Thanks!

Jim DeGeorge

 

[AccessAce]

You should create a query that pulls data from the two tables and base your form on that. The recorset fromthe two table should update the fields you need.

 

[jdegeorge]

AccessAce

Ah ha! Thanks! Enjoy the star.

Jim DeGeorge

 

[AccessAce]

Thank you for the star. It is much appreciated.