Solved New thoughts on my vinyl record collection database

[lodmark]

Hi!
A while ago I got help creating forms for my database for my record collection.
Among others, @arnelgp and helped me a lot.
Now I have started to put in my vinyl records and then I realized that I made a mistake when I wanted to be able to choose the songs after the artist of the record.
I have many records that are compilation records, both with original artists and where other unknown artist play the songs.
How can I get these into the database in a similar way as when one artist plays all the songs on the vinyl record?
I still want to be able to choose the song after I have chosen the artist, but on a compilation album there are several artists.
I think you could also choose the artist in the list of songs on the record and then which song it is.
But how do I do this?
I enclose the database. The form I want help with is frm_record2.

I trust in you

Leif

 

[lodmark]

A little clarification with a picture.
When I have previously selected an artist in the middle of the form, I can then choose from the artist's songs in the subform.
If I have selected Various artists (Varierande artist) in the field for artist, I want to be able to select the artist in the subform instead.
Or that it always will be posible to choose or change the artist in the subfield, but the default value in the subfield for artist is what is chosen at the top.
Hope it got clearer now.

 

[lodmark]

Can anyone help me with this?

 

[arnelgp]

my friend you test this.
Add New record to the Main Form (select "various artists" on the Main Form combo).
add records to the subform.

 

[lodmark]

That was quick @arnelgp!
What have you done, or was it just a suggestion on how to do it?
I wish I could see the artist in the list in the subform. That's why I added an artist field.
Now, of course, it only says various artist.

 

[MajP]

I interpreted this various different.

If you select various artist, then the filter for choosing records gets removed so you can select any song from any artist

  Dim strSql As String
   
If Me.Parent.Kombinationsruta25 = 1053 Then
   strSql = "SELECT tbl_music.music_ID, tbl_music.music_titel, tbl_music.music_artist_ID FROM tbl_music ORDER BY tbl_music.music_titel"
  Else
   strSql = "SELECT tbl_music.music_ID, tbl_music.music_titel, tbl_music.music_artist_ID FROM tbl_music WHERE (((tbl_music.music_artist_ID)=[Forms]![frm_record2]![record_artist_ID])) ORDER BY tbl_music.music_titel"
  End If
  Me.[music_record_music].RowSource = strSql
  Me.[music_record_music].Requery

The the artist needs to be to the song and not the record.

 

[lodmark]

I found the code you put in, nice.
I'll try and connect the arist field to the music field and maybe I can show the artist.

 

[lodmark]

I interpreted this various different.

If you select various artist, then the filter for choosing records gets removed so you can select any song from any artist

  Dim strSql As String
   
If Me.Parent.Kombinationsruta25 = 1053 Then
   strSql = "SELECT tbl_music.music_ID, tbl_music.music_titel, tbl_music.music_artist_ID FROM tbl_music ORDER BY tbl_music.music_titel"
  Else
   strSql = "SELECT tbl_music.music_ID, tbl_music.music_titel, tbl_music.music_artist_ID FROM tbl_music WHERE (((tbl_music.music_artist_ID)=[Forms]![frm_record2]![record_artist_ID])) ORDER BY tbl_music.music_titel"
  End If
  Me.[music_record_music].RowSource = strSql
  Me.[music_record_music].Requery

The the artist needs to be to the song and not the record.

View attachment 85401

View attachment 85402

THANKS!!!!! So Nice

 

[MajP]

You may find this useful. You have very long comboboxes which makes selecting a value very hard. I have a class module that turns any combo into a find as you type. It requires a single line of code to use.

So if I want to go search for Frank Zappa I can type in Fr.. or zap.. and it will filter the list to records that contain this. Below shows any records containing "za". You can use this on any combo in the db.

 

[lodmark]

You may find this useful. You have very long comboboxes which makes selecting a value very hard. I have a class module that turns any combo into a find as you type. It requires a single line of code to use.

So if I want to go search for Frank Zappa I can type in Fr.. or zap.. and it will filter the list to records that contain this. Below shows any records containing "za". You can use this on any combo in the db.
View attachment 85405

Thanks! I definitely look into this.
Leif

 

[lodmark]

THANKS!!!!! So Nice

Thanks @MajP But it didn't take it all the way.
There has to be some connection to the artist table, so if I start to choose the artist for the track I will get the artists songs that's in the database and the other way around I have to be able to write in a new artist if it's not in the table. There is a form for that, developed by @arnelgp
When I tried your solution tonight I've noticed that when I choose a song that's already in the music (song) table, I couldn't update the field for the length of the song, then I get an error message. It works well when I put in a new song, but then the artist doesn't get in the artist table. The artist for the new songs that I put in become the same as the last name I wrote. And they all has the same number as the "Varierande artister".

I hope you understand me and can look at this to.
The solution with different row source depending on the choose of artist was nice.
I haven't looked at your search example yet, but I will.

Leif

 

[MajP]

Sorry I do not understand. If I try to enter song length for an existing record or new record I have no problem.

There has to be some connection to the artist table, so if I start to choose the artist for the track I will get the artists songs that's in the database and the other way around I have to be able to write in a new artist if it's not in the table.

I am not sure what you are saying. But I think this is how I would handle it. If I pick Various Artist I make a second combobox visible called Track artist. You then have to pick an artist for the track. This then will filter the rowsource of the subform combo to records from that artist. This will allow you to tagged a record as Various Artist, filter the subform to songs from a specific artist, and add a song against that specific artist. This would use the same trick as before of controlling the rowsource.

 

