Listbox Selections from Recordset - RESOLVED

[Suidae]

Let me see if I can explain this without sounding like a total dolt.

I have a list box that is populated with five options. I select two of the five options and these selections are written back to the database. No problems here.

My question is if I want to edit this record, can I show the original five options with the two I’ve previously selected already selected / highlighted?

Clear as mud?

[Maldrid]

Yes you can. Just place the five options in the listbox. Then query your database for the appropriate records. Then you would use two for loops, the outer for loop you can use to loop through your recordset, and the inner to loop through your listbox. If any item in the listbox matchs the a field in the recordset then change the selected property for that item to equal true.

[Maldrid]

Here is an example, but obviously you would use your recordset to query a database instead of the way I did it and made it a disconnected recordset.

Dim rsList As New ADODB.Recordset
 
    'instead of appending fields you would do a query and open 
   'your recordset with the appropriate fields.  
    rsList.Fields.Append "Name", adBSTR
    
    rsList.CursorType = adOpenStatic
    rsList.LockType = adLockOptimistic
    rsList.Open
    
    'I am adding two records for test data, but you would
    'already have data from your query
    rsList.AddNew
    rsList.Fields("Name") = "Greg"
    rsList.AddNew
    rsList.Fields("Name") = "Joe"
    
   'now just loop through and highlight any items that are equal
    rsList.MoveFirst
    For i = 1 To rsList.RecordCount
        For n = 0 To List1.ListCount
            If List1.List(n) = rsList("Name") Then
                List1.Selected(n) = True
                Exit For
            End If
        Next
        rsList.MoveNext
    Next
    
    rsList.Close
    Set rsList = Nothing

[Suidae]

Many thanks Maldrid.. worked like a charm

[Maldrid]

Glad I could help