lookup to another table

[Gisel]

I have a lookup field pulling info from a publications
table (called materialslist). In the materialslist table
I have 4 fields; pubcode, content, title, and source.
Right now every time you choose a publication only the
pubcode shows, the rest of the info is blank. Right now
if you choose Brochure as the publication only the pubcode
shows in the materials table, I want the table to
automatically pull the rest of the info corresponding to
this publication from the materialslist when you choose a
publication. i.e. content, type, and source of
publication.

Can anyone tell me how I can do this?
Thanks
Gisel

 

[Lynn Trapp]

Gisel,
Actually, while you may feel that you need to store the other information
from the materialslist table in the materials table, that is actually not
the case. In fact, it is bad database design to do so. As long as you store
the pubcode in the materials table, you can always query the information and
get everything you need.

 

[gisel]

The materials table will be storing the records that is
chosen everytime a publication is sent out. I want the
materialslist to be a reference list for the pubcode list,
not a record in the materials table. how can i store this
list in the materials table and use it just as a reference
list, i don't want it to become a record and it is too big
to put as a value list. what can i do?

 

[Lynn Trapp]

Simply add a field to the materials table that stores the pubcode. Use the
lookup on your data entry form to look up the correct value from the
materialslist table. Then you are ready to go.

 

[John Vinson]

I have a lookup field pulling info from a publications
table (called materialslist). In the materialslist table
I have 4 fields; pubcode, content, title, and source.
Right now every time you choose a publication only the
pubcode shows, the rest of the info is blank. Right now
if you choose Brochure as the publication only the pubcode
shows in the materials table, I want the table to
automatically pull the rest of the info corresponding to
this publication from the materialslist when you choose a
publication. i.e. content, type, and source of
publication.

Can anyone tell me how I can do this?
Thanks
Gisel

Well... DON'T.

Table datasheets are very limited. Lookup Fields are even MORE
limited, to the point of having *less* than no value, in the opinion
of many MVP's (see http://www.mvps.org/access/lookupfields.htm for a
critique).

You're using a relational database - use it relationally! Create a
Query joining [Publications] to [Materialslist] joining by pubcode;
this lets you see all the fields from both tables. Better, base a Form
(for onscreen viewing) or Report (for printing) on this query.