Microsoft Access Combo Box Storing ID in table instead of Name

Question

I have 2 combo boxes. One that the user chooses a storage Area and then based on the storage Area that the user chooses it only displays the corresponding Locations in that Storage area. The Storage area table and the Location table are joined.<o:p></o:p>

The row source of the Location Combo box reads as follows: <o:p></o:p>

SELECT Locations.LocationID, Locations.Locations, Locations.[Storage ID] FROM Storage INNER JOIN Locations ON Storage.ID = Locations.[Storage ID] WHERE (((Locations.[Storage ID])=[Forms]![Inventory Data Entry]![cbostorage])) ORDER BY Locations.[Storage ID]; <o:p></o:p>

Everything in regards to the form display is perfect but when I go to view the data in the table that stores the form entry data the storage area and the location is only displaying the ID instead of the text associated with the ID like it is displayed in the form.<o:p></o:p>

I'm trying to train and launch this database next Monday so any help with this would be greatly appreciated<o:p></o:p>

Answer 1

That's fine - it is more efficient to store the IDs in the table than the description.

End users should never have to deal with the tables directly; they should only interact with the data through forms and reports.

But if you really want the table to display the description, you can use the following method (which is frowned upon by some developers):

• Open the table in design view.
• Select the LocationID field.
• Activate the Lookup tab near the bottom of the window.
• Set 'Display Control' to 'Combo Box'.
• Set the 'Row Source' to
• SELECT Locations.LocationID, Locations.Locations FROM Locations ORDER BY Locations.Locations
• Set 'Column Count' to 2.
• Set 'Column Widths' to 0.

Save the table.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

You should be aware that storing the LocationID and StorageID in two columns in the referencing table means that the table is not normalized to Third Normal Form (3NF), and consequently open to the risk of update anomalies.  With any tree-structured hierarchy only the lowest level in the hierarchy, LocationID in this case, should be stored in a column in the table.

In the data entry form the storage area should be selected in an unbound combo box, with which the bound location combo box is correlated.  You can still select a storage area, then location in the same way as you are doing, but it needs a little more work with some code in the form's module.

You'll find an example  in DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In this little demo file, the section on 'entering data via a form/subforms' includes a simple contacts form in which the country, then region, then city in which the contact is located can be selected in correlated combo boxes, of which only the last is bound.

In the demo the form is in single forms view.  For a form in continuous forms view the solution is a little more complex, requiring the use of 'hybrid' controls where a text box is superimposed on a combo box to give the appearance of a single combo box control.  You'll find examples of this, and alternative solutions, in ComboDemo.zip in the same OneDrive folder.

---

Ken Sheridan, Stafford, England