Solved

Access Combo Box

Posted on 2014-12-05
11
437 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 57
ID: 40483063
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 36

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 40483101
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
ID: 40483117
<<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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:clock1
ID: 40483134
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
ID: 40483142
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
 

Author Comment

by:clock1
ID: 40483155
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 36

Expert Comment

by:PatHartman
ID: 40483181
@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
ID: 40483189
<<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
ID: 40483219
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
ID: 40483223
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
ID: 40483245
<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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

730 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