I am using Access 2010 and I have a datasheet which records Invoice data:

Invoice number
Client ID
Client surname
Invoice date
Invoice amount
Date paid

Invoice number is the primary key and the table is linked to the Client
datasheet.

I would like to be able to enter the Client ID in the Invoice Datasheet and
have Access auto complete the Client surname field. Is that possible? Would
I need to build an expression or is there something in Design that can be
used?

All help appreciated.


Steve

On Wed, 24 Feb 2010 01:35:53 -0000, "StevePlym" <(E-Mail Removed)> wrote:

>I am using Access 2010 and I have a datasheet which records Invoice data:
>
>Invoice number
>Client ID
>Client surname
>Invoice date
>Invoice amount
>Date paid
>
>Invoice number is the primary key and the table is linked to the Client
>datasheet.
>
>I would like to be able to enter the Client ID in the Invoice Datasheet and
>have Access auto complete the Client surname field. Is that possible? Would
>I need to build an expression or is there something in Design that can be
>used?

The client surname should NOT EXIST in the Invoice table.

An invoice doesn't have a Surname as an attribute. Sure, a client does; but
you can *link to* the Client table, by the ClientID, to find it!

If you're interacting with data using a table datasheet... well, don't; that's
not what tables are for. They're to store data! You can instead use a Form;
this could (among other possibilities) have a Combo Box bound to (and storing)
the ClientID while displaying the client's surname, or (if you prefer) full
name. There is no need and no benefit to storing the surname redundantly.

--

John W. Vinson [MVP]