[Last Call] Learn how to a build a cloud-first strategyRegister Now


Combo Box - Displaying multiple bound columns in text boxes

Posted on 2009-04-30
Medium Priority
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
  • 3
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 24272050
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

ID: 24272300
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

ID: 24272421
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

ID: 24273696
Okay - I got it to work!

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

825 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