Bring 2nd field along

[Striker]

If I have a table that looks up values from another table in the form of a
list, Table/Query. How can I bring a second column of data along in another
fied in the second table.

For instanse: say I have a table with {FName}{LName}

in the second table say an invoice When the user selects {Fname} from the
drop down list, I want his second field in the new table to auto populate
with {LNAME}

ACCESS2007

 

[Beetle]

From your post it sounds like you are working directly
in your tables. If that's the case, you shouldn't be. All
data entry should be done with forms. Tables are for
storing raw data only.

It also sounds like you are using a Lookup field in your
table. Again, something you should not do. You should
use a combo box in a form instead. For a discussion of
this topic, see;

http://www.mvps.org/access/lookupfields.htm

As far as your tables, you should have something along
the lines of the following;

tblPerson
*******
PersonID (Primary Key)
FirstName
LastName

tblInvoice
*******
InvoiceID (Primary Key)
InvoiceDate
PersonID (Foreign Key to tblPerson)
other attributed of the invoice

When yo create an Invoice form, you would use a combo box
bound to the PersonID field in tblInvoice. The *only* value
that would be stored in tblInvoice would be PK value from
tblPerson (in this case PersonID). However, this combo box
would *display* (not store) a concatenated FirstName and
LastName via it's Row Source query. The properties of this
combo box might look like;

Control Source: PersonID
Row Source Type: Table/Query
Row Source: SELECT PersonID, FirstName & " " & LastName
AS FullName FROM tblPerson ORDER BY LastName
Bound Column: 1
Column Count: 2
Column Widths: 0", 2"