?
Solved

Access Combo Box

Posted on 2014-12-05
11
Medium Priority
?
452 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 58
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 38

Assisted Solution

by:PatHartman
PatHartman earned 1000 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 58
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 58
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 38

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

Accepted Solution

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses
Course of the Month15 days, 15 hours left to enroll

743 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