Access Combo Box

Attached is sample accdb.

Form 1 contains a Combo Box whose BoundColumn is set to 1.
The user selects an item from the Combo Box and the BoundColumn value is stored in the (Code) field on Form 1.

Question: If I set the Combo Box's BoundColumn to 2, is there a way to still store the value in the Combo Box's column 1 to the (Code) field on Form 1?
WPB-ComboBox.accdb
clock1Asked:
Who is Participating?
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.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Yes, but you'd need to push the value to the field/control.   In the combo's AfterUpdate event:

 Me.<field or control> = Me.<cbo name control>.Column(x)

 where x is the column number (0 based).

Jim.
0
PatHartmanCommented:
Why would you want to do that?  The bound column is just that - the column that is bound to the record source.  I'm not sure Jim's suggestion will work.  I think you might need two columns.  The second one can be hidden.

If you are futzing with this because you want to alter the column that is displayed, you can control that with the column widths property.  Typically we hide the "key" column and show the "text" column by setting the bound column width to 0".  If you want to show more than one columns, that is a different problem and can be handled in several ways depending on your desired outcome.  The simplest is to concatenate the columns you want to display.  so if your combo selects employees and is bound to EmpID but you want to show last name AND first name, then use something like the following as the query for the RowSource.

SELECT EmpID, LastName & ", " & FirstName AS EmpFullName
From tblEmp
Order By LastName & ", " & FirstName;
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Why would you want to do that?>>

 I've used this technique when I want to save myself from opening a recordset and fetching values.

 There are times when you might want to save some other values rather than doing a look-up else where (i.e. historical point in time capture).

However you need to be careful with combo's and overloading them with a lot of columns.   I generally cut things off at 3 or 4 columns and use other methods to grab data if more than that.

Jim.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

clock1Author Commented:
Here is reason why using sample accdb provided:

The user wants to select a color from the combo and have the code assigned to that color stored on the code control on Form 1.  Users finds it easier to type a color into the combo box rather than remember the code assigned to it.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Your understanding the combo control incorrectly then, as Pat was suggesting.

The Bound column property determines the value stored in the underlying control, which would be the code.

Using the column width's property, you can show/hide columns as needed and control what the user sees.  The bound column can be hidden from view and any other columns show (although you only see the first visible column if not dropped down).

Read Pat's comment and look at the example.  It's no different than your color code and description.

Jim.
0
clock1Author Commented:
In this case another function of the combo is the user can use type-ahead to key the name of the color they desire and quickly locate it in the list.  The type-ahead only works against the bound column.  Correct?
0
PatHartmanCommented:
@Jim -"I've used this technique when I want to save myself from opening a recordset and fetching values".  I don't think we are talking about the same thing.  The bound column of the combo is what is going to get saved.  The rest of the columns are window dressing and you can hide/show any of them or poke them into unbound or even bound controls but taking a non-bound column from the combo and poking it back into the combo to override the defined bound column???  Why would you just not specify the correct column and bind it in the first place?

@clock1 - no - type ahead works on the first VISIBLE column and it MUST be sorted to work correctly as I implied when I gave you a query sample.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<but taking a non-bound column from the combo and poking it back into the combo to override the defined bound column???  >>

 No, poking it into another control. I missed in the original question that poking the value into code was the same field.   Thought it was another control/field and the question was about how to use the values other than the bound column else where.

Jim.
0
clock1Author Commented:
Experts,  I comprehend how bound column works, and don't need to concatenate.

The user wants to select a color from the list by typing text i.e. "blue", "red", etc....

The user then wants the code assigned to the selected color to appear on the form control.
0
clock1Author Commented:
Did you look at sample provided?  All controls are on Form 1.  Thanks!
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<The user then wants the code assigned to the selected color to appear on the form control. >

You have three choices when you want to do something like this:

1. Include the description and the code as part of the visible field.  So something like:

MyColor:[ColorDescription] & " (" & [ColorCode] & ")"

2. Use an unbound control besides the combo and poke the value into it in the after update field:

Me.<unbound control name> = Me.<cbo name control>.Column(x)

3. Use two combo's, both bound to the same field, but for each, use different column widths to display the code or description.  

 By doing this, a user can type the code using one combo, or jump to the second one and type a description.

Jim.
0

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
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.