Solved Updating a table with a value from a mainform when a subform updates

[lodmark]

Hi, it's been a while now.

I have a small problem with my database.
I need to automatically fill in the media that a song has.
I have a form where I fill in the name of the album and artist, etc. There I also fill in the record company, year of release and the media that the album has, it can be LP, EP, Maxi, CD or single.
In the subform I fill in all the songs and their time and also the artist if it is a compilation album.
At first I didn't see the need to add the media here. But now that I'm searching for specific songs, it can be good to know in which media I have that particular song.
So how do I copy the album's media from the main form to the media field in the table where all the songs are?
This should happen when I add a new song to the subform.
So I want to update the song with which media the album has.

Probably a simple solution but I can't get it to work.

Leif

 

[bob fitz]

At first I didn't see the need to add the media here. But now that I'm searching for specific songs, it can be good to know in which media I have that particular song.

Not sure that it would be a good idea to have the media data in both tables.
I'd have thought that you could use a query to tell you in which media you have a particular song.

Can you post a copy of the db for us to play with.

 

[arnelgp]

you can use the Current Event of your subform:

Private Sub Form_Current()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
If Not IsNull(Me.Parent!Media) Then
    If rs.RecordCount <> 0 Then
        With rs
            .MoveFirst
            Do Until .EOF
                  If IsNull(!Media) Then
                      .Edit
                      !Media = Me.Parent!Media
                      .Update
                  End If
                  .MoveNext
              Loop
          End With
     End If
End If
Set rs = Nothing
End Sub

Or you can use Update Query to update the media.

 

[lodmark]

Not sure that it would be a good idea to have the media data in both tables.
I'd have thought that you could use a query to tell you in which media you have a particular song.

Can you post a copy of the db for us to play with.

Hi @bob fitz
I do understand the problems with "having the data in several tables" but its not the same data. One is for the record and the other for the song, even if the song is on the record.
I don't thing there's going to be any problems with this in my database.

I've tried picking out the media with a query but I didn manage it to work.

I tried to split the database but the filesize didn't change so I hoped I could share the large database with you, but it seems to be too large.

Leif

 

[bob fitz]

Hi @bob fitz
I do understand the problems with "having the data in several tables" but its not the same data. One is for the record and the other for the song, even if the song is on the record.
I don't thing there's going to be any problems with this in my database.

I've tried picking out the media with a query but I didn manage it to work.

I tried to split the database but the filesize didn't change so I hoped I could share the large database with you, but it seems to be too large.

Leif

Have you tried to "Zip" the file

 

[lodmark]

you can use the Current Event of your subform:

Private Sub Form_Current()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
If Not IsNull(Me.Parent!Media) Then
    If rs.RecordCount <> 0 Then
        With rs
            .MoveFirst
            Do Until .EOF
                  If IsNull(!Media) Then
                      .Edit
                      !Media = Me.Parent!Media
                      .Update
                  End If
                  .MoveNext
              Loop
          End With
     End If
End If
Set rs = Nothing
End Sub

Or you can use Update Query to update the media.

Hello @arnelgp

If I can understand the code correctly it's for updating the field for media for all the records in the table, and just for once.

I want it to happen every time I add a new record with songs?

Leif

 

[cheekybuddha]

At first I didn't see the need to add the media here. But now that I'm searching for specific songs, it can be good to know in which media I have that particular song.

You really should be able to glean this info just using a query.

Please describe the tables/queries (table names, field names, datatypes) used in main form and subform and which field relates them.

 

[lodmark]

Have you tried to "Zip" the file

Why didn't I think of that?

 

[bob fitz]

Can you at least tell us which form we need to look at.

 

[GPGeorge]

Unless there are differences between the media for an album and for each of the songs it contains, there is no need to duplicate the data. I'm being a little facetious, actually. The media is the same, so even though you want to look at the media on a song-by-song basis, you only have to look at the media for the album on which it exists.

Now, there is a second way to look at this: You have a vinyl album, an album on DVD and an album on VHS. They all contain the same song or songs. So, if you are looking at that song it exists on all three media. However, that's also no excuse for storing redundant data. A query, albeit slightly more complex, can display that information on a subform.

Here is a query that should do this for you.

SELECT
    tbl_music.music_ID,
    tbl_music.music_titel,
    tbl_record.record_name,
    tbl_media.media_text
FROM
    tbl_media
    RIGHT JOIN (
        tbl_record
        RIGHT JOIN (
            tbl_music
            LEFT JOIN tbl_music_record ON tbl_music.music_ID = tbl_music_record.music_record_music
        ) ON tbl_record.record_ID = tbl_music_record.music_record_record
    ) ON tbl_media.media_ID = tbl_record.record_media
