Query about MultiSelectAppend (1 Viewer)

Irish lad

Registered User.
Local time
Today, 20:31
Joined
Jun 19, 2018
Messages
21
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!

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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:31
Joined
Oct 29, 2018
Messages
21,358
Hi. It's probably easier to troubleshoot this if we could run the code to step through it. Are you able to share a copy of your db with test data?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:31
Joined
Aug 30, 2003
Messages
36,118
Maybe only do the append if both combos are selected:

Code:
If Len(Me.cboRoleTrack2 & vbNullString) > 0 AND Len(Me.cboMusicianTrack2 & vbNullString) >0 Then
  rs.AddNew
  ...
End If
 

Irish lad

Registered User.
Local time
Today, 20:31
Joined
Jun 19, 2018
Messages
21
Paul (pbaldy),

That worked a treat, thanks so much for replying and for your website.

All the advice on these forums has been so helpful.

Going out on a limb here: as noted, these are combo boxes (Musician/Role) using a multi-select Listbox for data entry. The form is set up for 10 dual combo boxes and it works perfectly thanks to the above solution, even if some combo boxes are empty.

However, is it possible to code VBA such that - upon opening the form - it asks how many combo boxes you want (e.g. not 10 but 20/5/43 etc?) and the code can take account of that (blank fields would not matter here since if you ask for x combo boxes then fill out x combo boxes). The above is a "nice to have" so please feel free to ignore.

To repeat, this forum has been so helpful thus far, thanks again.

Irish lad
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:31
Joined
Aug 30, 2003
Messages
36,118
Happy to help and glad my site was helpful.

Typically in a situation where there could be many records you'd use a continuous form or subform where the user could fill out as many or few records as desired. In the situation you're describing you'd have to allow for a maximum, have all the combo's on the form, and just show/hide as appropriate to the user's input. It's certainly doable, but kind of clunky. You'd still face the issue of the user saying "10" when they started and then they realized they only had 9 so left one blank (or they have 11 and nowhere to enter the last).
 

Users who are viewing this thread

Top Bottom