Solved

Access Combo Box

Posted on 2014-12-05
11
400 Views
Last Modified: 2014-12-05
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
0
Comment
Question by:clock1
  • 5
  • 4
  • 2
11 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
 

Author Comment

by:clock1
Comment Utility
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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
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!

 

Author Comment

by:clock1
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
@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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
 

Author Comment

by:clock1
Comment Utility
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
 

Author Comment

by:clock1
Comment Utility
Did you look at sample provided?  All controls are on Form 1.  Thanks!
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
Comment Utility
<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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

772 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

10 Experts available now in Live!

Get 1:1 Help Now