Automatically populate a field based upon another

[Guest]

Hi hope some one can help me on this. I have a form called Staff Entry for
entering staff into a table called Staff. On the form are among others two
combo boxes one called Department and one called Manager. These combo boxes
are based upon the Staff Table but on the Staff Table their data sources are
actually a table Called Departments. The Departments Table lists the
Department ID (Autonumber Primary Key) the department Name and the
departments manager.

What I would like to do on the Staff Entry Form is that when I select a
department that the Manager combo box is automatically populated with the
Departments manager.

I was using a after update event (If Department.Value="1" then
Manager.Value="Graham" which is fine but does not allow for changes in the
future so what I need but can't figure out how to code is (If
Department.Value="1" then set manager.value="department manager"

Corry if this is not making much sense I'll clarify any queries on reply.

Thanks in advance.

Graham

 

[Stewart Tanner]

If it was me I would add the manager as a second column in the Department
combo. Set the column width of the Manager column to 0.
Replace your Manager combo with a copy of the Department combo (bound to the
same field as Department), set the department column to 0 width and change
the manager column to a width so it displays. This way when you update the
department the manager automatically changes as well.

 

[Graham]

That is a good idea but I need the manager to be saved as
a value to the staff table for generation of reports, will
this work i.e. will the name of the manager be placed into
the manager column of the Staff Table?

 

[Stewart Tanner]

Graham,

no using that method the manager will not be saved. If you wish to update
the manager field create the Manager column as referenced and in the
afterupdate event of the Department Combo do something like
me.manager = me.cboDepartment.column(2)

 

[Guest]

Thanks for that stewart I've been off line all weekend so
sorry for the late reply I'll have a look at this further.