How to get second column value from a combo box to a bound field on a form

i have form to record data to a table, to get data for 3 fields from another table i have a combo on the form to select a record. I use the combo and column 0 data is assigned to the combo bound field.  how can i transfer data from combo columns 1,2 and 3 to fields on the form bound to the first table? (OpNameEN, SAM fields on form to get value from Operations table in attached file)
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

In the afterupdate event procedure for the combo box you do:

me.OpNameEN= me.comboboxname.column(1)   ' uses second column
me.SAM = me.comboboxname.column(2)  ' uses third column


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
imaksuAuthor Commented:
i have applied the solution  in "Product Opers" form Combo32 but now i'm getting "The expression After Update you entered as the event property setting produced the following error: A Problem occured while Microsoft Office Access was communicating with the OLE server or ActiveX Control.
How can i solve this? (File is attached).
There are a couple of errors in your code but putting those aside for now, there appears to be a serious problem with the database.  I can reproduce your problem but the cause of the problem appears to be some sort of corruption in the database.  I have discovered that if you create a similar form to yours based on the same table, the combo box operation works correctly provided there are no more than 7 fields on the form.  As soon as you add an 8th field you get the error you have seen. It doesn't matter which field you add as the 8th field.

I have imported the database objects into a new empty database and also used the \decompile option on the database but nothing resolves the problem.

I will see if I can get someone else to look at this.

Notes for other experts..
The basic requirmenet is to drop the value from the second column in the combo into a textbox.
Attached to my post is a db which demonstrates the problem.  There are notes on the forms.
One form is a demo of a working example imported from another database.
Another form is based on the table in the poster's database and at the moment works OK.  But if you add any field to the form and then select from the combo box you get an error.

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Leigh PurvisDatabase DeveloperCommented:
Hi Pete

I agree it's obscure - caused by Access itself I'd say when creating form/control objects based on that table, which has, I'd say, subtly corrupt - or has come from a weird international setting with which I'm not familiar (not difficult ;-)

It isn't the fact that it's any subsequent fields added that are causing the problem - but the specific fields which are added (and more importantly their names).  They just happen to be mostly the last few fields in the table.

The "i" characters (ASCII 105) in those field names are improperly represented.
They're actually ASCII 305 (looks like an "i" without the dot ;-)
They're legit Unicode characters - but clearly not acceptable to Access UI object naming conventions (I don't think it's getting as far as just VBA having a problem with them).

I created the table without those and then form objects named after those renamed fields (which is the problem that the form module is having) are legal.  (Though you can just adjust the control names in the original "Problem" form too if you like - all the same).
Sounds obvious now perhaps - but it isn't all that obvious to spot.  (Found due to some code as well as eyes ;-).
B&^%$£^&(  F**&^%$£$^*  H(**&^
Obvious - don't know what took you so long to sort it (:-)

"(Found due to some code as well as eyes) "
...and it's the i's which are the real clue in the end!

Well done.

Leigh PurvisDatabase DeveloperCommented:
T'was fun. :-)
(Anything to help prevent yet another undefined, unexplainable dismissal of an issue to Access corruption - the more of these that get explained then better).
imaksuAuthor Commented:
Thanks to Pete and LPurvis. Actually this has been 2 problems solved.
Thanks to Experts Exchange.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.