Automatically insert data into a table based on a previous table entry?

[w1dge]

I am setting up a database that will hold details on members of an art club,
their artwork, art sales information and the customer who has purchased the
artwork.

I have 4 tables tblArtist, tblArtwork, tblCustomer, tblArtSales

The table I am having trouble with is the tblArtSales which is designed to
hold details about the artist, the customer and the artwork.

What I would like to do is automatically retrieve information on the artist,
artwork and customers just be entering their unique membership numbers and
in the case of the artwork its unique catalogue number.

I have tried using the combo lookup wizard, which provides drop down details
about the artists, customers and artwork, but from these lists you can
select options that would destroy the integrity of the database!

Is there anyway that I can retrieve the correct information on the artist
(such as name address telephone etc) automatically just by entering their
membership number? The same would apply to the customers (again using their
unique membership number) and the artwork (by entering its unique catalogue
number).

This way the integrity could be maintained and the user would not have to
manually select all of the information.

Thanks for any help

Mark

 

[Doug Munich]

Are you saying that you are looking for a way for the user to select the
Artist, Artwork, and Customer on the form for an ArtSale? It sounds like
the Lookup Combo is what you want. What do you mean by 'but from these
lists you can select options that would destroy the integrity of the
database'?

Doug