Highlighting List Box Values

[bwo3333]

Could really use some help on something I've been stuck on for awhile now. Found a few threads but nothing which solved my issue. I have a list box (LOB_Multiselect) on a form. The list box calls and retrieves 20 values. I'm able to highlight and save the values to a field called LOB on the the table bound to the same form. I save each selected value with a comma between selections (ABC, MNO, etc) into this single LOB field. What I can't seem to do is open the same form with the list box highlighting each of the values previously choosen.

The code seems to fail at the msgbox "here" statement below. I don't get a value for varcurrentrow.

Is this the best way to get these cells highlighted? Is there another way? If not, any idea as to what is not correct?

Dim ctlSource As Control
Dim varCurrentRow As Variant
Set ctlSource = Me.LOB_Multiselect
stringdelimeter = ","
temp = Len(LOBvalue)
strtemp = ""
For M = 1 To temp
holdchar = Mid(LOBvalue, M, 1)
If (M = temp Or holdchar = ",") Then
' work with string

MsgBox "Here3" & varCurrentRow & "end"
For Each varCurrentRow In ctlSource.ItemsSelected

If Me.LOB_Multiselect.Selected(varCurrentRow) = strtemp Then
ctlSource.Selected(varCurrentRow) = True
End If
Next varCurrentRow
strtemp = ""
Else
strtemp = strtemp & holdchar
End If
Next M

 

[pbaldy]

See if this helps:

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

also noting the caution against storing data that way.

 

[bwo3333]

Thanks for the quick answer and the correct answer! This solved the issue. Thanks for the note of caution. Unfortunately setting up the purely normalized manner will cause extremely cumbersome maintenance of the data and is not practical. Maintenace goes from a few thousand records to a 100 K+ records quickly.

 

[pbaldy]

No problem, and welcome to the site!