Solved New thoughts on my vinyl record collection database

[lodmark]

I forgot one thing.
I'm still having trouble updating the fields that are blank. But it only happens before they are saved, if I return to the line later and add e.g. length for the song so it goes well. This applies to length, release date and music file.

I also need to be able to add new artists to the combo box you created.

Thanks!

 

[MajP]

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.

In order to make this work I had to switch to a continous form. This technique cannot be done in a datasheet. I am adding records the same way through the combobox. The textbox on top of the combobox is only there to continue to show the previous choices once you change the artist. A combobox cannot show anything previously selected if the rowsource no longer contains that value. This is a trick for doing cascading combox in a continuous form.

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.

Since I changed the name of the combobox the Not In List needed to get added back.

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.

I added a "Add Artist" button but you can add a not in list event just like you have on the main form.

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.

Order and cycle fixed.

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?

This you cannot do. Since the control has to be unbound, you cannot have an unbound control in a continuous form. If you change one unbound control all the controls will change. So it would look like they all are the same artist.

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").

I hid it.

 

[lodmark]

In order to make this work I had to switch to a continous form. This technique cannot be done in a datasheet. I am adding records the same way through the combobox. The textbox on top of the combobox is only there to continue to show the previous choices once you change the artist. A combobox cannot show anything previously selected if the rowsource no longer contains that value. This is a trick for doing cascading combox in a continuous form.



Since I changed the name of the combobox the Not In List needed to get added back.



I added a "Add Artist" button but you can add a not in list event just like you have on the main form.


Order and cycle fixed.


This you cannot do. Since the control has to be unbound, you cannot have an unbound control in a continuous form. If you change one unbound control all the controls will change. So it would look like they all are the same artist.


I hid it.

I can't wait untill I've looked at the file. At work know and in the eveneing I work at the radiostation, looks like I have to do some nightwork here.

 

[lodmark]

Hello @MajP
Now I've looked into it a bit. And it looks promising.
I have tried to add a song to the album Thank God It's Friday with an artist, Pattie Brooks, that wasn't in the list.
The artist went fine but when I've tried to add the song the form "frm_new_music" turns up, that's good but it 's looked to the artist 1053 (Varierande artister) and that's not working. The error is something like "...it's not in the list" (bad Swedish translation). May bee I need a button for that too?
Actually it would be nice if both frm_new artist and frm_new_music would work, in that case the same form is used every time i add a artist or a song (music) from inside a form.
Do you thing adding the "not in list event" solve the problem? As you suggested.
May bee a slice of code with the selection of "Varierande artister" that changes where the form "frm_new_music" fetches the artist?
That's how far I got with the testing.

Bye the way I understand what you said about what is possible to do and again I've appreciate your effort.

Goodnight!

Leif

 

[MajP]

Private Sub cboMusicRecord_NotInList(NewData As String, Response As Integer)
    If FormattedMsgBox(NewData & " finns inte i listan!" & vbNewLine & _
        "@Vill du lägga till den?@", vbQuestion + vbYesNo, "Music not found") = vbYes Then
        DoCmd.OpenForm FormName:="frm_new_music", WindowMode:=acDialog, _
            OpenArgs:=Me.Parent!record_ID & "|" & Me.cmboArtist & "|" & NewData
            Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
End Sub

In the above event procedure it was looking at the parent form to assign the artist. You need to change it to:
OpenArgs:=Me.Parent!record_ID & "|" & Me.cmboArtist & "|" & NewData

This way it references the subform combobox not the main form combobox

 

[lodmark]

Private Sub cboMusicRecord_NotInList(NewData As String, Response As Integer)
    If FormattedMsgBox(NewData & " finns inte i listan!" & vbNewLine & _
        "@Vill du lägga till den?@", vbQuestion + vbYesNo, "Music not found") = vbYes Then
        DoCmd.OpenForm FormName:="frm_new_music", WindowMode:=acDialog, _
            OpenArgs:=Me.Parent!record_ID & "|" & Me.cmboArtist & "|" & NewData
            Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
End Sub

In the above event procedure it was looking at the parent form to assign the artist. You need to change it to:
OpenArgs:=Me.Parent!record_ID & "|" & Me.cmboArtist & "|" & NewData

This way it references the subform combobox not the main form combobox

Thanks. Now I have something to do toninght.

 

[lodmark]

Hello @MajP

I've tried it and I'm so satisfied!
I even managed to copy some code so that your button for adding artist no longer is needed. Good by me, I think!
But then everything went wrong.
I've tried to sort the field music_record_musicorder in the subform so that the tracks get in the right order.
If you look in the record for Thank God It's Friday (search for "variera" and go forward one record) you see that i've entered the tracks in no order.
That was what I've tried to fix by adding a sort-code. But I've totally failed.
You've probably already seen what is wrong, but I have no idea what causing this.
The message form that shows is asking for a value and when I've close the message form the VB points at this code:

Private Sub SetSubFormArtist()
Dim id As Long
id = Nz(Me.Kombinationsruta25, 0)
Me.subForm.Form.cmboArtist = id
ShowHideArtistCombo
End Sub

I hope you can fix this and i promisse I wont touch it again.

The problems with adding length, date and file is still there, but only when I add a track. If I go back later I can add this.

I've attach the database again so you can see my problem.

Again I trust in your help.

Leif, tonight a broken man.

 

[Dreamweaver]

I am building the first of two music projects plus updating another project hope this will give you a few ideas

DJ 2020 Project Status

DJ 2020 Project Status

databasedreams.createaforum.com

 

[MajP]

I just sorted the query1 by the appropriate field, not code needed. On the after update I requery the form to show the updated order.

