Linking fields on form

[Abbos]

Hi everyone,

Having a little problem, have searched the forums but have not been able to find an answer.

I have a single db with 3 tables, artist, gallery and arts.

The main form should show the work details for each artist, i.e. each piece of art they have done. The problem I have is I think I have a link or relationship problem as when I try to create a new record in the arts table, I have a combo box which populates the artist name and gallery name and it should then store these details in the arts table along with the rest of the details I enter but it doesn't.

Each table has an autonumber and there is a one-to-one relationship from arts to artist and arts to gallery.

Can anyone help?

 

[Bobadopolis]

You need to restructure your data...

I envisage the same 3 tables but with the following structure:

tblArtists - details of each artist (stored once for each)
ArtistID (PK)
FirstName
LastName
DateOfBirth
etc...

tblArt - details about the art work created by each artist
ArtID (PK)
ArtistID (FK)
ArtName
GalleryID (FK)
etc...

tblGallery - a list of art galleries
GalleryID (PK)
Gallery

So in the form, the Art would be entered in a continuous subform and be linked by ArtistID to the Artist in the parent form. Gallery would be chosen in the subform by a combo box, if I've understood you correctly.

Do some reading on database normalization, "First Normal Form" (1NF) and "Second Normal Form" (2NF).

Bobadopolis

 

[Abbos]

Thanks Bobadopolis, appriciate the help. I'll give it a bash and post back the results.

 

[Bobadopolis]

OOh, and make sure your foreign keys are not unique indexes!

 

[Abbos]

Got this working. Thanks for the help!