ThisField, ThisTable

  • Thread starter Thread starter KK
  • Start date Start date
K

KK

Hello,

I have a table 'Staff' whose fields include 'Location'.

In Field Properties, under Lookup, I like to use a combo box with " SELECT
DISTINCT Staff.Location FROM Staff; " in 'Row Source' , with 'Limit to List'
set to 'No', which provides a drop-down box for input with a list of
locations already used, plus the option of adding a new one.

A minor problem is that if I copy the text " SELECT DISTINCT Staff.Location
FROM Staff; " to another field, or if I change the table or field names, I
have to edit the text. This is OK, but is there a more general version of
the SELECT statement which automatically picks up the current table and
field names?

Thanks

KK
 
Are you saying you have used the "lookup" data type in a table's field
definition? If so, be aware that Access stores the foreign key, but
displays the "lookup" field in the table, leading some folks to be confused
about what's actually in the table, or how to query against that field, etc.

The oft-recommended alternative (see tablesdbdesign newsgroup) is to not use
a "lookup" data type, but a corresponding (text for text key, long int for
Autonumber key, etc.) data type. Save the "lookup" activity for a form,
where Access does a much better job of displaying data. Tables work great
for storing, but not so great for displaying.
 
Back
Top