Expanded Lookup Values Hidden

[Phoenix Clarke]

I am having problems displaying the values in an expanded combo box on a form
(and in the underlying table) that have me stumped. About two weeks ago, an
enterprise automatic update was pushed down to my computer. Unfortunately, I
have no idea what that update included, but subsequently, I have a problem
with combo boxes in Access 2003 that did not appear before. My network
administrators have no intentions of rolling back the update so I am left to
try to fix this unusual problem myself.

There is a foreign key field in a table (say tblMain) that pulls values from
a lookup table (say tblVlook) into a specific field called LookId. In the
design of tblMain, the field LookId displays its values from tblVlook as a
combo box with a simple query and 2 columns. The first column is the PK in
tblVlook, and the second column is a description from the same table. Both
columns are shown, but the first column contains the values that are stored
in the tblMain table. While viewing tblMain in datasheet view, when I expand
the drop-down list for the field LookID, both columns are showing with the
appropriate spacing, but the values in the first column do not appear.
However, when I select a value from the list and it is no longer expanded,
that hidden value will appear in the field.

The same thing happens in the form for this same field, and I have verified
that the appropriate table relationships exist between the table, that the
column widths indeed allow enough space for both columns, that the bound
column is indeed 1, and that the column count is 2. Since the first column is
the bound column and the values allowed do show up when the list is not
expanded, I really need some help trying to figure out why those values
simply don’t show up when the list is expanded. Any ideas?

~Phoenix

 

[Larry Daugherty]

Your description of what is going on in your design seems ambiguous to
me. Therefore I'll make one guess. If that has nothing to do with
your problem then please post back giving specific table names and
field names and datatypes.

I'm guessing that you are using Lookup Fields in at least one of your
tables. If that is so, go back and remove them and design your tables
properly according to relational rules. For more information on this
see www.mvps.org/access regarding Lookup Fields. Note that Lookup
Tables are a good thing. They are regular tables that simply have a
defined purpose. Lookup Fields are an abomination provide by
Microsoft to allow novices to get a cheap boost of functionality in
very simple applications. Trying to build applications based on
Lookup Fields will lead to problems. It is unlikely that an
enterprise wide update would have anything to do with Lookup Field
issues.

HTH

 

[Phoenix Clarke]

After reading your email, I went back into the table and removed the lookup
field from the table where the problem exists and replaced the control from
the form to see if a different result would occur, but the same problem
exists. Since the previous email seemed a bit ambiguous, I'll give you exact
table and field names to see if it helps.

Within a table called tblFacilities, there is a field called Rel_Feat that
is a foreign key field linked to a table called tblRelFeatList. This table,
tblRelFeatList, contains a list of possible places where a person who is
collecting latitudes and longitudes for the facility identify the point. For
example, it can be at the entrance (ENTRA), center (CENTR), administrative
center (ADMIN), etc. Each one of those values are represented by a primary
key field that is a Text field up to 5 characters.

Within the Facilities table (tblFacilities), the field Rel_Feat is a foreign
that only allows values from the tblRelFeatList table. This one-to-man
relationship is appropriately defined in the table relationships. Within the
tblFacilities table, the Rel_Feat field is now shown as a text box, but on
the data entry form, this field is a combo box showing the key field
(5-character string) and the description ("Entrance to the site", etc.). The
combo box is bound to the key field, which is the value show in the box when
the user makes a selection.

The problem with the combo box is that when the user presses the down arrow
to expand the box and view the values, blanks appear in the first column but
the second column of descriptions show perfectly. After the user makes a
selection from the combo box, the key value selected (ENTRA, CENTR, etc)
shows up as it is supposed to but when the box is expanded, that key value
(along with the others) appears as blanks. Even after deleting the previous
combo box and reinserting the field as a text box (as it now appears in the
table), converting it to a combo box, and applying the appropriate
properties, the problem still persists.

I can scrap the form and rebuild it if I need to, but I am hoping to hear of
a better resolution. Does this more detailed explanation help you understand
the problem? I can send you a screen print of the expanded combo box for
clarity if that would help. Thanks, Larry, for your reply.