I have two tables News & Zones. In the table news I have a lookup field called NewsZone that refrences the table Zones like this:

SELECT ZoneNum, ZoneDesc FROM zones

Except I only want the ZoneNum in the News table, The ZoneDesc it a text description that should appear in the select box to make it easy to select a zone.

Another words how do you build a lookup table to show the text description but when selected return the corresponding id number?

Continue to use both columns in your dropdown list; however, keep the Bound Column property set to 1. Next change the Column Count property to 2. Set the Column Widths property to, for example, 1" and 3". Then set the list width to 4" (i.e. 1 + 3). When you click on the item, the dropdown will show both columns, however, it will only use column 1 for data.

Note that this structure in a table (putting dropdowns in it) is poo-pooed by a lot of people. Choosing from dropdowns best done in forms, not tables.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Big Thanks SerranoG

I got it to work but I will admit I have herd this rumor before do you know what the story is? Possibly slower performance or poor data structure.

It would be easy to use a form control and map the txt desc to an id #.

mossimo

It has to do with data integrity and structure. If you're the only person working on this database and table then it's perfectly all right to do it this way. You're the only person dealing with the data.

However, the instant you share this with other people, it becomes a poor practice because users should never edit data directly in a table. They should use a form. Dropdowns and the like are created and structured in the form. The table's purpose is just to hold the data... period. Therefore, you don't put dropdown elements in your table.

I can see the allure of making dropdowns in tables because whenever you create a form based on this table and bring in that field, it will automatically turn into a dropdown with the proper rowsource. However, some folks say it's even bad structure to base a form on a table or query and that you should always use unbound forms. It's all a matter of standard practices and uses.




Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Slower performance,bad structure, inability to export the data to a SQL Server. It is not a feature of access it is a "creature". It was meant to be used only for the non developers, basically MS Office Users. Just like the switchboard (hate it too, there is another thread on that one). Use dropdowns on forms only. Also do not use subdata sheets in tables.



Sal