Lookup not showing records, also captions not displaying correctly

[Tammy]

Hi,

We are using Access 2007.

I wasn't sure if this should be posted to the forms group, or queries, so
please let me know if I should repost to the queries group.

I have a form with a query as a source. There are many lookup fields used on
the form - these were created through the lookup wizard in the table design,
not as combo boxes on the form.

The fields are not showing the list of records from the drop down on the
form, however, they do display properly in the table - drop down list gives
me the choices. Also, when the fields were added to the form, the captions
from the tables do not show - when the fields are added to the form, the
fieldname displays as the caption.

When I reviewed the query, I realized that the query was not displaying info
correctly, either. So, it's starting at the source.

Side note - in some of the tables, there are many fields using the same
source for a lookup. (Different fields, but can get the "answers" from the
same lookup table.) If there is more than one field within a table using the
same source for a lookup, should the relationship window show a join for each
field to that source table? Even though all of the fields in the table
display the correct lookup, not all of them have been automatically linked on
that common field between the two tables. (If a table has ten fields using
the same lookup, I was expecting to see that field linked ten times in the
relationship window.)

I've used these types of lookup fields on many forms and have never run into
this problem. And, I'm pretty good at analyzing a database to find where the
problem lies, but this one has me baffled. The lookups are good in the
tables, bad in the query results (thus, bad on the form).

Any suggestions?? Do you need any additional info? Thanks very much!

 

[boblarson]

My opinion -

Lose the lookups at table level. See here for why:
http://www.mvps.org/access/lookupfields.htm

I think you'll find that 9 out of 10 experienced developers will say the same.

 

[Tammy]

Thanks very much for the article, Bob. Since this is the first time I've run
across problems with lookups, in general then, should I only use combo boxes

 

[boblarson]

Using lookups at FORM level (using combos, or listboxes) is perfectly fine
and won't cause you near the problems that you have with lookups at table
level.

 

[Tammy]

Thanks very much for your helpful advice, Bob. And, thanks for taking the
time to answer my post. Have a great weekend!