Selecting items in multi-select list box

[chronister]

Hello,



I have a list box that I have populated via VBA. As it is populating, I am also running another query to determine if this item is in another table. If so, then I want to select / highlight it. If not, move on.

Here is the loop and all works fine except the volunteerInterests.Selected(i) line. What am I missing?

Do While Not rs.EOF
            thisInterestID = rs.Fields("interestID").Value
            whereClause = "volunteerID = " & ID & " AND interestID = " & thisInterestID
            volunteerInterests.AddItem thisInterestID & ";" & rs.Fields("interestName").Value, i
            
         If Not IsNull(DLookup("ID", "tblVolunteerInterests", whereClause)) Then

           volunteerInterests.Selected(i) = True  ' THIS LINE IS NOT WORKING TO HIGHLIGHT THE JUST ADDED ITEM

         End If
      rs.MoveNext
      i = i + 1
   Loop

Thanks

[chronister]

Hello everyone,

Hope all are doing well!

Truly what I want to do is this, but can't find a good way to make it happen .....

I have a table called tblVolunteerInterestAreas that holds areas of volunteering interest ( [interestID] [interestName] [interestDescription] )

I have a table of volunteers with the PK called ID and a table called tblVolunteerInterests that holds volunteerID and interestID

On my Volunteers information form, I want a checkbox for each of the tblVolunteerInterestAreas records, and if the current volunteer [ID] and the interestID is in my joining table then the checkbox is checked.

I have tried to dynamically create checkboxes, but got errors about having to be in design mode.

The only thing I can think of is to create a from from scratch dynamically via VBA and populate it with what I want to display to the user and they make their choices, hit update and the form gets destroyed.

I have the basic structure above with looping through the records of the interestsAreas table and running a dlookup to determine if the volunteer and the current interest and volunteer exist for the checked / unchecked logic above.
Any help / tips / guidance on this task would be super helpful.

Thanks,

Nate

[ssanfu]

The only thing I can think of is to create a from from scratch dynamically via VBA and populate it with what I want to display to the user and they make their choices, hit update and the form gets destroyed.

This will entail *lots* of code and cause massive database bloat (even if you can do it).

The problem with using check boxes is that the interests can change. Interests can be added, changed or deleted. This would mean you would have to edit the forms if you had check boxes on them.

Have you thought about using a form/sub-form arrangement?

It would be a lot to explain (sub form & list box) so I created an example dB.

[chronister]

Thank you for your time to reply.

I have been working on it and I have a solution. Since the main list of possible interest areas must be flexible as they add or remove areas to volunteer in, I have my tables setup like I said. Interest Areas Table | Volunteer Interest Table | Volunteer Table

I have a button that triggers my code which goes to the database, grabs the items in the Interest Areas table, loops through them. Opens a blank (save for 2 buttons) form and generates the checkboxes. It can hold up to about 20 with the size window I have now. As it creates these, it runs a dlookup to see if the volunteerID (passed and stored in a generated hidden text field) has an entry in the middle table that joins the 2. If so, it checks it, if not, it leaves it blank.

The update process is not too bad either. Since the checks are dynamically generated I named them check_X where X is the number of the interest ID. When the update button is clicked, it deletes all entries from the middle table that exist for this user, and then adds the new ones back in.

It is really not a bad process. 1 form, and a couple of subs. In case anyone is looking to do this kind of thing, here is the code I used. If anyone can show me improvements in the coding, then please do.

Click button that says Edit Interests -> the misspelled click procedure runs.

Private Sub viewIntersts_Click()
    DoCmd.OpenForm "frmInterestsSubform", acDesign
        createInterestControls (ID)
    DoCmd.OpenForm "frmInterestsSubform", acNormal
    
End Sub

Private Function createInterestControls(volunteerID)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sqlStr As String
    Set db = CurrentDb
    Dim i As Long
    Dim col As Long
    
     Set rs = db.OpenRecordset("SELECT tblVolunteerInterestAreas.interestID, tblVolunteerInterestAreas.interestName, tblVolunteerInterestAreas.interestDescription FROM tblVolunteerInterestAreas ORDER BY tblVolunteerInterestAreas.[interestName]")
    If (rs.RecordCount <> 0) Then
        
        i = 1
        Dim ctrl_i As Control
        col = 1
        x = 0
        y = 0
        leftStart = 200
        topStart = 200
        
        leftOffset = 1300
        topOffset = 300
        
        Set volunteerIDField = CreateControl("frmInterestsSubform", acTextBox, acDetail, , , 5000, 50, 500, 300)
            volunteerIDField.Name = "volunteerID"
            volunteerIDField.DefaultValue = volunteerID
            volunteerIDField.Visible = False
        
        Do While Not rs.EOF
            thisInterestID = rs.Fields("interestID").Value
            thisInterestName = rs.Fields("interestName").Value
            
            ' start a new column
            If i > (col * 10) Then
                col = col + 1
                x = 0
                y = y + 1
            End If
            
            Set ctrl_i = CreateControl("frmInterestsSubform", acCheckBox, acDetail, , , leftStart + ((leftOffset * y) * col), topStart + (topOffset * x), 175, 175)
            ctrl_i.Name = "check_" & thisInterestID
            Set label_i = CreateControl("frmInterestsSubform", acLabel, acDetail, "check_" & thisInterestID, , leftStart + ((leftOffset * y) * col) + 200, topStart + (topOffset * x) - 50, 2500, 235)
            
            label_i.Caption = thisInterestName
            label_i.FontWeight = 700
                       
         If Not IsNull(DLookup("ID", "tblVolunteerInterests", "volunteerID = " & ID & " AND interestID = " & thisInterestID)) Then
            ctrl_i.DefaultValue = True
        Else
            ctrl_i.DefaultValue = False
         End If
      rs.MoveNext
      i = i + 1
      x = x + 1
      
   Loop
End If

End Function

Which creates this form.....



Click the update button and then to process.....

Private Sub updateInterests_Click()
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sqlStr As String
    Set db = CurrentDb
    Dim theFieldName As String
      
      DoCmd.SetWarnings False
      DoCmd.RunSQL "DELETE FROM tblVolunteerInterests WHERE volunteerID = " & volunteerID
      DoCmd.SetWarnings True
     
    Set rs = db.OpenRecordset("SELECT tblVolunteerInterestAreas.interestID, tblVolunteerInterestAreas.interestName, tblVolunteerInterestAreas.interestDescription FROM tblVolunteerInterestAreas ORDER BY tblVolunteerInterestAreas.[interestName]")
    If (rs.RecordCount <> 0) Then
    Do While Not rs.EOF
            ID = rs.Fields("interestID").Value
        If Me.Controls("check_" & ID) Then
          DoCmd.SetWarnings False
        DoCmd.RunSQL "INSERT INTO tblVolunteerInterests (volunteerID, interestID) Values (" & volunteerID & ", " & ID & "); "
        DoCmd.SetWarnings True
        End If
    rs.MoveNext
   Loop
    
    End If
   Forms!Volunteers!volInterestsListbox.Requery
    DoCmd.Close , , acSaveNo
End Sub

Likely very sloppy, but it works and does exactly what I want. A dynamic check box list that allows users to choose from them.

Thanks,

nate