Adding a record, need to "cascade"?

[Snecia]

Hello everybody. I am quite new to Access so I will do my best to explain as clearly as I can what I am trying to do.

I have a database that handles a collection of music. Simplified, let's just say the database has 3 tables:
Album(+albumName)
Genre(+genreType)
AlbumIsGenre(+album, +genre)

AlbumIsGenre is the table that handles the "many-to-one" relationship between Album and Genre (there are many albums, each has one genre).

I am trying to make a form that will allow me to view albums and their associated genre and add a new album. Currently, my form uses the AlbumIsGenre table (only) to display album names and genres. This works fine, I can view them all.

When I try to add a record however I get: "You can not add or change a record because a related record is required in table 'Album'."

I understand why this is happening and it is expected behavior. But how can I tell my form the add the new record albumName to Album before creating the new record in AlbumIsGenre?

Thank you for your time guys, help is greatly appreciated, need to hand in two forms for Monday!

PS: This is what we call "cascading" no? Not sure if I got the term right.

 

[jdraw]

see this for more info on relationships
http://msdn.microsoft.com/en-us/library/4s9x13w7.aspx

 

[CJ_London]

I think you need to consider your table structure first, from what you say you only need two tables

Album
AlbumID
AlbumName
GenreID

Genre
GenreID
GenreName

Then your form just needs to use the Album table

 

[Snecia]

Thank you for the quick reply, I realize that I didn't express myself correctly regarding my relationships (it's late ) but I do understand how they work and have them set up properly.

I'm really trying to understand how to make my form create the necessary record in Album before creating the record in AlbumIsGenre.

Basically I'm trying to make a form that adds a new album and it's genre, two records need to be created, one in each table.

 

[Snecia]

Thank you CJ London.
I understand your logic but that is not how my teacher wanted us to set up the tables. She clearly wanted three tables. One for the albums, one for the genres, and one to link them both. I can't change anything to that.

From this I need to create a form that can add an album and it's genre. I don't need to touch the genre table (there are only 4 genres possible and no need to add more).

I do need to make a form that has a textbox representing the albumName and an combobox representing the list of genres. Two records need to be created from this one form. One record with the new albumName in Albums, and one record with both the album name and the genre in AlbumIsGenre.

 

[CJ_London]

It's late here too! So don't have time to experiment.

What I suggest you look at is set the form recordsource to album and albumisgenre linked on albumid and set the recordset type to Dynaset (Inconsistent Updates)

 

[Snecia]

Thank you for your answers CJ London. They were playing in my head all night.
I managed to solve this morning (love it when you wake up with your answer!)

Any way, I had to put this SQL in the Form Record Source, with Recordset Type set to dynaset:

SELECT [AlbumIsGenre].[album], [AlbumIsGenre].[genre], [Album].[albumName]
FROM Album
INNER JOIN AlbumIsGenre ON [Album].[albumName] = [AlbumIsGenre].[album];

(I won't lie, the wizard helped me on that one)

I have a textbox on my form for Album.albumName and a comboBox for AlbumIsGenre.genre. I can now add a new record and it's created in both tables.

 

[CJ_London]

Well done!