All,
I'm (still) messing about with my music database and using the MultiSelectAppend code as part of it:
My code is modified to the database and worked fine with two unbound combo boxes (one for MusicianID, one for RoleID) and the listbox as described (for TrackID). In essence, I choose a Musician (combo), a Role (combo) and write it to Tracks (multi select listbox).
However, I am now looking to modify the code to allow multiple combo boxes to be written to the listbox selection as with the code below (so several Muscians/Roles at the same time to multiple tracks, have used 4 in the example). It works fine but if, say, one of the set of combo boxes is empty I get an error message about empty fields. It still writes fine but is there any way of preventing this. Ideally, I would like to have, say, 10 Musician/Role combo boxes and only write the ones that are filled in to the selected Tracks from the listbox. Even better would be code that allows the number of Musician/Role combo boxes to be selected on opening the form but suspect that is way beyond me. Any ideas appreciated as always!
I'm (still) messing about with my music database and using the MultiSelectAppend code as part of it:
Multi-Select Listbox
www.baldyweb.com
My code is modified to the database and worked fine with two unbound combo boxes (one for MusicianID, one for RoleID) and the listbox as described (for TrackID). In essence, I choose a Musician (combo), a Role (combo) and write it to Tracks (multi select listbox).
However, I am now looking to modify the code to allow multiple combo boxes to be written to the listbox selection as with the code below (so several Muscians/Roles at the same time to multiple tracks, have used 4 in the example). It works fine but if, say, one of the set of combo boxes is empty I get an error message about empty fields. It still writes fine but is there any way of preventing this. Ideally, I would like to have, say, 10 Musician/Role combo boxes and only write the ones that are filled in to the selected Tracks from the listbox. Even better would be code that allows the number of Musician/Role combo boxes to be selected on opening the form but suspect that is way beyond me. Any ideas appreciated as always!
Code:
Private Sub cmdEnterMusicians_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
On Error GoTo ErrorHandler
Set db = CurrentDb()
Set rs = db.OpenRecordset("jtblTrackRoles", dbOpenDynaset, dbAppendOnly)
'make sure a selection has been made
If Me.SelectTracks.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 Track"
Exit Sub
End If
'add selected value(s) to table
Set ctl = Me.SelectTracks
For Each varItem In ctl.ItemsSelected
rs.AddNew
rs!TrackID = ctl.ItemData(varItem)
rs!RoleID = Me.cboRoleTrack1.Value
rs!MusicianID = Me.cboMusicianTrack1.Value
rs.Update
rs.AddNew
rs!TrackID = ctl.ItemData(varItem)
rs!RoleID = Me.cboRoleTrack2.Value
rs!MusicianID = Me.cboMusicianTrack2.Value
rs.Update
rs.AddNew
rs!TrackID = ctl.ItemData(varItem)
rs!RoleID = Me.cboRoleTrack3.Value
rs!MusicianID = Me.cboMusicianTrack3.Value
rs.Update
rs.AddNew
rs!TrackID = ctl.ItemData(varItem)
rs!RoleID = Me.cboRoleTrack4.Value
rs!MusicianID = Me.cboMusicianTrack4.Value
rs.Update
Next varItem
ExitHandler:
Set rs = Nothing
Set db = Nothing
Exit Sub
ErrorHandler:
Select Case Err
Case Else
MsgBox Err.Description
DoCmd.Hourglass False
Resume ExitHandler
End Select
End Sub