Look up fileds - Advice please

  • Thread starter Thread starter Tony
  • Start date Start date
T

Tony

Hi,

I was browsing 'The Access Web' site and noted the a page
dedicated to 'The Evil of Look Up Fields. I have had
many, many problems in the past with look-ups and wish I
had read this article sooner. Though the article
describes in great depth the evils of look-ups it doesn't
actually describe possible workarounds.

I have a database which uses a look-up fields. I would
like to remove them but how do I do this. For example,
users can select Divisions from a combo-box called
cboDivisions, which is a look-up to tblDivisions. The ID
of the Division is stored and has caused me many problems
during filtering routines.

What is the best way to look up data in other tables so
that I avoid the evils of look-ups.

Thanks in advance

Tony
 
Tony-

You can remove the lookups by opening each table in Design view, selecting a
field that has a lookup, clicking the Lookup tab in the bottom half of the
window, and changing the Display Control to Text Box.

Lookups are OK on forms to help the user select the appropriate foreign key,
but, as you have discovered, if you click in the combo box and try to do a
Find on it, you cannot search by the text displayed. I will sometimes
include the lookup table and the display value as a separate field in the
form record source query. Create a locked Text Box to display the text and
place it right on top of the display area of the combo box. If the user
clicks there, they can do a search on the text "looked up" value. They can
still click the drop-down arrow to the right to pick a new foreign key
value.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
John, Thank You
Much appreciated
Tony
-----Original Message-----
Tony-

You can remove the lookups by opening each table in Design view, selecting a
field that has a lookup, clicking the Lookup tab in the bottom half of the
window, and changing the Display Control to Text Box.

Lookups are OK on forms to help the user select the appropriate foreign key,
but, as you have discovered, if you click in the combo box and try to do a
Find on it, you cannot search by the text displayed. I will sometimes
include the lookup table and the display value as a separate field in the
form record source query. Create a locked Text Box to display the text and
place it right on top of the display area of the combo box. If the user
clicks there, they can do a search on the text "looked up" value. They can
still click the drop-down arrow to the right to pick a new foreign key
value.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)



.
Click to expand...
 
Back
Top