How to substitute a value in Access when selecting from a drop-down list?

Question

Visual Basic

There is a table Employees with the fields Full Name and Position.
The form has a drop-down list (HeaderName) and a field (HeaderPosition)
How do I auto-substitute the corresponding position when choosing a full name?
I found a lot of examples, but I can't figure out the syntax.
Here's what I sketched

Private Sub РуководительФИО_AfterUpdate()
[РуководительФИО].Value = DLookup("[РуководительДолжность]", "[Сотрудники]", _
"[ФИО]='" & [Должность] & "'")
End Sub

Answer 1

Good. Colleague, well, everything is simple. Look, searching by last name is not very reasonable, you need to search by Id.
So what we do. We make the query the data source for the combo. I will write as I used to, and you will figure out how to remake it for your realities. However, I recommend making tables with the tbl_ prefix and using English names - it will come in handy.
Select Id_Staff, StaffName from tbl_Staff - here we got the employee's Id and his last name
Now we make the combo with two columns, like this: 0; 3 - then the employee's id will be hidden and no different from the usual combo - except that the values ​​in it will be limited to the list.
Let's go further. You must have a relationship between tables, I do this with an intermediate table tbl_StaffPosition.
Do you have such a table? When asking a question, it is very good to attach a structure to it, at least in the form of a screen.
It has three Ids -
Id_StaffPosition - primary key
Id_Staff - link to the employee
Id_Position - link to the position.
DataPositionStart -- start of work in position, invalid Null
DataPositionFinish -- end of work in position, allow Null
IsWorker -- sign that he is working now. And you should have this sign with a unique index with Id_Position together, based on: one employee cannot hold two positions at once.
Next, you make a query like:
select p.PositionName from tbl_Position p -- this is the job title
join tbl_StaffPosition sp on p.Id_Position = sp.Id_Position is a link to the staff table.
where sp.Id_Staff = cmbStaff.Column(0) -- value from combobox.
And already substitute the result in your textbox.
If you do not make a separate table with time in office - step on a rake. The rake is this:
now he is the head of the transport department, and tomorrow he was transferred to the senior janitors. If your position is stupidly updated in the table, then all previously signed documents will be from the senior janitor.