Help with populating fields for a new record

[daverskully]

I have two tables created and want to link two forms created from these tables so that specific fields are populated once one field is selected, but not all fields being populated, with a new record being saved into one of the tables.

Tables:

1. StaticInfo
contains fields:
1) Department
2) DepartmentNumber
3) AccountExecutive
and 7 yes/no fields

2. Data
contains fields:
1) Department
2) DepartmentNumber
3) AccountExecutive
7 yes/no fields
and 6 Memo Entry fields

The memo entry fields are the ones in which the data will be entered. The fields in the StaticInfo Table are fields that will not be changed often or maybe not at all. I want to make it faster/easier for the user to enter info by not having to enter all of that information in StaticInfo each time they open this form to create a new record. What I would prefer would be a drop-down done by Department, so that when the department is selected, all of the fields in the form are populated except for those 6 memo forms that new to be filled in for a new record (not just retrieving an old record and saving over it). This is why I have created the StaticInfo table. The new records will be held in the data table. Also, redundency or anything like that is not an issue and I just want to complete the task at hand, it's a small database and will not be slowed by any redundency.

Currently, I have a Data form created and a StaticInfo Sub Form in this Data form. As of now, I can select department from the drop down and all fields populate in the StaticInfo Sub Form. However, I don't know how to link the two so that I can enter that memo information and have it all save on a new record in the Data table.

I realize that this is a lot of information and I may not have explained it the best, I'll update my explaination if anyone requires further information. It actually seems like quite a simple thing to do, but I'm finding it difficult. I'm not even sure if I'm thinking about it the right way or if there may be a better solution. Any help, comments, suggestions would be greatly appreciated.
Thanks

[mgstlucia]

Hi, I did you get the field to automatically populate? I have been trying to do this with a form when a customer name is choosen, to poplulate the address, city, state, zip. Thanks

[mgstlucia]

No need to reply, I got it to work