[lodmark]

Sorry I do not understand. If I try to enter song length for an existing record or new record I have no problem.
View attachment 85413


I am not sure what you are saying. But I think this is how I would handle it. If I pick Various Artist I make a second combobox visible called Track artist. You then have to pick an artist for the track. This then will filter the rowsource of the subform combo to records from that artist. This will allow you to tagged a record as Various Artist, filter the subform to songs from a specific artist, and add a song against that specific artist. This would use the same trick as before of controlling the rowsource.

Hm! I got an error message every time I tabbed to the next field in the row. And only if wrote in a new song.
I will look in to your suggestion. With my experience of databases that is how it should be done, but I'm not shure how. Maybe I will come back with more questions.
Thanks for now.
Leif

 

[MajP]

If I pick Various Artist I make a second combobox visible called Track artist. You then have to pick an artist for the track. This then will filter the rowsource of the subform combo to records from that artist. This will allow you to tagged a record as Various Artist, filter the subform to songs from a specific artist, and add a song against that specific artist. This would use the same trick as before of controlling the rowsource.

Does that make logical sense? If so I can demo this. It requires a few tricks because if you change the rowsource of the combo box in a continous form you will lose visibility of things that are not in the current rowsource. So you need a workaround.

The other way to do this is to have an artist combo in the subform. It would default to what is ever selected in the main form. However now you can now select the artist for each track which filters the available tracks in the track combo and will assigns the track artist. That may be more user friendly IMO. If it is not a compilation you do not change it, it just matches the main form.

 

[lodmark]

Does that make logical sense? If so I can demo this. It requires a few tricks because if you change the rowsource of the combo box in a continous form you will lose visibility of things that are not in the current rowsource. So you need a workaround.

The other way to do this is to have an artist combo in the subform. It would default to what is ever selected in the main form. However now you can now select the artist for each track which filters the available tracks in the track combo and will assigns the track artist. That may be more user friendly IMO. If it is not a compilation you do not change it, it just matches the main form.

I like the second way @MajP with a combo box for the artist. Can you do that for me?
Leif

 

[MajP]

I like the second way @MajP with a combo box for the artist. Can you do that for me?

I just thought about that, and it would not work. I would have to be an unbound control because its only purpose is to filter. Since it is unbound there would be no way to associate it with the current record and each instance would change. The track artist comes from the track table and all you are doing here is assigning it to a record.
I will think if there is a cleaner way to do this.

 

[lodmark]

I just thought about that, and it would not work. I would have to be an unbound control because its only purpose is to filter. Since it is unbound there would be no way to associate it with the current record and each instance would change. The track artist comes from the track table and all you are doing here is assigning it to a record.
I will think if there is a cleaner way to do this.

Thanks! I have a lot of compilation records....

 

[MajP]

This is the best I could come up with. I put a combo on the subform. It is initially set by the value of the main form combo. You can change this combo to change the track artist to filter the tracks.
I did not hide it when it is not a compilation. But you can choose only to show the combo if the the selected record is a compilation. I did not make this a FAYT but you can.

 

[lodmark]

This is the best I could come up with. I put a combo on the subform. It is initially set by the value of the main form combo. You can change this combo to change the track artist to filter the tracks.
I did not hide it when it is not a compilation. But you can choose only to show the combo if the the selected record is a compilation. I did not make this a FAYT but you can.
View attachment 85472

Thanks again @MajP. The weekend is occupied with other things than databases bat I try to take a closer look at it on Sunday.
Have a good weekend!
Leif

 

[lodmark]

Hi @MajP
The best is very good!
Now I have added, or tried to add two discs to the database using your form.
First, I have to say that I like my appearance on the subform better, but it's more of a formatting issue than coding. Though you have chosen a different way to add records than I had. I've not seen this before.
The first album "Saturday Night Fever" went well, except for a few points that I go through below, the second "Thank God It's Friday" I had problems with.
The problems came from the fact that I could not add individual artists and their songs. I have made a radio program about the first record, so there were both artists and songs already in the database, but not for the second.
Alternatively, I need to add the artists directly to the artist table and the songs in their table or the intended "new artist" or "new music" form will appear if the artist / song is missing in the table.
The tab order needs to be changed, but it's also cosmetics, but I wish it had been possible to tab to the next line.
A thought! Would it be possible to place the new combo box, which you have added, instead of the field at the end of the line where the artist is visible, and that you choose the artist for the line there?
If possible, the function could be changed in that field depending on the choice of artist ("Varierande artister") in the main form.
If not, I wish the combo box is hidden until you choose ("Varierande artister").

In my own thoughts here at home in Halmstad, I had a suggestion to add an intermediate table between artist and record to enable a many to many relationship between these two tables.
But you have probably already thought about that.
Some of the fields that would end up in that table are already in the record table, such as record_ID and record_artist_ID. Just a thought as well.

Despite all my remarks, I think you have succeeded admirably with the form, my desired function is there, albeit with a few additional twists.
I am deeply impressed! Nearly
I spent some time trying to understand the code you created both in my database and with the extension FAYT, which you are welcome to apply to the combo box.
But there are too few comments for me to understand the code.
I enclose a new file based on the one you sent last (Skivsamling 4) but with my two new discs. During the time you worked with my database, I added about 20 vinyl LPs to the database, but it's only a few nights' work, so I added them again.
Thanks again so far!
I hope I here from you soon.