MS Access – Store a Different Value than Displayed in a Combo Box

It is occasionally usefully to display a different values in an Access combo box than what is actually stored in the table.  This can be done if you want to store the primary key in another table to make a reference easier, but display some other data.  It can also simply help save storage space when that is a concern.  One way to do this is to bind the table to one column, but tell it to display two columns with the bound column having a space of 0.

Naturally, this is easier to see with an example, so let us make a totally artificial and painfully simple database to store book ratings.  We will have a table that lists the rating options with both a number and a text description of the rating.

ratingsoptions1
Then we’ll make a table with three columns: ID, BookName, and BookRating.  We will use this to store our ratings, and we will make BookRating be a number.  We will store the value of the rating rather than the text description.  In a large database, this could save some space.  Then we can make a form based on BookRatings, with the actual BookRating being a combo box.  Since this is just an example, we will keep the form almost painfully simple.
bookratingsdesignview1
We will make certain that the BookRating field is bound to the first column, the primary key, of the RatingsOptions.  But then, we will set the display to display two columns.  The first will be effectively invisible with a width of zero and the second will be displayed with a width of 1”.

propertysheets1

And this will give us the text descriptions listed as the options, but it will only store the numeric primary key.
formandtable1

Advertisements