What I would do:
- make the dept manager name field a display only field
- add some VBA-code to the onchange event of the dept field.
What that code should do:
if the dept field is null: make the dept manager name field null also
if the dept field isn't null: do a dlookup to find the dept managers name and put it in the other field.
With a combobox/pull down list to choose the dept, there's another way, I think: add the manager's name to the query underlying the combobox and use that value in your VBA-code. Anyway, that's what I think, but I would have to experiment a little bit to sort out the details or find code in one of my applications that does this. So I'll leave that part to you. It's more elegant and faster, provided there is a pull-down list.
There might be example code in the Northwind database.
I am trying to help a temp doing data entry into an Access 2000 database, running under Windows XP. Is there a way to have the values in one field set if a value is chosen in a pull down list? Say the first field is department, can the value "dept. manager name" be populated in another field? Thanks.