Solved

MS-Access 2007 ComboBox Selected Value versus Displayed Value

Posted on 2009-06-27
21
3,668 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
[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
  • 12
  • 4
  • 3
  • +2
21 Comments
 
LVL 39

Assisted Solution

by:thenelson
thenelson earned 150 total points
ID: 24729986
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
ID: 24729990
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
ID: 24729995
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Ted Palmer
ID: 24730007
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
ID: 24730017
You run load multiple images per message.  Didn't get any.
0
 

Author Comment

by:Ted Palmer
ID: 24730022
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
ID: 24730037
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
ID: 24730056
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
ID: 24730060
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
ID: 24730170
TedPalmer,

For uploading files:

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

JeffCoachman
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
ID: 24730706
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
ID: 24730893
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
ID: 24731094
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
ID: 24731386
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
ID: 24731395
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
ID: 24731403
Format properties of my ComboBox.
PropertySheetFormat.bmp
0
 

Author Comment

by:Ted Palmer
ID: 24731411
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
ID: 24731510
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
ID: 31597628
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
ID: 24731721
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
ID: 24731742
You're welcome.  Glad to help and thank you very much for the points with "A" grade!

Happy Computing!

Nelson
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

742 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