ORDER BY
    tbl_music.music_titel,
    tbl_media.media_text;

Create a listbox on the subform and make this the rowsource for it. Since I don't know which of the forms is supposed to show this, here is a generic WHERE clause that you can modify and add to the query.

WHERE tbl_music.music_ID = Forms!frmParentForm.sfrmSubForm.Music_ID

 

[lodmark]

Can you at least tell us which form we need to look at.

Sorry.

The problem lies in the form frm_record2 where I insert new records.
There the medium is entered into the main form and I want that value to be saved for each record in the subform in the table tbl_music.

Leif

 

[Gasman]

Unless there are differences between the media for an album and for each of the songs it contains, there is no need to duplicate the data. I'm being a little facetious, actually. The media is the same, so even though you want to look at the media on a song-by-song basis, you only have to look at the media for the album on which it exists.

My thoughts exactly.

TBH I do not store the media, I am just interested in artist/album and tracks.
For that I read my Music folder for the files and populate a DB once in a while to keep it uptodate.

 

[Gasman]

Damn @xavier.batlle,
You are quick, I only just posted that.

 

[bob fitz]

Sorry.

The problem lies in the form frm_record2 where I insert new records.
There the medium is entered into the main form and I want that value to be saved for each record in the subform in the table tbl_music.

Leif

As I've already said, I don't think you should be keeping the same data in more than one table, but rather use a query to retrieve the relevant data.
Take a look at "qryPickMedia" in the attached. You could use a similar query as the Record Source of a form and use a combo on that form to filter the media.

 

[The_Doc_Man]

Sorry.

The problem lies in the form frm_record2 where I insert new records.
There the medium is entered into the main form and I want that value to be saved for each record in the subform in the table tbl_music.

Leif

But if you enter the new containing item (album?) that surely sets the format of the container item. But a "true" sub-form relationship means you could enter the data ONCE for the container and then just enter/link the container for each song, thus locking down both the media type AND the location of that song on its container.

The side effect of such a DB is that you WILL have name duplicates for individual musical selections, particularly if you have a few albums by some particular artist who then issues a "Joe Schmuckadeli's Greatest Hits" album for which probably half or more of the selections appear elsewhere. But that is a pitfall of being a music collector.

The ONLY times you care about medium are (a) original data entry - which is discussed in my opening comment (b) finding the containers holding that selection or (c) building a report. In all three cases, a simple JOIN query would allow you to get musical composition name, container name, and container format in a single data record. Any other time you don't really care.

 

[bob fitz]

Leif

As you liked the example query, I've attached a copy of the db with an example of how it might be done on a form (frmPickMedisMain)

 

[mike60smart]

Sorry.

The problem lies in the form frm_record2 where I insert new records.
There the medium is entered into the main form and I want that value to be saved for each record in the subform in the table tbl_music.

Leif

Hi Leif
In the attached modified Db I added an Unbound Control named "RM" to the Subform Header with a Form Reference to the Main Form Medium Control.
Then in the After Update of the cboMusicRecord I added the line Me.Music_Medium = Me.RM

I updated just the first 3 records in the subform.

 

[Ken Sheridan]

As far as I can see, you have a ternary relationship type between Albums, Songs and Media, which would be modelled by a fourth table which resolves the relationship type into three unary (one-to-many) relationship types. In an albums parent form a subform would be based on a query on the table modelling the relationship type, and linked to the parent form on AlbumID or similar. The query would reference an unbound combo or list box control in the parent form as a parameter. The combo or list box would allow a medium to be selected, and the subform would be requeried in its AfterUpdate event procedure.

To avoid unnecessary repetition when inserting rows into the subform a button could be added to the parent form to execute an 'append' query to insert those SongID values which have already been inserted for one medium when inserting the same set of songs for another medium, e.g. when you have the same album on vinyl and CD.

 

[Mark_]

As a bit of a purist, I'd suggest having a many to many relationship between records Tbl_Music and Tbl_Artist. This will allow you to track which artists performed on a track even when the "Album" doesn't list them. Purist in this case is for the music, not database theory. ☺

For media, that should ONLY be saved in Tbl_Music_Record. A given track that could be on pressed on a physical record, stored on a tape, or burned to CD. Would be the same artists, same song, same date, and same length with a different storage media.

As an option, you may also want to add "Band" as a parent along with artist. This will allow you to save "Queen" as the band and "Freddy Mercury" as an artist.

 

[arnelgp]

i am sorry, you only need to "show" the media from your query.
see query1 and also your form.
you don't need to add another field to your subform table.