Use one multi-select listbox to insert data using a second listbox

[Irish lad]

All,

I've previously posted about my music database which is working well and proving very helpful (no doubt to the fantastic help on this forum). However, I want to see if it is possible to do something for data entry purposes.

I have a junction table where Musicians (MusicianID) and Roles (RoleID) are combined with Tracks (TrackID) (i.e. who plays what on which track). Let's call this TrackRoles information. However, sometimes I add a CD that is already on the database but might, for example, have a different mastering. In that situation, for data entry it makes sense to copy over the existing information. At present, I use combo boxes to copy the TrackRoles information for each Track individually (select the Source Album/Track and Insert Into the junction table for the Destination Album/Track). So if there are 12 tracks I do this 12 times, once for each Track.

So, to the question. Is it possible to use two Multi-Select listboxes? The first would show the 12 TrackIDs on the Source Album and the second the 12 TrackIDs for the Destination Album (the albums selected using combo boxes). I would then want to press a button that would run code and insert into the junction table the MusicianID/RoleID information from the Source TrackIDs into the Destination TrackIDs. In essence, it would map the information from the first entry in the first listbox to the first in the second listbox, the second to the second and so on. Obviously the number of selections in each listbox would need to be the same.

I'm not sure if this is actually possible but thought it was worth checking anyway. Any ideas/help much appreciated.

 

[theDBguy]

Hi. This is not exactly answering your question, but I thought you might take a look at this article, just in case it's something you might want to try.

Microsoft Access tips: Duplicate the record in form and subform

How to duplicate the current record on a form in a Microsoft Access database, along with the related records in the subform.

allenbrowne.com

 

[Pat Hartman]

The simplest solution is to copy all the tracks and append them using an append query. Then delete the ones you don't want. Other solutions require VBA.

 

[MajP]

See demo.
Code

Private Sub cmboAlbum1_AfterUpdate()
  Dim strSql As String
  If Not IsNull(Me.cmboAlbum1) Then
    strSql = "SELECT TrackID, TrackName FROM tblAlbum INNER JOIN tblTracks ON tblAlbum.AlbumID = tblTracks.AlbumID "
    strSql = strSql & " WHERE tblAlbum.albumID = " & Me.cmboAlbum1 & " ORDER BY tblTracks.TrackName"
    Debug.Print strSql
    Me.lstAlbum1.RowSource = strSql
    Me.lstAlbum1.Requery
  End If
End Sub

Private Sub cmboAlbum2_AfterUpdate()
  Dim strSql As String
  If Not IsNull(Me.cmboAlbum2) Then
    strSql = "SELECT TrackID, TrackName FROM tblAlbum INNER JOIN tblTracks ON tblAlbum.AlbumID = tblTracks.AlbumID "
    strSql = strSql & " WHERE tblAlbum.albumID = " & Me.cmboAlbum2 & " ORDER BY tblTracks.TrackName"
    Debug.Print strSql
    Me.lstAlbum2.RowSource = strSql
    Me.lstAlbum2.Requery
  End If
End Sub

Private Sub cmdMap_Click()
  Dim ID1 As Long
  Dim ID2 As Long
  Dim Track1 As String
  Dim Track2 As String
  Dim i As Long
  Dim j As Long
  Me.txtMap = ""
  If Not IsNull(Me.cmboAlbum1) And Not IsNull(Me.cmboAlbum2) And Me.cmboAlbum1 <> Me.cmboAlbum2 Then
   For i = 0 To Me.lstAlbum1.ListCount - 1
    Track1 = Me.lstAlbum1.Column(1, i)
    ID1 = Me.lstAlbum1.Column(0, i)
    For j = 0 To Me.lstAlbum2.ListCount - 1
       Track2 = Me.lstAlbum2.Column(1, j)
       ID2 = Me.lstAlbum2.Column(0, j)
       If Track1 = Track2 Then
        Me.txtMap = Me.txtMap & vbCrLf & Track1
        MapPerformers ID1, ID2
       End If
    Next j
  Next i
  Else
    MsgBox "Map valid albums"
  End If
End Sub

Public Sub MapPerformers(trackID1 As Long, trackID2 As Long)
  Dim strSql As String
  strSql = "INSERT INTO jTblPlayedOn ( PerformerID, InstrumentID, TrackID ) SELECT jTblPlayedOn.PerformerID, jTblPlayedOn.InstrumentID, " & trackID2 & "  AS NewTrackID "
  strSql = strSql & "FROM jTblPlayedOn WHERE jTblPlayedOn.TrackID = " & trackID1
  Debug.Print strSql
  CurrentDb.Execute strSql
End Sub

For demo pick Form1
Pick selections below. It will map any tracks that match. A greatest hits album may have matches from many albums. In this case Ramones Manis is the greatest hits and no performers added.

 

[Irish lad]

Thanks all for your replies and special thanks to MajP for the code (in fact, thanks again as you were helpful with my questions on performers before). The generosity on here is very much appreciated.