Reselect Listbox entries from text string read

[txgeekgirl]

I have a gathering of items selected in a multi-select Listbox saved to a table as a comma seperated string. Can I use that string to re-select the listbox items, let's say, if the end user was re-opening to modify previous selections?

I have code to parse and read from comma to comma - what I need is how to reselect the list items.

Any help is appreciated!

 

[vbaInet]

It is possible but I would advise that you reverse the process. You should be saving each item as a single record. I can tell that you're trying to avoid creating a One-To-Many relationship but it's the way forward.

 

[txgeekgirl]

I don't understand what your saying:

Here are the values: Field 1 = "Egroup1, Egroup6, Egroup23"
Field 3 = "DataGroup2, Datagroup23, Datagroup37, Datagroup38"
Field 4 = "FolderGroup1, FolderGroup2, FolderGroup17"
Field 7 = "PrinterGroup1"

Egroup LB has 25 entries - those needing to be re-selected are 1, 6, & 23. So the loop goes through and parses the string and gives me a "Egroup1" then "Egroup6" then "Egroup23" - how do I make that translate into selected items on the form?

 

[vbaInet]

Field 1 should be linked to another table via an ID as follows:

ID    FieldValue
---------------
1     Egroup1
2     Egroup6
3     Egroup26

And not via a delimeted string like you have it. It should be relational, not flat.

I didn't say I don't understand your requirement, I'm just showing you how your data should be represented or else you will encounter problems in the future.

If you still want to go ahead with your idea:

1. Split() the string
2. Sort your split array
3. Ensure the sort is in the same order as what is in your list box
4. For each item in your list box ...
5. .... For each item in your sorted split array
6. Listbox.Selected(item) = True

Not quite as straightforward as you expected with your current data structure.

 

[txgeekgirl]

The listboxes are fed through multiple tables because the tables can and will change over time. The strings are necessary because they feed into a function, get parsed and used in another context.

I will try your sample. Thanks

 

[vbaInet]

Ok. In a properly normalized db the structure of the tables remain the same even with changing business needs. Most times fields are added to it (not added) or it may be necessary to join an ID to a new table. So I still think you should re-think your strategy.

If you were storing Numeric IDs, such as 3, 6, 1, then your task would have been slightly easier but not effecient.

Anyway, when it comes to sorting your array, you will find a function on the net because there's no sort method on arrays in VBA.

 

[pbaldy]

I adapted the method here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210203

I certainly agree with storing the data in a normalized way. I do this type of thing when I have a multiselect listbox and the users want to be able to resort the listbox without losing the items they've selected. Basically I grab the selections into a delimited string, resort the listbox, then reselect the items.

 

[txgeekgirl]

I actually did go back and reqork the tables. Added a field for Active (Yes/No) so that if the selection is no longer applicable the Item ID does not change it's place in the list.

Here is my code to re-select:

If Not IsNull(DLookup("[EmailGroups]", "[NewStaffRequests]", "[ID] = " & myrec)) Then
       myNeeds = DLookup("[EmailGroups]", "[NewStaffRequests]", "[ID] = " & myrec)
 
        Do While Len(myNeeds) > 0
            'End of list - can't have a string going from 1 to 0
            If InStr(myNeeds, ",") <> 0 Then
                index = InStr(myNeeds, ",")
            Else
                index = 50
            End If
 
            sEachGroup = Mid(myNeeds, 1, index - 1)
            'MsgBox (sEachGroup)
 
            Item = DLookup("[ID]", "[EmailGroups]", "[EmailGroups] = '" & sEachGroup & "'")
            If Not IsNull(Item) Then
                Me.lbEmailGroups.Selected(Item - 1) = True
            End If
            myNeeds = Mid(myNeeds, index + 1)
        Loop
    End If