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.

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.
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”.


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


Making Changes to Many Forms In Access

Although generally I prefer to create custom interfaces for my databases using fully developed programming languages like Python or C#. I prefer to have full access to their computational capabilities and in particular their graphing even when the initial plan does not need these. Projects have a tendency of expanding over time. But when I need to quickly create a simple front end for a database I sometimes turn to MS Access.

One issue I have faced occasionally is being asked to make the same change to all of the forms or all of the reports. Fortunately, the VBA built into Access makes this relatively simple for certain types of changes. You can iterate through all forms, make the desired change, and then save that change so it is permanent.

This is best described with an example, though I have only tested this with Access 2010 and 2013. For this example, I am just going to create a database with one table and two forms. I will put the VBA code itself into a module. The code will look like:

Public Function ChangeBkColor()
    Dim obj As AccessObject 'Entities in Allforms are AccessObjects
    Dim CurForm As Form
    'We want everything to be closed to avoid any problems
    'with reopening later
    For Each CurForm In Application.Forms
        DoCmd.Close acForm, CurForm.Name
    Next CurForm
    For Each obj In Application.CurrentProject.AllForms
        'If not opened as design, it may not save the change
        DoCmd.OpenForm obj.Name, acDesign
        'There should only be one open form now
        Set CurForm = Application.Forms(0)
        'Change the color to red, but could change others
        CurForm.Section(acDetail).BackColor = RGB(100, 0, 0)
        'Setting acSaveYes here is significant
        DoCmd.Close acForm, CurForm.Name, acSaveYes
    Next obj

End Function

I normally execute by highlighting the code in the VBA editor and then pressing F5, but it could also be tied to a macro if that is more convenient. Executing it then opens each form, changes the property in question, and then closes it again, saving the change. Using techniques like this has saved me a fair bit of time in trying to standardize certain things across numerous forms in a database.