Beginner seeks help with sub form and Auto List update

[xyzlook]

I am a novice user of Access. For past several weeks I have been trying to develop a Database for a CD collections. I am running into a couple of problems.

TABLES: (not all tables or fields are listed here):

1. tblSongs: List of Songs (SongID / Primary Key PK; RecordingArtistID / Foreign Key FK)
2. tblArtists: List of Artists (RecordingArtistsID /PK)
3. tblSongsDetails: TrackID / PK; SongID / FK; RecordingID/FK; RecordingArtistID /FK
4. tblCDDetails: RecordingID / PK; SongID / FK; RecordingArtistID /FK;

FORMS:

1. frmSongs: a form to add to the list of songs)
2. frmArtists: a form to add to the names of artists)
3. sfrmSongsDetails: it is a subform added to the bottom of the CD Details form. Fields include are: TrackID, TrackNo, SongID, ArtistID)
4. frmCDDetails: a main form to add CDs to the Database with fields like RecordingID, CategoryID, LanguageID etc. on the main part and includes the sfrmSongDetails as a subform.

Problem#1:

The RecordingArtistID field on the sfrm includes events (Not On List and On DoubleClick) that prompts me to add an artist who does not exist in the tblArtists. It works just fine.

I used the save event processes to include a song which may not be on the tblSongs. This does not work. I get the message: You tried to assign the Null Value to a variable that is not a Variant data type.

Why do I get this message? When both the SongID and RecordingArtistID are similar fields (Auto Number and Primary Key)

Problem#2:

When I update the tblSongs with new songs, sfrmSongDetails does not reflect this change (which is based on tblSongDetails).

Can you please help?

 

[Pampers]

DO you have Link Master/Child Field property set for the subform...


Pampers
Just back from holiday...
that may it explain it.

 

[TheAceMan1]

Roger That tekkyun . . .

Big gray area with what you've posted . . . might I suggest, at your leisure, you go over the following links and then return with your new insight!

Fundamentals of Relational Database Design
Normalizing Tables
Table Relationships

See Ya! . . . . . .

 

[MajP]

Looks like you are doubling up your keys.

For example your CD table.

If you list the song FK, you do not need to list the artist FK in that table. That artist is related to the Song in the Song table. The song table is linked to the artist table. So just by having a link to the Song table, you already have a relation to the artist.

Theoretically you could have a CD relate to an artist, and a song relate to a CD, this would make less data entry but could break down in this case:

1) Compilation CD or a CD with a primary artist and other artists

If you relate each song to an artist and not (an artist related to the CD) then you can handle 1. The field in the CD details would be "CDArtist", which then may or may not be different than the song artist.