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

Posted on 2008-11-19
Last Modified: 2013-11-28
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)
Question by:imaksu
    LVL 77

    Accepted Solution

    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


    Author Comment

    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).
    LVL 77

    Expert Comment

    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.

    LVL 44

    Assisted Solution

    by:Leigh Purvis
    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 ;-).
    LVL 77

    Expert Comment

    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.

    LVL 44

    Expert Comment

    by:Leigh Purvis
    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).

    Author Comment

    Thanks to Pete and LPurvis. Actually this has been 2 problems solved.
    Thanks to Experts Exchange.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now