Solved

MS-Access 2007 ComboBox Selected Value versus Displayed Value

Posted on 2009-06-27
21
3,652 Views
Last Modified: 2013-11-28
EE Experts,

I have made a best effort to research a solution to my problem without success. All the solutions that I have found here on EE and elsewhere just don't fit my situation exactly. A solution to this problem should be easy to find because I would think that everybody needs it at some point in time. I'll quit whining now.

I'll use a classic metaphore to illustrate my problem though my details of implementation just have different attribute names, but the relationship is the same. Let's say I have a form where the user needs to select one of the 50 states of these here United States of America as part of an address record that is being captured for savings in a database. Because some people, myself included, can not remember the states codes for Arizonia, Arkansas, and Alaska plus they can't spell Massachusetts, Mississippi, California, or Texas correctly; so therefore I want to give them a drop down pick list; i.e. a ComboBox with the state names spelled out in full. But I want to save to the database the 2 character state codes recognized by the United States Postal Service.

In Visual Basic .NET, this is no problem because in VB.NET I can store objects in a ComboBox and set in the ComboBox the properties for the Display member and Value member of the stored object.
Thus the user sees the state name spelled out, but doesn't even see the state code that gets stored in the database.

I don't need SQL for selecting state code and state names from a table. I just need the particulars of how to use the capabilities of the ComboBox in Microsoft Access.

Thank you in advance from a marginally good VB.NET programmer.

Ted Palmer
0
Comment
Question by:Ted Palmer
  • 12
  • 4
  • 3
  • +2
21 Comments
 
LVL 39

Assisted Solution

by:thenelson
thenelson earned 150 total points
Comment Utility
Put the State names in Column 1, the  state codes in column 2.
Set the:
bound Column to 2
Number Of columns: 2
column widths:
    If you want to see both state names and codes:  Leave blank
    If you want to see just the state names: 1";0
0
 
LVL 39

Expert Comment

by:thenelson
Comment Utility
The combobox wizard which pops up when you inset a Combo box into the form will do all this for you.
0
 
LVL 13

Assisted Solution

by:ioane
ioane earned 150 total points
Comment Utility
Hi TedPalmer,

Try creating a table with two character state codes in first column (key) and state name in second column.

Bind your combo-box to this table (Using 'Row Source' = [your_table_name]).

Set combo box properties as follows:
Bound Column = 1
Width = 0cm;4cm

Viola!

Let me know if any problems.
0
 

Author Comment

by:Ted Palmer
Comment Utility
thenelson and Tramtrak,

Here is what I have based on what information that I could find and it seems to me it should work. I'm uploading screen shots. Maybe I haven't fully figured out how this EE file uploading works yet. It seems like one per comment. So I am getting all 4 of my screen shots up here as fast as I can.

TedPalmer
0
 
LVL 39

Expert Comment

by:thenelson
Comment Utility
You run load multiple images per message.  Didn't get any.
0
 

Author Comment

by:Ted Palmer
Comment Utility
EE is uploading my file. I get a progress bar, but I don't get a dialog popup that asks me to enter a short text description of the content of the file. I don't know why the file upload functionality is not working as I experienced it before. The file size is only 864K and the size limit is 50 MB. I don't know what the problem is here with the file upload (Attach) capability.

This sucks. I wonder if IE8 has anything to do with it. I just upgraded this past week.

TedPalmer
0
 

Author Comment

by:Ted Palmer
Comment Utility
I attached my rowsource which is a simple SQL statement. My bound column is 1. When I select the ComboBox - it has the focus. I click a row displayed in the list and the value for column 1 shows up in the text box area of the ComboBox. I am using the ComboBox now as criteria in a query that works it gives me the result that I want in a report. It is just that the user is seeing the value of column 1 when I want them to see the value of column 2. There is more to the story that I could show you in the screen shot if I could just get it to appear after the upload?????

Ted Palmer
SELECT forwardercos.id, forwardercos.ForwarderCoName1, forwardercos.ForwarderCoName2

FROM forwardercos

ORDER BY forwardercos.[ForwarderCoName1];

Open in new window

0
 

Author Comment

by:Ted Palmer
Comment Utility
I tried uploading screen shot again. I just clicked "Submit" after the upload without entering anything. I got a popup from the webpage that said "please enter a comment in the space provided", but I can't get the insertion cursor to go into the space provided.

This is really pissing me off.
0
 

Author Comment

by:Ted Palmer
Comment Utility
My column count is 3 and my column width is 0.4";1.7917";1.3327". I made the first column width 0.4 in an experiment and didn't change it back. Before I changed it to 0.4 it was zero (0).
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
TedPalmer,

For uploading files:

"If you are using IE8 you will probably have to use the IE7compatiability button to attach a file."

JeffCoachman
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
Comment Utility
In addition to the other Expert comments:

Access stores whatever value is in the Bound column you specify. If your rowsource is this:

Select StateCode, StateName FROM tStates

And your Bound column is 1, then Access will store the value from [StateCode] for the item selected by the user.

Access can show you different values, depending on various properties set in Access. In the example Rowsource above, if I set

ColumnCount = 2
ColumnWidths = 0;1

