MS-Access 2007 ComboBox Selected Value versus Displayed Value

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
Ted PalmerInformation Technology ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

thenelsonCommented:
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
thenelsonCommented:
The combobox wizard which pops up when you inset a Combo box into the form will do all this for you.
0
ioanePlanning & Analytics ManagerCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Ted PalmerInformation Technology ConsultantAuthor Commented:
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
thenelsonCommented:
You run load multiple images per message.  Didn't get any.
0
Ted PalmerInformation Technology ConsultantAuthor Commented:
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
Ted PalmerInformation Technology ConsultantAuthor Commented:
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
Ted PalmerInformation Technology ConsultantAuthor Commented:
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
Ted PalmerInformation Technology ConsultantAuthor Commented:
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
Jeffrey CoachmanMIS LiasonCommented:
TedPalmer,

For uploading files:

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

JeffCoachman
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ted PalmerInformation Technology ConsultantAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Ted PalmerInformation Technology ConsultantAuthor Commented:
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
Ted PalmerInformation Technology ConsultantAuthor Commented:
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
Ted PalmerInformation Technology ConsultantAuthor Commented:
Format properties of my ComboBox.
PropertySheetFormat.bmp
0
Ted PalmerInformation Technology ConsultantAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Ted PalmerInformation Technology ConsultantAuthor Commented:
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
Ted PalmerInformation Technology ConsultantAuthor Commented:
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
thenelsonCommented:
You're welcome.  Glad to help and thank you very much for the points with "A" grade!

Happy Computing!

Nelson
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.