Problem with setting text box value based on combobox choice

[siema24]

Hi,
a have a small problem with text field value. Let me give you a simple example:
I've created a table tblNameSurname with fields id, name, surmame. This is where I put actors details.


Second table is tblFilmCrew with fields id, surname. Field surname is a numer field linked with id from tblNameSurname. This table I use for choosing actors that have a role in a new movie. Whatever

For entering data to tblFilmCrew I use a form that is shown below:



It consists of a combobox 'surname' and a textbox 'name'. Although textbox is used just for additional information i want it to displaly an actor's name based on a surname choice. For this purpose I've created a combobox after_update event:



but unfortunately it doesn't work good - when a new actor is chosen all existing actors names change to a new-one name.
In this example, if I choose "stone" as a new actor, then all affleck, freeman and clooney names will be replaced with "emma".

Do you guys know how to fix it? Maybe there is a better and a more simple way to set textbox value based on combobox choice? Thank you!

[CJ_London]

it is because your name txtbox is unbound - so will be the same value for each record

remove the vba code and put the assignment into the controlsource of the textbox like this

=surname.column(2)

[siema24]

Hi,
thank you, seems that your approach is fine. I've removed vbc code and put assignment into the textbox controlsource. However I'm able only to get into column(0) (which is useless ID field - 1,2,3,4...) by putting just "surname" into controlsource.
Could you please confirm whether syntax =surname.column(n) is ok?

[CJ_London]

I'm assuming that 'surname' is the name of your combo

However I'm able only to get into column(0)

- that is because column 0 is the default value. You need the same. Access should add appropriate brackets but I would expect it to look like

=[surname].[column](2)

[siema24]

unfortunately =[surname].[column](2) still doesn't work. Access adds brackets and changes it to: surname.[Column(2)]

I'll try with a few other combinations, I'm sure that it's almost done...