Populating fields based on another table.

[daverskully]

The tables are as follows:

This table has about 100 records that will remain virutally the same forever, however there may be slight changes to some records.

1) Table Name: DepartmenInfo
Department
DepartmentNumber (Unique Identifier, one department number for each department [100 departments])
AccountExecutive (One account executive looking after many departments)
7 Text boxes that relate to each department and will not be changed

2) Table Name: Data
Department
DepartmentNumber
AccountExecutive
7 Text boxes that relate to each department and will not be changed
Only new fields: 6 memo boxes to input information

Information about these departments will be inserted bi-weekly and the 6 memo boxes are the only thing that will be entered.

What I am looking to do is have a form, based on the table Data, so that once I click on the Department or Department Number from a combo box, it will populate all fields that relate to that department based on the information from the DepartmentInfo table, thus leaving the user to input only the 6 memo boxes that are available in the Data table. Once inputted, I want all of the information to go on a record in the Data table.
I have been specifically asked to do it this way, I realize there are normalization and redundancy issues, but if possible can somebody please throughly explain a method about how this can be done? If you require any further information just ask, I will be very quick to repsond. Thanks in advance

 

[sclark]

Same problem

I have a bit of the same problem. I have a combobox that once the value is selected, it should populate another text box then store that second value (as well as the first) in a table. The first value stores O.K., but the second value does not store since it is a text box that has the source as a query based on the first combobox. To overcome this, I made the second text box a combobox also with the source as the correct field in the table where I want the value stored, but the row source is a query to the first combobox to get a value based on the selection in the first combobox. The user then has to select the value from the drop-down of the second combobox (even though there will be only one). This selecting action "stamps" the second combobox value to the data table.

I am sure there is a slicker way to do this, but it works.

 

[CEH]

Your first hurdle would be explaining to the person to do it "specifically" this way, that they are wrong. Second would be getting someone here to tell you how to do it knowing it is incorrect.
Having said that, it would not take much to make it correct.
Your first table would be fine.."tblDepartment".. DepartmentNumber (PK), DepartmentName,AccountExecutiveID (FK),With your textboxes you say will not change (that would be in question). One change you see would be AccountExecutiveID, you would have one other table tblAccountExecutive, two fields, AccountExecutiveID, and AccountExecutiveName.
As for the second table you would have no need for the duplicate fields. In this table say "tblData" you would have "DataID",(I would make an AutoNumber field) "DataInputDate",DepartmentNumber (FK),and your six memo boxes. Your relationship would be one to many from DepartmentNumber(PK) in tblDepartment to DepartmentNumber (FK) in your tblData.
Your input form would be designed from a query containing these three tables.
If they don't like it setup correctly.... I would suggest using Excel.