Solved

Access Combo Box

Posted on 2014-12-05
11
424 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
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 34

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
 

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
Backup Your Microsoft Windows Server®

Backup 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: 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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

932 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