Microsoft Access - Using listbox to fill listbox

[bobeep]

HI this is my first post on this forum so bear with me. I have 2 tables "Albums" and "Songs". I am trying to create a form where I have 2 listboxes one for displaying albums and one for displaying songs. I want to be able to select an album from the "albums listbox" to bring up all the songs from that album in the "songs" listbox.

Im kinda confused with the logic aspect of it, and am completely lost on the coding part of it. Vb is confusing enough it seems to be even more confusing when you have tables and database objects in there as well.

Any help is much appreicated, thx.

 

[janebristol]

Make sure that each table has a keyfield (albumid, songid) and that you store the album keyfield in the songs table for a lookup. (My keyfields are both Text fields.)

Set up 2 listboxes – one with records from albums and one from records in songs (drag listboxes onto your form and follow the wizard “I want records to look up a value in a table or query”)

(My albums listbox is List0 and my songs table listbox is List2)

Type the following VBA code into the After Update event of your albums table listbox, so that whenever you click an album, the songs for that album appear in the 2nd listbox.

Private Sub List0_AfterUpdate()
List2.RowSource = "select * from tblsongs where albumid = '" & List0 & "'"
List2.Requery
End Sub

(If your keyfields are autonumbers, you would need to have different punctuation in the 2nd line of the sub)

List2.RowSource = "select * from tblsongs where albumid = " & List0

Hope this helps - I can't work out how to put screen shots in here to help explain!