Combo Box - Displaying multiple bound columns in text boxes

Posted on 2009-04-30
Last Modified: 2013-11-05
Hey all. I searched previous questions and found some simular to mine, but none really helped me.

I have a form running off a qry (qryTroubleTracking).  Inside qryTroubleTracking I have mulitple tables linked pulling data from different areas into one main table (Trouble Tracking). One particular table linked to my main table (Trouble Tracking) is  'Opp Center Specialist'.  In Opp Center Specialist table I have three columns - Old Acct ID, Specialist and MDMA Analyst.  A specific Old Acct ID number is linked to specific Specialist and MDMA Analyst.

With that said, on my form, when I select a number (Old Acct ID) from my combo box, I want the corresponding names to populate in two different text boxes. One for Specialist and another for MDMA Analyst.  I've tried different suggestions from other topics, but cannot get the code or tips to work. All I can get it to do it show me an error message or have both text boxes show the same name.  lol

Please help.
Question by:Larkwood
    LVL 84

    Accepted Solution

    Are you using all 3 columns in the Combo? You should be ... and you can hide them by setting the ColumnCount and ColumnWidths property. For example. if you have a  RowSource like this:

    SELECT [Old Acct ID], [Specialist], [MDMA Analyst] FROM [Opp Center Specialist]

    With these values:

    ColumnCount = 3
    ColumnWidths: 1;0;0

    Then Access will show the First column, but not the other two. You can, however, refer to those columns as needed, perhaps in the AfterUpdate event of the combo:

    Me.SomeTextbox = Me.Combo0.Column(1) '/second column
    Me.SomeOtherTextbox = Me.Combo0.Column(2) '/third column


    Author Comment

    Yes, I'm using all 3 columns.  I actually have my column widths set as {2.5;0;0}.

    I also set my row source as typed above.  I then went to AfterUpdate(Event Procedure) and typed in the following code:

    Me.Specialist = Me.Old_Acct_ID.Column(1)
    Me.MDMA_Analyst = Me.Old_Acct_ID.Column(2)

    So - now the Old Acct ID numbers show up in my combo box.  That's a move in the right direction!  But both my text boxes just show the number i select in my combo box. What should I have under my control source for both text boxes?


    Author Comment

    I'm also getting a Mircosoft Visual Basic Run-Time error

    "You can't assign a value to this object".  This refers to the code.

    Think I figured out my control sources for my text boxes. I used:

    =[Old Acct ID].[Column](1)
    =[Old Acct ID].[Column](2)


    Author Comment

    Okay - I got it to work!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    779 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

    12 Experts available now in Live!

    Get 1:1 Help Now