Then Access would display the [StateName] but would store [StateCode]

So, in Access, the "Bound Column" would be your .NET "Value" property, and the other columns would be your .NET "Display" property.

Note also that Access will show only the first non-zero-width column ... so if you included other columns in your combo, Access would display them in the DropDown (assuming you didn't set the width to zero), but when you selected an item from the list, Access would show the value from the FIRST non-zero-width column ONLY.
0
 

Author Comment

by:Ted Palmer
Comment Utility
Thank you JeffCoachman. On Tools upper right side of IE8 window frame I found "Compatibility View Settings". I added EE to Compatible websites. It didn't say compatible with what, but it worked I think. I'll know soon.
MyFileByClientDropdown.bmp
0
 
LVL 84
Comment Utility
Have you tried changing your ColumnWidths property so that your first column has a zero width:

0";1.7917";1.3327"

That should hide your first column ...
0
 

Author Comment

by:Ted Palmer
Comment Utility
Off Topic:

Sorry I have been gone for a short while. I had not finished uploading my screen shots because I got the attached screen. Maybe I was the trigger of this? I have been an EE member for 5 years and this is the first time I have seen this screen. It is a strange coincidence that I have a problem uploading my files as attachments. I express my furstration using words like "sucks" and "pissing me off". I get help from an expert who is not answering my question with the fact that I have just upgraded my MS browser to IE8 and then this shows up. I think EE administrators have a batch job that runs on a schedule doing string searches for words like the ones that I just quoted and others of course. That would be a good way to detect problems. Thank God for Experts Exchange. I just wish it was around when I was trying to teach myself Object Oriented Programming (OOP) in PowerBuilder.

I am going to finish uploading my screen shots. In response to LSMConsulting. . . . I am glad you are part of this conversation. You have been a big help to me before. Thank you. YES I did try what you suggested. When I click on the row in the drop down list that I want to choose, my ComboBox text box part just has the integer number in it from the bound column. I'll upload that in a minute.

Ted Palmer

Ted Palmer
EE-UnanticipatedMaintenance.bmp
0
 

Author Comment

by:Ted Palmer
Comment Utility
This is what I got when I clicked on the row with id = 12. I am going to upload 2 property sheets now.
MyFileByClientSelectedValue.bmp
0
 

Author Comment

by:Ted Palmer
Comment Utility
Format properties of my ComboBox.
PropertySheetFormat.bmp
0
 

Author Comment

by:Ted Palmer
Comment Utility
These are the data properties of my ComboBox. I am going to try changing that first width parameter back to zero (0) and see what happens.
PropertySheetData.bmp
0
 
LVL 84
Comment Utility
I've seen that EE screen about maintenance pop up a few times recently ... they're obviously doing some work, but it rarely lasts more than a few minutes.
0
 

Author Closing Comment

by:Ted Palmer
Comment Utility
LSMConsulting, I had to split the points because thenelson and Tramtrak both gave me perfectly good answers last night. I just didn't get around to trying them because of the problems I wa having uploading my screen shots.

Thank you all..!!
0
 

Author Comment

by:Ted Palmer
Comment Utility
All:

LSMConsulting, I had to split the points because thenelson and Tramtrak both gave me perfectly good answers last night. I just didn't get around to trying them because of the problems I was having uploading my screen shots.

Maybe I was the first to report to EE, in a very indirect way, the problems I was having with attaching files using Microsoft Internet Explorer version 8. For all who might read this. . . . If that was the case, it probably is because it seems to me that very few EE users take advantage of the attach file capablility. As I search through EE questions and solutions, the only screen shots I see are mine. Everybody ought to start using this functionality in EE. It makes it a lot easier to understand your question and just what property values you have selected. There are way too many to type all that information in by hand. Seeing your data entry screens makes it a lot easier and quicker to understand your question. That is important to me because I benefit from the answers to the questions you ask also.

I keep an instance of Microsoft Paint program on my task bar all the time. When ever I want to make a screen shot I just press <<alt-Print Screen>>. Pop up my Paint program instance and press <<Ctrl-C>>. If the image I want has a lot of superflus stuff around it. I click (in the tool box) the star then rectangle. This allows me to mark the area of the image that I want to select in side of a box. Press <<Ctrl-X>> to cut the part of the image I want to the clipboard. On the Paint program File menu I click on "New" like I want to create a new file. When the dialog box asks me if I want to save the current image, I say "No". When I get a new drawing surface to work with, I just press <<Ctrl-V>> and I got the part of the image I want to keep. Click on File >> Save As and save the image to a file in one of many temp folders that I keep ready for just such a situation. I save as 24 bit BMP file type. That seems to give the best image resolution that can be read by all after I attach and upload the file to EE.

Now there. Wasn't that quick and easy?

Use more screen shots. It doesn't take that much extra time and effort.

Let's all sing: OH HAPPY DAY..!!

Ted Palmer
0
 
LVL 39

Expert Comment

by:thenelson
Comment Utility
You're welcome.  Glad to help and thank you very much for the points with "A" grade!

Happy Computing!

Nelson
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
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 different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

763 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

6 Experts available now in Live!

Get 1:1 Help Now