I noticed the problem with adding length. If I clicked out of the record I could then add the length. I know that when I leave the record that Access will commit the changes to the table. So the problem had to be that the form was still dirty and the song ID was not yet saved to the underlying table. So I added this

Private Sub cboMusicRecord_AfterUpdate()
Me.Dirty = False
End Sub

 

[lodmark]

I am building the first of two music projects plus updating another project hope this will give you a few ideas

DJ 2020 Project Status

DJ 2020 Project Status

databasedreams.createaforum.com

Thanks for the input, your view over the tables and relations looks a little bit too much for my project.

 

[lodmark]

I just sorted the query1 by the appropriate field, not code needed. On the after update I requery the form to show the updated order.

I noticed the problem with adding length. If I clicked out of the record I could then add the length. I know that when I leave the record that Access will commit the changes to the table. So the problem had to be that the form was still dirty and the song ID was not yet saved to the underlying table. So I added this

Private Sub cboMusicRecord_AfterUpdate()
Me.Dirty = False
End Sub

Thanks, again and again. I was probably too tired last night, I've looked so hard for a way to sort the subform that I missed the simplest one.
I've learned a lot but has still a lot too leran.

You fixed my error. What was the problem with the message box that I got?

If you like to listen too my programs it's on air tonight at 8 (swedish time) at this adress http://78.66.174.185:8000/stream.
Of course the program is in swedish but the music is mostly in english.

I am very grateful for the work you have done with my database so that I got the functionality I wanted. I thank you for that.

Leif

 

[MajP]

What was the problem with the message box that I got?

Many times when you get a weird Parameter pop up it is because you have some values you do not want in the properties
Filter
Order By

This can happen when you filter or sort when the form is not yet saved, then you save it. Those values will then persist.
You had in the filter: [Status] = "some value"
I do not remember what the value was.

 

[lodmark]

Hi @MajP
Now I have continued to add records to the database and it works great.
However, I have discovered two things that are a bit annoying.
One is that the tracks on the discs are sorted a little strangely if there are more than 9 tracks on one side.
As the picture shows.

Is it possible to do something about this without changing the table structure?

I do not have many discs that have more than nine tracks on each side but it does not look so nice.

The other problem is still the one with the tab function.
When I click tab on a track on a record that I added, it jumps to the next track when I come last on the line.
But when I add tracks, it always jumps to the first field on the top track when I want it to end up on the next track.
I do not understand why. Maybe it has something to do with that a "requery" is run every time the subform is updated?

If you have any thoughts about this I'll be happy if you let me know.

Leif

 

[MajP]

Since your tracks are strings A10 comes before A2,3...
You could change your table Side (A, B) and TrackNumber (1,2,3,4)

If you do not want to change the table then you you need to sort by the letter portion than by the number portion converted to a number. Something like this

SELECT
 tblTracks.track,
  Left([track],1) AS TrackSort1,
  CInt(Mid(Nz([Track],"A0"),2)) AS TrackSort2
FROM tblTracks
ORDER BY
 Left([track],1),
 CInt(Mid(Nz([Track],"A0"),2));

 

[Dreamweaver]

This is how I deal with discs and tracks

 

[lodmark]

Since your tracks are strings A10 comes before A2,3...
You could change your table Side (A, B) and TrackNumber (1,2,3,4)

If you do not want to change the table then you you need to sort by the letter portion than by the number portion converted to a number. Something like this

SELECT
tblTracks.track,
  Left([track],1) AS TrackSort1,
  CInt(Mid(Nz([Track],"A0"),2)) AS TrackSort2
FROM tblTracks
ORDER BY
Left([track],1),
CInt(Mid(Nz([Track],"A0"),2));

Fantastic, I managed to convert your code example into the Query1.
Thanks AGAIN @MajP

Any thoughts on the second question?

Leif

 

[lodmark]

Fantastic, I managed to convert your code example into the Query1.
Thanks AGAIN @MajP

Any thoughts on the second question?

Leif

I've deleted "Me.Requery" in the example from my database below. Now it jumps to the next track when i add new songs to a record.
@MajP But I wonder what this does that I've not yet discovered?

 

[MajP]

The purpose of the me.requery was in case you added a track out of order. Lets say you added tracks in order A1, A2, B1, A3 The requery would enforce the sort immmediately after entry. If not they will stay in the entered order until leaving the current main form record.

 

[lodmark]

The purpose of the me.requery was in case you added a track out of order. Lets say you added tracks in order A1, A2, B1, A3 The requery would enforce the sort immmediately after entry. If not they will stay in the entered order until leaving the current main form record.

Thanks for the clarification, I thought it did something like that. But with the new code in the query that sorts the items it will still be in the right order, but not at first. I think!

I have a few thoughts left that i will return with. In the mean time I will concentrate in adding records.

Leif

 

[lodmark]

I am building the first of two music projects plus updating another project hope this will give you a few ideas

DJ 2020 Project Status

DJ 2020 Project Status

databasedreams.createaforum.com

Hi @MickJav
When I started asking questions in this forum about how to create functionality in my database, I got a question about how I had intended to do with duets, ie songs with two artists. I then replied that I had intended to write the song with both artists.
It now turns out to be a bad idea to do so. In my form (frm_artist) which is about the artist, I want to be able to see both which records I have with the artist but also which songs I have used in my program, but where I do not have the record.
Songs that have two artists become a "new" artist in the artist table and then become a single entry in the form.
I have tried to figure out how you have done in your relationship structure @MickJav but have not succeeded so well.
How have you thought about this with duets or other songs that have several artists performing the songs?
I enclose a picture of my structure as it looks today.

I guess I need an intermediate table between Artist and song (music) somehow.

Leif