Joan:
I don't use Access 2007 myself, but my understanding is that this is really
just an extension of the Lookup Wizard which has long been present in earlier
versions, and has been constantly derided by experienced Access developers
for an equally long period. For reasons why see:
http://www.mvps.org/access/lookupfields.htm
While this might seem like a useful feature at first sight I would seriously
caution against its use. Tables can easily be designed via the normal design
interface without recourse to it, and will not suffer from the problems which
the above link details.
What the Lookup Wizard essentially does is create foreign key in a table on
the 'many' side of a one-to-many relationship which references the primary
key of a table on the 'one' side. This can easily be done via normal table
design. Say you have tables Customers and Orders for instance, the former
having an autonumber primary key CustomerID. You simply need to add a
CustomerID column (field) to the Orders table, of long integer number data
type in this case, not an autonumber, and create a relationship between the
tables on the CustomerID columns, enforcing referential integrity.
In the Orders table you will not see the customer name of course as you would
if the Lookup Wizard is used, but the numeric value of the CustomerID. This
is no disadvantage, however, as in any database application data should only
be viewed and/or edited via forms or reports, never in raw datasheet view.
In a form you can use a combo box bound to the CustomerID column in Orders to
show the customer name (the combo box wizard can set this up for you); in a
report you'd base the report on a query which joins Customers and Orders and
include the customer name (and any other customer details) from Customers in
the report along with the data from Orders.
Its encouraging to hear that its at the form stage that you are anticipating
this being a problem, as this shows that you are intending to design an
interface using forms rather than relying on the raw datasheets. What
appears in the raw datasheet of a table is immaterial, therefore, which is as
it should be.
The reason you might be having problems with this feature could be that you
are trying to add an inappropriate field to the table in the context of how
the tables are related. This does emphasise the need to have a good
understanding of how a relational database models the real world in terms of
tables, which represent 'entity types', columns, which represent attributes
of each entity type, and the relationships between the tables on one or more
attributes. You'll find the links to which Gina referred you invaluable in
this respect.
Ken Sheridan
Stafford, England
