Update one value in form based on another value in the same form

[Arnau]

I am heading an NGO taking care of sick people in Swaziland. I have an Access 2010 table with all the names of the Caregivers (Table_Caregivers) where each caregiver has a unique ID number. I have another table (Table_Clients) where the personal info of each client is stored. Each client also has a unique ID number and each Caregiver can have multiple clients.

I have created a form (Form_Client) in which I add the personal details of each client, e.g., name, type of sickness, etc. I also have a field on the form where I add the ID number of the Caregiver of the specific client. All this info is stored in Table_Clients. The Caregiver ID forms the link between the two tables.

As I am dependent on hand-written documents to input the data of the clients, I would like to add a non-editable field (Caregiver Name) on Form_Client with the name of the Caregiver which needs to update automatically (from Table_Caregivers), based on the value of the Caregiver ID on the input form. This will allow me to be certain that the ID number of the Caregiver on the handwritten document is correct.

I know that this can be done with a sub-form and a combo box, but I am sure there must be an easier way, where the name of the Caregiver can be extracted from Table_Caregivers and then displayed directly on the input form.

I am a bit stumped and would appreciate some help.

[Minty]

No subform required.
Simply make your caregiver ID control a combo (call it cmbCarerID ?), with it's row source set to the caregiver table. Add the CaregiverName as a second column, then in your after update event set

Me.Caregivername = Me.cmbCarerID.Column(1).

Make the CareGiverName control Unbound. Add the same code to your current event on the form and the name will update as you step through existing records. Don't store the name, just the Carer ID - you will be duplicating data unnecessarily.

[Arnau]

Thanks Minty! Using your advice I've been able to do exactly what I wanted.