Multi-Select Listbox: Loop through, ignoring duplicate items

[pcb]

Hi All,

I have a multi-select listbox, that can have duplicate items (i.e
samples numbers that are analyzed in duplicate - the sample IDs are not
the same, but these IDs are hidden from the user).

I want to loop through the items on the listbox, but only use unique
sample numbers. Here is what I have:
************************************************** ***********************
Dim varItem As Variant
Dim frm As Form, ctl As Control
Dim strSampleList As String

Set frm = Forms!frmUpdateSamples
Set ctl = frm!lboSamples

For Each varItem In ctl.ItemsSelected
If ** ??? not duplicate ??? ** Then
strSampleList = strSampleList & ctl.Column(1, varItem) & vbCrLf
End If
Next varItem

************************************************** ***********************

Any help would be great. Thanks!
Peter

[Rich P]

Hi Peter,

My solution isn't super pretty, but it works. I am using a temp table
as follows:

Sub GetUniqueVals()
Dim varItem As Variant
Dim strSampleList As String
Dim RS As DAO.Recordset, obj As AccessObject

DoCmd.SetWarnings False
For Each obj In CurrentData.AllTables
If obj.Name = "Temp1" Then
DoCmd.RunSQL "Drop Table Temp1" '--delete table first
Exit For
End If
Next
DoCmd.RunSQL "Create Table Temp1 (fld1 Text(20))"
For Each varItem In List0.ItemsSelected
DoCmd.RunSQL "Insert Into Temp1(fld1) Values('" & List0.Column(1,
varItem) & "')"
Next

Set RS = CurrentDb.OpenRecordset("SELECT fld1 FROM Temp1 GROUP BY fld1")
Do While Not RS.EOF
strSampleTest = strSampleTest & RS(0) & ", "
RS.MoveNext
Loop
DoCmd.SetWarnings True
Debug.print strSampleTest
End Sub

HTH

Rich

*** Sent via Developersdex http://www.developersdex.com ***